How to compress logs on SQL Server and reduce the size of SQL Server files

Generally, steps 4 and 6 are not recommended. Steps 4 are not secure and may damage the database or cause data loss. If the log reaches the upper limit in step 1, subsequent database processing will fail and the log can be restored after being cleared.

　　1. Clear logs

Dump transaction database name WITH NO_LOG

　　2. truncate transaction logs

Backup log database name WITH NO_LOG

　　3. Shrink database files

(Without compression, the database file will not be reduced by the Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract the database -- contract the file

Select log file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to shrink 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 shrink is displayed. Enter this number directly and click OK, you can also use SQL statements to complete

　　Shrink Database

Dbcc shrinkdatabase (customer profile)

Shrink the specified data file. 1 indicates the file number. You can use this statement to query:

Select * from sysfiles

Dbcc shrinkfile (1) 4. To maximize log file size (for SQL 7.0, this step can only be performed in the query analyzer)

　　Separate database:

Enterprise Manager -- server -- database -- Right-click -- detach Database

Delete LOG files in my computer

　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.

　　Separation

EXEC sp_detach_db @ dbname = 'pubs'

　　Delete log files

　　Attach

EXEC sp_attach_single_file_db @ dbname = 'pubs ',
@ Physname = 'C: \ Program Files \ Microsoft
SQL Server \ MSSQL \ Data \ pubs. mdf'

　　4. Make the following settings to automatically contract later

Enterprise Manager -- server -- Right-click Database -- Property -- option -- select "auto contract"

SQL statement setting method:

EXEC sp_dboption 'database name ',
'Autoshrink', 'true'

　　5. 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 size allowed by you in SQL Server)

SQL statement settings:

Alter database name modify file (name = logical file name, maxsize = 20)

Here, I will share with you some of my frequently used methods:

DUMP TRANSACTION [jb51] WITH NO_LOGBACKUP LOG [jb51] WITH NO_LOGDBCC SHRINKDATABASE([jb51])

Jb51 is the database name.

The above content is an introduction to SQL Server's compressed logs and database file size. I hope you will find some gains.