Published on

June 6, 2008

Understanding SQL Server SYSALTFILES and SYSFILES

When working with Microsoft SQL Server, it is important to understand the system tables SYSALTFILES and SYSFILES. These tables store information about the files of all databases in the SQL Server instance and the files of a specific database, respectively.

Typically, a database consists of at least one data file and one log file. If a database is set to grow automatically, the user can specify either a percentage or a size in megabytes by which the file should grow.

While it is possible to fetch the auto-growth factor for a database file through Enterprise Manager, using T-SQL can be more efficient and faster. However, there is an issue when trying to retrieve the growth factor using T-SQL.

The column “STATUS” in the system table MASTER.DBO.SYSALTFILES does not always match the same column in the system table SYSFILES for the same database file. This can lead to erroneous interpretation of the growth factor.

For example, if the “STATUS” column in MASTER.DBO.SYSALTFILES is 3 and the “Growth” column is 10, one might assume that the growth factor is 10 pages. However, when checking the growth factor in Enterprise Manager, it might be displayed as 10 percent. This discrepancy can cause confusion.

To resolve this issue, it is recommended to rely on the SYSFILES system table for interpreting data file growth factors. In multiple tests, the values of the “Growth” column in SYSFILES have consistently matched those in Enterprise Manager, while the values in MASTER.DBO.SYSALTFILES have not.

A script can be created to traverse the SYSFILES system table in each database and provide consistent results on database file growth factors. This script removes any ambiguity regarding whether the growth factor is in pages or percentage.

It is important to note that when growth is specified in pages, SQL Server Enterprise Manager considers only whole numbers. For example, if the growth factor is 32 pages, it is interpreted as 1 MB in Enterprise Manager.

In conclusion, when studying database file sizes and growth factors, it is recommended to use the SYSFILES system table present in each database rather than the SYSALTFILES system table present only in the master database. Although this approach may be lengthier, it provides more accurate results.

As a database administrator with limited time and multiple databases to manage, utilizing T-SQL instead of Enterprise Manager can be beneficial in terms of speed and portability of code.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.