Database Settings Changes – Red Handed

One of my pet-peeves (and consequently frequent topic of discussion) is finding database settings (or any setting that has changed) without knowing about it. Worse yet is finding that the change has occurred and nobody claims to have any knowledge of it or having done the deed.

This happened again recently where a database was set to single_user and suddenly performance in the database tanked. Change the database back to multi_user and the performance issues are magically resolved.

Fortunately there is a means to combat this. Well, sort of. The default trace in SQL Server does capture the event that occurs when the database is set to single_user or read_only. Unfortunately, all that is captured is that an Alter Database occurred. There is no direct means of mapping that event to the statement or setting that changed.

This inadequacy got me to thinking. The default trace is looking at a set of specific “events”, why wouldn’t that set of events be available within Extended Events. It only seems logical! So I decided to query the event catalog and lo and behold, I found just the event I was seeking – object_altered. Combine this with a recently used predicate (object_type = ‘DATABASE’) and we are well on our way to having just the trap to catch the source of these database changes red-handed.

USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'DBSettingChange' )
DROP EVENT SESSION DBSettingChange 
    ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO
CREATE EVENT SESSION DBSettingChange ON SERVER
ADD EVENT sqlserver.object_altered ( SET collect_database_name = ( 1 )
ACTION ( sqlserver.sql_text,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.client_hostname,
package0.collect_system_time,package0.event_sequence ) 
WHERE object_type = 'DATABASE'
AND sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
)
ADD TARGET package0.event_file ( SET filename = N'C:DatabaseXEDBSettingChange.xel' );
/* start the session */ALTER EVENT SESSION DBSettingChange 
ON SERVER 
STATE = START;
GO

Easy enough to create this particular session. The event does not capture the “whodunnit” without a little extra prodding. So, I added in a couple of actions to get that information – sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.client_hostname. Additionally, the event does not explicitly tell me what setting changed – just that some setting changed. For this, I decided to add the sql_text action so I could correlate event to the actual setting being changed. Then to cap it all off, I made sure the predicate specified that we only care about database settings changes as previously mentioned.

Running the session and then testing some settings changes should prove fruitful to capturing good info. Here are a few of the tests that I ran and the results of those tests (by querying the session data).

ALTER DATABASE AdventureWorks2014
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2014
SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO
USE [master]
GO
ALTER DATABASE [AdventureWorks2014] SET  READ_ONLY WITH NO_WAIT
GO
ALTER DATABASE [AdventureWorks2014] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [AdventureWorks2014] SET RECOVERY SIMPLE WITH NO_WAIT
GO

Now to try and look at the results.

USE master;
GO
SELECT event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name
, event_data.value('(event/data[@name="database_name"]/value)[1]',
'varchar(max)') AS DBName
, event_data.value('(event/data[@name="ddl_phase"]/text)[1]',
'varchar(max)') AS DDLPhase
, event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS [TIMESTAMP]
, event_data.value('(event/action[@name="collect_system_time"]/value)[1]',
'varchar(max)') AS SystemTime
, event_data.value('(event/action[@name="client_hostname"]/value)[1]',
'varchar(max)') AS ClientHostName
, event_data.value('(event/action[@name="server_principal_name"]/value)[1]',
'varchar(max)') AS ServerPrincipalName
, event_data.value('(event/action[@name="nt_username"]/value)[1]',
'varchar(max)') AS nt_username
, event_data.value('(event/action[@name="sql_text"]/value)[1]',
'varchar(max)') AS sql_text
FROM ( SELECT CONVERT(XML, t2.event_data) AS event_data
FROM ( SELECT target_data = CONVERT(XML, target_data)
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s
ON t.event_session_address = s.address
WHERE t.target_name = 'event_file'
AND s.name = 'DBSettingChange'
) cte1
CROSS APPLY cte1.target_data.nodes('//EventFileTarget/File') FileEvent ( FileTarget )
CROSS APPLY sys.fn_xe_file_target_read_file(FileEvent.FileTarget.value('@name',
'varchar(1000)'),
NULL, NULL, NULL) t2
) AS evts ( event_data )
ORDER BY [TIMESTAMP],DDLPhase,event_data.value('(event/action[@name="event_sequence"]/value)[1]',
'varchar(max)');

There you have it! I have just been caught red-handed changing my AdventureWorks2014 database to single_user and multi_user.

Bonus

For more ideas on settings and changes and so forth, Andy Yun (blog | twitter) has invited all to participate in TSQL2SDAY on this very topic. He has invited all to talk about their experiences with “default settings” and what you might change them to! You can read about it here. I have another article coming up that will fit just nicely with that. Let’s just call this a preview and maybe it can help you get those tsql2sday juices flowing.