Detailed description of how to shrink MSSQL database logs

There are two steps:

Step 1. Truncate the log content in the database

The code is as follows:	Copy code
Backup log database name WITH TRUNCATE_ONLY

Step 2 force the database to compress its size

The code is as follows:	Copy code
Dbcc shrinkdatabase (database name, TRUNCATEONLY)

These two steps need to be executed using the query analyzer. For more information, see
The assist file of the Transact-SQL statement.

Another way is to choose database Properties> options in the MSSQL Enterprise Manager, set the fault recovery> Model to simple, and then confirm, which can also directly reduce the volume of log files.

Shrink mssql database logs using stored procedures

Method 2

The code is as follows:

Copy code

SET NOCOUNT ON DECLARE @ LogicalFileName sysname, @ MaxMinutes INT, @ NewSize INT USE tablename -- Name of the database to be operated SELECT @ LogicalFileName = 'tablename _ log', -- log file name @ MaxMinutes = 10, -- Limit on time allowed to wrap log. @ NewSize = 1 -- the size of the log file you want to set (M) -- Setup/initialize DECLARE @ OriginalSize int SELECT @ OriginalSize = size FROM sysfiles WHERE name = @ LogicalFileName SELECT 'original Size of 'db_name () 'log is' CONVERT (VARCHAR (30), @ OriginalSize) '8 K pages or' CONVERT (VARCHAR (30), (@ OriginalSize * 8/1024) 'mb' FROM sysfiles WHERE name = @ LogicalFileName Create table DummyTrans (DummyColumn char (8000) not null) DECLARE @ Counter INT, @ StartTime DATETIME, @ TruncLog VARCHAR (255) SELECT @ StartTime = GETDATE (), @ TruncLog = 'backup log' db_name () 'WITH TRUNCATE_ONLY' Dbcc shrinkfile (@ LogicalFileName, @ NewSize) EXEC (@ TruncLog) -- Wrap the log if necessary. WHILE @ MaxMinutes> DATEDIFF (mi, @ StartTime, GETDATE () -- time has not expired AND @ OriginalSize = (SELECT size FROM sysfiles WHERE name = @ LogicalFileName) AND (@ OriginalSize * 8/1024)> @ NewSize BEGIN -- Outer loop. SELECT @ Counter = 0 WHILE (@ Counter <@ OriginalSize/16) AND (@ counter< 50000 )) BEGIN -- update INSERT DummyTrans VALUES ('fill log ') DELETE DummyTrans SELECT @ Counter = @ Counter 1 END EXEC (@ TruncLog) END SELECT 'final Size of 'db_name () 'log is' CONVERT (VARCHAR (30), size) '8 K pages or' CONVERT (VARCHAR (30), (size * 8/1024) 'mb' FROM sysfiles WHERE name = @ LogicalFileName Drop table DummyTrans SET NOCOUNT OFF

Instance

The following example separates pubs and attaches a file in pubs to the current server.

A. Separation

The code is as follows:	Copy code
EXEC sp_detach_db @ dbname = 'pubs'

B. Delete log files

C. Attach

The code is as follows:	Copy code
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:

The code is as follows:	Copy code
EXEC sp_dboption 'database name ', 'Autoshrink', '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)