Resolve SQL Server Log Full issues

This article first provides a complex method of compressing logs and database files.

1. Empty log: DUMP TRANSACTION library name with NO_LOG.

2. Truncate transaction log: BACKUP log database name with NO_LOG.

3. Shrink the database file (if not compressed, the database file will not be reduced:

Enterprise Manager-> Right button you want to compress the database-> All Tasks-> Shrink Database-> shrink file-> Select log file-> in the contraction mode to choose to shrink to XXM, here will give a permit to shrink to the minimum m number, directly enter this number, Make sure it's OK.-> Select the data file--in the contraction method to choose to shrink to XXM, here will give a allow to shrink to the minimum m number, directly enter this number, OK, can also be done with the SQL statement.

--Shrinking database: DBCC shrinkdatabase (Customer profile)

--Shrink the specified data file, 1 is the file number, you can query through this statement:

select * from sysfiles DBCC SHRINKFILE(1)

4. In order to maximize the reduction of log files (if it is SQL 7.0, this step can only be done in Query Analyzer).

A. Detach database: Enterprise Manager--server--database--right----detach database.

B. Delete log files on my computer.

C. Additional database: Enterprise Manager--server--database------Append database.

This method will generate a new log with a size of only 500 KB or in code:

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

A. Separation: execsp_detach_db @dbname = ' pubs '.

B. Deleting a log file

C. Additional:

EXECsp_attach_single_file_db
@dbname = 'pubs',
@physname =
'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

5. In order to be able to automatically shrink later, do the following settings:

Enterprise Manager-> Server-> right-key database-> properties-> option-> Select the Auto Shrink->sql statement setting method:

EXEC sp_dboption ' database name ', ' autoshrink ', ' TRUE '.

6. If you want to not let it grow too large log:

Enterprise Manager-> Server-> right-key Database-> property-> transaction log-> limits file growth to XM (x is the maximum data file size you allow). How the->sql statement is set:

ALTER DATABASE Database name modify file (name= logical filename, maxsize=20).

Special attention:

Follow the steps, do not follow the steps, or you may damage your database.

Generally do not recommend to do 4th, 62, 4th step unsafe, it is possible to damage the database or loss of data; step 6th if the log reaches the upper limit, subsequent database processing will fail, and the log will not recover until it is cleaned up.

In addition, a simpler method is suggested.

A simpler approach:

1, the right to build the Database Properties window--Failure to restore the model--set to simple.

2, the right to build the database all tasks-shrink the database.

3, the right to build the Database Properties window--The failure to restore the model--set to bulk-logged.