Compressing database logs

From http://topic.csdn.net/t/20040406/13/2931507.html

-- Compressed log and database file size

/* -- Pay special attention
Follow these steps. Do not follow these steps.
Otherwise, your database may be damaged.
--*/

1. Clear logs
Dump transaction database name with no_log

2. truncate transaction logs:
Backup log database name with no_log

3. Compress database files (if not compressed, the database files will not be reduced
Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file
-- Select log file -- select to shrink to xxm in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.
-- Select data file -- select to shrink to xxm in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.

You can also use SQL statements to complete
-- Shrink Database
DBCC shrinkdatabase (customer profile)

-- Contract the specified data file. 1 is the file number. You can use this statement to query: Select * From sysfiles
DBCC shrinkfile (1)

4. To minimize log files (for SQL 7.0, this step can only be performed in the query analyzer)
A. Separate the database:
Enterprise Manager -- server -- database -- Right-click -- detach Database

B. Delete log files in my computer

C. Additional database:
Enterprise Manager -- server -- database -- Right-click -- attach Database

This method generates a new log with a size of more than 500 K.

Or use the code:
The following example separates pubs and attaches a file in pubs to the current server.

A. Separation
Exec sp_detach_db @ dbname = 'pubs'

B. Delete log files

C. Attach
Exec sp_attach_single_file_db @ dbname = 'pubs ',
@ Physname = 'C: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ pubs. MDF'

5. In order to automatically contract in the future, make the following settings:
Enterprise Manager -- server -- Right-click Database -- Property -- option -- select "auto contract"

-- SQL statement setting method:
Exec sp_dboption 'database name', 'autowrite', 'true'

6. If you want to prevent the log from increasing too much in the future
Enterprise Manager -- server -- Right-click Database -- properties -- transaction log
-- Limit file growth to xm (X is the maximum data file size you allow)

-- SQL statement settings:
Alter database name Modify file (name = logical file name, maxsize = 20)

 

-- The following is the stored procedure of compression processing. You can call this stored procedure regularly to compress logs.

Use master -- note that this stored procedure should be created in the master database
Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_compdb] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_compdb]
Go

/* -- Compress the General stored procedure of the database

Compressed log and database file size
Because the database needs to be separated
Therefore, stored procedures cannot be created in a compressed database.

-- Producer build 2004.3 --*/

/* -- Call example
Exec p_compdb 'test'
--*/
Create proc p_compdb
@ Dbname sysname, -- Name of the database to be compressed
@ Bkdatabase bit = 1, -- because the log separation step may damage the database, you can choose whether to automatically Database
@ Bkfname nvarchar (260) = ''-- Name of the backup file. If this parameter is not specified, the backup file is automatically backed up to the default backup directory. The backup file name is: database name + date and time.
As
If @ bkdatabase = 1 -- determine whether to back up the database
Begin
If isnull (@ bkfname, '') =''
Set @ bkfname = @ dbname + '_' + convert (varchar, getdate (), 112)
+ Replace (convert (varchar, getdate (), 108 ),':','')
Select prompt = 'Back Up the database to the default SQL Backup Directory. Backup File Name: '+ @ bkfname
Exec ('backup database ['+ @ dbname +'] to disk = ''' + @ bkfname + '''')
End

-- 1. Clear logs
Exec ('dump transaction ['+ @ dbname +'] With no_log ')

-- 2. truncate transaction logs:
Exec ('backup log ['+ @ dbname +'] With no_log ')

-- 3. compress the database file (if it is not compressed, the database file will not be reduced
Exec ('dbcc shrinkdatabase (['+ @ dbname +']) ')

-- 4. Set automatic contraction
Exec ('exec sp_dboption ''' + @ dbname + ''', ''autoshrink '', ''true ''')

/* -- The subsequent steps are dangerous. If you cannot determine the danger, do not enable them.
-- 5. Database Separation

-- Separate
Create Table # T (fname nvarchiar (260), type INT)
Exec ('insert into # T select filename, type = Status & 0x40 from ['+ @ dbname +'] .. sysfiles ')
Exec ('SP _ detach_db ''' + @ dbname + '''')

-- Delete a log file
Declare @ fname nvarchar (260), @ s varchar (8000)
Declare TB cursor local for select fname from # t where type = 64
Open TB
Fetch next from TB into @ fname
While @ fetch_status = 0
Begin
Set @ s = 'del "'+ rtrim (@ fname) + '"'
Exec master .. xp_mongoshell @ s, no_output
Fetch next from TB into @ fname
End
Close TB
Deallocate TB

-- Attach a database
Set @ s =''
Declare TB cursor local for select fname from # t where type = 0
Open TB
Fetch next from TB into @ fname
While @ fetch_status = 0
Begin
Set @ s = @ s + ', ''' + rtrim (@ fname) + ''''
Fetch next from TB into @ fname
End
Close TB
Deallocate TB
Exec ('SP _ attach_single_file_db ''' + @ dbname + ''' + @ s)
--*/
Go