Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kumarich1
Yak Posting Veteran
99 Posts |
 Posted - 2008-05-15 : 08:54:23
|
| Hi,We have a database in production which has free space about 200 GB in Data files and Index files, I want to shrink Data files and Index files.If I do incremental shrink in daytime does it hurt the performance of the database or please advise what is the best practice.thanks |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-15 : 08:59:57
|
| why you have to shrink when you have 200 GB free space? If you shrink database, you are disorganizing index pages . Yes, you will hurt performance |
 |
|
|
kumarich1
Yak Posting Veteran
99 Posts |
Posted - 2008-05-15 : 12:36:28
|
quote:
Originally posted by sodeep
why you have to shrink when you have 200 GB free space? If you shrink database, you are disorganizing index pages . Yes, you will hurt performance
Sorry, I mean if I shrink data files and index files I will get extra 200gb.I want to back up a big database on this server, so I need to shrink data and index files to accomodate backup file.Thanks for your reply. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-15 : 13:04:05
|
| Better to get temporary space for backup purposes or you can backup to network share if you have space. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-15 : 13:05:36
|
| If you do shrink in small chunks, then it shouldn't noticeably impact performance.But you will be causing noticeable performance issues when the files have to expand again. There's a reason why they are that size.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-15 : 13:07:53
|
| Also you can backup your database to multiple files so that you can distribute files accross drives which has space. |
|
|
|
|
|
|
Shrinking database files in production