SQL 2005 - TempDB Tattle Tale
TempDB is recreated each time SQL is started or restarted. So this is a nice way to determine what time SQL Server services started.
-- Steven Ormrod
-- SQL Server Start Time
-- sql 2005
select create_date from sys.databases
where database_id = 2
|SQL Start Time from TempDB|
SQL 2008 - DMVs Delight
The DMV sys.dm_os_sys_info will give you some basic info about the underlying operating system. For example, it will tell you about the CPU cores, physical and virtual memory, buffer pool, and os workers and schedulers.
SQL 2008 introduced a new column, sqlserver_start_time, to this DMV that returns when SQL Server last started.
-- sql 2008, 2012
select sqlserver_start_time from sys.dm_os_sys_info
|SQL Start Time from DMVs|
Collecting DMV Stats
The main reason I am curious to know when SQL Server last started is when trouble shooting performance problems. Several of the DMVs collect stats and information about SQL Server. However, this information is wiped clean when SQL Server is restarted.
So if the stats have only been collecting for a short time, then I may not be able to draw a strong conclusion from them. Generally speaking, I like to see stats collected for at least one business cycle. For some situations, one day may be enough, or you may need a month or more. Each situation will be different.