he first step in my initiation as a SQL Server admin came after only a week or two on the job. I was confronted with a message that made my palms sweat. The error read something like this:
Error: 1105, Severity: 17, State:
2: Could not allocate space for
the transaction log for database
'Registration' because the device
is full. Dump the log or enlarge
the device to create more space.
I learned that any error with a severity greater than 16 is a "showstopper," meaning the server won't work until the error is cleared. My distress only increased when my attempts to dump the log were prohibited because the log was out of space
! Because every transaction is logged in the transaction log, there was no more room to log the changes to the log.
After much fear and loathing, Books-On-Line gave me the instructions that were required to clear the error. Unfortunately, the instructions were almost as daunting as the error. They required changes that could be accomplished only by resetting server options and then selectively allowing and prohibiting writes to the system tables. I started to feel like the cure was as bad as the disease. Eventually, I was able to free space in the transaction log and clear the error; then I had to lie down for a while.
New and Improved SQL Server with AutoGrow
Fast-forward to SQL Server 2000. Instead of a device running out of space, we have the ability to let data and log files "autogrow." SQL Server 2000's AutoGrow feature is checked by default when the database is created, and since the source of this option is recorded in Model, the settings in Model determine how new databases will be configured.
You can set a limit to how large the data or log files can grow and also set the mode through which it grows (either by a fixed number of megabytes or as a percentage of the file's size). However, leaving the database at its default setting or not specifying a maximum size carries a new risk: the database files can grow to the limit of available disk space.
The integration between the Windows operating system and SQL Server will prevent the oversized transaction(s) from locking you out because Windows will make SQL Server roll the transaction back. However, this event could be quite an inconvenience on your local 40GB drive and an even bigger embarrassment on your employer's RAID cage!
The moral of the story is cap the size of your data and log files at creation or the files will grow to the limits of your disk space. On the other hand, having the data and log files sized too small will degrade performance, making your server incur the overhead of enlarging the files while the system is in use.
In this 10-Minute Solution, I will illustrate how to plan ahead for out-of-space errors with the appropriate setup, sizing, and maintenance to keep logs from growing out of control. Should such errors catch you by surprise, you'll need to set up monitoring facilities that will catch them for you, which I will cover as well.
How do I keep my transaction log at a reasonable size so it doesn't cause out-of-space errors?
Plan ahead with appropriate setup, sizing, and maintenance and set up monitoring facilities that will catch out-of-space errors for you.