April 21, 2009 at 3:25 pm
I have an upgraded SQL Server database. We upgraded from the express version to the standard edtiion of SQL Server 2005. The size of the mdf for the database was very large, though I know the database did not need that much data. Our mdf file size was 3.1 GB, but the tables in sum only used about 500 MB. When I ran the sp_spaceused stored procedure it showed three our our tables with a reservedSize much larger than I needed. This basically accounted for the extra space.
My question is how can I shrink the size of this reservedSize? I have tried using DBCC shrinkfile, but that does not free up any of the space. I'm guessing this is because this space has been reserved. I would like a method to alter the table and/or have some other way to change this reserved size for the tables in my database.
Any and all help is very much appreciated.
Thanks,
Michael
April 21, 2009 at 4:19 pm
You can try rebuilding the indexes:
ALTER INDEX ALL ON yourtable REBUILD;
If that doesn't work, verify that you have a clustered index on the table. If you don't have a clustered index, pick a likely candidate and create one (you can remove once completed, but you really should have a clustered index).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 22, 2009 at 9:21 am
Hi,
Thank you very much for your help! It's much appreciated. The clustered index creation fixed the problem. The database is remotely located, so apparently whoever set this up forgot to put on the indexes. Sometimes I guess you overlook the simple solutions. Good thing to file away for next time.
Regards,
Michael
April 22, 2009 at 9:27 am
That is great - glad I could help and thanks for the feedback.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply