I ran across a thread recently where someone was asking how to get the Windows start time from within SQL Server. Since SQL Server is a process running on the Windows host, I wasn’t sure how one would do this without running something like Uptime from xp_cmdshell. There was an answer, however, that I found very interesting and hadn’t realized would provide the information.

SELECT
DATEADD(s,((-1)*([ms_ticks]/1000)),GETDATE()) AS last_restart
FROM sys.[dm_os_sys_info];

This queries the DMV sys.dm_os_sys_info, which contains all sorts of information from the host OS. You can get:

• logical CPUs
• the hyperthreading ratio
• physical memory
• virtual memory
• schedulers
• affinity
• virtualized environment
• virtualization hypervisor

and more. It’s a view into a few OS level items that you don’t need often, but which you might want to check when troubleshooting some issues. Knowing that the OS restarted, in addition to the SQL service, can be valuable.

Filed under: Blog Tagged: sql server, syndicated