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 |
|
propanecan
Yak Posting Veteran
60 Posts |
 Posted - 2007-01-10 : 15:26:27
|
| I'm having trouble shrinking the primary filegroup in a development database. The database has three data files, one .mdf and two .ndf files. Using SHRINKFILE I can shrink down both .ndf files, however the .mdf command runs, but does not appear to shrink the file. |
|
|
Gopher
Yak Posting Veteran
83 Posts |
Posted - 2007-01-12 : 03:37:50
|
| Hi propanecanHave you tried this in Query analyser or via the Enterprise manager?I have had a similar issue with or DB (which is around 1.6TB) and basically what I did was:Run a DB reindex on you largest tables: In query analyser - dbcc dbreindex ('<TableName>','',0) to free up the space in the tables.In Enterprise manager shrink the files individually and set the 'Truncate free space from end of file option' or write in is transact SQL and run it as a job overnight (if you DB is active during the day!)And it worked, we manage to reclaim 270GBHope this helps!Gopher |
 |
|
|
propanecan
Yak Posting Veteran
60 Posts |
Posted - 2007-01-12 : 14:41:41
|
| Thanks for the response.I'm using Query Analyzer. I also tried to shrink the files individually in EM with the same results.Let me run a DB reindex on the largest tables and follow your other suggestions and see what happens. |
|
|
|
MohammedU
Posting Yak Master
145 Posts |
Posted - 2007-01-13 : 01:48:03
|
| Does your mdf file has any tables with LOB columns? if yes take a look the following article..DBCC SHRINKFILE and SHRINKDATABASE commands may not work because of sparsely populated text, ntext, or image columnshttp://support.microsoft.com/kb/324432MohammedU |
|
|
|
propanecan
Yak Posting Veteran
60 Posts |
Posted - 2007-01-17 : 14:22:12
|
| I ran a full dbreindex on this database over the weekend. Then ran the shrinkfile command again only this time it was successful. So it's likely that even though the database recognized the empty space it was unable to shrink the file until the tables were defragmented. |
|
|
|
|
|
|
SHRINKFILE blues