Clear SQL Server database logs

Clear database logs

Declare @ database nvarchar (50)

Set @ database = 'lh _ officefile'

-- 1. Clear logs
Dump transaction @ database with no_log

-- 2. truncate transaction logs
Backup log @ database with no_log

-- 3. shrink the database
DBCC shrinkdatabase (@ database)

-- 4. Set automatic database contraction
Exec sp_dboption @ database, 'autowrite', 'true'

========================================================== ================================
Compressed log and database file size

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.

You can also use SQL statements to complete
-- Shrink Database
DBCC shrinkdatabase (database name)

-- 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
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 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 filesmicrosoft SQL servermssqldatapubs. 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)

Clear SQL Server database logs