{"id":89,"date":"2010-12-17T11:14:58","date_gmt":"2010-12-17T16:14:58","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=89"},"modified":"2014-11-13T14:04:05","modified_gmt":"2014-11-13T19:04:05","slug":"determine-the-uptime-of-a-sql-server-instance","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/","title":{"rendered":"Determine the Uptime of a SQL Server Instance"},"content":{"rendered":"

Every once in a while it is helpful to know the time that has passed since a SQL Server instance was last restarted. This information is particularly helpful when dealing with accumulating dynamic management views like sys<\/span><\/span><\/span><\/span>.<\/span><\/span><\/span><\/span>dm_db_index_usage_stats<\/span><\/span><\/span><\/span>.<\/p>\n

If you do a search in your favorite search engine, you come across four different ways to get to that information:<\/p>\n\n\n\n
\n

SELECT <\/span><\/span><\/span><\/span>DATEADD<\/span><\/span><\/span><\/span>(<\/span><\/span><\/span><\/span>ms<\/span><\/span><\/span>,<\/span><\/span><\/span><\/span>-<\/span><\/span><\/span><\/span>sample_ms<\/span><\/span><\/span>,<\/span><\/span><\/span><\/span>GETDATE<\/span><\/span><\/span><\/span>() )<\/span><\/span><\/span><\/span>AS <\/span><\/span><\/span><\/span>StartTime<\/span><\/span><\/span><\/p>\n

FROM <\/span><\/span><\/span><\/span>sys<\/span><\/span><\/span><\/span>.<\/span><\/span><\/span><\/span>dm_io_virtual_file_stats<\/span><\/span><\/span><\/span>(<\/span><\/span><\/span><\/span>1<\/span><\/span><\/span>,<\/span><\/span><\/span><\/span>1<\/span><\/span><\/span>);<\/span><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

\n\n\n\n
\n

SELECT <\/span><\/span><\/span><\/span>create_date<\/span><\/span><\/span>\u00a0 AS <\/span><\/span><\/span><\/span>StartTime<\/span><\/span><\/span><\/p>\n

FROM <\/span><\/span><\/span><\/span>sys<\/span><\/span><\/span><\/span>.<\/span><\/span><\/span><\/span>databases<\/span><\/span><\/span><\/span><\/p>\n

WHERE <\/span><\/span><\/span><\/span>name<\/span><\/span><\/span> = <\/span><\/span><\/span><\/span>'tempdb'<\/span><\/span><\/span><\/span>;<\/span><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

\n\n\n\n
\n

SELECT <\/span><\/span><\/span><\/span>login_time <\/span><\/span><\/span> AS <\/span><\/span><\/span><\/span>StartTime<\/span><\/span><\/span><\/p>\n

FROM <\/span><\/span><\/span><\/span>sys<\/span><\/span><\/span><\/span>.<\/span><\/span><\/span><\/span>dm_exec_sessions<\/span><\/span><\/span><\/span><\/p>\n

WHERE <\/span><\/span><\/span><\/span>session_id<\/span><\/span><\/span> =<\/span><\/span><\/span><\/span>1<\/span><\/span><\/span>;<\/span><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

\n\n\n\n
\n

EXEC <\/span><\/span><\/span><\/span>xp_readerrorlog<\/span><\/span><\/span><\/span>;<\/span><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

\n

So let us examine them a little closer:<\/p>\n

According to BOL the column sample_ms<\/span><\/span><\/span> in sys<\/span><\/span><\/span><\/span>.<\/span><\/span><\/span><\/span>dm_io_virtual_file_stats<\/span><\/span><\/span><\/span> contains the \u201cNumber of milliseconds since the computer was started.<\/span><\/span><\/span>\u201d That means it does tell us how long ago the computer running SQL Server was restarted. It does however not tell us, if SQL Server was recycled since then.<\/p>\n

That means the information returned by sys<\/span><\/span><\/span><\/span>.<\/span><\/span><\/span><\/span>dm_io_virtual_file_stats<\/span><\/span><\/span><\/span> does not give us a reliable way to determine the SQL Server instance uptime.<\/p>\n

BOL says: \u201ctempdb<\/span><\/span><\/span>is re-created each time the instance of SQL Server is started<\/span><\/span><\/span>\u201d. It also states that you can\u2019t drop, restore, take offline or remove tempdb in any other way from a running system. So the create_date<\/span><\/span><\/span> returned from sys<\/span><\/span><\/span><\/span>.<\/span><\/span><\/span><\/span>databases<\/span><\/span><\/span><\/span> for tempdb should get us close to the time we are looking for. However, creating tempdb is not the first step SQL Server executes when starting up. Most other databases get initialized first. Depending on the amount of recovery work that needs to be done, there can be a significant difference between the instance start time and the creation time of tempdb.<\/p>\n

Moving on: SQL Server has several system processes running. We all have heard of SPID < 50 means it is a system process. However, as many recently discovered, this undocumented feature you cannot rely on. SQL Server is free to use any SPID for system processes. While it is not likely to change, I recommend to never base production code on undocumented features.<\/p>\n

Also, in my experience, the start time returned by sys<\/span><\/span><\/span><\/span>.<\/span><\/span><\/span><\/span>dm_exec_sessions<\/span><\/span><\/span><\/span> is most of the time later than the start time returned by sys<\/span><\/span><\/span><\/span>.<\/span><\/span><\/span><\/span>databases<\/span><\/span><\/span><\/span>. So, this one is out too.<\/p>\n

That leaves us with xp_readerrorlog<\/span><\/span><\/span><\/span>. BOL tells us that \u201cA new error log is created each time an instance of SQL Server is started<\/span><\/span><\/span>\u201d. That is great news. If you look at the output of this procedure the first few lines look similar to this:<\/p>\n\n\n\n\n\n\n\n\n\n
\n

LogDate<\/p>\n<\/td>\n

\n

ProcessInfo<\/p>\n<\/td>\n

\n

Text<\/p>\n<\/td>\n<\/tr>\n

\n

2010-12-17 14:46:01.570<\/p>\n<\/td>\n

\n

spid53<\/p>\n<\/td>\n

\n

Microsoft SQL Server 2008 (SP1) \u2026<\/p>\n<\/td>\n<\/tr>\n

\n

2010-12-17 14:46:01.570<\/p>\n<\/td>\n

\n

spid53<\/p>\n<\/td>\n

\n

(c) 2005 Microsoft Corporation.<\/p>\n<\/td>\n<\/tr>\n

\n

2010-12-17 14:46:01.580<\/p>\n<\/td>\n

\n

spid53<\/p>\n<\/td>\n

\n

All rights reserved.<\/p>\n<\/td>\n<\/tr>\n

\n

2010-12-17 14:46:01.580<\/p>\n<\/td>\n

\n

spid53<\/p>\n<\/td>\n

\n

Server process ID is 7260.<\/p>\n<\/td>\n<\/tr>\n<\/table>\n

\n

Those entries where created when the error log file was created. Creating this file is one of the first steps SQL Server executes, so this gives us the closest estimate of our server start time yet.<\/p>\n

But the above quoted sentence about the error log in BOL continues on to tell us that \u201cthe sp_cycle_errorlog system stored procedure can be used to cycle the error log files without having to restart the instance of SQL Server<\/span><\/span><\/span>\u201d. After that procedure executes, a new log file is created and the information shown above is written into it, only with a new time stamp. There is no simple way to detect that this happened and then go and find the old log file that contains the real instance start time if it even still exists. That together with the fact that xp_readerrorlog<\/span><\/span><\/span><\/span> is also an undocumented procedure makes it not very helpful for our purposes.<\/p>\n

So in the end the closest we can get to the start time of the SQL Server instance is the creation date of the tempdb database. While it is not a 100% accurate reflection of the real instance start time, it is a reliable way to get fairly close to it. Considering that not really a lot happens on a SQL Server instance until all databases, including tempdb, are initialized, it is probably the better time to use anyway in most practical applications.<\/p>\n

UPDATE:<\/p>\n

After talking to my friend Kalen Delaney<\/a>, I have to post an update to this blog post. Starting with SQL Server 2008, there actually is a reliable way to get the exact SQL Server instance start time. It is documented and the time returned is actually even a little earlier than the time you can find in the error log. That means it is the real start time of the SQL Server service. So I have to change my recommendation: if you are looking for the start time of your SQL Server instance use this query:<\/p>\n\n\n\n
\n

SELECT<\/span><\/span><\/span> sqlserver_start_time<\/span><\/span><\/p>\n

FROM<\/span><\/span><\/span> sys<\/span><\/span><\/span>.<\/span><\/span><\/span>dm_os_sys_info<\/span><\/span><\/span>;<\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

\n","protected":false},"excerpt":{"rendered":"

Every once in a while it is helpful to know the time that has passed since a SQL Server instance was last restarted. This information is particularly helpful when dealing with accumulating dynamic management views like sys.dm_db_index_usage_stats. If you do [more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5],"tags":[],"class_list":["post-89","post","type-post","status-publish","format-standard","hentry","category-general"],"acf":[],"yoast_head":"\nDetermine the Uptime of a SQL Server Instance<\/title>\n<meta name=\"description\" content=\"examines different methods to determine the start time of a SQL Server instance\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Determine the Uptime of a SQL Server Instance\" \/>\n<meta property=\"og:description\" content=\"examines different methods to determine the start time of a SQL Server instance\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2010-12-17T16:14:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T19:04:05+00:00\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/89\\\/determine-the-uptime-of-a-sql-server-instance\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/89\\\/determine-the-uptime-of-a-sql-server-instance\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Determine the Uptime of a SQL Server Instance\",\"datePublished\":\"2010-12-17T16:14:58+00:00\",\"dateModified\":\"2014-11-13T19:04:05+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/89\\\/determine-the-uptime-of-a-sql-server-instance\\\/\"},\"wordCount\":820,\"commentCount\":4,\"articleSection\":[\"General\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/89\\\/determine-the-uptime-of-a-sql-server-instance\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/89\\\/determine-the-uptime-of-a-sql-server-instance\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/89\\\/determine-the-uptime-of-a-sql-server-instance\\\/\",\"name\":\"Determine the Uptime of a SQL Server Instance\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2010-12-17T16:14:58+00:00\",\"dateModified\":\"2014-11-13T19:04:05+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"examines different methods to determine the start time of a SQL Server instance\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/89\\\/determine-the-uptime-of-a-sql-server-instance\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/89\\\/determine-the-uptime-of-a-sql-server-instance\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/89\\\/determine-the-uptime-of-a-sql-server-instance\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Determine the Uptime of a SQL Server Instance\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Determine the Uptime of a SQL Server Instance","description":"examines different methods to determine the start time of a SQL Server instance","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/","og_locale":"en_US","og_type":"article","og_title":"Determine the Uptime of a SQL Server Instance","og_description":"examines different methods to determine the start time of a SQL Server instance","og_url":"https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2010-12-17T16:14:58+00:00","article_modified_time":"2014-11-13T19:04:05+00:00","author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Determine the Uptime of a SQL Server Instance","datePublished":"2010-12-17T16:14:58+00:00","dateModified":"2014-11-13T19:04:05+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/"},"wordCount":820,"commentCount":4,"articleSection":["General"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/","url":"https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/","name":"Determine the Uptime of a SQL Server Instance","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2010-12-17T16:14:58+00:00","dateModified":"2014-11-13T19:04:05+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"examines different methods to determine the start time of a SQL Server instance","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/89\/determine-the-uptime-of-a-sql-server-instance\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Determine the Uptime of a SQL Server Instance"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-1r","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/89","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=89"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/89\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=89"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=89"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=89"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}