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 |
|
samrat
Yak Posting Veteran
94 Posts |
 Posted - 2004-11-29 : 18:05:23
|
| Hello Team, Just wanted to know your thoughts on what should happen in a following scenario.1. Highly Fragmented Database2. Run DBCC DBREINDEX on all the tables and rebuild the index and reduce fragmentation. (Executing this step increase the size of data files)3. Run DBCC Shrinfile on Individual data files to reclaim the increased space in step 2. Now my question is would running DBCC Shrinfile cause fragmentation in the database? If yes, what is the reason for the same.Thanks & Regards,Samrat |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-29 : 18:07:15
|
| You should only run DBCC SHRINKFILE or DBCC SHRINKDATABASE if you know you won't need the space again in the near future. The reason being is that you receive a performance penalty when the shrink occurs. So if you need it in the near future, leave it.I'm not aware of fragmentation occuring due to shrinking.Tara |
 |
|
|
samrat
Yak Posting Veteran
94 Posts |
Posted - 2004-11-29 : 19:54:22
|
| Good Point Tara, I forgot to mentoin that we had our system offline during that stage. Looking at our growth we had heaps of spare space in file so I decided to trim it a bit to make all the files of similar sizes. We had a small discussion within our DBA team as some DBA's were of the opinion that Running Shrinkfile would cause database fragmentation and hence deafeating the purpose of running DBreindex. Samrat |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-30 : 01:35:08
|
| I believe Shrinkfile can move pages towards the begining of the file, to enable truncation I suppose. And maybe that mucks up the neat & tidy arrangement of pages.I'm doing battle with a database at the moment on a server that is almost full, very heavily loaded, and in need of daily optimisation balanced between fastest performance and careful use of disk space. I haven't found the answer! and still my LDF file is bigger than my MDF :-(Kristen |
|
|
|
|
|
|
DBCC Shrinfile and Database Fragmentation