Shrink Db | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Shrink Db

Hi , 1. Why the shrink file or log hurts performance ?
2. What is the guideline to shrink trans log /db ? I have a OLAP Db which run ETL
on every midnight to upload data. After the ETL complete , the A. tempdb
B. livedb trans log keep growing. Currently , I shrink it on weekly basis. 3. Should I perform Shrink DB after DBCC Reindex or DBCC IndexDefrag ? Thanks ,
Travis
is it possible your to restart sql server service after all the opeations. Then temp db will get to its normal size —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

1. Shrink operation is bit intensive and in case if the server is already stressed with the queries then it will have the performance issue. The log file should be taken care to keepup the size in order to accomodate the newer transaction log. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Hello Travistan You already got answers for the case of log file. I am trying to give my view on this, it is not advised shrink file frequently if ‘autogrowth’ option is enabled. Consider a database which has auto growth option enabled. The data file grows whenever needed and at the same time if you schedule shrink file frequently, you are again shrinking it after growth. This frequent growth and shrinking of databases is nothing but moving the data pages and if you do that frequently, this might cause of fragmentation in your databases. In simple words you are trying to do 2 opposite things at the same time. If you still want to shrink the dabases very frequently check for any fragmentation now and then. Hope this helps
Satya
I have re-read the question and it seems you are mixed up with transaction log and TEMPDB sizes due to the ETL process you have overnight. See the bloghttp://sqlserver-qa.net/blogs/tools…ks-and-transaction-log-is-filing-up-help.aspx to get a better understanding.
Wth regard to Q3, how often you are performing the reindex on the database and are you including all the tables in re-org? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>