Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ptegan
Starting Member
7 Posts |
 Posted - 2005-05-02 : 04:33:11
|
| Hi...this is the first time I haven't been able to find a solution here without having to post my problem but it's probably worth registering to say "thanks" for all the other times :)I have a database (SQL2000) that's about 68Gb in size. After a few days I weeded out the info that wasn't needed and removed the unused indexes. One or two 'drop tarbes' here and there and now I have only 18Gb of data and 4Gb of free space.My problem is that I can't seem to get SQL to give this back to the OS. I tried compacting/shrinking via Enterprise Manager. When I open up the tool and click on 'files' and then choose my data file, I can't reduce it to the min size (18Gb) though it does recognize that as being the smallest file available.The DBCC SHRINKDATABASE (base,10) doesn't seem to work either, with or without notruncate/truncateonly. When I run it on another base on the same server it displays the results after a few momements. But on the database with the problem it just gives me a message after 6 seconds everytime saying that the procedure correctly ran and I should contact my sys admin in case of problems.Before I used 'drop table' to get rid of old tables, the shrinkdatabase command always worked before but now it just doesn't seem to want to.Any ideas out there? |
|
|
pentiumsingh
Starting Member
9 Posts |
Posted - 2005-05-02 : 06:48:32
|
| hi try this dump tran [databasename] with no_log go dbcc shrinkdatabase ([databasename])go and please let me know the output |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-05-02 : 14:29:03
|
| Try DBCC SHRINKFILE(fileName)You can find the logical filenames with the sp_helpfile system proc.rockmoose |
|
|
|
ptegan
Starting Member
7 Posts |
Posted - 2005-05-03 : 06:18:12
|
| pentiumsingh THANKS ! Your solution worked but I'd like to know why :)I had been running two commands :backup log (databasename) with truncate_onlygodbcc shrinkdatabase (databasename, 10)gobut it wasn't working. What's the difference between yours and mine? :) |
|
|
|
|
|
|
Unable to shrink database