Solved

MSSQL Error 8309

Posted on 2009-05-19
9
1,196 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

816 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now