Solved

MSSQL Error 8309

Posted on 2009-05-19
9
1,207 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 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