SQL Server 2000 size maintenance

An introduction to size maintenance on SQL Server.

Database size maintenance is among the "boring" tasks that DBAs have to perform. Even though storage space is becoming cheaper each day, disk drives still have limits. If you allow your databases to grow without ever managing their size you're very likely to get in trouble. Regardless of how large your disk drives are, once your database and log files grow large enough, not managing them closely will cause your pager to go off freq...

uently.

So why is the database size an issue? Can't you configure SQL Server 2000 to grow and shrink files as it pleases? You can, but that doesn't necessarily mean that you should. It's easy to configure your database files to grow automatically, but once your file fills up the disk it's sitting on you're in trouble; your database users won't be able to initiate any transactions until you allocate more space to the database. On the other hand, if you don't check database files for unused space, SQL Server might be using considerably more disk space than it has to.

The above paragraph may get you thinking about turning on the AUTOSHRINK database option. Do yourself a huge favor and forget that idea. Turning on AUTOSHRINK in a production environment can degrade system performance significantly. The only time you might want to use AUTOSHRINK is on your development server(s), where optimal performance is not as necessary.

Allowing SQL Server to grow data and log files automatically is generally a good idea; however, you should keep in mind that allocating extra disk space comes with considerable overhead. If you notice that database files keep growing daily you might wish to increase the growth percentage or amount of megabytes added to the file size each time the database grows.

The bottom line is that database and log files should be monitored and managed with care. You can allocate additional space to the database using the ALTER DATABASE command. Both data and log files can be shrunk using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands. The latter allows you to shrink individual files, whereas the former shrinks all database files (if you have more than one) in parallel. Unless I have a small database to manage I prefer using DBCC SHRINKFILE since it allows more control by specifying which file to shrink. The syntax of DBCC SHRINKFILE is as follows:

DBCC SHRINKFILE ({fileid | 'filename'}, [compress_size [, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])

As shown above, you may provide a file identifier (found in sysfiles system table) or file name you wish to shrink. In addition you can specify the compress size and one of the shrinking options. If you omit the compress_size parameter, DBCC SHRINKFILE will attempt to reduce the file to its minimum size. The NOTRUNCATE option moves data pages within the file but does not release free space to the operating system. TRUNCATEONLY does the exact opposite -- it releases free space at the end of the file, but does not re-shuffle the data pages. EMPTYFILE option is for special occasions when you wish to move all data to a different file and drop the data file. Feel free to get additional information on DBCC SHRINKFILE from SQL Server online documentation.

Please be forewarned that shrinking a large data file (one that is several gigabytes or larger) can generate much system overhead and might take a dozens of minutes to complete.

Managing log files is somewhat different from management of data files. The difference is due to the architecture of the transaction log. Regardless of how many log files you add to your database, it is still considered to be a single physical log; therefore you cannot backup individual transaction log files. On the logical level, the transaction log is made up of multiple Virtual Log Files (VLF). Transaction logs may contain transactions that have been committed as well as those that have not. Committed transactions are saved to the disk each time the CHECKPOINT command is executed.

Whether you can shrink the transaction log depends on whether the last virtual log file contains active transactions. You can examine the output of an undocumented DBCC command DBCC LOGINFO, to see if VLF's contain active transactions. If you pass no parameters to DBCC LOGINFO, it will execute in the context of the current database. Alternatively you can pass the database name OR database identifier as a parameter. For example:

DBCC LOGINFO('pubs')

Sample results:

FileID FileSize StartOffset FSeqNo Status Parity CreateLSN
------ -------  ----------- ------ ------ ------ ---------
2      253952   8192        909780 0      64     0
2      253952   262144      909779 0      64     0
2      253952   516096      909778 0      128    0
2      278528   770048      909777 0      128    0
2      262144   1048576     0      0      0      909788000000397000000
2      262144   1310720     0      0      0      909788000000457000000
2      262144   1572864     0      0      0      909789000000019000000
2      262144   1835008     0      0      0      909790000000017000000
2      262144   2097152     0      0      0      909791000000039000000
2      262144   2359296     0      0      0      909792000000035000000
7      458752   48300032    909901 2      64     909884000000410000000
7      327680   48758784    909894 2      64     909886000000400000000
7      327680   49086464    909893 2      64     909886000000400000000
7      327680   49414144    909892 2      64     909886000000400000000
7      524288   49741824    909891 2      64     909886000000400000000

This sample shows that the transaction log consists of two files: file identifiers found in the sysfiles system table for log files are 2 and 7. The transaction log contains 15 virtual log files (total number of rows in the output) of which 5 contain active transactions -- this is true for VLF's that have 2 in the status column of the output. Note also that virtual log files containing the active transactions are at the bottom of the output. Until the status of these VLF's changes to 0 you cannot shrink the transaction log. The status will change when transactions are saved to the disk.

Once the status of a virtual log file changes to 0 you can run DBCC SHRINKFILE against each physical log file (in this case against files 2 and 7) to reduce their size. For example:

DBCC SHRINKFILE (2)

Results:

Dbid fileid currentsize minimumsize usedpages estimatedpages
---- ------ ----------- ----------- --------- --------------
8    2      128         128         128       128

This output lets us know that the file size has been reduced to its minimum.

Reader Feedback

Darlene D. writes: This was an excellent article. Thanks for identifying and reminding us of a critical piece of DB maintenance. Your thorough explanation of the command and options available were clear, easy to understand.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, SQL Server, DB2, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.

This was first published in January 2004
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchServerVirtualization

SearchCloudComputing

SearchExchange

SearchSQLServer

SearchWinIT

SearchEnterpriseDesktop

SearchVirtualDesktop

Close