Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How To Check SQL SERVER Uptime Through T-SQL


How To Check SQL SERVER Uptime Through T-SQL

Author
Message
bc_
bc_
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1396 Visits: 7307
this script:

SET NOCOUNT ON
DECLARE @crdate DATETIME,
@days varchar(3),
@hr VARCHAR(50),
@min VARCHAR(5)

SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

SET @min = DATEDIFF (mi,@crdate,GETDATE())
PRINT @min
PRINT '------------------------'
SET @days= @min/1440

gives this result:
*
------------------------
Msg 245, Level 16, State 1, Line 12
Conversion failed when converting the varchar value '*' to data type int.



So for whatever reason, @min (varchar) is getting set with an asterisk value and then the script breaks when trying to set the @days variable because is can't do math on *.

bc
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
i didn't get any error.


SET NOCOUNT ON
DECLARE @crdate DATETIME,
@days varchar(3),
@hr VARCHAR(50),
@min VARCHAR(5)

SELECT @crdate=crdate FROM sysdatabases WHERE name='tempdb'

SET @min = DATEDIFF (mi,@crdate,GETDATE())
PRINT @min
PRINT '-------------'
SET @days= @min/1440
print @days
PRINT '-------------'

Result:

24293
------------------------
16
------------------------

SQL DBA.
DavidSimpson
DavidSimpson
SSC Eights!
SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)

Group: General Forum Members
Points: 982 Visits: 1074
I don't know why DATEDIFF would return a "*" rather than the number of minutes. At least the INT version works but still very odd.

I can run the query with VARCHAR or INT and both work fine.

David



bc_
bc_
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1396 Visits: 7307
it's because the varchar(5) is not big enough to hold mine which is 6 characters. try setting yours to varchar(3) and you should get the same error.

bc

bc
DavidSimpson
DavidSimpson
SSC Eights!
SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)

Group: General Forum Members
Points: 982 Visits: 1074
Good to know, thanks

David



Steve Margolis
Steve Margolis
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 259
Please bear with me as I'm somewhat of a novice when it comes to scripting. This is a handy script, but here's a challenge for someone. Running the script as is obviously shows the uptime of SQL Server on the server, but I notice that when I replace tempdb with any of the user database names on the server, the uptime changes to reflect what I hope is the uptime for that database.

Here's the challenge: What modifications need to be done to this handy script to show the database name and the uptime of each database on the server?

If that was pretty easy, what further modifications would be needed to loop through a list of all of my servers to produce a report of the uptime of each server and of each user DB on each server?

Thanks!
DavidSimpson
DavidSimpson
SSC Eights!
SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)

Group: General Forum Members
Points: 982 Visits: 1074
The script actually returns the amount of time since the database was created. Since the tempdb is created every time SQL Server is started, this gives you the up-time for SQL Server.

You can run this to get list of all database names, total minutes, total days, hours within days and minutes within hours since each database has been created.

SELECT @@servername,
name, DATEDIFF (mi,crdate,GETDATE()) as 'Total Minutes',
DATEDIFF (day,crdate,GETDATE()) as 'Total Days',
(DATEDIFF (mi,crdate,GETDATE()) - (DATEDIFF (day,crdate,GETDATE()) * 1440)) / 60 as 'Hours within day',
DATEDIFF (mi,crdate,GETDATE()) - (DATEDIFF (day,crdate,GETDATE()) * 1440) -
(((DATEDIFF (mi,crdate,GETDATE()) - (DATEDIFF (day,crdate,GETDATE()) * 1440)) / 60) * 60) as 'Minutes with hour'
FROM sysdatabases
ORDER BY name



As for looping through all your servers here are couple of options:
1) Create linked servers and run the query against each of them.
2) Create SSIS package that loops through a list of servers and runs the script

For either of these, you can load the data into a local working table then run a final query to pull that data out of the working table. You probably would want to include @@servername in the query so your results will include the server name.

Hope this helps
David



ArjunaReddy
ArjunaReddy
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 554
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END
select * from sys.databases


Two corrections needs to be done in the script for successful execution

1. Change sysdatabases to sys.databases
2. Change the 3 line in the script (query) from "SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'" to "SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'". The error in the original query is, there is no such column (crdate) exists in the sys.databases table.

Correct me if am wrong anywhere.

Thanks,
Nagarjun.
edrost
edrost
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 56
Why not just using DMV for 2008+

SELECT sqlserver_start_time from sys.dm_os_sys_info
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search