February 5, 2009 at 9:11 am
In SQL Server 2005 Standard Edition, I have a nightly job that executes the "Rebuild Index Task" for all user databases. After one particular execution of this job, the database bloated up to 100+GB more than the normal 45GB size of the database. This is not unused allocated space, it shows up as used space. The job failed due to insufficiant space on the disk which was 100+GB free the day before. I am wondering if, due to the failure, extra objects were left out there to bloat the database size and they need to be cleaned up/deleted in order to reduce the database size. And if so, how would I identify those objects/copies of indexes, in order to drop them?
No other jobs are running that would have populated tables or added data to use that space.
Please advise or if you require more info, just let me know.
Thanks!
Tall Paul
February 5, 2009 at 9:25 am
Have you run an integrity check on the database? That's where I'd start with what you're describing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2009 at 9:31 am
Do you know what file(s) is using the space? Is this strictly the data file that grew or is the log file a part of the size that you are referring to? If it is the log file, that is to be expected during the index rebuild / reindex process and backups should be used to manage that size growth.
Post back with what you find.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 5, 2009 at 9:35 am
Yes, the log was big as well. But it has since been shrunk. The database file is the problem. I don't understand what has caused it to grow AND make it used, allocated space.
February 5, 2009 at 9:38 am
So, when you look at the output of sp_spaceused is the output not what you expect for data structures and index structures?
Might be wise to take GSquared's advice as well.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 5, 2009 at 10:19 am
No, it is not what I expected. But this just in from the users..................
There "may" actually have been a job that populates tables that may have doubled the row counts of several tables. They deny that this is the problem though. :crazy:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply