• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1231
  • Last Modified:

MSSQL Error 8309

Our backup software is working but in the event log I am getting below error at 6am every morning on the dot.  It appears 5 times within 60 seconds then I dont see it till 6am the next day.

Running SQL 2005 Express (microsoft).  Had a look around the Express Management Studio but cant find any obvious log settings.

Please can someone advize?
Event Type:	Error
Event Source:	MSSQL$SQLEXPRESS
Event Category:	(6)
Event ID:	8309
Date:		19/05/2009
Time:		06:01:19
User:		DOMAIN\user365
Computer:	FILESERVERP01
Description:
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.

Open in new window

0
infadmin
Asked:
infadmin
  • 4
  • 3
  • 2
1 Solution
 
Marten RuneCommented:
Your backupsoftware is using old T-SQL syntax:
BACKUP LOG WITH TRUNCATE_ONLY
or
BACKUP LOG WITH NO_LOG

These commands are being deprecated. They will stop to work sooner or later, when a hotfix is released, or when another sp is released. This message is for you so that you can alter the way the backup software works.

It says: Use simple recoverymode if you dont use the log files. I e
USE [master]
GO
ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE
GO

/Marten
0
 
Sander StadCommented:
I wouldn't recommend using the simple recovery because you'll use a lot of data in your transaction log.

Try this:
ALTER DATABASE databasename SET RECOVERY SIMPLE
GO
CHECKPOINT
GO
ALTER DATABASE databasename SET RECOVERY FULL
GO
BACKUP DATABASE databasename TO DISK= 'c:\databasename.bak' WITH INIT
go
0
 
infadminAuthor Commented:
Hello,

Thanks.  I have run the script and got the following confirmation message in the Event Console.  I will check on the logs in the morning and if its gone, the we look good to go!!!  Thanks for your help!
Event Type:	Information
Event Source:	MSSQL$SQLEXPRESS
Event Category:	(2)
Event ID:	5084
Date:		19/05/2009
Time:		10:30:38
User:		Domain\User
Computer:	FILESERVERP01
Description:
Setting database option RECOVERY to SIMPLE for database dbname.
 
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Marten RuneCommented:
Simple recovery means you will NOT be able to restore database to point in time. Only to the last backup taken. Check your SLA to see if this complies with what you agreed as the SLA for the databases.

This code will show you all your databases that are not in SIMPLE recovery mode. I e full or bulk logged

select name as "DBNAME"
from master.dbo.sysdatabases
Where name not in ('master','model','msdb','tempdb')
and DatabasePropertyEx(name,'Status') = 'ONLINE'
and DatabasePropertyEx(name,'Recovery') <> 'SIMPLE'

From there start looking att the SLAs for each database, and determine if SIMPLE is sufficient, if not. See if you can alter the backupsoftware with other parameters.

/Marten
0
 
infadminAuthor Commented:
As it is backup software I think I am OK with Simple.  Its simply a log of successful backups and errors which is also emailed to me so I have a visual way of looking and backing up.

Thanks for your suggestions.
0
 
Marten RuneCommented:
sstad: Quote 'I wouldn't recommend using the simple recovery because you'll use a lot of data in your transaction log'

Do you mean that the log file will keep filling up and expanding when the database is in SIMPLE recovery mode?
If so you need to read up a little!

Quote from Tibor Karaszi (MVP on SQL Server, link: http://www.eggheadcafe.com/conversation.aspx?messageid=33464958&threadid=33464917)
That is not correct. Simple recovery mean that log truncation occurs automatically, this has been the case since 2000 (when recovery model concept was introduced) on to 2008. If this doesn't happen, you should investigate the case, look in sys.databases, DBCC OPENTRAN, Google etc and possibly open a case with MS.

The SET RECOVERY SIMPLE and CHECKPOINT and then SET RECOVERY FULL and CHECKPOINT will not help one bit if I'm not mistaken.

The problem is resolve either by choosing SIMPLE recoverymodel (loose the option of point in time restore) or by changing options in your backupsoftware to actually backup log files, instead of truncating them.

Regards Marten
0
 
Sander StadCommented:
martenrune: I made i little typo so I see ;)

I meant that you'll have a lot less data in your log in case your database becomes corrupt. The word 'use' had to be 'need' in my sentence.


0
 
Marten RuneCommented:
sstad:
Yup thats for sure!
you'll have only uncommitted transactions in the log since it truncates immediatly once committed.

If the database becomes corrupt it's up to the DBA to fullfill the signed SLA.
As stated above, this is the crossroad when choosing SIMPLE, FULL or BULK LOGGED recovery mode.
At least IMHO I would recommend to review the SLA's when choosing recovery mode.

Cheers Marten

PS: This posting is for clarification of the thread. Please no points for this comment
0
 
infadminAuthor Commented:
Worked like a dream :)!  No errors this morning.  Backups are running fine also!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now