SQL - How to create a new SQL Transaction log file (4319816)

SQL - How to create a new SQL Transaction log file

Sometimes the SQL Transaction log file becomes too big and unmanageable, and truncating does not recover enough valuable hard drive space. There is a way to delete, or archive the old log and have SQL create a new one.

Excessive database and/or maintenance activity. 

Note: Make sure there is a current backup of the database.  Also, starting in Active Administrator 6.5 the Audit Database Maintenance Guide can be followed for Active Administrator.

In SQL Management Studio, right click on the database and select Tasks | Detach.

Make sure the “Keep...”, and “Drop...” boxes are checked and click “OK”.

Browse to the “*.LDF” file and rename it. The default location is “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data”.

In this example it is called TextDev_log.ldf.old.

Right click on the database root and select “Attach”.

Click “Add” when the Attach form appears.

Select the .MDF file and click “OK”.

Select the *.LDF file (it may indicate that the file is not found and that is correct) and click “Remove” then click “OK”.

Browse to the SQL data folder and notice that a new *. LDF file is created with a size of only 504 KB.

Additional Information

Choosing the Recovery Model for a Database

How to stop the transaction log of a SQL Server database from growing unexpectedly