This month’s T-SQL Tuesday (and Halloween?) theme is one of horrific realization. The same kind of terror experienced by the characters in H.P. Lovecraft’s stories. And this month’s horrible blog party is being hosted by Nick Haslam.
While the story I am about to convey may not be the most terrible that I have experienced, it is certainly fresh in my memory.
At the beginning of every job, I investigate the SQL Servers that I’ll be caring for. I check to make sure backups are occurring. I set up scripts like sp_Blitz and sp_WhoIsActive to help guide my initial actions. I’ll set up alerts so I know when something goes wrong on the server. I cover all the basic DBA tasks.
When investigating storage and file sizes, I came across a user database that had grown to nearly 50GB. The size is not the issue. We had plenty of storage space, and 50GB isn’t even close to the largest databases I’ve cared for. I hadn’t thought anything about it until I began to look at the growth settings for all the databases on the server.
SELECT mf.database_id ,mf.[file_id] ,mf.name ,mf.type_desc ,mf.physical_name ,mf.size/128 AS [Size in MB] ,mf.max_size/128 AS [Max Size in MB] ,CASE WHEN mf.is_percent_growth = 1 THEN CONVERT(VARCHAR(12),mf.growth) + '%' WHEN mf.is_percent_growth = 0 THEN CONVERT(VARCHAR(12),mf.growth/128) + 'MB' END AS [Growth] ,CASE mf.is_percent_growth WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS is_percent_growth FROM sys.master_files AS mf /* * Any records returned are using the default log growth sizing, which can lead to run-away growth */ --WHERE mf.max_size = 268435456 -- AND mf.growth = 10 -- AND mf.is_percent_growth = 1 -- AND mf.type_desc = 'LOG' /* * This combination can also lead to run-away growth without proper care */ --WHERE mf.max_size = -1 -- AND mf.is_percent_growth = 1 /* * Any records returned may be affected by a log growth bug described by Paul Randal * Paul notes that this is fixed in SQL11 (SQL Server 2012) * See: http://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx */ --WHERE (mf.growth/128) % 4096 = 0 -- AND mf.is_percent_growth = 0 -- AND mf.type_desc = 'LOG'
That bit of code will show all the databases on your server, as well as the locations of the files and the current growth settings. You’ll notice that I’ve provided three default WHERE clauses. The first will return records for LOG files that are still using the default setting upon a fresh installation of SQL Server. The second clause shows files that have the potential for run-away growth because they aren’t capped at a maximum size and use a percentage growth. (On a side note, there isn’t anything particularly wrong with percentage growth, it’s just that every time a file grows, it will grow a little more the next time. It’s not as simple to plan storage around that kind of increasing growth.) Finally, the last WHERE clause is based on a 4GB growth size bug that Paul Randal blogged about. I believe that it has been fixed in SQL Server 2012.
Getting back to my story, while I was investigating the growth settings for my files, I saw something so horrifying that it cannot be described. I’m sorry, that was another reference to one of my favorite authors, H.P. Lovecraft. It totally can be described: that 50GB user database had a percent growth setting set for 12,800%! It only needed to grow once, and when it did, the size jumped from 3.5MB to nearly 50GB!
The Fix is In
Immediately, the first thing I did was to adjust the growth setting of that database from 12,800% to 1GB. That prevents the potential for the 50GB to grow again and completely wipe out the storage space on the server when it tries to gobble up 625TB of space.
Then the real investigation began: what is this database, who uses it, and how much space is it actually using? Luckily in my case, the database stored application settings, but wasn’t used for actual user data. This meant that I was able to shrink the file down to a reasonable 1GB.
Do note that there isn’t anything wrong with having a large data file that isn’t using all its space. Nothing bad will come from it. After talking over the pros and cons with my manager and some users, it was a personal call to shrink the data file to free up the space for use by other databases on the server. It also needs to be noted that shrinking a database is a horrifically destructive process (as far as performance is concerned), and you’ll need to take immediate care to check your indices for fragmentation – which will likely cause your newly shrunken database to grow again. But in my case, that was okay because I had fixed that growth setting and I didn’t end up with a 50GB data file.