September 16, 2015 at 3:07 am
Kristen-173977 (9/15/2015)
ScottPletcher (9/15/2015)
Can't imagine why SQL would automatically issue a checkpoint just because an index is rebuilt. I have no idea whether a maint plan does or not, although, again, I can't imagine why it would.So a maintenance plan is going to perform ALL its index rebuilds (which for the standard maintenance plan may well be?? a rebuild on every index on every table in the DB) as a SINGLE transaction?
No it doesn't, and I'm not sure how 'doesn't perform an explicit checkpoint' = 'runs within a transaction'.
And index rebuilds are an unlikely candidate as they are minimally logged in simple recovery model.
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
September 17, 2015 at 6:17 am
We had a similar issue. I used the Shrink file option in SSMS - "Empty file by migrating to other files in similar filegroup." That worked for us. We were able to then shrink the log files and data files, and it released the space back to the operating system.
September 17, 2015 at 6:33 am
1) Don't shrink data files without a good reason. It'll cause massive index fragmentation
2) Shrinking should be done only when something has resulted in a log/data file far larger than will be needed again, not as a regular process
3) Shrink with the EmptyFile option is for when you're going to drop a data file. It tells SQL not to put data into the shrunk data file again, it's not something you'd use for a regular shrink
4) EmptyFile has no effect on log files, they cannot be emptied by a shrink operation.
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 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply