Click here to monitor SSC
  • Av rating:
  • Total votes: 45
  • Total comments: 4
Pop Rivett

Pop Rivett and the Expanding Log

22 June 2007

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:

  1. Stopping the transaction log growing
  2. Backing up the log and shrinking the log file.
  3. Freeing some disk space so that the log file can automatically grow.
  4. Moving the log file to a disk drive with enough space.
  5. Increasing the size of a log file (if you have opted to restrict its growth).
  6. Adding a log file on a different disk.
  7. Completing or killing a long-running transaction that is preventing a log backup freeing enough space.
  8. 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)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_7vaf.asp

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

Pop Rivett

Author profile:

Pop spent his formative years working in assembler on IBM Series/1 but retrained in VB when that went out of fashion. He soon realised how little relational database expertise existed in most companies and so started to spend most of his time working on that. He now sticks to architecture, release control / IT processes, SQL Server, DTS, SSIS, and access methods in VB/ASP/.NET/Crystal Reports/reporting services. He has been involved with SQL Server since the old days of v4.2 to v2005. He tries to stay away from anything presentation oriented (see www.mindsdoor.net). Theoretically he is semi-retired but seems to keep being offered potentially interesting work.

Search for other articles by Pop Rivett

Rate this article:   Avg rating: from a total of 45 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Another way
Posted by: Anonymous (not signed in)
Posted on: Friday, July 06, 2007 at 12:31 AM
Message: A quick way to reclaim space (that admittedly leaves you with your pants down momentarily) is backup log with truncate_only to clear space. Dbcc shrinkfile to resize the log. Full backup to make sure that your db is recoverable.

Then find whoever executed the runaway SQL on your production server and punch them in the face.

Subject: re: Another way
Posted by: The SQL Server Thought Police (view profile)
Posted on: Friday, July 06, 2007 at 6:00 AM
Message: When striking errant developers caught messing with production databases, we DBAs were always, i the past, instructed to slap them firmly, rather than punch them, as it causes less bruising. Nowadays, in these liberal times, one has to use the savage weopons of Irony, Sarcasm, litotes, paradox, epizeuxis, cacophony and anaphora. In such a way has a DBAs life become more complex:-)

Subject: re: Another way
Posted by: Anonymous (not signed in)
Posted on: Friday, July 06, 2007 at 10:08 AM
Message: I do a full backup first, then zip it. This gives me the best option if (well when) I screw this up.

Subject: Yet another to consider...
Posted by: Anonymous (not signed in)
Posted on: Saturday, July 07, 2007 at 9:14 PM
Message: Sometimes I run across a situation where the system is simply designed for long running transactions but the log file is full or has eaten up the drive space available. At least that is what it seems like... In actuality, the counter in the file is towards the end of physical file which means it won't release the space at the front of the file. The trick in these situations is to get back just enough space to run 1000 or so junk transactions on a dummy table. A table with a int id field and a 10 character string usually works and when the transactions are run the pointer will wrap around to the beginning of the file allowing you to release the space at the tail end using the standard shrink commands.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.