Browse DevX
Sign up for e-mail newsletters from DevX


Control Log File Growth to Avoid Out-of-Space Errors-2 : Page 2




Building the Right Environment to Support AI, Machine Learning and Deep Learning

The Hardest Working File in the Database
The transaction log has to store everything that happens before and after transactions are committed. The log does get "emptied" after its transactions are moved to the database, but it remains the same size. Backing up the log truncates it; it doesn't back up the database. Think of it as a sumo wrestler who's gone on a crash diet. Once he loses the weight, his skin still hangs on to its original size.

Although dumping the log and the DBCC ShrinkDatabase or DBCC ShrinkFile operations can change the log size, understanding how the log decides where to begin can be a useful. The log is truncated from a point called the Log Segment Number (LSN), the place where it begins. Knowing this, you should dump the log and database as soon as its created to establish a LSN pointer when the log is at its smallest. This allows you to shrink the log down to its minimum size (as specified in Model) without having to dupe it with ghost transactions later. Remember, the database has to remember only what the log has released, which is why the transaction log is the hardest working file in the database.

Additionally, SQL Server creates a new transaction log for you when you either attach or restore a database without a transaction log. This new log is useless, however, because it doesn't know anything about the transactions in the database. If you find yourself in this position, be sure to take a full database backup before doing anything else.

One more caveat to the detach/attach approach is that it will work only on a system where the log is in one physical file. Still, setting the LSN on creation will help you establish the optimum start size for the log.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date