The phone rings
“Aargh! Quick Pop, we need help! The website’s down, we can’t trade, we’re losing business and the browser page shows nothing but some gobbledygook saying ‘Can’t allocate space for object syslogs in database Snibbo because the logsegment is full‘. What should I do!?”
“Hmmm, an interesting problem that.”
“That’s not quite the word that the boss used.”
“Well, laddie, I wouldn’t worry because it is something one can put right pretty easily. The transaction log is something I always find fascinating. Whenever a data update is made, entries are added to the transaction log. This is an important element of the mechanism by which SQL Server maintains integrity, particularly during recovery. It uses the active section of the log to do this.”
“Pop. Please, what must I do? “(Sound of raised voices in the background)
“I’ll come to that shortly. Firstly, though, I ought to explain what I mean by the ‘active section’. The transaction log is a ‘circular file’, in that, when the end of the file is reached, any free entries at the start will then be used: This means that, all being well, the file will stay at a constant size, as the current entry cycles round the ‘circular file’. In your case, all is not well.”
“Go on…quickly!” (Faint screams, and sounds of running feet in the distance)
“I’m glad you’re interested, laddie. The system maintains the Minimum-recovery Log Sequence Number or MinLSN, which is a pointer to the first active log record.
Any log records before this (in the circular file) are free, in that they play no role in recovery. They are required only to roll forward updates, when using log backups to restore a database to the point of failure. The MinLSN will be prevented from moving forward and overwriting records by the presence of any open transactions. The oldest open transaction entry will be greater or equal to the MinLSN. The MinLSN is updated at checkpoint, so that even if the transaction is committed it will not immediately free entries, and anything that holds up the checkpoint can therefore cause problems.”
“I think we know about problems”(gritted teeth)
“Ah yes. If the database is in Simple Recovery mode, then all the entries prior to the MinLSN will be freed at checkpoint. If a full backup has not been taken then the log will be truncated at each checkpoint irrespective of the recovery model; you can’t restore the logs without a full backup so they wouldn’t be any use.If the database is in Full Recovery mode, and a full backup has been taken, then the entries before the MinLSN will only be freed by a transaction log backup, but not by an additional full backup.”
“Pop….”
“…I’m getting there. Unfortunately, because of the SQL Server default of ‘Full Recovery’ in the ‘model’ database, the databases that you create are in full recovery mode. This means that if no action is taken to backup the log, then no transaction log entries will be freed and the log file will eventually fill the disk and crash the system.
The SQL Server installation process is very simple and commonly left to be carried out by the inexperienced. The installed server will appear to work happily at first, but cause problems later. I always recommend setting the model database to simple recovery mode, which in turn sets a default mode of Simple for new databases, and only using Full Recovery when you need to.”
(Distant sobbing on the line)
“Well, enough of the background. I suggest we get down to business. There are several alternatives depending on your requirements:
- Stopping the transaction log growing
- Backing up the log and shrinking the log file.
- Freeing some disk space so that the log file can automatically grow.
- Moving the log file to a disk drive with enough space.
- Increasing the size of a log file (if you have opted to restrict its growth).
- Adding a log file on a different disk.
- Completing or killing a long-running transaction that is preventing a log backup freeing enough space.
- Detaching and then Attaching the log file
“Help me out here Pop! My continued survival would be the first requirement!”
“Well, you see, this error mostly seems to happen when someone has set the database into full recovery mode when you don’t need it and you haven’t been taking log backups. If this is the case then you can simply stop the transaction log file (.ldf) from growing.
Stopping the transaction log growing
If the log file has grown due to being unintentionally in full recovery mode, then set it to simple before going any further.
If you are using Enterprise manager,
Right click on the database
Click on properties, then Options
Set model to simple, click OK.
In SSMS,
Right click on the databaseClick on properties, then Options in the navigation bar on the left
Set Recovery Model (near the top) to simple, click OK.
Or, In TSQL…
EXEC sp_dboption [dbname], 'trunc. log on chkpt.', 'true'
This tells SQL Server to clear out inactive transaction log entries at every checkpoint, rather than using them for restore operations. The active transaction log entries will still required to roll back transactions and for recovery. It will only be possible to restore your database back to the point at which the last backup was taken.
“But Pop, this is a production database…I’ve got to keep it in full recovery mode!”
“Well in that case, you’ll need to shrink the log file…”
Backing up the log and shrinking the log file
If the log file has already grown too big, and you need to be in full recovery mode, you need to to implement log backups to free up log space, as well as the more obvious reason as being able to do a full restore. So the first thing to do is backup the log, then shrink the log, and finally schedule log backups.
In Enterprise manager,
Right click on the database in the object browser
Click ‘All tasks’
Click on ‘Shrink database’
Click ‘Files’
Select log file, click OK.
In SSMS,
Right click on the database in the object browser
Click ‘Tasks’
Click on ‘Shrink’
Click ‘Files’
Select log file in the ‘File Type’ drop-down, click OK.
Or, alternatively, in TSQL,
dbcc shrinkfile ([db_log_name])
(Here [db_log_name] is the logical name of the log file as found from sp_helpdb or the sysfiles table
If this doesn’t work, the chances are that you have a long-running transaction that is preventing the transaction log backup from clearing enough space in the log file. For details of how to do this, look at Managing Long-Running Transactions:
http://technet.microsoft.com/en-us/library/ms366331.aspx
If that fails, then there is another way of shrinking the log file, if you are completely sure that you can safely dispose of the log data. You can do a Detach followed by an Attach. Always take a full backup before doing a Detach.
By detaching the database, deleting/renaming the log file, and reattaching the database, you will create a minimum size log file. The log file must be deleted or renamed otherwise it will be re-used, even though it is not mentioned in the Attach.
From the Enterprise manager,
Right click on the database
Click All tasks
Click on Detach database, click OK.
Delete/rename the disk log file.
Right click on databases, All tasks
Attach database, Select the .mdf file, OK, Yes (to create the new log message).
From SSMS
Right click on the database, Click on Tasks, click Detach…, click OK
Delete/rename the disk log file.
Right click on databases, Tasks
Attach…, Select the .mdf file, click OK, Yes (to create the new log message).
Alternatively, from TSQL
sp_detach_db [dbname]
(then Delete or Rename the disk log file.)
sp_attach_single_file_db [dbname], [filename]
(where [filename] is the name of the physical data file -.mdf).
“Well, that’s about all there is to know, Laddie. Laddie? Laddie?”
(Silence but for a sound of distant running feet)
Further reading:
Troubleshooting a Full Transaction Log (Error 9002)
http://technet.microsoft.com/en-us/library/ms175495.aspx
Truncating the Transaction Log (SQL Server 2000)
https://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx
Causes of SQL Transaction Log Filling Up:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B110139
Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317375
Load comments