Speak guys!
In this post today, I would like to share a solution I created to answer a question in one of the Telegram groups in which I participate, which was the question of how to know how long a database has been online in SQL Server. That is not how long has the instance been online but how long has a database been online.
To solve this doubt, I thought about using the SQL Server log itself to identify when each database was started and return to the user. The query below will return all banks that are in the last log file, along with the date of the last time the bank was started and the time since then to the current time.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
IF (OBJECT_ID('tempdb..#StartupDB') IS NOT NULL) DROP TABLE #StartupDB CREATE TABLE #StartupDB ( [LogNumber] TINYINT, [LogDate] DATETIME, [ProcessInfo] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI, [Text] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AI, [Database] AS (REPLACE(REPLACE(SUBSTRING([Text], CHARINDEX('''', [Text]), 128), '''', ''), '.', '')) ) INSERT INTO #StartupDB (LogDate, ProcessInfo, [Text]) EXEC master.dbo.sp_readerrorlog 0, 1, N'Starting up database ', NULL SELECT [Database], MAX(LogDate) AS LogDate, (CASE WHEN DATEDIFF(SECOND, MAX(LogDate), GETDATE()) > 86400 THEN CAST(DATEDIFF(SECOND, MAX(LogDate), GETDATE()) / 86400 AS VARCHAR) + 'd ' ELSE '' END) + RIGHT('00' + CAST((DATEDIFF(SECOND, MAX(LogDate), GETDATE()) / 3600) % 24 AS VARCHAR), 2) + ':' + RIGHT('00' + CAST((DATEDIFF(SECOND, MAX(LogDate), GETDATE()) / 60) % 60 AS VARCHAR), 2) + ':' + RIGHT('00' + CAST(DATEDIFF(SECOND, MAX(LogDate), GETDATE()) % 60 AS VARCHAR), 2) + '.' + RIGHT('000' + CAST(DATEDIFF(SECOND, MAX(LogDate), GETDATE()) AS VARCHAR), 3) AS TimeRunning FROM #StartupDB GROUP BY [Database] ORDER BY 1 |
Result:
In case you want to analyze the entire startup history of the databases, scanning through all the log files (not just the last one):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
IF (OBJECT_ID('tempdb..#Arquivos_Log') IS NOT NULL) DROP TABLE #Arquivos_Log CREATE TABLE #Arquivos_Log ( [idLog] INT, [dtLog] NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AI, [tamanhoLog] INT ) IF (OBJECT_ID('tempdb..#StartupDB') IS NOT NULL) DROP TABLE #StartupDB CREATE TABLE #StartupDB ( [LogNumber] TINYINT, [LogDate] DATETIME, [ProcessInfo] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI, [Text] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AI, [Database] AS (REPLACE(REPLACE(SUBSTRING([Text], CHARINDEX('''', [Text]), 128), '''', ''), '.', '')) ) INSERT INTO #Arquivos_Log EXEC sys.sp_enumerrorlogs DECLARE @Contador INT = 0, @Total INT = (SELECT COUNT(*) FROM #Arquivos_Log) WHILE(@Contador < @Total) BEGIN INSERT INTO #StartupDB (LogDate, ProcessInfo, [Text]) EXEC master.dbo.xp_readerrorlog @Contador, 1, N'Starting up database ', NULL, NULL, NULL -- Atualiza o número do arquivo de log UPDATE #StartupDB SET LogNumber = @Contador WHERE LogNumber IS NULL SET @Contador += 1 END SELECT * FROM #StartupDB ORDER BY LogDate DESC |
Result:
In the two solutions presented, you can use the “Database” column to filter the selected banks and I believe that the original question was answered by these 2 scripts.
That's it, guys! I hope you liked this tip. Do you know any other solution to answer this question? Leave it here in the comments 🙂
A big hug and until next time!
Grande Dirceu, sensational the solution.
Congratulations….