comptechmike
asked on
Set log_reuse_wait in SYS.DATABASES, SQL 2005
Hi,
I've got a SQL job that for some inexplicable reason, has begun executing and the log consuming more than 100GB of hard drive space, as of 9:42 this morning. I've troubleshooted the hell out of it, after deleting the log, then with great difficulty getting db back online, researched the hell out of it on the net, and what it appears like is my log_reuse_wait time has somehow got set to 4, and this is causing the log to not shrink. When I set the job to stop at some amount of maximum log growth, it errors out.
So I'm trying to set the value of log_reuse_wait in SYS.DATABASES from 4 to 0, and nowhere can I find instructions on where to click, or what command to input, to do this. I realize this may not "directly" address my problem, but if I can just figure out how to set this, it may get me a step further.
Suggestions?
I've got a SQL job that for some inexplicable reason, has begun executing and the log consuming more than 100GB of hard drive space, as of 9:42 this morning. I've troubleshooted the hell out of it, after deleting the log, then with great difficulty getting db back online, researched the hell out of it on the net, and what it appears like is my log_reuse_wait time has somehow got set to 4, and this is causing the log to not shrink. When I set the job to stop at some amount of maximum log growth, it errors out.
So I'm trying to set the value of log_reuse_wait in SYS.DATABASES from 4 to 0, and nowhere can I find instructions on where to click, or what command to input, to do this. I realize this may not "directly" address my problem, but if I can just figure out how to set this, it may get me a step further.
Suggestions?
>> set the value of log_reuse_wait in SYS.DATABASES from 4 to 0,
The SYS.DATABASES is nothing but a view on the various tables that make the Master DB.
What we really need to know what the properties of your database(s) and then we can make suggestions. I suspect that you are in full recovery model and someone is loading in a crapload of data without out using transactions.
You need to setup tran log backups at 15 minute intervals and then we can work the real resolution over time.
The SYS.DATABASES is nothing but a view on the various tables that make the Master DB.
What we really need to know what the properties of your database(s) and then we can make suggestions. I suspect that you are in full recovery model and someone is loading in a crapload of data without out using transactions.
You need to setup tran log backups at 15 minute intervals and then we can work the real resolution over time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks! I found another work around (basically just put "truncate log" statements every few steps in the job), but I'm saving your answer as the best, for future reading, when I actually have time to fix the darn problem.
http://www.windows-tech.info/15/43f0e9a304ac548f.php
http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/d6ed59d8-5234-42ec-813b-c389907b9c5c
Gary