August 7, 2003 at 12:30 am
Hi there all,
is there a way around this one. when you run maintenance plan with optimisation and have regularly transaction log dumps, the log dumps is as large as the database. this is because of dbreindex that SQL does with optimisation. I have tried to change the mode to bulk-logged this reduce the size of the log file but the log dump is still large, and the time the log dump takes during the optimisation run, is as long as the optimisation job itself.
August 7, 2003 at 4:30 am
Hi there
I have the same issue, but with lots of space and a small DB (5Gb) its not an issue. I was contemplating switching the recovery model, if you do this, I would recommend a full back immediately after as "best practice", even so, its more of a value judgement as you understand the DBMS and its B&R plan better than I 🙂
Another idea, remove the maintenace plan, and write a simple 1 liner with calls to the DBCC routines for indexdefrag etc and analyse the differences in log space used, then write an article and tell us your findings ...easy money from the SSC crew 🙂
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
August 8, 2003 at 9:20 am
I have the same issue, changing to bulk-logged was the only way around it. I think it's better to have the job take longer than it is to run out of disk space and not get a backup. Optimization needs to be done otherwise I have poor performance (badly designed db that I inherited and can't redesign). One tool that is great for reducing time and space is SQL Lite Speed, considering the savings by using it, it is worth the cost. Check out a trial version of it if you haven't already. I ran dozens of scenarios using 20+GB of data and it does standup to the test.
If you find a better way please do post it.
Thanks and good luck.
K
Edited by - kelseyv on 08/08/2003 09:21:05 AM
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply