April 8, 2011 at 10:26 am
I have another thread about 6 or 8 threads down about doing the output clause with a delete statement, this is along the same lines as that but a different problem.
I had an archive job run last weekend that selected MILLIONS of rows from 3 different tables in our primary database, and it was going to copy them over to my archive database, then delete the originals from the primary. The job took MUCH longer than anticipated, and ended up causing a bunch of problems and we ended up having to recycle our SQL Server. That, in-turn, caused my entire archive job to rollback everything that it had done.
Now here's the problem, prior to running the archive job, our primary database size was 405GB and the log file was approximately 60GB. The archive database was approximately 80GB and that log file was very small, probably 100MB.
AFTER the job ran, and got rolled back, the primary database grew from 405GB to 650GB, and the archive database ALSO grew from 80GB to 270GB. Both log files stayed about the same.
So, first, any idea why the databases grew so much? First thought is that the job had already started inserting the new rows of data (ie created the space in the database), but since it got rolled back, it cleared the data from that database without "shrinking" it back down to the original size (this is for the archive). As for the primary, my guess is kind of similar, it started to clear out the space where the records got deleted, but since it had to rollback, it created new entries instead of using the space that was already allocated for those records.
Are my assumptions correct as to why BOTH databases grew? (The growth size is kind of expected, not quite that much, but expected)
As for the cleanup, I have some daily reports (thanks SQL Server Central) that spit out index sizes, database sizes, etc. None of the indexes for the primary databases tables grew like I originally thought (I thought the indexes is where the bloating would have occurred). So where did all of this extra space go, and how do I get rid of it? It doesn't appear as though an index rebuild will fix the issue since the indexes didn't grow in size, is there something else to check, or to run that could get rid of all this waisted space?
Thanks to ANYONE that can help with this problem....it's causing us some issues with backups and the space that we have available on our SAN.
April 8, 2011 at 4:21 pm
Hi,
it depends on the table definition especially the index definition of the table(s). So please post the table definition with the index information. If you have a clustered index set on this table a rebuild index fix the issue of the fillfactor. Look at the fillfactor of the table so you can see whether it is a problem or not.
But if you have a heap it is a little more complicated.
And how much free space is available in the data files? Or have you posted the net space?
Greets
Patrick Fiedler
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply