February 20, 2011 at 2:42 pm
Anyone know if its possible and if so how to get the system/sever uptime using T-SQL? I found a nice little Powershell script that I was going to use with the xp_CmdShell XSP but I'm having problems getting it to work and that got me think that maybe there's a way to get this using native SQL Server functionality. I know how to get the date/time for when SQL Server started last but thats not necessairly the same as when the system restarted.
My preference is to go with as much native T-SQL functionality as possible and not relying on outside code even PowerShell for there always seems to be problems with porting it to other SQL Server instances. This Powershell code I'm using works when run from the PowerShell GUI (Powershell - ISE) so I know it’s right but xp_CmdShell balks at it. I have verified I can run PowerShell from xp_CmdShell for a simple command like "dir *.* " works.
Any comments or suggestions are appreciated as always.
BTW - The error I get is returned as the following 3 lines (from Output window in query tool). If anyone reading this knows how to address this that woudl be great too.
'$wmi' is not recognized as an internal or external command,
operable program or batch file.
NULL
Kindest Regards,
Just say No to Facebook!February 20, 2011 at 2:46 pm
just a quick copy/paste from my script library:
-- how to determin SQLServer Uptime
-- zie Tracking Uptime by Brian Moran http://www.winnetmag.com/SQLServer/Article/ArticleID/38042/SQLServer_38042.html
--
-- dd 20060912 JOBI - aangepast voor SQL2005 (DMV)
--
-- new SQL2008
select sqlserver_start_time
from sys.dm_os_sys_info
SELECT @@servername as ServerName, datediff(mi, login_time, getdate()) as SQLServer_UpTime_Minutes
FROM sys.dm_exec_sessions
WHERE session_id = 1
go
SELECT @@servername as ServerName, login_time as StartUp_DateTime
FROM sys.dm_exec_sessions
WHERE session_id = 1
go
SELECT @@servername as ServerName, getdate() - login_time as SQLServer_UpDateTime_1900_01_01
FROM sys.dm_exec_sessions
WHERE session_id = 1
go
SELECT @@servername as ServerName, Year( SQLServer_UpTime) - 1900 - case when month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end < 0 then 1 else 0 end as Years
, month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end as Months
, day( SQLServer_UpTime) - 1 as Days
, substring(convert(varchar(25), SQLServer_UpTime,121),12,8) as Timepart
from (
SELECT getdate() - login_time as SQLServer_UpTime -- opgepast start vanaf 1900-01-01
FROM sys.dm_exec_sessions
WHERE session_id = 1
) a
go
SELECT @@servername as ServerName
, SQLServer_UpTime_YY
, SQLServer_UpTime_MM
, SQLServer_UpTime_DD
, (SQLServer_UpTime_mi % 1440) / 60 as NoHours
, (SQLServer_UpTime_mi % 60) as NoMinutes
from (
SELECT datediff(yy, login_time, getdate()) as SQLServer_UpTime_YY
, datediff(mm, dateadd(yy,datediff(yy, login_time, getdate()),login_time) , getdate()) as SQLServer_UpTime_MM
, datediff(dd, dateadd(mm,datediff(mm, dateadd(yy,datediff(yy, login_time, getdate()),login_time) , getdate()) ,login_time) , getdate()) as SQLServer_UpTime_DD
, datediff(mi, login_time, getdate()) as SQLServer_UpTime_mi
FROM sys.dm_exec_sessions
WHERE session_id = 1
) a
go
SELECT @@servername as ServerName
, Year(SQLServer_UpTime) - 1900 - case when month(SQLServer_UpTime) - 1 - case when day(SQLServer_UpTime) - 1 < 0 then 1
else 0
end < 0 then 1
else 0
end as Years
, month(SQLServer_UpTime) - 1 - case when day(SQLServer_UpTime) - 1 < 0 then 1
else 0
end as Months
, day(SQLServer_UpTime) - 1 as Days
, substring(convert(varchar(25), SQLServer_UpTime, 121), 12, 8) as Timepart
, create_date as tsStartup
, GETDATE() as now
from (
SELECT create_date, getdate() - create_date as SQLServer_UpTime -- opgepast start vanaf 1900-01-01
FROM sys.databases
WHERE name = 'tempdb'
) x
;WITH cteServerUpTimeInfo AS (
SELECT dm_io_virtual_file_stats.sample_ms / 1000.00 AS server_up_time_sec
,(dm_io_virtual_file_stats.sample_ms / 1000.00 ) / 60.00 AS server_up_time_min
,((dm_io_virtual_file_stats.sample_ms / 1000.00 ) / 60.00) / 60.00 AS server_up_time_hr
,(((dm_io_virtual_file_stats.sample_ms / 1000.00 ) / 60.00) / 60.00) / 24.00 AS server_up_time_day
FROM sys.dm_io_virtual_file_stats(1,1) AS dm_io_virtual_file_stats )
SELECT CAST(server_up_time_min AS decimal(12,2)) AS server_up_time_min
,CAST(server_up_time_hr AS decimal(12,2)) AS server_up_time_hr
,CAST(server_up_time_day AS decimal(12,2)) AS server_up_time_day
,CAST(DATEADD(ss,-server_up_time_sec,GETDATE()) AS smalldatetime) AS approx_server_start_datetime
,CAST(DATEADD(ss,-server_up_time_sec,GETUTCDATE()) AS smalldatetime) AS approx_server_start_utc_datetime
FROM cteServerUpTimeInfo;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 21, 2011 at 11:31 am
Thanks for sharing ALZDBA but I think thats just for showing how long SQL Server has been running and not the uptime for the Windosw Server its running on. If that incorrect let me know. Also from the notes it looks like that may be for SQL Server 2008 only, is that correct?
Kindest Regards,
Just say No to Facebook!February 21, 2011 at 12:26 pm
Have you tried using the windows UPTIME command?
Gives detailed command help:
uptime /help
Gives system uptime:
uptime \\MyServerName
\\MyServerName has been up for: 26 day(s), 9 hour(s), 17 minute(s), 56 second(s)
Gives history of system shutdowns and restrarts:
uptime \\MyServerName /s
Uptime Report for: \\MyServer
Current OS: Microsoft Windows Server 2003, Service Pack 2, Multiprocessor Free.
Time Zone: Eastern Standard Time
System Events as of 2/21/2011 2:20:20 PM:
Date: Time: Event: Comment:
---------- ----------- ------------------- -----------------------------------
4/16/2009 3:22:16 PM Shutdown
4/16/2009 3:23:16 PM Boot Prior downtime:0d 0h:1m:0s
4/16/2009 3:42:42 PM Shutdown Prior uptime:0d 0h:19m:26s
...
...
2/11/2011 10:48:27 AM Abnormal Shutdown Prior uptime:149d 0h:37m:54s
2/11/2011 10:50:27 AM Boot Prior downtime:0d 0h:2m:0s
Current System Uptime: 10 day(s), 3 hour(s), 30 minute(s), 29 second(s)
--------------------------------------------------------------------------------
Since 4/16/2009:
Total Reboots: 22
Mean Time Between Reboots: 30.73 days
Total Bluescreens: 0
--------------------------------------------------------------------------------
Since 4/22/2009:
System Availability: 99.9474%
Total Uptime: 669d 22h:21m:24s
Total Downtime: 0d 8h:28m:9s
Total Reboots: 16
Mean Time Between Reboots: 41.89 days
Total Bluescreens: 0
Notes:
4/22/2009 is the earliest date in the event log where
sufficient information is recorded to calculate availability.
February 21, 2011 at 4:56 pm
I don't think uptime is installed by default, you have to download it, so your servers might not have it.
This any good via xp_cmdshell?
http://support.microsoft.com/kb/555737
---------------------------------------------------------------------
February 21, 2011 at 5:29 pm
george sibbald (2/21/2011)
I don't think uptime is installed by default, you have to download it, so your servers might not have it.This any good via xp_cmdshell?
The UPTIME.EXE only has to be on the server where you are running it, not on the remote server you are trying to get uptime info for.
February 22, 2011 at 12:12 am
YSLGuru (2/21/2011)
Thanks for sharing ALZDBA but I think thats just for showing how long SQL Server has been running and not the uptime for the Windosw Server its running on. If that incorrect let me know. Also from the notes it looks like that may be for SQL Server 2008 only, is that correct?
Oh, darn ... I must have overlooked you were actually looking for the OS-uptime. My bad :blush:
It's only the first select statement that is new for SQL2008. (new dmv)
The others also work on sql2005.
Maybe one of the dm_OS* dmos expose os uptime.
I'll check.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 22, 2011 at 12:50 am
I didn't find it in sys.dm_os at a first glance ...
so here are some examples of how you can do it with WMI and/or Powershell
http://social.technet.microsoft.com/Forums/en-GB/ITCG/thread/c4e577d3-ee7f-42a7-83da-375361d56bde
Integratable in CLR :crazy:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 22, 2011 at 3:33 am
Michael Valentine Jones (2/21/2011)
george sibbald (2/21/2011)
I don't think uptime is installed by default, you have to download it, so your servers might not have it.This any good via xp_cmdshell?
The UPTIME.EXE only has to be on the server where you are running it, not on the remote server you are trying to get uptime info for.
very true. good point. Which is when you have to specify the server name you want uptime for.
---------------------------------------------------------------------
February 22, 2011 at 3:43 am
primitive but working ....
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Management
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function ufn_GetOSStartUpTime() As SqlDateTime
' Add your code here
'http://social.msdn.microsoft.com/Forums/en-CA/sqlnetfx/thread/9a7c9f46-f197-4ca6-916a-d13c95a15770
Dim mosOS As New ManagementObjectSearcher("SELECT * FROM Win32_OperatingSystem")
Dim _str As String
Dim _dt As Date
For Each moOS As ManagementObject In mosOS.[Get]()
_str = moOS("LastBootUpTime").ToString
Next
_dt = ManagementDateTimeConverter.ToDateTime(_str)
Return New SqlDateTime(_dt)
End Function
End Class
select SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') as HW_ComputerName
, dbo.ufn_GetOSStartUpTime() as Os_Uptime
/*
HW_ComputerNameOs_Uptime
WS200980022011-02-22 07:38:28.377
*/
Think about the consequences of opening Pandoras box ( CLR )
You need to:
/*
exec sp_configure 'clr enabled', 1
reconfigure
go
alter database YourDB set trustworthy on;
use YourDb
CREATE ASSEMBLY [System.Management] AUTHORIZATION [dbo]
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'
WITH PERMISSION_SET = UNSAFE
*/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 22, 2011 at 3:12 pm
Thanks guys for replying with the suggestions. I actually stumbled across another method that I went with because of its ease of use. It does require using the Extended SP xp__CmdShell which some may not want to use in their environment but its fine for what I needed. The complete code to get the Uptime for any server on the domain is:
CREATE PROCEDURE spWD_GetLastSystemBoot @sSystemName VARCHAR(256) /*i.e. 'DBA'*/
AS
BEGIN
DECLARE @dtLastBoot DATETIME
DECLARE @dtBootDate DATETIME
DECLARE @sRetVal VARCHAR(256)
DECLARE @sRetTimestamp VARCHAR(256)
DECLARE @sRetTime VARCHAR(256)
DECLARE @sPSCmd VARCHAR(8000)
DECLARE @sServer VARCHAR(256)
DECLARE @sBootHour CHAR(2)
DECLARE @sBootMin CHAR(2)
DECLARE @sBootSec CHAR(2)
SELECT @sServer = @sSystemName
SELECT @sPSCmd = 'powershell.exe -c '
SELECT @sPSCmd = @sPSCmd + '$wmi=Get-WmiObject -class Win32_OperatingSystem -computer ' + @sServer + '; $wmi.Lastbootuptime;"'
CREATE TABLE #TEMP_OUTPUT (sCmdMsgLine01 VARCHAR(255))
INSERT #TEMP_OUTPUT
EXECUTE master..xp_cmdshell @sPSCmd
SELECT @sRetVal = T.sCmdMsgLine01,
@sRetTimestamp = Left(T.sCmdMsgLine01,14),
@dtBootDate = Convert(DATETIME, Left( Left(@sRetVal,14),8)),
@sRetTime = Right(Left(T.sCmdMsgLine01,14),6)
FROM #TEMP_OUTPUT T
WHERE 1 = 1
AND T.sCmdMsgLine01 IS NOT NULL
DROP TABLE #TEMP_OUTPUT
SELECT @sBootHour = Left(@sRetTime,2),
@sBootMin = Substring(@sRetTime,3,2),
@sBootSec = Right(@sRetTime,2)
SELECT @dtBootDate = DateAdd(hh,( Convert(INT,@sBootHour) ), @dtBootDate)
SELECT @dtBootDate = DateAdd(mi,( Convert(INT,@sBootMin) ), @dtBootDate)
SELECT @dtBootDate = DateAdd(ss,( Convert(INT,@sBootsec) ), @dtBootDate)
SELECT @dtBootDate AS 'dtLastReboot', @sServer AS 'sSystem',
DateDiff(dy,@dtBootDate, GetDate()) AS 'iUpDays',
DateDiff(hh,@dtBootDate, GetDate()) AS 'iUpHours',
DateDiff(mi,@dtBootDate, GetDate()) AS 'iUpMinutes',
DateDiff(ss,@dtBootDate, GetDate()) AS 'iUpSeconds'
END
With this method I can execute the SP passing any server on my domain and get the uptime details including duration. I listed the multiple variations on duration because I had a few of the IT guys wanting a reliable way to get this same data when dealing with virtual servers which apparently can be problematic.
This can be easily changed to return just the time the system last rebooted or just the uptime (duration) in whichever value you want . I'm using it to compare SQL Serverice startups against system reboots to find the times when the service is restarted and the OS has not.
Thanks again,
Kindest Regards,
Just say No to Facebook!November 18, 2016 at 8:27 am
exec xp_cmdshell 'systeminfo|find "Time:"'
This works for me
November 18, 2016 at 8:44 am
this is what i've used for quite a long time:
I wanted both server uptime and the SQLService uptime.
i made a simple stored proc, sp_help_uptime and put it in master, that returns results form a dmv:
--Results:
ServerRestartTime ServiceRestartTime ServiceUpTimeInDays ServerUpTimeInDays
2016-10-15 00:20:34.153 2016-11-11 10:15:24.023 7 34
the proc:
IF OBJECT_ID('[dbo].[sp_help_uptime]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_help_uptime]
GO
CREATE PROCEDURE sp_help_uptime
AS
select
DATEADD(second, -1 * ( ms_ticks / 1000),getdate()) as ServerRestartTime,
i.sqlserver_start_time As ServiceRestartTime,
DATEDIFF(dd,i.sqlserver_start_time,GETDATE()) AS ServiceUpTimeInDays,
DATEDIFF(dd,DATEADD(second, -1 * ( ms_ticks / 1000),getdate()),GETDATE()) AS ServerUpTimeInDays
from sys.dm_os_sys_info i;
GO
Lowell
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply