Disappearing Data Files

TSQL Tuesday

Boy has it gotten hot outside. As the heat turns up outdoors, it is frequently a really good idea to get together with friends and family to relax with a little outdoor party. As luck would have it, it is now time for a fabulous party. Sadly, this party likely is being held indoors for everybody. It is time for a little TSQL Tuesday gathering!!

This party, that was started by Adam Machanic, has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

This month, the blog party is all about inspecting and evaluating failure – of sorts. Kerry Tyler  (b | t) is confident there are lessons to be learned from all sorts of failures. As it so happens, I am of the same opinion, so long as you investigate the failure and try to determine the root cause of the failure. You can find the invite from Kerry – here.

There are many opportunities to learn from failure. Personal failure, a friends failure, a child’s failure, any failure really. One does not need to experience a failure in order to be a beneficiary of the learning opportunity that comes from the failure. People get better through failure if they embrace the failure and continue to strive for success. Here are some thoughts to bear in mind as you read the remainder of this article.

“Failure should be our teacher, not our undertaker. Failure is delay, not defeat. It is a temporary detour, not a dead end. Failure is something we can avoid only by saying nothing, doing nothing, and being nothing.” – Denis Waitley

“The only real mistake is the one from which we learn nothing.” – Henry Ford

“Failure isn’t fatal, but failure to change might be” – John Wooden

Time to Fail

One of my favorite pet peeves is having to repeat a fix over and over and over again. You know the feeling? You know you have something fixed, you documented the fix, there is an email chain with pictures to illustrate the fix, yet somehow your fix doesn’t stick so you have to do it again.

This repetitive fix syndrome reared its ugly head with me again this past week while investigating an AG failure for a client. While doing my root cause analysis (RCA), I discovered that somehow the tempdb had become grossly mis-configured. This is something that not only did I know was fixed once or twice previously, but others working on this client were able to confirm they had fixed it previously as well. If the configuration had been fixed so many times, then how does it continually keep getting un-fixed?

Unfortunately, trying to dive in and generate an RCA proved rather unfruitful. The issue wasn’t caught quickly enough and evidence had rolled out of logs in this particular case. What to do? What to do?

This is where a light bulb moment occurs and the thought enters to use one of my favorite tools in SQL Server – Extended Events (XEvents). Now that a tool had been selected and an idea formed, it was time to figure out the details and put the idea to the test.

Having had experience with similar needs, I knew there were events that would likely perform the desired function ( sqlserver.object_altered, sqlserver.object_deleted, and sqlserver.object_created ). With these three events, the next task is simple – create an event session and validate that the events will capture the desired data should any tempdb files end up getting changed. One more key component to this test session is to restrict the session to the “Database” object_type. To discover all of the available object_types that can be used to filter the data, use the following query.

SELECT DISTINCT
       mv.name AS MapName,
       mv.map_value,
       xp.name AS PackageName
FROM sys.dm_xe_map_values mv
    INNER JOIN sys.dm_xe_packages xp
        ON mv.object_package_guid = xp.guid
WHERE xp.name = 'sqlserver'
      AND mv.name = 'object_type'
ORDER BY mv.map_value;

Armed with a little experience and the events previously noted, we can now assemble the session to help us Audit changes to our data files.

USE master;
GO
-- Create the Event Session
IF EXISTS
(
    SELECT *
    FROM sys.server_event_sessions
    WHERE name = 'DBFileChange'
)
    DROP EVENT SESSION DBFileChange ON SERVER;
GO
EXECUTE xp_create_subdir 'L:DatabaseXE';
GO
CREATE EVENT SESSION [DBFileChange]
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,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.username,
         sqlserver.session_nt_username,
         sqlserver.client_app_name,
         sqlserver.session_id,
         sqlserver.context_info,
         sqlserver.client_connection_id
     )
     WHERE ([object_type] = 'DATABASE')
    ),
    ADD EVENT sqlserver.object_deleted
    (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,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.username,
         sqlserver.session_nt_username,
         sqlserver.client_app_name,
         sqlserver.session_id,
         sqlserver.context_info,
         sqlserver.client_connection_id
     )
     WHERE ([object_type] = 'DATABASE')
    ),
    ADD EVENT sqlserver.object_created
    (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,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.username,
         sqlserver.session_nt_username,
         sqlserver.client_app_name,
         sqlserver.session_id,
         sqlserver.context_info,
         sqlserver.client_connection_id
     )
     WHERE ([object_type] = 'DATABASE')
    )
    ADD TARGET package0.event_file
    (SET filename = N'L:DatabaseXEDBFileChange.xel')
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = ON,
    STARTUP_STATE = ON
);
ALTER EVENT SESSION DBFileChange ON SERVER STATE = START;
GO

Note: I have a specific file path in my session for the output of the captured events. This path will need to be changed in order to work properly for your environment.

Here is another tiny little note, I am note restricting this to just changes in the tempdb database. Nor am I restricting this to just object changes. That said, any time a data file is added, dropped or altered, the object_altered event will capture the needed data (for any object_type that is lumped together in the “Database” classification). If you wish to scope the session to be more restrictive – be my guest.

With this session in place, any time somebody decides to drop one of my tempdb files or change the growth factor of the files again, I will know who did it. Here is a good example shown in the following image.

Now that the technical aspects have been vetted, it is time to perform the social aspects. Before deploying, it is best to coordinate the change and get approval from interested parties. Once the approval is gained, then proceed with deploying the XEvent audit to the server.

Despite not having an RCA for the current problem of the tempdb being changed, the lesson here is to be better prepared should it happen again. When this problem happens again, I will certainly have solid evidence that could provide me the RCA that I need. In short, the failure here was inadequate measures in place to capture the changes that kept recurring. The lesson to be learned is to better be prepared and that SQL Server comes with tools that can help us be adequately prepared to handle situations like this.

Wrapping it Up

This article has just shared the power of XEvents and yet another use in helping to audit the database environment for changes. Not only do we have this powerful tool at our disposal for troubleshooting, but it is there for use as an audit tool as well as to help us create an RCA when needed.

Feel free to explore some of the other TSQL Tuesday posts I have written.

If you are in need of a little tune-up for your XE skills, I recommend reading a bit on Extended Events to get up to date. For some “back to basics” related articles, feel free to read here.