[dba-SQLServer] SQL Server 2005 - Shrinking databases

Michael Maddison michael at ddisolutions.com.au
Sun Jan 21 21:31:14 CST 2007


NP,

I've found the whole shrink thing rather strange at times too.
What you need to keep in mind is that IIRC shrink only reclaims the log
to the last backup point backwards...
So in effect you cannot reclaim the log that has not been backed up...
of course I could be wrong with the details...  lol

cheers

Michael M

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Monday, 22 January 2007 2:19 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SQL Server 2005 - Shrinking databases

LOL, running out of space is not my problem.  Remember that I have a
terabyte raid6 array here.  The DB in question takes up about 200 gb and
there is well over 600 gb free.

At any rate, I am not sure this is a real problem.  I just closed the
management studio and the whole compact shut down.  Strange in itself.
I then went in and "shrank" just the database file.  Again it finished
in just
a few seconds but did not physically shrink the file itself.    I assume
it
just reclaimed the internal space.  If it can efficiently reuse the
space I will leave well enough alone.

Oddly, when I shrank just the LOG file it DID shrink the file itself.
Ahhh, the mysterious of SQL Server.

I had it on Bulk Logged, but have set it to simple as you suggest.  

At any rate I am back in business now.

Thanks for all the suggestions.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Michael
Maddison
Sent: Sunday, January 21, 2007 9:22 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SQL Server 2005 - Shrinking databases

 Hi John,

While I agree with Stuart...  A couple of things to consider...

Set the logging option to Simple.  This should minimise the log growth.
Don't bother reclaiming database space unless you really really need to.
It will just grow again and growing takes time and will slow you down,
this is especially pertinent to OLTP databases  (not yours).

I've had shrink operations take a long time when it starts to run out of
physical space.
It actually uses a LOT of space to do a shrink on the DB...  IIRC Just
let it run or fail...

cheers

Michael M

1. No idea
2. You should be able to still work with it.  
BOL - :
<quote>
DBCC SHRINKDATABASE
......
The database being shrunk does not have to be in single user mode; other
users can be working in the database when it is shrunk This includes
system databases.
</quote>

3. You can break it into pieces by using DBCC SHRINKFILE

4. It's certainly not necessary if you set the Autoshrink option in the
database properties.
Otherwise, according to MS
<quote>
Microsoft(r) SQL ServerTM 2000 is effective at quickly allocating pages
to objects and reusing space freed up by deleted rows. These operations
are internal to the system and use data structures not visible to users
</quote>

On 21 Jan 2007 at 18:48, JWColby wrote:

> I was doing a log of work in my database this weekend and the file 
> size grew to a couple of hundred gig.  I then "shrank" the individual 
> files.  This happened VERY rapidly, as in a few seconds.  The log file

> had well over 80 gb of empty space and shrank down to just a few 
> megabytes.  I then went back in to my main database and deleted a ton 
> of "temporary" individual tables that I had been working with.  I 
> decided that since it was so quick to shrink again.  Unfortunately 
> this time I made the mistake of "shrinking" the database instead of 
> the database files.  SIX HOURS LATER it is still not done.
>  
> So the first question is, what the heck is it doing that takes six 
> hours where the individual files only took a few seconds?
>  
> Second, is there any way to break out and tell it to give me back
control?
> I don't appear to be able to do anything while it is "shrinking" and I

> have work to do.
>  
> Third, is it possible to determine how long this is going to take, 
> break it into pieces or anything similar.  It seems unreasonable to me

> to completely disable the database for hours on end in order to shrink
a database.
>  
> Fourth, is it even necessary?  IOW, is it like Access where unused 
> space is never reused?  Does it fill back up the now empty space if 
> you don't shrink it?  Does it get fragmented in some way that makes 
> shrinking useful if not absolutely necessary?
>  
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>  
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list