Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a production database that's consuming a significant amount more disk space than it should be.

Using sp_spaceused (only the interesting tables), I get.

Table Name    Reserved (KBs)    Data (KBs)    Index Size (KBs)
----------    --------------    ----------    ----------------
E              149600             142992         1136
H              184968             12104        162272
M               94104             29512         62480
T               25616             13544          9464
MH              16072             10640          5208
U             5399312           2984184       2414376
Total         5869672           3192976       2654936

However, the on disk size is a whopping 16 GB database and 6 GB log file.

The culprit is mainly Table U. Most of the data is very static with just lots of UPDATE to existing rows. Table U is the ony with loads of data being added constantly, and being archived weekly.

Table U's definition is,

[ID]      INT                         NOT NULL,
[RD]      DATETIME                    NOT NULL,
[UN]      CHAR (7)                    NOT NULL,
[A]       BIT                         NOT NULL,
[C]       BIT                         NOT NULL,
[S]       DATETIME                    NOT NULL,
[rowguid] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL

It receives about 5 million rows per week, and archives another 5 million rows per week.

It has not been partitioned / placed into separate file groups.

The system has been arround for approximatley 6 months, and I don't believe our DBA team have ever shrunk it / defragged it!

Questions

  1. Why is SQL using so much more disk space than sp_spaceused says?
  2. Is the churn responsible for most of this disk space issue?
  3. How should I use (SQL commands please) partitions and file groups to better optimise this churn?
share|improve this question

1 Answer 1

up vote 2 down vote accepted

Assuming that you did your homework correctly and these tables listed are indeed the way space is used (ie. there is no other table consuming the space) then what you have now is state and you ask about history. The state of the database now is a 16GB file with ~6Gb accounted for and 10Gb unexplained. How did it end up in this state? we cannot answer. There are a number of avenues that could lead up to this:

  • the database could had been created with a 16Gb data file size.
  • there could had been one or more big tables once that now are dropped
  • some operation needed temporary space and could had grown the database to 16Gb size and leave it so

You could consult the history captured in the default administrative trace and in the ERRORLOG and see if some of the info captured can shed light on the mystery.

If I'd venture a guess I would say you have a maintenance task of some sort, perhaps a table rebuild, that runs periodically. Rebuild operations require a great amount of data space as all the data exist for a while duplicated (old index being rebuild and new index being created) and, when the default OFFLINE option is used, they require a similarly great amount of log space as the entire operation is a single transaction. A log of 6Gb, data of approximately 6Gb size and a DB with +10Gb space would point toward that ~6Gb data being rebuild, maybe once manually, or maybe periodically by an index rebuild maintenance task.

Now about your real question: is there a better way to do this? Not an easy one. Such pattern of high churn with large range deletes is indeed very unhealthy. If you can prove that it causes real problems (sorry, but a 16Gb data file is not really a problem, and fragmentation is more often than not a red herring) then there are solutions, the most effective being using a partitioning sliding window for efficient deallocation via switch-out+truncate (The link contains the exact how-to). This is a heavy impact change that would ripple everywhere, including the application code, so do consider all the aspects. See How To Decide if You Should Use Table Partitioning.

share|improve this answer
    
Thank you, looking at the output (there was some truncated data when I ran sp_spaceused), I can see the missing ~10GBs in another couple of tables. The transactions makes a lot of sense. Ref a 16GB file, completley agreed that's not a problem, but every estimate I've done in the past showed 6 GBs of used disk space. There is however another 5GBs, one used by SQL merge replicate, and another 5 GBs used by merge replicate's predecessor. Surprisingly the significant churn has not been the cause, but data replicaiton! –  M Afifi Oct 22 '12 at 11:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.