March 13, 2012 at 10:11 am
I am a BA that has been asked to play a dual role of managing our MS SQL databases. Our databases currently use the Simple Recovery Model and that is working ok for the recovery needs. However, I have one database that is changing from Simple to Full Recovery Model and I do not know what process could be changing this. since we do not have a backup plan in place the log file fills up the drive and shuts down the server.
I would like to keep the Simple Recovery model. Can anyone point me in the best direction to determine what is changing the model.
Thanks.
March 13, 2012 at 10:14 am
i would suggest creating a profile trace which looks at the text data of all connections for the following strings
ALTER DATABASE nameofyourdatabase SET RECOVERY FULL
or
ALTER DATABASE nameofyourdatabase SET RECOVERY SIMPLE
that will give you the host, user and application which is performing the change then you can take the nessesary action like limiting the permissions on the db to stop it from happening.
March 13, 2012 at 10:20 am
check the error log. It will show when the change was made. If you find a pattern, you can then set up a trace to track all activity around that time and find out what's making the change.
March 13, 2012 at 11:43 am
Thanks. I have found the error in the log and I have found that many of the databases are getting changed to Full model then getting moved back to Simple a few hours later. I am checking on what would do this.
March 13, 2012 at 1:23 pm
Please let us know. That's a little strange. Usually you change from full to simple for some things and then back.
March 13, 2012 at 1:37 pm
I found that there is a VB 6 windows application that seems to be changing the database to Full. I have reviewed the code but could not find anything that made the change. Then a second process changes the database back to SIMPLE after the VB 6 program is complete. I am still reviewing. I will keep you updated
March 13, 2012 at 4:20 pm
Here is the culprit. Part of a Stored Procedure.
IF @Option = 0
BEGIN
EXEC sp_dboption @DBName, 'trunc. log on chkpt.', 'FALSE'
END
ELSE
BEGIN
EXEC sp_dboption @DBName, 'trunc. log on chkpt.', 'TRUE'
END
The procedure passes in the 0 option which chagnes the database backup to FULL then passes in a 1 to return to SIMPLE.
Question: If the database is in FULL Recovery Mode, would the 0 option switch it to Simple?
March 13, 2012 at 4:48 pm
No.
From Books Online:
Starting with SQL Server 2000, setting the trunc. log on chkpt. option to true sets the recovery model of the database to SIMPLE. Setting the option to false sets the recovery model to FULL.
So your code is equivalent to
IF @Option = 0
BEGIN
ALTER DATABASE @DBName SET RECOVERY FULL
END
ELSE
BEGIN
ALTER DATABASE @DBName SET RECOVERY SIMPLE
END
Except that Alter Database doesn't take variables. I would recommend trying to get rid of this. Not only is it not a good idea (if you ever decide to go full recovery with log backups that will mess things up) but also:
This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER DATABASE instead. To modify database options that are associated with replication (merge publish, published, subscribed), use sp_replicationdboption.
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
March 14, 2012 at 3:31 am
GilaMonster (3/13/2012)
From Books Online:
Except that Alter Database doesn't take variables.
Gail,
I am interested in this point. I have no valid reason why database doesn't take variables.
Do you know any valid reason?
March 14, 2012 at 6:41 am
The ALTER DATABASE statement doesn't take variables, nothing more I can say, the syntax rules don't permit it.
This fails with an error
DECLARE @DBName sysname = 'Testing'
ALTER DATABASE @DBName SET RECOVERY full
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@DBName'.
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
March 14, 2012 at 8:20 am
ames.f (3/13/2012)
Here is the culprit. Part of a Stored Procedure.IF @Option = 0
BEGIN
EXEC sp_dboption @DBName, 'trunc. log on chkpt.', 'FALSE'
END
ELSE
BEGIN
EXEC sp_dboption @DBName, 'trunc. log on chkpt.', 'TRUE'
END
The procedure passes in the 0 option which chagnes the database backup to FULL then passes in a 1 to return to SIMPLE.
Question: If the database is in FULL Recovery Mode, would the 0 option switch it to Simple?
Good job finding the issue...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply