January 17, 2010 at 3:28 pm
Comments posted to this topic are about the item How long has passed since SQL Server service started?
January 18, 2010 at 1:22 am
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 🙂
January 18, 2010 at 1:32 am
Hi jks-591682
To get the start time of the service, you could add this line:
PRINT 'SQL Server was started: ' + CONVERT(char,@starttime)
February 4, 2010 at 2:15 am
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
February 4, 2010 at 2:26 am
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
February 4, 2010 at 2:48 am
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
March 2, 2010 at 1:39 am
Fantastic....
November 5, 2010 at 5:11 am
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
November 5, 2010 at 5:43 am
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
November 5, 2010 at 3:54 pm
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
November 8, 2010 at 1:06 am
Hi Scott
Thanks for your improvement input - much appreciated 🙂
Best regards
Lars Mikkelsen
JN Data
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply