February 8, 2008 at 9:08 am
Hi, the Recovery model of the database keeps changing to Simple which results in the failure of the transaction log backup jobs. I am trying to find out how's it's getting changed, any ideas are appreciated. Thanks!!
February 8, 2008 at 10:22 am
Is it a user database or a system database? What was the recovery model when the database was created?
Greg
February 8, 2008 at 10:36 am
Keep in mind that the reason it "fails" is that there's ultimately nothing to back up in Simple mode. since the transaction log automatically truncates as soon as the activity is committed - there's nothing retained long enough for it to get backed up.
Perhaps it's not such a bad thing: could be perfectly appropriate depending on the purpose of said database.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 8, 2008 at 11:51 am
Greg Charles (2/8/2008)
Is it a user database or a system database? What was the recovery model when the database was created?
It's a user db, I do'nt know the recovery model when the db was created. Thanks for the speedy response.
February 13, 2008 at 2:13 pm
I've never heard of a database spontaneously change recovery models. I'd say that either the database is being restored from a backup made when the recovery model was simple or an Alter Database command is being executed by a member of sysadmin or db_owner.
Greg
February 13, 2008 at 3:28 pm
Greg Charles (2/13/2008)
I've never heard of a database spontaneously change recovery models. I'd say that either the database is being restored from a backup made when the recovery model was simple or an Alter Database command is being executed by a member of sysadmin or db_owner.
I checked all the sql jobs in that server I can't find any where about the altering of the db to simple. And also it's really wierd that it's happening to only one db out of 20 db's. And that db belongs to Desktop authority application, I do'nt think that application is changing the db to simple, Thanks for all the responses.
February 14, 2008 at 9:25 am
I have a couple more ideas. Changing the recovery model should be logged in the Windows Server Application log. Look in Event Viewer to see if there's a user or application related to the change.
You could run a Profiler trace to capture actions in the database. If the change always seems to occur at a particular time (like at night), you wouldn't have to run the trace very long.
Greg
February 14, 2008 at 9:40 am
Is the db a reporting or read only db? Is the db restored on a nightly basis from some other source?....I only mention this because I have seen this behavior with on of my dbs. The source db is set to simple, recovering the backup to the reporting system changes the destination from full recovery back to simple.
February 14, 2008 at 10:25 am
Greg Charles (2/14/2008)
I have a couple more ideas. Changing the recovery model should be logged in the Windows Server Application log. Look in Event Viewer to see if there's a user or application related to the change.You could run a Profiler trace to capture actions in the database. If the change always seems to occur at a particular time (like at night), you wouldn't have to run the trace very long.
Here is information below from the event viewer:
Event Type:Information
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17055
Date:2/10/2008
Time:1:01:21 AM
User:username
Computer:server
Description:
5144 :
Autogrow of file 'DB_log' in database 'DB' cancelled or timed out after 0 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size
I think this might have caused it to change to Simple, but how can I fix this issue. Thanks!!
February 14, 2008 at 10:34 am
You can also check the db datecreated if it's recent, you'll have an idea when the database just got restored.
_____________
Donn Policarpio
February 14, 2008 at 10:54 am
Run a profiler trace against the server. Someone or something is changing the recovery mode. It doesn't change on its own.
Trace to disk and load the trace into SQL once it's finished. Then you can query the table. Important columns would be the login name, starttime and the textdata
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2008 at 1:01 pm
I checked the date created on the db, it's on 9/24/07. Thx!
February 15, 2008 at 2:56 am
At my old place we had some DTS packages that 'helpfully' changed the database mode while a load processes were running. Needless to say it took a while to track down where this happened. The ALTER statements are still in the package, but are now prefixed by --
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 15, 2008 at 4:57 am
some apps include processes to run their own db maintenance. maybe there is a stored proc in the database which the app runs to do this.
quick and dirty check run 'sp_helptext' in a loop to output all your stored procs and do a search for string 'simple' or 'alter database'
---------------------------------------------------------------------
February 17, 2008 at 12:42 pm
I think there is one application's stored proc which might be changing the recovery model.
Thanks!!
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply