Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Database Growth Tracker Tool – New Version

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

Print | posted on Friday, July 09, 2010 2:10 PM |

Feedback

Gravatar

# re: Database Growth Tracker Tool – New Version

Very very useful Tara, thanks for another great contribution...
7/12/2010 7:30 AM | Jon
Gravatar

# re: Database Growth Tracker Tool – New Version

Hi Tara

Cool code, although I've never used CLR.

I don't mean to hijack your post, but I've taken a different approach to the same problem, see my blog post above. I use the backupset table in the msdb database to track database size. This acurately reflects the actual space used for the db and not the mdf size. The mdf size can be misleading. For example if the mdf is 1GB and the data space used is 100MB and grows by 100MB, no growth will be noted for 10 months. Thats not correct. I've used T-SQL to gather the data and tied this together for 285 servers using Powershell. I then publish the data in 1 reporting services report. I can select a server and then a db on that server and a graph of growth over time, together with a grid of the data is display. Great for capacity planning, trending and management reports.

Cheers
David
7/27/2010 12:38 AM | David
Gravatar

# re: Database Growth Tracker Tool – New Version

I keep getting "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" and I'm not sure how to fix that.
7/27/2010 8:06 AM | Autumn Moone
Gravatar

# re: Database Growth Tracker Tool – New Version

Thanks for updating us about the tool's new version. Have also read some of your articles on SQL server & DB., very informative. Appreciate your help. Once again Thank you.
7/28/2010 8:41 PM | Rachana
Gravatar

# re: Database Growth Tracker Tool – New Version

David,

Your method will not work when backup compression is being used, which is what we are using.
9/22/2010 12:40 PM | tarad
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET