sqlserver收缩日志收缩数据库日志-20200305

 

--sqlserver收缩日志收缩数据库日志
--查询出数据库对应的日志文件名称
DECLARE @strDBName AS NVARCHAR(500)
DECLARE @strLogName AS NVARCHAR(500)
DECLARE @strSQL AS NVARCHAR(1000)
SELECT  @strLogName = B.name, @strDBName = A.name
FROM    master.sys.databases AS A
INNER JOIN sys.master_files AS B ON A.database_id = B.database_id
WHERE   A.database_id = DB_ID('数据库名称')
 --DB_ID()

SET @strSQL = '
use [' + @strDBName + ']
--ALTER DATABASE [' + @strDBName + ']  SET RECOVERY SIMPLE WITH NO_WAIT;
ALTER DATABASE [' + @strDBName + '] SET RECOVERY SIMPLE;
DBCC SHRINKFILE (''' + @strLogName + ''' , 5 );
--ALTER DATABASE [' + @strDBName + ']  SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE [' + @strDBName + '] SET RECOVERY FULL '
EXEC(@strSQL)