January 8, 2004 at 9:10 am
I have a job scheduled to run twice a week at midnite to shrink a large database.
DBCC SHRINKDATABASE ([Employee, 20])
Problem is that this seems to be a logged procedure because I backup the tranlog at 9PM and the next tranlog backup is at 9AM at it is 3.2GB.
This seems like a flaw when you log that many transactions while shrinking your database?
Is there anyway to shrink the db without logging everything?
Thank you much.
Randy
January 8, 2004 at 9:50 am
How large your log backup typically from 9PM to 9AM without performing database shrinking?
DBCC SHRINKDATABASE moves the data and index pages within the database files and such activites should be logged for recovery purpose.
January 8, 2004 at 9:52 am
I agree with Allen. I might also run a couple more log backups in the middle of the night.
January 8, 2004 at 9:56 am
Check out the BOL, use the Index tab and enter DBCC SHRINKDATABASE. Select the Transact-SQL option and scroll down to Remarks. Find the paragraph that starts with "Because a log file can only be shrunk to a virtual log file boundry...". That paragraph and the one following may be of help.
-SQLBill
January 8, 2004 at 10:50 am
Typical 9AM log bkps are 10 - 30MB.
It just seems odd to log a transaction when sql is just moving data pages and index pages without changing any data rows; you would want to recover the data, not particularly what page it was previously on. Even still, I don't think that many pages have changed since I run this twice a week.
I guess running another log backup after the shrink job completes and before business hours would be the thing to do.
Thank you all for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply