Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How long has passed since SQL Server service started? Expand / Collapse
Author
Message
Posted Sunday, January 17, 2010 3:28 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:17 AM
Points: 70, Visits: 472
Comments posted to this topic are about the item How long has passed since SQL Server service started?
Post #848914
Posted Monday, January 18, 2010 1:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 24, 2012 2:43 AM
Points: 1, Visits: 84
Hi Lars. Very nice Scripts , if you could supply the startdate, ( SQL Server was started: Dec 15 2009 9:56AM Time since SQL Server service was started: 33 days 23 hours 17 minutes.) in the Output - It will fullfill it. Thanks
Post #849006
Posted Monday, January 18, 2010 1:32 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:17 AM
Points: 70, Visits: 472
Hi jks-591682

To get the start time of the service, you could add this line:

PRINT 'SQL Server was started: ' + CONVERT(char,@starttime)

Post #849010
Posted Thursday, February 4, 2010 2:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 25, 2014 1:50 PM
Points: 49, Visits: 336
Lars,

you can also retrieve the start time from this Dynamic Management View

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

(Requires VIEW SERVER STATE permission on the server)

Marco
Post #859430
Posted Thursday, February 4, 2010 2:26 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:17 AM
Points: 70, Visits: 472
mchofman (2/4/2010)
Lars,

you can also retrieve the start time from this Dynamic Management View

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

(Requires VIEW SERVER STATE permission on the server)

Marco


Hi Marco

That's correct for SQL Server 2008. The sqlserver_start_time in sys.dm_os_sys_info was added in SQL Server 2008 and wasn't there in SQL Server 2005.

Using the tempdb works for all our current versions :) (yes, we still have SQL Server 2000)


Kind regards

Lars

Post #859438
Posted Thursday, February 4, 2010 2:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 25, 2014 1:50 PM
Points: 49, Visits: 336
Lars,

I didn't know it isn't available in SQL 2005. Nice to know for a project I'm working. Thanks for your solution!

Regards
Marco
Post #859450
Posted Tuesday, March 2, 2010 1:39 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 6, 2014 1:47 AM
Points: 292, Visits: 1,084
Fantastic....
Post #874925
Posted Friday, November 5, 2010 5:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:42 PM
Points: 1,352, Visits: 982
Hej Lars,

For SQL Server 2008 you need to change line 1
from
SELECT crdate FROM sysdatabases WHERE name = 'tempdb'
to
SELECT create_date FROM sys.databases WHERE name = 'tempdb'

Med venlig hilsen
Henrik Staun Poulsen
Stovi Software



Post #1016450
Posted Friday, November 5, 2010 5:43 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:17 AM
Points: 70, Visits: 472
Hi Henrik,

actually you don't need it, but I agree it would be better practice for SQL Server 2005 as well as SQL Server 2008 to use the sys.databases view which is available from SQL Server 2005 and forward.

Do do this while still maintaining the compatibility with SQL Server 2000, you could replace this:
-- determines when tempdb was created (done at startup)
DECLARE @starttime datetime
SET @starttime = (SELECT crdate FROM sysdatabases WHERE name = 'tempdb' )

With this:
-- determines when tempdb was created (done at startup)
DECLARE @starttime datetime
DECLARE @sql_version sysname
SET @sql_version = convert(sysname, (SELECT SERVERPROPERTY('Productversion')))

IF (@sql_version NOT LIKE '8.%')
BEGIN
-- SQL Server 2005 and forward
SET @starttime = (SELECT create_date FROM sys.databases WHERE name = 'tempdb')
END
ELSE
BEGIN
-- SQL Server 2000
SET @starttime = (SELECT crdate FROM sysdatabases WHERE name = 'tempdb')
END

The premises for this however is that you don't execute it on versions older than SQL Server 2000, but I hope you don't have any of them running anyway


Best regards

Lars Søe Mikkelsen
JN Data

Post #1016464
Posted Friday, November 5, 2010 3:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 31, 2010 9:46 AM
Points: 274, Visits: 473
Very nice! You can shorten it up a bit though

DECLARE @minutesSinceSQLStarted int

SELECT @minutesSinceSQLStarted = DATEDIFF(MINUTE,
-- determines when tempdb was created (done at startup)
(SELECT crdate FROM master.dbo.sysdatabases WHERE name = 'tempdb'), GETDATE())

PRINT 'Time since SQL Server service was started: ' +
CONVERT(varchar(4), @minutesSinceSQLStarted / (60*24)) + ' days ' +
CONVERT(varchar(2), @minutesSinceSQLStarted % (60*24) / 60) + ' hours ' +
CONVERT(varchar(2), @minutesSinceSQLStarted % (60*24) % 60) + ' minutes.'



Scott Pletcher, SQL Server MVP 2008-2010
Post #1016816
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse