Here is a T-SQL script which tells when and who took the database offline or online recently.
This script utilizes the default trace and if the trace is reset after the database went offline or online then you have change the trace file path and name in the script.
This script utilizes the default trace and if the trace is reset after the database went offline or online then you have change the trace file path and name in the script.
DECLARE @DBNAME nvarchar(100) ,@FileName nvarchar(max) ,@spid int ,@LogDate Datetime ,@Status nvarchar(10) SET @DBNAME = 'AdventureWorks2008R2' -- Change DB Name SET @Status = 'OFFLINE' --[OFFLINE or ONLINE] SELECT @FileName=[path] FROM sys.traces WHERE is_default=1 DECLARE @ErrorLogTable table (Logdate datetime, ProcessInfo nvarchar(10), [Text] nvarchar(max)) INSERT INTO @ErrorLogTable EXEC xp_readerrorlog 0,1, @Status, @DBNAME, NULL, NULL, 'desc' SELECT TOP 1 @spid=cast(SUBSTRING(ProcessInfo,5,5) AS int) ,@LogDate=cast(Logdate AS nvarchar) FROM @ErrorLogTable SELECT DatabaseID, DatabaseName, HostName, ApplicationName, LoginName, StartTime FROM sys.fn_trace_gettable( @FileName, DEFAULT ) WHERE spid=@spid and DatabaseName=@DBNAME and CAST(StartTime AS nvarchar)=@LogDate
No comments:
Post a Comment