June 7, 2011 at 1:07 pm
Hi,
I'm trying to grab some stats on Database up time. I can't seem to get this to parse out the last two characters though. Any thoughts?
Create Table #TmpLog(LogDate datetime, ProcessInfo varchar(255), TextStr varchar(7000))
INSERT #TmpLog exec master.dbo.sp_readerrorlog
Select SUBSTRING(LTRIM(RTRIM(TextStr)),23,LEN(TextStr)-2) as 'DatabaseName'
,LogDate as 'Up Since'
,DATEDIFF(DAY,LogDate,GETDATE()) as 'days online'
,DATEDIFF(HH,LogDate,GETDATE()) as 'Hours Online'
from #TmpLog
WHERE TextStr like '%Starting up database%'
Drop table #TmpLog
June 7, 2011 at 1:19 pm
It looks like if I insert the first parsed field into another temp table, I can slice the right part off.
June 7, 2011 at 1:33 pm
That will only work of the errorlog file hasn't been rolled over ( more than your number of kept files )
If your database has "autoclose off" - which I hope you have, you could as well check your instance startup time.
e.g.
-- how to determin SQLServer Uptime
-- zie Tracking Uptime by Brian Moran http://www.winnetmag.com/SQLServer/Article/ArticleID/38042/SQLServer_38042.html
--
-- dd 20060912 JOBI - aangepast voor SQL2005 (DMV)
--
SELECT @@servername as ServerName, datediff(mi, login_time, getdate()) as SQLServer_UpTime_Minutes
FROM sys.dm_exec_sessions
WHERE session_id = 1
go
SELECT @@servername as ServerName, login_time as StartUp_DateTime
FROM sys.dm_exec_sessions
WHERE session_id = 1
go
SELECT @@servername as ServerName, getdate() - login_time as SQLServer_UpDateTime_1900_01_01
FROM sys.dm_exec_sessions
WHERE session_id = 1
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 7, 2011 at 1:45 pm
The last parameter in SUBSTRING() is number of characters, not the ending position. If the length of the string is 30, you're asking for 28 characters from an 8 character substring, so you're getting the entire rest of the string.
Try the following instead.
Select SUBSTRING(LTRIM(RTRIM(TextStr)),23,LEN(TextStr)-24) as 'DatabaseName'
,LogDate as 'Up Since'
,DATEDIFF(DAY,LogDate,GETDATE()) as 'days online'
,DATEDIFF(HH,LogDate,GETDATE()) as 'Hours Online'
from #TmpLog
WHERE TextStr like '%Starting up database%'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 8, 2011 at 9:50 am
ALZDBA Your solutions is certainly simpler and warrants looking into. Is there a way to incorporate that to look at individual databases as opposed to singluar servers?
Drew I see that now. This did work, but when testing this by taking test databases offline and back online, it appears to be different lengths. I would imagine this has to do with how SQL server parses the error log when using the sp_readerrorlog. See below
Create Table #TmpLog(LogDate datetime, ProcessInfo varchar(255), TextStr varchar(7000))
INSERT #TmpLog exec master.dbo.sp_readerrorlog
create table #ParcedErrorLog (eventTime datetime, TextStr varchar(7000))
Select *,LEN(TextStr), SUBSTRING(LTRIM(RTRIM(TextStr)),23,LEN(TextStr)-24)
from #TmpLog
where TextStr Like '%Starting%DWM%'
LogDate,ProcessInfo,TextStr,TextLen,dbname
2011-06-07 11:54:45.180,spid14s,Starting up database 'DWM_DB'.,30,DWM_DB
2011-06-08 11:31:17.360,spid57,Starting up database 'DWM_DB'.,32,DWM_DB'.
June 8, 2011 at 12:51 pm
If you mean adding the db name to the result rows ...
SELECT @@servername as ServerName, getdate() - S.login_time as SQLServer_UpDateTime_1900_01_01
, D.name as DbName
FROM sys.dm_exec_sessions S
cross apply sys.databases D
WHERE S.session_id = 1
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 9, 2011 at 5:53 am
ALZDBA (6/8/2011)
If you mean adding the db name to the result rows ...
Does this show you the actual start time of all databases? What if one database goes offline?
June 9, 2011 at 6:29 am
No, it doesn't
It shows the actual startup time of the instance and just adds the database names at row level.
If you really seek for db startup, you may be able to find that by subscribing to the appropriate sqlserver event.
I don't think you'll find it in the db related dmv.
Relying on sqlserver errorlog or the default trace file(s) is ( imho ) not usable for "dynamic" querying of this data, so you should persist it yourself if needed.
Errorlog and default trc can be rolledforward by switch commands ( e.g. errorlog.1 , 123.trc ) so may no longer exist when you request the info and a number of switch commands have been issued.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 9, 2011 at 6:35 am
Thank you, I will look into it
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply