Michael Maddison
michael at ddisolutions.com.au
Sun Jan 21 20:21:47 CST 2007
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