DDL Trigger to Fire when Database Option Changed

We have a staging database in which tables get dropped and recreated every day. I have set it to Simple recovery model numerous times, but it keeps getting changed back to Full recovery (I suspect a maintenance script somewhere, possibly even running on a different server). The log does not get backed up, so eventually it fills up and crashes whatever is loading data into the database.

I would like to create a DDL trigger that will set recovery mode back to Simple when it gets changed to another model. I've read the MSDN site, but not sure if there is an event that fires and how I would code. I want to only monitor this one database.

We are running SQL Server 2012. Do DDL triggers have to be 'enabled' or is the mere creation of one all that is necessary?
LVL 15
dbbishopAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
I suspect a maintenance script somewhere, possibly even running on a different server.

It means that full recovery model is the requirement of that maintenance script. If you create a trigger on event of recovery model change would not the maintenance script fail because trigger will fire immediately.  Anyway if you want to do it here is two options you can go with which you like.

option 1.
This can be done using Server level DDL trigger but something tricky.
You can create a DDL trigger and configure a sql agent job then on the event of alter database this trigger will call to sql agent job and this job will change back the recovery model to simple.

Option 2.

Create a sql server agent job and schedule it for one or two hour interval. This job will monitor the recovery model of database and change it to simple if not set to simple.
0
dbbishopAuthor Commented:
FULL recovery model is not required. We have maintenance scripts that take databases out of full recovery model to perform maintenance and then set them back to FULL. They are indiscriminate of whether the DB was in FULL model when the script started.

I asked how to create a trigger, not if I could. I've never written a DDL trigger, and not sure what event would be fired if the recovery model was changed, and how to query the event to see if that is what occurred and if it was on the database I wish to monitor. I am certain that I can set recovery model back to simple within the trigger without having to kick off a job.
0
dbbishopAuthor Commented:
Okay, I've got the following working (after going down about three pages in Google)
CREATE TRIGGER ResetRecoveryMode ON ALL SERVER
	FOR ALTER_DATABASE
AS
	BEGIN 
		DECLARE	@tsql NVARCHAR(MAX); 
		DECLARE @Server SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(MAX)')
		SELECT	@tsql = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'); 
		IF ( CHARINDEX('RECOVERY', @tsql) > 0 ) AND ( @Server = 'ETL_Staging' )
			BEGIN 
				ROLLBACK;
			END 
	END;

Open in new window

However, now I see the reason for triggering a job within the code. Leaving as-is will throw an error when the ROLLBACK is performed:
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
which will cause the underlying maintenance script(s) to fail, and I can't allow that. So, I will change the rollback to an 'EXECUTE sp_start_job ...'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dbbishopAuthor Commented:
Changed code slightly:
ALTER TRIGGER ResetRecoveryMode ON ALL SERVER
	FOR ALTER_DATABASE
AS
	BEGIN 
		DECLARE	@tsql NVARCHAR(MAX); 
		DECLARE @Server SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(MAX)')
		SELECT	@tsql = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'); 
		IF (( CHARINDEX('RECOVERY FULL', @tsql) > 0 ) OR ( CHARINDEX('RECOVERY BULK', @tsql) > 0 )) AND ( @Server = 'ETL_Staging' )
			BEGIN 
				EXECUTE msdb.dbo.sp_start_job 'Reset ETL_Staging Recovery Model';
			END 
	END;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Deepak ChauhanSQL Server DBACommented:
Yes you get the correct script. You can do it this way, and Agent job will require because you can not use Alter database command within the trigger.
0
dbbishopAuthor Commented:
I had done some research and was able to create the trigger initially using ROLLBACK before deepakChauhan's comment. Although I likely would have figured it out, his comment "You can create a DDL trigger and configure a sql agent job..." pointed me in the right direction a little quicker. Thanks for the hint.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.