How to Tell When SQL Server Started?
April 28, 2010 13 Comments
As a consultant, I’m regularly asked to diagnose and solve problems when I have incomplete or conflicting information given to me by the client. They don’t mean any harm, but different people in the organization have different perspectives and different memories. Sometimes it is difficult to know what is really happening.
When I’m working with systems like SQL Server, it is always good to gather as much information from the system itself as I can and not rely too heavily on someone else’s memory. Trust but verify as President Ronald Reagan once said.
When was the server restarted?
SQL Server maintains a lot of information about how it’s doing and when certain events ocurred. Much of this information resides in the Dynamic Management Views (DMVs) of SQL Server 2005 and 2008.
For example, let’s say we want to know when the SQL Server instance was last restarted. When can easily collect this information using a quick query. There are several other methods as well but this one is my preferred way.
SELECT
Login_Time
FROM
sys.dm_exec_sessions
WHERE
Session_Id = 1;
Additional Resources
SQL Server Books Online has more information about this and other DMVs. Additionally there are some other great resources on the web.
- Glenn Berry’s DMV a Day blog series
- Eric Brown’s Simple-Talk article Troubleshooting with Dynamic Management Views
- MSSQLTips articles
And there are many, many more. Just use your favorite search engine and begin using the DMVs to make your job a little easier.
Question:
- What’s your favorite DMV?
I also like this query:
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
It only works on SQL Server 2008 or above.
I have been using this query. Almost the same time.
SELECT create_date from sys.databases where name = ‘tempdb’
Hi Sankar – Yes, I like that one as well since tempdb is recreated each time the service starts.
Thanks for sharing the new one, Glenn! I’ve got some catching up to do on my blog reading, but the ones I’ve read from your DMV series have been great.
Pingback: Dew Drop – April 29, 2010 | Alvin Ashcraft's Morning Dew
On 2005/2008 you can also do
select
start_time
from
sys.traces
where
is_default = 1
Jack,
Its a neat trick but what happens if the default trace is disabled? I am NOT recommending to disable default trace but being on the forums there are quite a few ppl who disable the default trace.
Pingback: Tweets that mention How to Tell When SQL Server Started? « WebbTech Solutions -- Topsy.com
Pingback: What We’re Reading: May 7th 2010 | Brent Ozar - Too Much Information
I usually do not drop a comment, but after browsing a few of the remarks
on How to Tell When SQL Server Started? | WebbTech
Solutions. I do have some questions for you if it’s okay. Is it simply me or do a few of these remarks appear like they are written by brain dead visitors?😛 And, if you are writing on other online social sites, I’d like
to follow anything fresh you have to post. Would you make a list of
every one of all your social community pages like your Facebook page, twitter feed, or linkedin profile?
I am truly grateful to the holder of this web site who has shared this fantastic piece of writing at at this time.
Our hacking group overhauls Deer Hunter 2014 Hack like clockwork so will possibly
be operating for an exceptionally extended time.
Pingback: flyff all stars hack tool