Jim Lawrence
accessd at shaw.ca
Mon Jan 22 19:25:58 CST 2007
Hi Arthur: Thanks for the comment but I do not think John is talking about importing data as much as he is about modifying the existing data within the tables. If I am wrong I stand corrected. Regards Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com Sent: Monday, January 22, 2007 5:11 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQL Server 2005 - Shrinking databases My experience is contrary, Jim. IME, drop the indexes, do a bcp and then rebuild the indexes. That will almost always be quicker. A. ----- Original Message ---- From: Jim Lawrence <accessd at shaw.ca> To: dba-sqlserver at databaseadvisors.com Sent: Monday, January 22, 2007 2:06:37 PM Subject: Re: [dba-SQLServer] SQL Server 2005 - Shrinking databases 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 _______________________________________________ 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