Stuart McLachlan
stuart at lexacorp.com.pg
Sun Jan 21 18:25:57 CST 2007
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® 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 >