Menu
I ran into a situation where we were working on a migration and had been directed to put all databases into FULL recovery model in anticipation of using log shipping to push databases to the new server. Once we are ready to go live on the new server the plan was to ship the last transaction logs and then restore them WITH RECOVERY in an effort to make the final cutover as quick as possible. Of course this means that we had to make sure that all databases were having regular log backups, which we did. Things were going along nicely until we started receiving log backup failure notifications.
Upon checking things, we found that one of the databases had been changed to SIMPLE recovery model. You can find this type of information in the default trace or you can simply scroll through the SQL error logs until you find the entry that you are looking for. If you have a busy instance that has a lot of entries in the error log, this can be a bit time consuming so I came up with a set of queries that will grab the error log entry and attempt to tie it to the info in the default trace so that it was easier to identify WHO was the culprit who made an unauthorized change to the database properties.
DECLARE @tracefile VARCHAR(500)
DECLARE @ProcessInfoSPID VARCHAR(20)
CREATE TABLE [dbo].[#SQLerrorlog](
[LogDate] DATETIME NULL, [ProcessInfo] VARCHAR(10) NULL, [Text] VARCHAR(MAX) NULL)
/*
Valid parameters for sp_readerrorlog
1 – Error log: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
2 – Log file type: 1 or NULL = error log, 2 = SQL Agent log
3 – Search string 1
4 – Search string 2
Change parameters to meet your needs
*/
— Read error log looking for the words RECOVERY
–and either FULL, SIMPLE or BULK_LOGGED indicating a change from prior state
INSERT INTO #SQLerrorlog
EXEC sp_readerrorlog 0, 1, ‘RECOVERY’, ‘FULL’
INSERT INTO #SQLerrorlog
EXEC sp_readerrorlog 0, 1, ‘RECOVERY’, ‘SIMPLE’
INSERT INTO #SQLerrorlog
EXEC sp_readerrorlog 0, 1, ‘RECOVERY’, ‘BULK_LOGGED’
UPDATE #SQLerrorlog
SET ProcessInfo = SUBSTRING(ProcessInfo,5,20)
FROM #SQLerrorlog
WHERE ProcessInfo LIKE ‘spid%’
— Get path of default trace file
SELECT @tracefile = CAST(value AS VARCHAR(500))
FROM sys.fn_trace_getinfo(DEFAULT)
WHERE traceid = 1
AND property = 2
— Get objects altered from the default trace
SELECT IDENTITY(int, 1, 1) AS RowNumber, *
INTO #temp_trc
FROM sys.fn_trace_gettable(@tracefile, default) g — default = read all trace files
WHERE g.EventClass = 164
SELECT t.DatabaseID, t.DatabaseName, t.NTUserName, t.NTDomainName,
t.HostName, t.ApplicationName, t.LoginName, t.SPID, t.StartTime, l.Text
FROM #temp_trc t
JOIN #SQLerrorlog l ON t.SPID = l.ProcessInfo
WHERE t.StartTime > GETDATE()-1 — filter by time within the last 24 hours
ORDER BY t.StartTime DESC
DROP TABLE #temp_trc
DROP TABLE #SQLerrorlog
GO
You can find more on the following:
sp_readerrorlog is an undocumented procedure that actually uses xp_readerrorlog – https://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/
sys.fn_trace_getinfo – https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-trace-getinfo-transact-sql
sys.fn_trace_gettable – https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-trace-gettable-transact-sql
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRxSupport@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!