June 6, 2012 at 10:57 pm
Hi,
I am planning to shrink a database and the server is running 24/7 providing services to end user.
My question is :
1. Will shrinking impact on server performance?
2. Will the logs be recorded during shrinking?
June 7, 2012 at 1:19 am
First off I will say that you first should look at getting more disk space allocated to your servers.
Shrinking a database is a costly operation and causes a range of issues within the database which will require you to rebuild all your indexes, which will force your database to grow, which could grow larger than already sized. Now if you cant rebuild your indexes online (Standard edition, or XML, BLOB etc columns in indexes) then it will significantly affect availabilty and performance.
I would first look at if you have any free space in your databases, if not a shrink wont help you.
If you do, work out how much space it will take to rebuild your indexes, typically 2.2 times the size of the index.
Create a test environment which mimics production, shrink and rebuild to see how long it will take.
June 7, 2012 at 1:36 am
Yes and yes. Shrinking a DB is not recommended and should only be done if there's been some huge archive or delete or data.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply