(reproduced) SQL Server 2005 log file is too large to handle

Due to the installation of no space, the default installed in the system disk, and did not do automatic backup, truncation transaction log, etc., soon the LDF file reached more than 10 g, or dozens of G, at this time will have to deal with.

Backup and plan not to say, now how to remove it first:

1: Detach the database First

2: In order to insure, do not delete, the LDF file renamed under

3: Attachment database.

4:ok.

The above problems may be encountered:

1: User connected, unable to detach (tick "disconnect all connections")

2: Attachment number of the database when prompted to find the LDF file, do not panic, in the attachment, the path of the LDF to delete, and then click "OK", so that the attachment succeeds, and then go to the database data directory to see, LDF only hundreds of K, which is automatically generated, attachment success.

3:ok, Attachment success, open the application, see if the access is normal (if the database is also connected to other machines in the network, this time need to repair the next IP, or disable the re-enable it, because sometimes the repair window is always closed), normal after the big file can be deleted.

Of course, the best way is to plan in advance, set up, add a job schedule, so that will automatically back up, shrinkage and the like, control in a certain size range.

After learning, the other simple processing methods:

1: Empty the Log

DUMP TRANSACTION Library name with NO_LOG

2: Shrink Log

Enterprise Manager--Right click on the database you want to shrink--all tasks--shrink the database--Shrink the file--Select the log file--in the contraction mode to choose to shrink to **m, here will give an allowable contraction to the minimum number of M, enter this number directly, OK (recommended in 200~300m, To prevent the need to resume use)

3: Delete Log

1: Detach Database Enterprise Manager-> Server-> Database-> Right-click-> Detach Database (check "Disconnect all connections" if connected)

2: Delete log file

3: Attach database Enterprise Manager-> Server-> Database-> Right-click-> Attach Database

This method generates a new log with a size of only hundreds of K.

This database setting is then automatically shrunk

4: If you want to not let it grow 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)

How to set the--sql statement:

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

5. Set to Auto shrink

Enterprise Manager--server--right-click Database-Properties--Options--select "Auto Shrink"

(reproduced) SQL Server 2005 log file is too large to handle