During an insert or update to a SQL database, you may encounter an error like this:

[Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database 'MyDataBase' is full. Back up the transaction log for the database to free up some log space.

If the database is set to full recovery, the LDF file can get huge. Example 27 GB.

These steps can be used to remove the LDF and replace it so that it is smaller. These steps were done using SQL Server 2005 Management Studio.

• Right click the database
• Select Properties
• Go to the Options Page
• Note what the recovery model is set to. Mine was "Full"
• Click OK
• Right click the database
• Select Tasks
• Select Back up
• Change the Backup Type to "Transaction Log"
• Select current destination backup file listed.
• Click Remove. Note – depending on your nightly backup you may have to put this back. I did not since my backups do not depend on this.
• Click Add
• Enter in a UNC path to another server to store the backup. Example: //Server1/common/MyDataBase_Log.bak
• Click OK
• Click OK to start the backup

Once the backup completed.

• Right click the database
• Click Detach
• Check the Drop Connections check box
• Check the Update Statistics checkbox
• Click OK
• Moved the bloated LDF file off the server (or delete it)
• Right click the server in Management Studio
• Selected Attach
• Click Add
• Locate the MDF
• Click OK
• Select the LDF in the bottom pane
• Click remove (a new one will get built)
• Click OK
• The LDF is auto rebuild and is small.
• Right Click the database
• Select Properties
• Go to the Options page
• Set the Recovery back to the way it was originally. I changed mine from Simple to Full.