201902 – MS SQL – Who offline the database?!

 

You may be reported that one of the database is offline. In an enterprise environment, It is always a question on should I bring it up? Could be someone working on the database and bring it offline on purpose?

Normally, I need to act as a detective to find out when and who bring the database offline and later find out why by asking the person directly.

There is no direct answer to it, we need to find out this ‘simple’ answer by below steps:

Step 1: Find out the exact time the database was take offline.

We could get it from error log, and the search term is ‘Offline’ and ”

How to read error log is explained in my old post “201711 – MS SQL – Read SQL Log’.

Now I execute the command: exec xp_readerrorlog 0, 1, "offline", "ABC"
Query result return as below:
LogDate ProcessInfo Text
2019-02-26 23:43:27.260 spid114 Setting database option OFFLINE to ON for database 'ABC'.

Now I have found two important factor:
1. Start Time: 2019-02-26 23:43:27.260
2. spid: 114

Step 2: Check Windows Application log
I could add ‘User’ as a column in Event Viewer -> windows application log and see who act on it. And it shows ‘N/A’. Very good, this is a hint that the operation may done by a SQL authentication account. In this step, if it is done by a Windows authenticated account, the user name will be show up directly on the windows application log.

Step 3: Query SQL trace log
I use a query I found online below and get no result. Is there something wrong with the command? NO ! It is failed because, there are multiple copies of trace log file and the query below only check the latest one!

DECLARE @FileName VARCHAR(MAX)
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;

SELECT DatabaseID, HostName, ApplicationName, LoginName, DatabaseName
FROM sys.fn_trace_gettable( @FileName, DEFAULT )
where starttime = '2019-02-26 23:43:27.260'
and spid='114'

I am gonna to create my own may to find it out!

select top 10 * from sys.traces
It return me the correct trace log file is : E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\log_1246.trc
Go to the same folder and find the trace log close to the incident time, it gives me: E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\log_1243.trc

Now I get my own query:

exec xp_readerrorlog 0, 1, "offline", "ABC"
select top 10 DatabaseName,Starttime, LoginName, * from sys.fn_trace_gettable ('E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\log_1243.trc', default)
where spid = '114'

Finally, let’s analysis the result:
DatabaseName: look for records the same as what we look for
ApplicationName: Program used for this action. It could be ‘Microsoft SQL Server Management Studio’ —> Human action; or ‘.Net SqlClient Data Provider’ _–> application action. In this case: It is a human action via SSMS!
StartTime: I found 2 records at ‘2019-02-26 23:49:19.260’ most closed to the offline action. It was not exactly the same as the time return by xp_readerrorlog, but with few minutes later.
LoginName: It is the SQL authentication that bring the database offline.
HostName: Server or Client computer which initial this transaction.

Is that the end? We can find out more by check the logon history on the HostName server.

How? Easies way is go to C:\Users and look for the Date modified attribute. Now I spot out the person. I am going to send him emails on his reason of offline the database. 🙂

Wait Wait! I cannot find this user in our outlook address book! OK, check his Active Directory data. Em, a consultant!

get-aduser AABBCC -properties *

Well, I know his first name, last name, entity and internal contact (manager). If anything comes to ask, I know how to answer!

The company also record the consultant external mailbox in Active Directory.
I can draft an email to the consultant external mail and cc his internal contact person.

And will we do it to show someone is watching their activity? It will be depends on the company policy.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s