SQLSERVER压缩语句

SQLSERVER压缩语句
SQLSERVER压缩语句压缩⼀条数据库语句如下：
CREATE Procedure [dbo].[sp_Compress]
AS
BEGIN
DECLARE @db_name varchar(200)
set @db_name='dangjiae' --数据库名
DUMP TRANSACTION @db_name WITH NO_LOG
BACKUP LOG @db_name WITH NO_LOG
DBCC SHRINKDATABASE(@db_name)
END
--01.get all database name
SELECT DBNAME =DB_NAME(s_mf.database_id) INTO #DN FROM
sys.master_files s_mf
WHERE
s_mf.state =0and-- ONLINE
HAS_DBACCESS(DB_NAME(s_mf.database_id)) =1-- Only look at databases to which we have access GROUP BY s_mf.database_id
ORDER BY1
e cursor to fetch db name
-----02.01 declare cursor
DECLARE cur CURSOR FOR
SELECT DBNAME FROM #DN
-----02.02 open cursor & declare var
OPEN cur
DECLARE@DbName VARCHAR(200)
-----02.03 loop db bane
FETCH NEXT FROM cur into@DbName
WHILE(@@FETCH_STATUS=0)
BEGIN
print'shrinking '+@DbName+' ...'
-------02.04 only shink log file
exec(
'
declare @dn varchar(200);
declare @ln varchar(210);
select @dn = name from '+@DbName+'.dbo.sysfiles where fileid=1
select @ln = name from '+@DbName+'.dbo.sysfiles where fileid=2
use ['+@DbName+'] backup log ['+@DbName+'] with no_log dbcc shrinkfile (@ln)
'
)
-------02.04 shink db file & log file
-- dbcc SHRINKDATABASE (@DbName)
print@DbName+' done'
FETCH NEXT FROM cur INTO@DbName
END
-----02.05 colse cursor
CLOSE cur
DEALLOCATE cur
-----03.drop temp table
DROP TABLE #DN
-------------------------------
测试服务器的sql和⼀志经常就太⼀了，写⼀个存储过程，然后job定期执⼀，避免磁盘空间浪费。