production database log full

Question

Post reply

production database log full

striker-baba

SSCrazy

Points: 2170
More actions
April 26, 2010 at 11:46 am

#156906

Hi all
all my daily jobs are failed on my prod DB
this was due to logspace which occupied the whole drive.
How ever I dont need the log back up. I just want to truncate the log as soon as possible and make the disk space available for the jobs and start all the jobs.
can any one suggest be the best and fast way of truncating the log .
Thanks in advance.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

danfinuc SSC Enthusiast Points: 126 More actions · Answer 1

This will probably help you out.

http://www.mssqltips.com/tip.asp?tip=1464

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

So, you just want to treat the symptom instead of the cause, correct?

The first thing that comes to my mind, and really should not be done, change the recovery model of your production database to SIMPLE, then change it back to FULL (or BULK_LOGGED). Take a full backup of the database, and shrink the transaction log.

This will help your immediate problem, but you will encounter this again.

To treat the cause, first thing we need to know, is point in time recovery important to your users. If not, simply change the database to use the SIMPLE recovery model and run periodic full backups (perhaps with differentials more frequently).

If you need point in time recovery, don't do the above, instead run a transaction log backup (maybe 2), then shrink the t-log to an appropriate size. Then schedule periodic transaction log backups to manage the size of the t-log and keep it from consumming the entire hard drive.

For more information on this, start by reading the last article I have referenced below in my signature block.

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

We posted 2 pages the other day trying to help you with this problem. Instead of starting a new thread, you could read the replies, answer the questions, and read the information given to you so that you can come up with the correct solution to the problem:

http://www.sqlservercentral.com/Forums/Topic909851-146-1.aspx

striker-baba SSCrazy Points: 2170 More actions · Answer 4

Thank you Lynn

That was apt solution for my problem..

I changer the recovery mode to simple and back to full and it worked gud..

one of my friend suggested me to use this command

Backup log db_name with truncate_only.

Do you think this one works....

I think both of them are same.

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

Did you take a full backup after switching back to full recovery ?

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

striker-baba (4/26/2010)
Thank you Lynn
That was apt solution for my problem..
I changer the recovery mode to simple and back to full and it worked gud..
one of my friend suggested me to use this command
Backup log db_name with truncate_only.
Do you think this one works....
I think both of them are same.

So your friend told you that throwing away the ability of the business to recover the database to a point in time was a great idea and that the business wouldn't have any problem with it if the database were to fail shortly after you switched it to simple and you were only able to recover X amount of the transactions in the system. Nice friend.

If you really need the system to be in Full recovery mode and you really need to maintain your log backups, then that's what you should do. If you don't need full recovery, then just turn it off all the time. But arbitrarily tossing some of the transactions because it's easy is a highly dangerous practice and not one that you, as a loyal worker for your company, should support.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

striker-baba (4/26/2010)
Thank you Lynn
That was apt solution for my problem..
I changer the recovery mode to simple and back to full and it worked gud..
one of my friend suggested me to use this command
Backup log db_name with truncate_only.
Do you think this one works....
I think both of them are same.

First, BACKUP LOG ... WITH TRUNCATE_ONLY has been depreciated.

Second, apparently you didn't like any of the answers in your other thread that I happened to miss. You really should try READING the responses your got and reading the recommended articles that have been suggested.

Third, you need to take a full backup NOW! Then you need to set up periodic transaction log backups to prevent this from occuring AGAIN, and it will of you DON'T!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Lynn Pettis (4/26/2010)
striker-baba (4/26/2010)
Thank you Lynn
That was apt solution for my problem..
I changer the recovery mode to simple and back to full and it worked gud..
one of my friend suggested me to use this command
Backup log db_name with truncate_only.
Do you think this one works....
I think both of them are same.
First, BACKUP LOG ... WITH TRUNCATE_ONLY has been depreciated.
Second, apparently you didn't like any of the answers in your other thread that I happened to miss. You really should try READING the responses your got and reading the recommended articles that have been suggested.
Third, you need to take a full backup NOW! Then you need to set up periodic transaction log backups to prevent this from occuring AGAIN, and it will of you DON'T!

Also, I hope you duly noted that I RECOMMENDED AGAINST changing the recovery model to SIMPLE and back to FULL.

homebrew01 SSC Guru Points: 55548 More actions · Answer 9

Lynn Pettis (4/26/2010)
Also, I hope you duly noted that I RECOMMENDED AGAINST changing the recovery model to SIMPLE and back to FULL.

So ... you would advise against creating a nightly job to set the database to simple and truncating the log ? :Whistling:

Richard M. SSCertifiable Points: 7401 More actions · Answer 10

homebrew01 (4/26/2010)
Lynn Pettis (4/26/2010)
Also, I hope you duly noted that I RECOMMENDED AGAINST changing the recovery model to SIMPLE and back to FULL.
So ... you would advise against creating a nightly job to set the database to simple and truncating the log ? :Whistling:

LOL... let's not confuse the OP more 🙂

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Richard M. (4/26/2010)
homebrew01 (4/26/2010)
Lynn Pettis (4/26/2010)
Also, I hope you duly noted that I RECOMMENDED AGAINST changing the recovery model to SIMPLE and back to FULL.
So ... you would advise against creating a nightly job to set the database to simple and truncating the log ? :Whistling:
LOL... let's not confuse the OP more 🙂

LOL... I don't think it possible. He ignored the advice in his previous thread and started a new one.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

homebrew01 (4/26/2010)
Lynn Pettis (4/26/2010)
Also, I hope you duly noted that I RECOMMENDED AGAINST changing the recovery model to SIMPLE and back to FULL.
So ... you would advise against creating a nightly job to set the database to simple and truncating the log ? :Whistling:

Most assuredly!

homebrew01 SSC Guru Points: 55548 More actions · Answer 13

There are probably no backups, so it may not matter how the t-log is shrunk.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

homebrew01 (4/26/2010)
There are probably no backups, so it may not matter how the t-log is shrunk.

Probably right. I know I shouldn't be, but I am somewhat concerned about the company's data based on what has been posted on both threads. Even his own DBA didn't provide good advice, iirc.