December 10, 2007 at 9:30 am
SQL Server 2000, SP4.
I have a stored procedure that basically does this:
Truncates a table Analysis; for each id in the Transactions table calculates a set of aggregate values and insert one line in Analysis. So basically Analysis has only inserts on it. The job runs once per week. Up to this weekend the final table after all IDs were summarized and inserted was around 600 GB. Out of the blue this weekend Analysis is around 10 GB. No dramatic change in the data happened and from what I know the code wasn't change recently for the procedure that fills up ANALYSIS. If I run sp_spaceused on Analysis table I get something as below:
[font="Courier New"]
name rows reserved data index_size unused
-------- ----------- ------------------ ------------------ ------------------ ---------------
ANALYSIS 206201 5485104 KB 748248 KB 4752 KB 4732104 KB
[/font]
As you can see, the space reported as unused is very high, more than 6 times higher than data, which makes the table extremely large. Before the last execution of the job the unused space was less than 1000 KB for comparable number of rows.
Did anyone have this kind of problem? What may be the cause and how can I make things normal again?
Any help will be highly appreciated.
Thanks.
Gabriela
December 10, 2007 at 9:51 am
sp_spaceused does *not* reports the actual values unless you update the ussage of the the database ( dbcc updateusage (0) ) because of that sp_spaceused gives you an extra parameter ( @updateusage ) in which you can specify *if* you need the up-to-date usage.
you can recover the space by DBCC REINDEX ( tablename,'',100)
cheers,
* Noel
December 10, 2007 at 9:57 am
UPDATEUSAGE doesn't make much of a difference, the unused space is still huge. The question was why does it happen, because it didn't happen before...
December 10, 2007 at 10:40 am
Another thing: I do not have any indexes )yet) on the table, so DBCC DBREINDEX won't work.
And what is even more strange:I restarted SQL Server and everything is back to normal. The table is under 700 MB with unused space in tens of KB...
Gabriela
December 10, 2007 at 12:31 pm
Without indexes your table could be *very* fragmented ... so clustered index will put it under control. The fact the a reboot "fixed" the problem is something to be really concerned about. I know update usage will fix the "actual" vs the current estimates but I have no explanation for a reboot fixing table usage 😉
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply