September 22, 2008 at 1:07 pm
I recently read somewhere (could have been in BOL or even in a post on this forum) that running the Shrink Database/File opertaion in SSMS 2005 can cause Indexes to become fragmented, including Indexes that were just de-fragged via one of the defrag operations like the 'ALTER INDEX' T-SQL command.
I was just curious if anyone could verify if this is applicable only when trying to shrink the database or the data file. If I'm running the shrink on just the Log file then the operation should not have any effect on table indexes, correct?
Thanks
Kindest Regards,
Just say No to Facebook!September 22, 2008 at 1:54 pm
You get inden fragmentation from shrinking the data file. If you repeatedly shrink and grow the log though, you'll get fragmentation within the log.
The transaction log consists of a number of virtual log files. If the log grows in repeated small amounts, you'll have lots of these virtual log files. If the file is created at the size it needs to be and doesn't have to grow, then there will be a fairly small number of virtual log files.
Lots of VLFs can make your backups and database recoveries slower than they should be.
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 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply