I have been working with SQL Server 2000 for a while now. I’m no DBA…
Today I encountered a problem that the investigation of which led me to a greater understanding of SQL Server.
We have a reasonably large database that is recreated everyday as a snapshot of a larger system. The transaction log was getting rather large. Given that the database is set up to use the Simple recovery model and we were using no explicit transactions why is it getting so large?
Why can’t we switch off the transaction log?
It turns out that it is almost impossible to write directly to the database – bulk inserts only. A majority of data access goes directly into the transaction log which is then applied to the database. In order to clear down the transaction log you need to backup the transaction log or pretend that you have.
This can be performed with the command BACKUP LOG {database_name} NO_LOG.
Databases can be set up with the Auto-Shrink option (but it is not a good idea to do so). This will allow the transaction log to shrink itself. The problem is that you have no control over when this happens and it will kill performance when it occours – it will pick the worst possible time to do it. The best option is to pick a known quiet time and backup the log yourself.
For more details see “Shrinking the Transaction Log” in Books Online.
In addition we found that all data modification commands are wrapped in an automatic transaction. This meant that our transaction log included a 21 million row insert as a single transaction…
Developer Rants 