[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MSSQL Error 8309

Posted on 2009-05-19
9
Medium Priority
?
1,225 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 1000 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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