asked on

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?

SOLUTION

Deepak Chauhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

ASKER

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.

D B

ASKER

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 ...'

ASKER CERTIFIED SOLUTION

D B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Deepak Chauhan

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.

D B

ASKER

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.