System uptime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

System uptime

Author

Topic

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-07 : 19:01:25

To view the system uptime.
The @noprocs variable needs to be set to the number of processors the server has.
Also the convert function has to have the correct mask for the regional settings of the os.
I suggest you just run
"net statistics server" from the command prompt and see what the time format is for the server.
(103 is for British/French setting)

CREATE procedure dba_uptime as
set nocount on
create table #output(outp nvarchar(1000))
insert #output(outp)
exec master.dbo.xp_cmdshell N'net statistics server'

declare @upsince datetime, @sqlupsince datetime, @noprocs int
set @noprocs = 8
set @upsince = 
(
	select	convert(datetime,replace(outp,'Statistics since ',''),103) as system_up_since
	from	#output
	where	outp like('Statistics since %')
)

drop table #output

set @sqlupsince =
(
	select	dateadd(s,-(32.0/@noprocs)*(cast(@@idle as float)+@@cpu_busy+@@io_busy)/1000.0,getdate())
)

select	@upsince as systemUpSince
	,@sqlupsince as sqlServerUpSince

go

exec dba_uptime
/*
systemUpSince           sqlServerUpSince
----------------------- -----------------------
2006-02-19 03:23:00.000 2006-03-29 08:35:26.803
*/

rockmoose

Edit: fixed convert to int overflow, should work for appx 68yrs uptime now.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-07 : 19:29:13

hyperthreading counts as how many processors?

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-07 : 19:32:38

quote:
Originally posted by spirit1

hyperthreading counts as how many processors?

double

It's not extensively tested, just on 2 servers with different setups and settings.
There is also a mention of multiplying @@cpu_busy by @@timeticks in bol, but never got that to work very well.

rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-07 : 20:06:00

For SQL Server start time, I use this:


select SQL_Server_Start_Time = min(login_time) from sysprocesses

SQL_Server_Start_Time                                  
------------------------------------------------------ 
2006-04-07 17:03:23.493

(1 row(s) affected)

For system uptime, I usually get it with the PSINFO utility:


C:\>PSINFO \\MYCOMPUTER

PsInfo v1.73 - Local and remote system information viewer
Copyright (C) 2001-2005 Mark Russinovich
Sysinternals - www.sysinternals.com

System information for \\MYCOMPUTER:
Uptime:                    0 days 2 hours 50 minutes 58 seconds
Kernel version:            Microsoft Windows 2000, Uniprocessor Free
Product type:              Professional
Product version:           5.0
Service pack:              4
Kernel build number:       2195
Registered organization:
Registered owner:          Dr. Evil
Install date:              3/18/2002, 5:48:16 PM
Activation status:         Not applicable
IE version:                6.0000
System root:               C:\WINNT
Processors:                1
Processor speed:           930 MHz
Processor type:            Intel Pentium III
Physical memory:           256 MB
Video driver:              Radeon DDR

C:\>

You can also get the system start time from the system event log with the PSLOGLIST utility by looking to see when the event log was last started:


C:\>psloglist -id 6005 -n 1

PsLoglist v2.62 - local and remote event log viewer
Copyright (C) 2000-2005 Mark Russinovich
Sysinternals - www.sysinternals.com

System log on \\MYCOMPUTER:
[5986] EventLog
   Type:     INFORMATION
   Computer: MYCOMPUTER
   Time:     4/7/2006 5:03:10 PM   ID:       6005
The Event log service was started.

Combining the two sources of info:


set nocount on
drop table #t
create table #t (cmdout varchar(500) )
insert into #t
exec master.dbo.xp_cmdshell 'psloglist -id 6005 -n 1'

select 
	SYSTEM_UP_SINCE =
		convert(datetime,substring(cmdout,14,22)),
	SQL_SERVER_START_TIME
from 
	#t
	cross join
	(select SQL_SERVER_START_TIME = min(login_time) 
	from sysprocesses ) a
where
	cmdout like '%   Time: %' 

Results:

SYSTEM_UP_SINCE         SQL_SERVER_START_TIME
----------------------- -----------------------
2006-04-07 17:03:10.000 2006-04-07 17:03:23.493

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-08 : 05:03:03

Interesting as usual.
Nice MVJ.

"Registered owner: Dr. Evil"

rockmoose

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-10 : 04:19:10

>>Nice MVJ.

Yes except that sysprocesses should be Master.Dbo.sysprocesses

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-04-24 : 04:52:44

"should work for appx 68yrs uptime now"

Another compromise software development solution then, eh?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-24 : 05:28:10

quote:
Originally posted by Kristen

"should work for appx 68yrs uptime now"

Another compromise software development solution then, eh?

*LOL*

There are still some compromises but we no longer implement the fix problem by reboot strategy!

rockmoose

Kristen
Test

22859 Posts

Posted - 2006-04-24 : 15:33:21

"There are still some compromises but we no longer implement the fix problem by reboot strategy!"

Sadly I can visualise departments where the 68yrs uptime bug is regarded as a more serious threat than "reboot once a week" limitation. Bunch of Jobsworths!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources