Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Transaction Logs for SQL Database - Backup Exec 2012

Created: 20 Jan 2013 | 10 comments

Hi All,

Would appreciate some help please.

I get the following error....

"Transaction log backups are not being performed.  This will result in the log growing to fill all available disk space.  Regular log backups should be scheduled or the database should be changed to the simple recovery mode.Transaction log backups are not being performed.  This will result in the log growing to fill all available disk space.  Regular log backups should be scheduled or the database should be changed to the simple recovery mode."

Now I was getting the same error before, only it said i needed to switch the simple recovery mode to full backup mode, which I did, and then got the above error.

Now could someone please explain to me as simply as possible (and please assume i am a complete idiot!!!!!!!), how I clear this error in a good way. That is to say, perform the log backups, and prevent the logs from growing out of control. Please highlight if you are talking about settings in the SQL section of BE, or indeed on the actual SQL server management conseol itself.

Apart from this (really annoying) error, BE is working amazingly fine. If i could solve this issue, i would get green lights accross the board each day and i would be over the button moon!!!

Thankyou so much in advance for the right answer. At the risk of sounding blunt, which isnt my intention...please dont post links to other pages. I am looking for someone who actually knows and can explain the answer clearly and understands it so I can learn. Chances are its one of the sites i've filled my evenings with and been to over the past 3 weeks anyways.

Comments 10 CommentsJump to latest comment

Backup_Exec's picture

Hi

Please check the link below

There are two types of recovery model simple and full. In simple recovery model transaction logs are overwritten at time interval & but in full transacation logs are not

With backup exec you should perfrom full and seperate log backup to for database truncation

http://www.symantec.com/docs/TECH126063

Hope that helps

Thanks

Sameer

Don't forget to give a "Thumbs Up" or Mark as "Solution" if someones advice has helped you.

pkh's picture

In simple recovery model transaction logs are overwritten at time interval

Since when??? What is the time interval that you are talking about? Check your facts before posting them.

With backup exec you should perfrom full and seperate log backup to for database truncation

Database truncation???  Since when does BE truncates databases?? 

Please to do not advise others when you are not familiar with the subject.

Backup_Exec's picture

Hi

The Simple recovery model brings forth the fastest bulk operations and the simplest backup-and-restore strategy. Under this model, SQL Server truncates the transaction log at regular intervals, removing committed transactions.

http://www.symantec.com/docs/TECH11060

About database truncation yes it was a typo I wanted to right log truncation thanks for correcting me on this

And to clear more on this with backup exec after doing the log backup comitted logs would be set for truncation but will not be purged, the activity of purging need to be done using SQL maintainence so that the change log size is seen else the log size would still show the same.

Sameer

Don't forget to give a "Thumbs Up" or Mark as "Solution" if someones advice has helped you.

pkh's picture

The document is not quite correct.  In simple recovery mode, the logs are truncated when a transaction is committed and not at regular intervals, like in very 1 hour or some fixed time period.

doing the log backup comitted logs would be set for truncation but will not be purged, the activity of purging need to be done using SQL maintainence so that the change log size is seen else the log size would still show the same.

How do you purge a SQL log?  You cannot purge a SQL log either through SQL maintenance or some other means.  You can however shrink the SQL log to recover unused spaces which is freed up when the logs are truncated and you don't use SQL maintenance to do this.  You use the DBCC Shrinkfile command.

Also read the OP's request about not posting links and explain things to him IF you can.

pkh's picture

@ChrisLTQ -

Now I was getting the same error before, only it said i needed to switch the simple recovery mode to full backup mode, which I did, and then got the above error.

I don't know why you switch to full recovery mode.  If you look at the error message, the advice is to switch to SIMPLE recovery mode, not full recovery mode.  When your databases are in full recovery mode, transaction logs are maintained and you need to periodically truncate the logs or the logs would keep on growing and your disk will fill up.

You have two ways to get rid of the error message.

1) switch to SIMPLE recovery mode which does not maintain transaction logs.  Before you do so, check with the database owner.  Some applications cannot work with simple recovery mode, i.e. they need full recovery mode.

2) use full recovery mode and run periodically run a BE log backup job.  This log backup job will truncate the transaction logs and you would not get the error message.  I run a transaction backup job AFTER each database backup.  Note that truncating the logs will not change the physical size of the log file.  It only allow the space previously occupied by the transactions to be re-used.  To shrink the log file, you need to use the DBCC Shrinkfile command.

ChrisLTQ's picture

@pkh

AS explained, I switched to FULL becuase the error I got before I switrched to full, said that I needed to change to FULL (proof attached) or the logs would grow out of control. Now that i have changed it to FULL, it now shows the above error saying I should change it to SIMPLE.

Thats why I changed it, because I thought BE wanted me to, not because I though "what the hell....il'll go ahead and change it"

Also, in your second explaination, you say to "periodically run a BE log backup job. This log backup job will truncate the transaction logs and you would not get the error message. I run a transaction backup job AFTER each database backup."

Could you please specify how this is done, and where it is done ie on the SQL server in the SQL management console, or in BE2012 itself???

Colin Weaver's picture

Hi Chris with SQL backups the word "Full" can be used two ways as such I am wondering if you may have misunderstood the errors you saw. "Full" can be used both against the type of backup job and the configuration of the logging mode within SQL itself.

If your SQL database is in "Simple" mode and you attempt to run a Transaction Log backup you will get an error indicating the SQL should be configured in "Full" mode. Really if you get this error you have 2 choices 1) Stop running Transation Log backups as you don't need them in "Simple" mode or 2) Change the SQL database to "Full" mode - which should only be done after consulting you SQL experts/dbas

If your SQL databse is in "Full" mode and you only run Full backups you may periodically see an erro indicating that SQL logs are not being truncated. Again there are two choices here: 1) Run periodic Transation Log backup jobs or 2) Change the SQL database to "Simple" mode - again you should check with a SQL exper/dba before doing this.

Not sure if this helps but it might explain the  confusion regarding the error messages.

ChrisLTQ's picture

Hi All,

Thanks for the help. Let me just clarify a little bit more as it seems people are not understanding what i am trying to explain. Possibly i havent explained clearly enough so here goes again.....

@Colin...

I think this is what i was initially trying to say but think i didnt explain well enough. My quesiton is, "How do I Run periodic Transation Log backup jobs??" - Thanks for your help though. You've so far been the most helpful.

We have basically got a weekly full, and daily incrementals set up. The SQL database we are trying to backed up was initially set to simple recovery mode.

When the full backup ran, i got the error stating that the log was increasing in size etc, and that i should change the SQL to FULL recovery mode.

So i changed the SQL DB to full (in the SQL Management console), and re re-ran the FULL backup in BE.

I then got the same error, only this time it said the log was getting really big etc... and that i should change it to simple which it was originally.

Basically...in my mind.... its told me to do one thing (ie change it from SIMPLE Recovery mode to FULL) to to fix the issue, but then once thats been done, told me to change it back (FUll recovery mode back to simple) to what it was.

The image I have posted, was the error I got AFTER I had changed the SQL DB from simple to full recovery mode as it (it being Backup Exec 2012) suggested i do so the first time.

1222256's picture

You should have the database set for full and then back up the database and then do a log backup on the database.

Symantec was telling you that if you do simple you don't do a transaction log backup.

If you change it to do a full backup and then don't run a transaction log backup it warns you that your logs will continue to grow unless you do a transaction log backup.