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?
comptechmikeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim P.Commented:
>>  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.
Mark WillsTopic AdvisorCommented:
The log-reuse-wait is not a setting as such that you can change. More so it is an indicator showing you why / what the log file is waiting for.

In your case it seems to be an outstanding "active" transaction.

You might need to check that job you are running to see how it is managing a "transaction", and possibly put in some checkpoints, or discrete "begin transaction / commit" type processing.

Have a look at : http://msdn.microsoft.com/en-us/library/ms178534.aspx and scroll down to the flag and it will describe it a bit more (and the section under has a link to http://msdn.microsoft.com/en-us/library/ms345414.aspx )

It does depend a lot on your recovery model. If simple recovery then the log shouldnt really be much of a problem, so, assume it is in Full recovery mode and you really must run a full backup, and then follow with transaction log backups (in between full backups).

Might also be worth having a quick read of : http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Managing-the-Transaction-Log-for-the-Accidental-DBA.html

Sorry about the bad news - but you can only influence that setting by other actions. It is not directly set, but telling you what it is waiting on...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
comptechmikeAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.