Solved

MSSQL Error 8309

Posted on 2009-05-19
9
1,214 Views
Last Modified: 2012-05-07
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
Comment
Question by:infadmin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 20

Accepted Solution

by:
Marten Rune earned 250 total points
ID: 24419912
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
 
LVL 9

Expert Comment

by:Sander Stad
ID: 24419948
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
 

Author Comment

by:infadmin
ID: 24419949
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 20

Expert Comment

by:Marten Rune
ID: 24419951
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
 

Author Comment

by:infadmin
ID: 24419974
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 24419996
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
 
LVL 9

Expert Comment

by:Sander Stad
ID: 24420517
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 24422032
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
 

Author Closing Comment

by:infadmin
ID: 31582898
Worked like a dream :)!  No errors this morning.  Backups are running fine also!
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question