Monday, April 9, 2012

SQL Server Start Time

Have you ever needed to determine when SQL Server Services were started? There are two very simple methods depending on which version of SQL you are running.
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.

[sql]
-- Steven Ormrod
-- 5/31/2011
-- SQL Server Start Time

-- sql 2005
select create_date from sys.databases
where database_id = 2
go

[/sql]

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]

-- sql 2008, 2012
select sqlserver_start_time from sys.dm_os_sys_info
go

[/sql]

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.

1 comment:

  1. [...] http://stevenormrod.com/2012/04/sql-server-start-time/ Share this:TwitterFacebookLike this:LikeBe the first to like this [...]

    ReplyDelete