April 10, 2006 at 1:16 pm
I am having issues with transactional replication and my maintenace plan. When my maintenace plan (optimization and integriity) runs it blows up my replicated db transaction log. I am not sure how to handle this situation. We are running SQL 2000 sp4. We are running into space issue, lack there of. I need to run the optimization and integrity checks but I cannot run out of room. The db we are replicating is in simple recovery model.
Thank you,
Carol
April 11, 2006 at 2:57 am
Hi Carol,
This isn't my script - I got it on this site. But it works for the optimization side.
EXEC sp_MSforeachtable @command1="DBCC DBREINDEX ('[yourdb].?') WITH NO_INFOMSGS CHECKPOINT"
BACKUP LOG WUMS WITH TRUNCATE_ONLY -- There should be nothing to truncate
DBCC SHRINKFILE (yourdb_Log',500) -- You can adjust for a minimum log file size
Regards
Eoin
April 11, 2006 at 4:45 am
Quick question, what WUMS stand for? Is a generic name? I used this script in the past, but I stated the Log name.
Another quick one, can youo shrink your log as much as you possible can after the snapshot has been created?
Thank you
April 11, 2006 at 7:44 am
YOu can shrink the log as much as you want, however keep it in reasonable proportion to the Data file
WUMS is our testdb name. My bad.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply