Knowing SQL Server uptime and restart time is important to ensure optimal performance and minimize the risk of downtime and data loss for businesses relying on a SQL Server database. There are several ways to check when the SQL Server Instance last started, such as using the DMV (sys.dm_os_sys_info) that contains a column holding the startup time for SQL Server, checking when the TempDB was created or last restarted, and monitoring the First Login or First Batch to determine server performance. These tasks can be easily achieved with T-SQL scripts, which offer a quick and efficient way to perform these checks and monitor processes, ensuring optimal SQL Server performance and minimizing the risk of downtime and data loss. Regular use of these scripts is a valuable tool for database administrators to keep SQL Server running smoothly and effectively.
/* This script uses the sys.dm_os_sys_info dynamic management view to
retrieve the date and time when the SQL Server service was started. */
SELECT
sqlserver_start_time
--, *
FROM sys.dm_os_sys_info
/* This script queries the sys.databases system catalog view to find the
date and time when the TempDB database was created or last restarted. */
SELECT
LastServerRestart = create_date
FROM sys.databases
WHERE
[name] = 'tempdb'
/* This script queries the sys.sysprocesses system catalog view to retrieve information about user processes
on the server, including the date and time of the first login and the date and time of the first batch. */
SELECT
MIN(login_time) as LastServerRestart1
, MIN(last_batch) as LastServerRestart2
FROM sys.sysprocesses
/* The above scripts can be useful for monitoring SQL Server uptime, identifying server restarts or downtime, and troubleshooting performance issues. */
Source: mssqltips
