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 |
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
 Posted - 2010-11-27 : 16:33:05
|
| As a result of regular heavy transaction the MDF has grown and this is a result, needs to be shrinked. It has done with shrink,but later again got to grow resulting reindex process.I knew this is required to keep the transaction to stay in the data file system, but transaction which has grown as a result of Reindex process should be trimmed.Please advise. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-11-27 : 16:49:40
|
| Thanks for quick answer Tkizer.I know but this is purely Unwanted space as a result of the 'transaction' needs to be released.If the database is in FULL recovery mode the transaction is written to LDF file and later back to MDF; Recovery Mode is SIMPLE then it is directly writting to MDF but no evidence of empty segments being released.I am quite confused with this nature.Thanks for your time and patience. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-28 : 03:39:42
|
| If you shrink a file and it grows back again that is a sign that your system needs that space - in which case don't shrink it again!Shrinking will fragment the index you just carefully rebuilt, and performance will suffer.You will also fragment the file on the disk if you repeated Shrink / Grow the file. That will make it slower too,."If the database is in FULL recovery mode the transaction is written to LDF file and later back to MDF; Recovery Mode is SIMPLE then it is directly writting to MDF but no evidence of empty segments being released."The difference between FULL and SIMPLE Recovery Mode is that in SIMPLE the data is written to the LDF (same as FULL) but when the transaction is committed, and the next CHECKPOINT occurs, that space in the LDF is immediately made available for reuse.If FULL Recovery Model the space only gets made available for reuse when you backup the LDF file. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-28 : 08:22:44
|
quote:
Originally posted by dbalearner
If the database is in FULL recovery mode the transaction is written to LDF file and later back to MDF; Recovery Mode is SIMPLE then it is directly writting to MDF but no evidence of empty segments being released.
That's how Exchange logs work. It is not how SQL logs work. Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]and [url]http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url]--Gail ShawSQL Server MVP |
|
|
|
|
|
|
MDF Growth