SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC Shrinkfile issues


DBCC Shrinkfile issues

Author
Message
Laura Schmalzbauer
Laura Schmalzbauer
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 355
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?
Sean Pearce
Sean Pearce
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4172 Visits: 3436
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
Calibear
Calibear
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 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
Laura Schmalzbauer
Laura Schmalzbauer
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 355
The database is in full recovery mode. I have checked to see if a transaction log needed to be taken and it didn't.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230366 Visits: 46344
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 recoverability

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search