Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am looking for a way to find out when the Windows Server was last started using only t-sql commands.

I am trying to stay within the default security configurations (i.e. I don't want to enable xp_cmdshell)

share|improve this question

3 Answers 3

up vote 10 down vote accepted

You can use the ms_ticks column from sys.dm_os_sys_info. This is the number of milliseconds since the computer was started.

SELECT DATEADD(SECOND, (ms_ticks/1000)*(-1), GETDATE())
    FROM sys.dm_os_sys_info

Will get you this information.

share|improve this answer
1  
@datagod Thanks for the edit. Good catch and sorry about that. –  swasheck Jul 31 '13 at 15:46

Another way apart from sys.dm_os_sys_info is to use

sys.dm_io_virtual_file_stats --> column sample_ms -> Number of milliseconds since the computer was started

-- find windows server restart date:

SELECT DATEADD(ms,-sample_ms,GETDATE() )AS StartTime
FROM sys.dm_io_virtual_file_stats(1,1);
share|improve this answer
    
Thanks, much better signal:noise. :-) –  Aaron Bertrand Jul 31 '13 at 16:24
    
I just ran this code on my machine, and it gave me a date in the future -- it looks like the sample_ms column is negative in the DMV (removing the negative sign gave me the correct answer). I tried 3 other servers and the code works okay as-is. Interesting. –  Jon Seigel Jul 31 '13 at 16:31
    
@JonSeigel Interesting ... I ran it on 5 different servers and I got the same results .. no change in code .. working perfectly fine. –  Kin Jul 31 '13 at 16:35
2  
Ah, I see. The DMV returns an int value. That means the value flips +/- every ~25 days and wraps every ~50. This is a bug in the DMV -- it should return bigint. This hasn't been fixed in 2012 either. –  Jon Seigel Jul 31 '13 at 16:36
5  
Bug reported here: connect.microsoft.com/SQLServer/feedback/details/795556 –  Jon Seigel Jul 31 '13 at 16:55

Perhaps you should be using Powershell instead of T-SQL to build whatever you're using this information for? See the SQL Server Powershell Provider on MSDN for more info.

You can use SQLPS to do any database queries that you need, while still using PowerShell to access all the WMI objects you could ever need.

share|improve this answer
    
I agree powershell is powerful, but does have a steep learning curve. Not every server has it installed. The SQL script I am using is a general health check and runs on version going back as far as SQL2000. It is nice and simple but provides a ton of information for the DBA. –  datagod Aug 1 '13 at 14:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.