August 20, 2008 at 7:23 am
Preface: We are running 2000 Enterprise, auto grow is on and set to 10%, current file size is 55 GB, current data size (including indexes) is 38 GB. We are planning on an upgrade to 2005 Enterprise in the next three months.
Our network admin came to me last week and told me that one of our databases has been growing at “an alarming rate” and asked me to look into it. I ran DBCC CHECKDB to see if it could find any problems and it did come back saying that I needed to run DBCC UPDATEUSAGE. When I look at that in BOL, it indicates it will correct incorrect space usage reports returned by the sp_spaceused system stored procedure (a bug?). Curious as I am now, I ran that stored procedure on the database and it reports my database size at 88.2 GB (apparently the size on disk of the data and log files combined) and unallocated space at -26.7 GB. Negative?? The next data set returned seems to report correct information.
To further add to the puzzle, I am using Spotlight on SQL Server from Quest and it is reporting different information from what SQL Server is reporting. However, the Quest information does seem to be more accurate. My theory at this point is that the incorrect space usage reporting is causing SQL Server to think it is running out of space prematurely and therefore, causing auto grow to go get more space.
For upgrade testing purposes, we do have a copy of this running on a 2005 server. I got the same results there so I went ahead and ran the DBCC UPDATEUSAGE and now the unallocated space is returning a positive number as I would expect. Quest reporting against this copy in 2005 is consistent with what SQL Server is reporting.
My questions are:
1. Is my theory correct about why the file size is growing disproportionately to the data growth? The file size has grown nearly 10 GB over the last two months while the data size has seen about 800 MB in growth.
2. What will be the impact to my production database of running DBCC CHECKDB and DBCC UPDATEUSAGE? If I do not run these until our upgrade to 2005, will there be any negative ramifications?
3. Is there anything else I can look at to try to determine why the file size is growing disproportionately to the data growth?
Thanks in advance for any and all responses.
August 26, 2008 at 4:18 am
Hmmmm....interesting,
Do you have any processes running deal with moving lots of data or calculating data ?
ARe you doing any form of maintenance on this database?
Regards
Graeme
August 26, 2008 at 6:56 am
There is a weekly maintenance job that rebuilds the indexes - that explains to me why the log file is so large. The difficulty with explaining why the data is so large is that I have only been here for five months so I'm not yet intimately familiar with the data as I was in my past job. We are scheduling a maintenance window in the next two weeks for me to run the DBCC CHECKDB just to make sure the database is in good shape. Then I'll run UPDATEUSAGE and depending on how long that takes to run, I may schedule it to run regularly.
We do have nightly jobs to do a fairly significant amount of work, which may or may not be the cause. I'm working on a way to track, over time, the growth so I can begin to understand how the data is used.
August 26, 2008 at 8:25 am
SQL Server doesn't look at the internal space counters, AFAIK, to determine when to grow the file. It looks to allocate space from one of the IAM/GAM/SGAM pages and if they're full, it asks the OS for more space. The reporting we get from sp_spaceused is based on indexes and other stored counters that may or may not be accurate. I've seen them go negative and never had file growth.
File growth depends on data insertion, and possibly fragmentation. Heavily fragmented tables might use lots more space than they need. Typically they have to be dis-proportionally large for you to notice, but you might check fragmentation levels (dbcc showcontig) and see what is reported. Rebuilding indexes should fix this.
My guess is that what the network admin regards as "alarming" is in no way based on the activity of the server. It's strictly a file size thing.
August 26, 2008 at 9:13 am
Steve Jones - Editor (8/26/2008)
My guess is that what the network admin regards as "alarming" is in no way based on the activity of the server. It's strictly a file size thing.
This is exactly what I have been trying to communicate. However, being the "new kid on the block," I don't think they really believe me. They think that for the small number of properties we own and manage (ten properties with about 6,500 units), it is impossible for the data to grow that fast.
I decided to add a table and a nightly procedure to start collecting the size statistics to show them how much the data is actually growing. Of course, I'll grow the database but at this point, I'm just trying to show them it is working as expected.
The only oddity left in my mind is why the file size is growing when I'm not seeing a need by the amount of free space reported? Is there an article somewhere I can review to try to put this together in my mind and perhaps explain to the folks here exactly what is going on?
August 26, 2008 at 12:39 pm
Hi Jim,
Indexes also count towards the size of the mdf. If you have wide indexes and a clustered index along with "fk" indexes, the size of your indexes can actually grow well beyond that of the data.
I just archived a bunch of data by moving it from some very heavily indexed tables to some non-indexed archive tables (heaps) within the same database. The MDF file started out at 134 gigabytes... after the "archive" and full blown reindexing (DBCC DBReIndex) of the indexes and considering that the data actually remains in the same database it started, I have to attribute the 56 giga byte reduction is the "used" size of the database to space recovered from indexes.
Run DBCC Update usage again and then run sp_spaceused on some of your tables... you may have an "index surprise" waiting for you.:D
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply