upc internet hungary Microsoft SQL Server 2008 Internals in Visual Basic .NET

Maker UPC-A Supplement 2 in Visual Basic .NET Microsoft SQL Server 2008 Internals

Remember that truncating is not shrinking. A database should be truncated so that it is most shrinkable, and if the log is in autotruncate mode and the autoshrink option is set, the log is physically shrunk at regular intervals. If a database has the autoshrink option on, an autoshrink process kicks in every 30 minutes (as discussed in 3) 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 autoshrink processes. The autoshrink process marks the shrinkpoint of the log as 125 percent of the maximum log space actually 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 it gets truncated or backed up. It s possible to have autoshrink without having the database in autotruncate mode, although you cannot guarantee that the log actually shrinks. For example, if the log is never backed up, none of the VLFs are marked as reusable, so no shrinking can take place. As a nal note, you need to be aware that just because a database is in autotruncate mode, you cannot guarantee that the log won t grow. (It is the converse that you can be sure of that if a database is not in autotruncate mode, the log will grow.) Autotruncate means only that VLFs that are considered recoverable are marked as reusable at regular intervals. But VLFs in an active state are not affected. If you have a long-running transaction (which might be a transaction that someone forgot to commit), all the VLFs that contain any log records since that long-running transaction started are considered active and can never be reused. One uncommitted transaction can mean the difference between a very manageable transaction log size and a log that uses more disk space than the database itself and continues to grow.

You can see the current size of the log le for all databases, as well as the percentage of the log le space that has been used, by running the command DBCC SQLPERF( logspace ). However, because it is a DBCC command, it s hard to lter the rows to get just the rows for a single database. Instead, you can use the dynamic management view sys.dm_os_performance_counters and retrieve the percentage full for each database s log:

SELECT instance_name as [Database], cntr_value as "LogFullPct" FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Percent Log Used%' AND instance_name not in ('_Total', mssqlsystemresource') AND cntr_value > 0;

The nal condition is needed to lter out databases that have no log le size reported. This includes any database that is unavailable because it has not been recovered or is in a suspect state, as well as any database snapshots, which have no transaction log.

4

As you probably know by now, this book is not intended to be a how-to book for database administrators. The bibliography in the companion content lists several excellent books that can teach you the mechanics of making database backups and restoring and can offer best practices for setting up a backup-and-restore plan for your organization. Nevertheless, some important issues relating to backup and restore processes can help you understand why one backup plan might be better suited to your needs than another. Most of these issues involve the role the transaction log plays in backup and restore operations, so I ll discuss the main ones in this section.