DBCC Shrinkfile issues
-
First of all, I know I shouldn't do a shrink file because it causes fragmentation. however, I am working with a 3rd party tool and the vendor would like us to shrink the DB so we can move the DB and upgrade the application.
I am running a SQL 2005 DB on a SQL server 2008 R2 SP2 server.
I am running the following query within the DB that I need to shrink.
USE SCData_Default
GO
DBCC SHRINKFILE ('SCData_Default', 73559);
the size of the DB is 73559 MB. So I'm only trying to shrink the DB right by 200 MB. The query takes ~20 minutes to run. I see a lot of pageIOLatches when i run the Activity Monitor. After a while, I lose connection to the server from my query window and the database was never shrunk.
I've also encountered an error that stated the "database could not locate file for database in 'master' in sys.databases..." However, like I stated previously I am running the query in the DB that I need to be shrunk.
I have also run:
USE SCData_Default
GO
DBCC SHRINKFILE (1, 73559);
with the above issues as well.
The database is in Simple recovery mode.
What can I do to fix this and be able to shrink the DB?
-
If you have LOB data your shrink will be slow
http://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/
Paul Randal's shrink alternative is the method I use when I have to reclaim space from my files.
http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
So what if you *do* need to run a shrink? For instance, if you’ve deleted a large proportion of a very large database and the database isn’t likely to grow, or you need to empty a file before removing it?
The method I like to recommend is as follows:
•Create a new filegroup
•Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
•Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)
-
Depending on what your recovery model is, you may not be able to truncate any data from the transaction log until a transaction log backup has been run. What is your recovery model?
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455 -
The database is in full recovery mode. I have checked to see if a transaction log needed to be taken and it didn't.
-
The presence or absence of a log backup will make absolutely no difference in this case as you're shrinking a data file, not a log file.
LOB data causes shrinks to run slowly, as do heaps with nonclustered indexes on them. If you have lots of either of those then you'll just have to be patient.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply