December 1, 2005 at 1:26 pm
We have SQL Server 2000 (SP3). I have executed the first DBCC Command below.
--Used Terminal Services when running the next two commands.
--Relocate used pages from above the target_size line to the front of the file.
DBCC SHRINKFILE (PEMaster_Data, NOTRUNCATE)
--Causes any unused space in the files to be released to the o/s and shrinks the file to the last allocated extent, reducing the file size w\o moving any data.
DBCC SHRINKFILE (PEMaster_Data, TRUNCATEONLY)
The Database Size is 156 GB. The first command has been running for 6 hours. I would like for the command to complete and then run the second command to reclaim the unused space. But, it looks like I may have to cancel the first command. Is it okay to stop the DBCC SHRINKFILE (dbname, NOTRUNCATE) Command while it is executing? Would this cause any problems? Is it normal for this command to take this long on a database of this size? Would the second DBCC Command execute faster?
Thanks in advance, Kevin
December 1, 2005 at 1:53 pm
It is safe to cancel at any time.
It works as single transaction, probably it can take some time for cancellation as well.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
December 2, 2005 at 7:13 am
Just an FYI - I have found that shrinking in smaller amounts will speed things up considerably. No more than 2GB at a time seemed to be optimum for our environment.
Determine a reasonable target size and loop using the smaller amount until you reach the target.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
December 2, 2005 at 12:09 pm
It's totally safe. The previous post about it working as a single transaction is wrong - it works as a series of very small system transactions so there is nothing to rollback.
Why are you shrinking in the first place? There are very few scenarios where shrinking is sensible (following a massive data delete when the DB won't grow again) because shrinking causes fragmentation and usually the DB will just grow again. See SS2005 BOL where I've explicitly recommended not shrinking.
Regards
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 2, 2005 at 2:58 pm
Paul thanks for your reply. I was shrinking the database because of a large data delete (100 GBs) with the 2nd DBCC Command below. I was trying to execute the 1st DBCC Command below to move the used pages to the froint of the file but I cancelled it after over 6 hours of execution. The database was 156 GBs. Is there a dbcc command I can execute to remove the fragmentation which you say is caused by the DBCC SHRINKFILE Command? Are there any other commands which I may execute to make sure the database is running efficiently as possible? Would moving the used pages to the front of the database increase efficiency?
--Used Terminal Services when running the next two commands.
--Relocate used pages from above the target_size line to the front of the file.
DBCC SHRINKFILE (PEMaster_Data, NOTRUNCATE)
--Causes any unused space in the files to be released to the o/s and shrinks the file to the
----last allocated extent, reducing the file size w\o moving any data.
DBCC SHRINKFILE (PEMaster_Data, TRUNCATEONLY)
Thanks in advance, Kevin
December 2, 2005 at 4:22 pm
Hi Kevin,
Removing the (index) fragmentation should only be done if the fragmentation is going to affect the perf of your query workload. Basically, if the fragmented indexes are involved in range-scans then you should do it. Have a look at the whitepaper we wrote below that has a lot more detail:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
As far as how to make your db more efficient - that's more than we can go into in this medium. I suggest you search on the Internet or get one of the many books on the subject.
The answer to your last question is possibly, depending on the query workload and db schema - again see the various resources on perf turning.
BTW, you don't need to issue two shrink commands - just issue one without any options and it'll shrink then truncate.
Be aware that shrink is not a terribly efficient operation and is single-threaded so depending on the concurrent workload and bandwidth of your IO subsystem it could take a long time to run. In SS2005 shrink has built-in progress reporting so you can work out how much longer it has to go (as does DBCC CHECKDB)
Hope this helps.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply