Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC Shrinkfile issues Expand / Collapse
Author
Message
Posted Friday, April 5, 2013 2:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:37 PM
Points: 64, Visits: 335
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?
Post #1439480
Posted Wednesday, April 10, 2013 2:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 906, Visits: 2,868
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)




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1440696
Posted Saturday, April 27, 2013 11:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
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/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1447312
Posted Monday, April 29, 2013 6:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:37 PM
Points: 64, Visits: 335
The database is in full recovery mode. I have checked to see if a transaction log needed to be taken and it didn't.
Post #1447468
Posted Monday, April 29, 2013 6:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1447480
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse