A while back I wrote a CLR object to track database growth.  The CLR object queries sp_databases for each passed in server and saves the data into a table.  By using this CLR object, I can track all of my systems in one location.  I blogged about this tool in this article.

A few weeks ago, I noticed that my SQL Agent job that runs this CLR object was failing.  It was only failing on one of my database servers, so I ran sp_databases on that system to figure out what was going on.  I noticed that for one database, the DATABASE_SIZE column was NULL.  After doing some digging, I realized that sp_databases has a bug in it for any database that is 2TB in size or greater. 

Sp_databases is using int data type for the DATABASE_SIZE column.  Well that’s not big enough for a 2+TB database!  A bigint should have been used instead.  For backward compatibility reasons, Microsoft decided to display NULL for this situation rather than an error in SQL Server 2005 and 2008.  In SQL Server 2000, it throws an error. 

I found out that there is no bug fix for this, so I had two options.  The first option would be to manually patch sp_databases on each of the servers.  The second option would be to patch my CLR object.  If I patched my CLR object, I could either create a new stored procedure, perhaps name it sp_databases2, or put the query directly into the C# code. 

I didn’t like the option of patching sp_databases on potentially hundreds of servers even though I could easily deploy it using a batch file that calls sqlcmd.  I don’t like the idea of modifying Microsoft’s code even though it’s a simple bug fix.

With the second option of patching my CLR object, I didn’t like the idea of creating a new stored procedure as that adds a dependency to my tool.  So I instead decided to patch the CLR object by putting the correct query directly in the code.

You can download the new version here.  Please reference the old article for how to call it, and especially how to call it for all of your servers.  The old article also links to the new version of the code, since I didn’t change functionality. 

Here’s the query in sp_databases that has a bug:

select
    DATABASE_NAME   = db_name(s_mf.database_id),
    DATABASE_SIZE   = convert(int,
                                case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...
                                when convert(bigint, sum(s_mf.size)) >= 268435456
                                then null
                                else sum(s_mf.size)*8 -- Convert from 8192 byte pages to Kb
                                end), 
    REMARKS         = convert(varchar(254),null)
from
    sys.master_files s_mf
where
    s_mf.state = 0 and -- ONLINE
    has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
group by s_mf.database_id
order by 1

And here’s the bug fix:

select 
    DATABASE_NAME = db_name(s_mf.database_id), 
    DATABASE_SIZE = convert(bigint, convert(bigint, sum(s_mf.size))*8), 
    REMARKS = convert(varchar(254),null) 
from sys.master_files s_mf 
where 
    s_mf.state = 0 and 
    has_dbaccess(db_name(s_mf.database_id)) = 1 
group by s_mf.database_id 
order by 1