SQL SERVER compression statement

The statement for compressing a database is as follows:

 

CREATE Procedure [dbo]. [sp_Compress]
AS

BEGIN
DECLARE @ db_name varchar (200)
Set @ db_name = 'dangjiae '-- Database Name
Dump transaction @ db_name WITH NO_LOG
Backup log @ db_name WITH NO_LOG
Dbcc shrinkdatabase (@ db_name)

END

 

 

 

SQL server 2005 compresses all database and log file statements in an instance

 

-- 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 = 0 and -- 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 by 1
-- 02.use cursor to fetch db name
----- 02.01 declare cursor
DECLARE cur CURSOR
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
-------------------------------
The SQL and logs on the test server are often too large. Write a stored procedure and run the job periodically to avoid disk space waste.