SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 System uptime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 04/07/2006 :  19:01:25  Show Profile  Reply with Quote
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.

Edited by - rockmoose on 04/23/2006 18:55:03

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/07/2006 :  19:29:13  Show Profile  Visit spirit1's Homepage  Reply with Quote
hyperthreading counts as how many processors?

Go with the flow & have fun! Else fight the flow
Blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 04/07/2006 :  19:32:38  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/07/2006 :  20:06:00  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 04/07/2006 20:48:00
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 04/08/2006 :  05:03:03  Show Profile  Reply with Quote
Interesting as usual.
Nice MVJ.

"Registered owner: Dr. Evil"


rockmoose
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 04/10/2006 :  04:19:10  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>Nice MVJ.

Yes except that sysprocesses should be Master.Dbo.sysprocesses

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/24/2006 :  04:52:44  Show Profile  Reply with Quote
"should work for appx 68yrs uptime now"

Another compromise software development solution then, eh?
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 04/24/2006 :  05:28:10  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/24/2006 :  15:33:21  Show Profile  Reply with Quote
"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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000