Changes in Log Size

No matter how many physical files have been defined for the transaction log, SQL Server always treats the log as one contiguous file. For example, when the DBCC SHRINKDATABASE command determines how much the log can be shrunk, it does not consider the log files separately but determines the shrinkable size based on the entire log.

The transaction log for any database is managed as a set of virtual log files (VLFs) whose size is determined internally by SQL Server based on the total size of all the log files and the growth increment used when enlarging the log. A log always grows in units of entire VLFs and can be shrunk only to a VLF boundary. (Figure 5-2 illustrates a physical log file along with several VLFs.)

A VLF can exist in one of the following three states:

  • Active The active portion of the log begins at the minimum log sequence number (LSN) representing an active (uncommitted) transaction. The active portion of the log ends at the last LSN written. Any VLFs that contain any part of the active log are considered active VLFs. (Unused space in the physical log is not part of any VLF.)
  • Recoverable The portion of the log preceding the oldest active transaction is needed only to maintain a sequence of log backups for restoring the database to a former state.
  • Reusable If transaction log backups are not being maintained or if you have already backed up the log, VLFs prior to the oldest active transaction are not needed and can be reused.

click to view at full size.

Figure 5-2. The VLFs that make up a physical log file.

SQL Server will assume you're not maintaining a sequence of log backups if any of the following are true:

  • You have truncated the log using BACKUP LOG WITH NO_LOG or BACKUP LOG WITH TRUNCATE_ONLY.
  • You have set the database to truncate the log automatically on a regular basis with the database option trunc. log on chkpt. or by setting the recovery mode to SIMPLE.
  • You have never taken a full database backup.

In any of these situations, when SQL Server reaches the end of the physical log file, it starts reusing that space in the physical file by circling back to the file's beginning. In effect, SQL Server recycles the space in the log file that is no longer needed for recovery or backup purposes. If a log backup sequence is being maintained, the part of the log before the minimum LSN cannot be overwritten until those log records have actually been backed up. After the log backup, SQL Server can circle back to the beginning of the file. Once it has circled back to start writing log records earlier in the log file, the reusable portion of the log is then between the end of the logical log and the active portion of the log. Figure 5-3 depicts this cycle.

You can actually observe this behavior in one of the sample databases, such as pubs, as long as you have never made a full backup of the database. If you have never made any modifications to pubs, the size of its transaction log file will be just about 0.75 MB. The script below creates a new table in the pubs database, inserts three records, and then updates those records 1000 times. Each update is an individual transaction, and each one is written to the transaction log. However, you should note that the log does not grow at all, even after 3000 update records are written. (If you've already taken a backup of pubs, you might want to re-create the database before trying this example. You can do that by running the script instpubs.sql in the folder \Program Files\Microsoft SQL Server\MSSQL\install. If your SQL Server is a named instance, you'll need to replace MSSQL with the string MSSQL$<instance_name>.)

click to view at full size.

Figure 5-3. The active portion of the log circling back to the beginning of the physical log file.

 CREATE TABLE newtable (a int) GO INSERT INTO newtable VALUES (10) INSERT INTO newtable VALUES (20) INSERT INTO newtable VALUES (30) GO DECLARE @counter int SET @counter = 1 WHILE @counter < 1000 BEGIN UPDATE newtable SET a = a + 1 SET @counter = @counter + 1 END 

Now make a backup of the pubs database after making sure that the database is not in the SIMPLE recovery mode. I'll discuss recovery modes later in this chapter, but for now, you can just make sure that pubs is in the appropriate recovery mode by executing the following command:

 ALTER DATABASE pubs SET RECOVERY FULL 

You can use the following statement to make the backup, substituting the path shown with the path to your SQL Server installation:

 BACKUP DATABASE pubs to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL\backup\pubs.bak' 

Run the update script again, starting with the DECLARE statement. You should see that the physical log file has grown to accommodate the log records added. The initial space in the log could not be reused because SQL Server assumed that you were saving that information for backups.

Now you can try to shrink the log back down again. If you issue the following command or if you issue the DBCC SHRINKFILE command for the log file, SQL Server will mark a shrinkpoint in the log but no actual shrinking will take place until log records are freed by either backing up or truncating the log.

 DBCC SHRINKDATABASE (pubs) 

You can truncate the log with this statement:

 BACKUP LOG pubs WITH TRUNCATE_ONLY 

At this point, you should notice that the physical size of the log file has been reduced. If a log is truncated without any prior shrink command issued, SQL Server marks the space used by the truncated records as available for reuse but it does not change the size of the physical file.

In the previous version of SQL Server, running the preceding commands exactly as specified did not always shrink the physical log file. The cases in which the log file did not shrink happened when the active part of the log was located at the end of the physical file. Physical shrinking can take place only from the end of the log, and the active portion is never shrinkable. To remedy this situation, you had to enter some "dummy" transactions after truncating the log, to force the active part of the log to move around to the beginning of the file. In SQL Server 2000, this process is unnecessary. If a shrink command has already been issued, truncating the log internally generates a series of NO-OP log records that force the active log to move from the physical end of the file. Shrinking happens as soon as the log is no longer needed.

Log Truncation

If a database log backup sequence is not being maintained for a database, you can set the database into log truncate mode by setting the recovery mode to SIMPLE or by using the older mechanism of setting the database option trunc. log on chkpt. to TRUE. The log is thus truncated every time it gets "full enough." (I'll explain this in a moment.)

Truncation means that all log records prior to the oldest active transaction are removed. It does not necessarily imply shrinking of the physical log file. In addition, if your database is a publisher in a replication scenario, the oldest open transaction could be a transaction marked for replication that has not yet been replicated.

"Full enough" means that there are more log records than can be redone during system startup in a reasonable amount of time, which is referred to as the "recovery interval." You can manually change the recovery interval by using the sp_configure stored procedure or by using SQL Server Enterprise Manager (right-click the server, select Properties, click the Database Settings tab, and set the value in the Recovery Interval box), but it is recommend that you let SQL Server autotune this value. In most cases, this recovery interval value is set to 1 minute. (SQL Server Enterprise Manager shows zero minutes by default, meaning SQL Server will autotune the value.) SQL Server bases its recovery interval on the estimation that 10 MB worth of transactions can be recovered in 1 minute.

The actual log truncation is invoked by the checkpoint process, which is usually sleeping and is only woken up on demand. Each time a user thread calls the log manager, the log manager checks the size of the log. If the size exceeds the amount of work that can be recovered during the recovery interval, the checkpoint thread is woken up. The checkpoint thread checkpoints the database and then truncates the inactive portion.

In addition, if the log ever gets to 70 percent full, the log manager wakes up the checkpoint thread to force a checkpoint. Growing the log is much more expensive than truncating it, so SQL Server truncates the log whenever it can.

NOTE


If the log is in auto truncate mode and the autoshrink option is set, the log will be physically shrunk at regular intervals.

If a database has the autoshrink option on, an autoshrink process kicks in every 30 minutes and determines the size to which the log should be shrunk. The log manager accumulates statistics on the maximum amount of log space used in the 30-minute interval between shrinks. The autoshrink process marks the shrinkpoint of the log as 125 percent of the maximum log space used or the minimum size of the log, whichever is larger. (Minimum size is the creation size of the log or the size to which it has been manually increased or decreased.) The log then shrinks to that size whenever it gets the chance, which is when the log gets truncated or backed up. It's possible to have autoshrink without having the database in auto truncate mode, although there's no way to guarantee that the log will actually shrink. For example, if the log is never backed up, it will never be cleared.



Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
ISBN: 0735609985
EAN: 2147483647
Year: 2005
Pages: 179
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net