June 15, 2004 at 5:54 pm
I have a large database from which I deleted all the big tables to make a smaller version for testing. I tried to shrink the db from EM in SQL2K. It still shows 95% free space in the db after the operation. What does 'shrinkdb' do? Does it shrink the data files? If not, please let me know how. Thanks a lot!
June 15, 2004 at 8:55 pm
What precentage free spaces do you expect after the database shrink? It shrinks the size of the data files in the specified database to desired percentage of free space left in the database file. It does shrink the database files.
June 16, 2004 at 9:58 am
All I need is 5-10% of free space. But it gives me more than 90% of free space. So the data files didn't seem to be shrinked. Any idea?
June 16, 2004 at 10:52 am
Try this:
1. In Enterprise Manager, find the database. Right click on it and select Properties. Go to the Data Tab and find the name of the data file. Do the same thing for the log.
2. In Query Analyzer, run these commands:
USE <dbname>
GO
DBCC SHRINKFILE ('<datafilename>', 10)
GO
USE <dbname>
GO
DBCC SHRINKFILE('<logfilename>', 10)
GO
Replace the <dbname> with your database name, <datafilename> with the actual filename for the data, and <logfilename> with the actual filename for the log.
-SQLBill
June 17, 2004 at 5:32 am
you may have to restart the sql server service, to show/commit the changes after dbcc shrinkfile, don't ask me why
p.s. the 10 in "DBCC SHRINKFILE('<logfilename>', 10)" refers to the size you want the file to be shrunk to although you shouldn't be able to shrink it to less than the actual sign it will winge at you
Max
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply