Post reply

Large amount of "unused" space on table

  • February 6, 2012 at 8:42 am

    #100438

    I have been trying to identify why the BAK file for my live database is still so large after many rounds of data housekeeping. There is one table in particular that is large and I currently have a SQL job running every 30 minutes that is deleting the oldest data (this table tracks user activity on our website so it grows very rapidly). I have noticed that the table has 7GB of space allocated to it and sp_spaceused on the table shows 2GB of data, 0.5GB of index and 5GB is unused. There is 1 clustered index and 2 non-clustered indexes on the table.

    Couple of questions:

    1) Does this unused space affect the size of the backup file .BAK or just the MDB? It is only the size of the BAK file I'm concerned about because we pay for our backup by the GB and the MDB is excluded from the backup routine. So if it does:

    a) What is this unused space likely to be caused by?

    b) how to I reclaim it?

    c) Do any of the methods do deal with this require the database to be in single-user mode or can they be run without affecting client access?

    Apologies if these questions are rather obvious but we've been running SQL 2000 for so long without the need for much maintenance I am somewhat rusty!

    Thanks in advance.

    Julia

  • February 6, 2012 at 8:48 am

    #1443223

    Rebuild the clustered index. See if that fixes things.

    Does the table have a LOB column?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 6, 2012 at 9:11 am

    #1443240

    Thanks for the fast response. There are no LOBs in this particular table. The clustered index is the PK so should I used DBCC DBREINDEX on the table and am I right in thinking the DB will need to be taken off line to do this? Does this operation require additional drive space to run, if so what proportion of the original table/index is needed?

    Thanks so much.

    Julia

  • February 6, 2012 at 9:14 am

    #1443242

    Yes, DBCC DBREINDEX and no, the database does not need to be taken offline (and if you mean the database state OFFLINE, reindex won't be able to run). That said, the table will be unavailable for the duration.

    Probably 120% of the table's space free in the data file, if you're in full recovery about the same in the log file.

    But then, if maintenance is done regularly on the database, you should know how long index rebuilds take and how much space they need from previous times it was done.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 6, 2012 at 9:45 am

    #1443272

    Excellent! Thanks for your help.

    Julia

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply