[dba-SQLServer] SQL Server 2005 - Shrinking databases

Jim Lawrence accessd at shaw.ca
Mon Jan 22 13:06:37 CST 2007


Hi John:

I was talking with a fellow from Calgary a while back and their company
removes all the indexes when manipulating large tables that will change the
indexes. If there is to be a lot of modifications one script is designed to
remove the indexes and another one is created to add them back. Sometimes
the most processing time on a project is waiting for the indexes to rebuild.

Jim  

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

Interesting.  Is there a way to turn that off?  I didn't specifically tell
it to, OTOH I didn't tell it not to either.  I just assumed that it was
going to copy everything real into a new db file, leaving out the now empty
space. 


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 Jim
Lawrence
Sent: Monday, January 22, 2007 7:04 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SQL Server 2005 - Shrinking databases

Hi John:

A lot of the speed problem is related to the rebuilding of the keys and
indexes. Logs file do not have any. It sounds like it is rebuilding the
index as it goes.

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Sunday, January 21, 2007 7: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

_______________________________________________
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