February 18, 2010 at 10:12 am
I'm the OP and it seems my situation is not typical.
The database is used for syslogs. Data is periodically truncated from tables and this leaves a lot of free space.
February 18, 2010 at 10:19 am
Ser Tharg (2/18/2010)
The database is used for syslogs. Data is periodically truncated from tables and this leaves a lot of free space.
The free space will be reused though? If it's a logging DB then there will be more data added after the truncations?
The only reason to shrink is if the space freed by the archiving/deletion will not be reused. If you're expecting the tables to be populated again, don't shrink. There's no benefit in having free space in the filesystem as opposed to the database file.
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
February 19, 2010 at 7:22 am
In my situation I'd expect the space freed not be reused. As I mentioned I don't think my sitiation is typical!
In any event - an eye -opener for me many thanks for replies! 🙂
February 19, 2010 at 7:36 am
Ser Tharg (2/19/2010)
In my situation I'd expect the space freed not be reused.
Not ever?
In that case, do a once-off shrink followed by a rebuild of all indexes. I've got no problems with doing a shrink when you've cleaned out a lot of data that won't be replaced, provided it's followed by a rebuild, but shrink should never be scheduled in a maint plan.
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
February 19, 2010 at 8:47 am
This is news to me, so a very useful topic!
I thought SHRINKDATABASE was a good thing to do, because it reduces a large and useless transaction log file at the end of a SSIS data load. Useless because there is at that point nothing I'd ever roll back; in event of fault I'd restore from backup and/or reload the original data. Size mattered because during development we were copying the database+log files between servers over a fairly slow link.
Being new to SQL Server, I had naively thought that SHRINK would either defragment the indexes, or at least copy them as-is. What does it do that actually makes them more fragmented?
February 19, 2010 at 9:08 am
David Data (2/19/2010)
Being new to SQL Server, I had naively thought that SHRINK would either defragment the indexes, or at least copy them as-is. What does it do that actually makes them more fragmented?
Shrink on the data file works page by page. It takes the page closest to the end of the file and moves it as far to the beginning of the file as it can. Repeat with the next page and the next, etc. Hence it can completely reverse the order of an index. It's purely concerned with moving pages as early in the data file as possible, it doesn't care what indexes they are part of.
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 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply