Introduction

This article explores the system table SYSALTFILES in the master database and the table, SYSFILES in each database, and figures out a method of arriving at the growth factor for each database file in an instance of Microsoft SQL Server 2000.

Detailed Discussion

The system table, SYSATLFILES resident in the master database stores information on the files of all the databases in the SQL Server instance. Similarly, the system table, SYSFILES resident in each database stores information on the files of that particular database only. The structure of the two system tables, SYSALTFILES and SYSFILES is similar and so is the information contained except that the former has information on all the databases and the latter has information on only the database in which it resides.

As we already know, every database would be comprised of at least one data file and at least one log file. If a database is set to grow automatically, user has the option to specify either the percentage by which it should auto-grow or a size in Megabytes by which the file should grow. Fetching the already specified auto-growth factor for a database file can be accomplished through Enterprise Manager. But such as exercise may be good at the beginning stages of one’s career when speed of work is not a factor but at a later stage nothing beats the efficiency or speed of the prolific T-SQL.

The issue comes when a user has to get the growth factor for a database file using T-SQL. One would imagine that reading through SYSALTFILES instead of SYSFILES would be a better choice as information for all files of all databases can be gathered in a single operation. Such a perception is natural.

SQL Server Books Online specifies that if the status entry for a particular database file is 0x100000, the growth column entry in SYSALTFILES for that particular database file should be perceived as a percentage. If not, then the corresponding entry in the growth column should be read as number of pages. The same logic holds true for the system table, SYSFILES.

Issue

The column, STATUS of the system table, MASTER.DBO.SYSATLFILES does not always match the same column, STATUS of the system table, SYSFILES for the same database file. This leads to erroneous interpretation of growth factor at times. Let me clarify this point with the situation that I encountered recently. The column, Status was 3 and the column, Growth was 10 in MASTER.DBO.SYSATLFILES for a particular database file. When I perform the T-SQL AND operator on the status column as follows, the result is 0:

select 3 & 0x100000

A value of 0 means that the growth is not in percentage but it is in number of 8K pages. SO I should assume that the Growth factor is 10 pages. When I looked up the growth factor in the Enterprise Manager for the same database file, the value was 10 percent. So my interpretation of the STATUS column in MASTER.DBO.SYSALTFILES for this file was wrong.

Now, in Query Analyzer, I choose the database in question and read from the table SYSFILES for the same database file. The column, Status was 1048579 and the column, Growth was 10 for the same database file (the Status column-value in SYSALTFILES was 3). And the AND operation of Status with 0x100000 was non-zero. That means, The Growth value of 10 in this case means the data file is set to auto-grow by 10% each time it runs out of space. This is validated by the reading in the Enterprise Manager.

Solution

Based on the above analysis, it could be concluded that SYSFILES is absolutely reliable for interpreting data-file growth factors. I have validated the same with several databases in our environment and each time the value of Growth in SYSFILES has been consistent with that in the Enterprise Manager, but the value of Growth in MASTER.DBO.SYSALTFILES has not.

I have created a script based on this conclusion. The script traverses the system table, SYSFILES in each of the databases in an instance of SQL Server and provides consistent results on database-files’ growth factor removing all ambiguity on whether the growth factor is in pages or in percentage.

Again, in case of growth by number of pages, SQL Server Enterprise Manager considers whole numbers only. For example, for the system database MSDB, the growth factor is 32 pages for the data file MSDBData, which is equivalent to

        (32 * 8192)/(1024 * 1024) MB = 0.25 MB.

0.25 MB is interpreted as a whole number in Enterprise Manager as 1 MB. Such a consideration has also been handled in the script.

The T-SQL script is attached herewith as CODE_SYSFILES_GROWTH.txt.

Conclusion

In all cases where database file sizes and their growth factors need to be studied, it would be better to use the system table SYSFILES present in each database rather than the system table, SYSALTFILES present only in the master database. Although a lengthier approach, the results are perfect. In these days where a DBA has less and less time and more and more databases to manage, T-SQL instead of Enterprise Manager comes in handy in terms of fast results and portable code as shown in this article.