SQLServerPedia

All to often I see people online saying that they detach a database then delete the log file to shrink it.  This is a very dangerous operation which can leave you with a corrupt database that you can’t do anything with.

While you can normally attach a database without the log file, it doesn’t always work.  Sometimes SQL Server won’t be able to recover from this and create a new log file and you’l have lost the database.

If your database is growing and has gotten very large, then you’ll want to change the recorvery mode from FULL to SIMPLE.  This will help keep the transaction log file small, and is perfectly safe to do.  The only thing you will loose is the ability to do point in time restores of the transaction log.  However point in time restores are only available if you backup the transaction log (which would have prevented the log from growing out of control to begin with).

The reason that many people do this, is that the name of the file is misleading.  Normally logs are very safe to delete, however the transaction log isn’t your normal log file.  A transaction log keeps a record of every change to the database, along with the before and after values.  All changes are written to the transaction log before they are commited to the database itself so that if the server fails the database is left in a consistant state.

If your log file has simply gotten huge, and you need to make it smaller, you can use the DBCC SHRINKFILE command to shrink the log file to a smaller size.  This shouldn’t be done on a regular basis.  If you are doing this on a regular basis leave the file large it’s at the size it needs to be or change the database recovery to SIMPLE.

Denny