Erratic table storage requirements

[rt63]

Hi,

We are on SQL 7 and have been facing a peculiar problem. We have a table which contains approx .5 million records. We perform all 3 kinds of operation on the table Insert/Update and Delete but the steady state size is .5 million records only.

As per the table size calculations, it should be taking approx. 100 MB of data space but we discovered recently that it was actually accounting for 22 GB of space. It has a primary clustered key and besides that there are no indexes. The database has auto-update statistics on and the fill factor for primary index has been defined as default 0.

We could not figure out the reason for this excessive disk space utilisation. So we copied the entire table data to a temporary table and truncated the original table and copied back data from the original table and now the table was taking up disk space as expected (100MB only).

Now after 15 days of subsequent operations, again the table size is .5 million records only but the storage space is 5 GB. Has anyone faced similar problems? Any clues what starts consuming the storage space? We have very frequent queries on this table - 2000+ in a day - does auto update statistics or query caching have anything to do with it?

Hoping for some ideas/solutions.

RT

 

[AWithers]

I think the problem lies with the primary clustered key. All tables with indexing of any sort can rapdily grow in size as changes are made to them(regardless of the number of records). I can't admit to knowing all the ins and outs but its a similar situation in Access where databases can rapidly grow in size when additions/deletions etc are made and they need to be regularly compacted to reset the indexing.

I think the way round may be to drop and re-create the key.

Andy

 

[rt63]

We also thought so. Even did a reindex of the table / drop and recreate of primary clustered key but it did not help - only marginal recovery. The size reduced only after we truncated the table and copied data back to it. I think the problem is something else.

Thanks anyway

RT

 

[SQLBill]

I don't see any mention of you doing backups. One thing that might be causing the large size is the transaction log. If you aren't doing backups, then the transaction log will keep on growing and can become larger than the data itself.

-SQLBill

 

[rt63]

Hi SQLBill,

U r right. Transaction log grows if we do not have regular backups. But we do a backup every night. Moreover, transaction log is a separate file by the extension .ldf - what I am talking about is the main database .mdf file which expands erratically.

Thanks anyway for ur response

RT

 

[codestorm]

i was under the impression the deleting data from a table didn't remove the used space UNTIL the table was trucated .. codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[rt63]

Hi codestorm,

Thanks for response. Maybe u r right. To recover, the full space, we need to truncate the table. When, I mentioned the 22 GB table size, it contained 3.5 million records. We brought it down to .5 million records by deleting (not by truncating and re-inserting) and the table size came done to 5 GB. So about 17 GB space was indeed released without having to truncate the table. The issue is that why it did not come to 100MB as expected from table definition etc. So a maybe a truncate is required to recover the balance.

Fortunately for us, this table does not have any tables referring to it for FK checks and we can do a periodic truncate table etc. But we have some other tables where truncating the table will become a little messy. Is not there a cleaner way of recovering the unused space?

RT

 

[SQLSister]

Did you try DBCC SHRINKDATABASE with the TRUNCATEONLY option?

 

[rt63]

Hi SQLSister,

Thanks for your suggestion.

Yes we have a daily job for shrinking the database. In fact we are using, DBCC SHRINKDATABASE with no option and according to the help


DBCC SHRINKDATABASE with neither option is equivalent to a DBCC SHRINKDATABASE with the NOTRUNCATE option followed by a DBCC SHRINKDATABASE with the TRUNCATEONLY option.
The NOTRUNCATE option, with or without specifying target_percent, performs the actual data movement operations of DBCC SHRINKDATABASE including the movement of allocated pages from the end of a file to unallocated pages in the front of the file. However, the free space at the end of the file is not returned to the operating system and the physical size of the file does not change. Therefore, data files appear not to shrink when the NOTRUNCATE option is specified.

The TRUNCATEONLY option reclaims all free space at the end of the file to the operating system. However, TRUNCATEONLY does not perform any page movement inside the file or files. The specified file is shrunk only to the last allocated extent.


So basically a combination of both options is required which is what DBCC SHRINKDATABASE without any option does.

I have one small doubt on the scheduled job we have made for the shrinking database. Though the job is shrinking the main database , the job is invoked through master database. I have changed in to the main database itself. Will see if it makes any difference though I dont think it should.

RT