January 28, 2008 at 11:15 am
Is the Date/Time of the latest SQL Server 2005 instance restart stored somewhere in the system catalog?
This value is quite relevant when retrieving information on the costliest unused indexes in the instance.
thank you,
Marios Philippopoulos
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 28, 2008 at 12:44 pm
Not directly, but you can get the login time of any of the system processes from sys.dm_exec_sessions. Since all the system processes start when the instance starts the login time is for any system process is the time the server started.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2008 at 12:52 pm
Thank you Gail, much appreciated.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 28, 2008 at 1:01 pm
Could you add the cpu_busy and idle from sp_monitor? You would have to parse it a little to get the value total values....
Chad
January 28, 2008 at 1:20 pm
Let me clarify a little bit....
sp_monitor returns multiple resultsets, which are tough to deal with inside MSSQL... a little easier if you are using a client, but that's probably not applicable here. At one point, I wanted to build an automated server monitoring dashboard, but needed to be able to track CPU/IO etc. on a regular basis in a SQL table. sp_monitor looked good, but the multiple resultsets made it difficult to use inside a job. So I rewrote my own sp_monitor, using the MS shipped code as a starting point. If you look inside sp_monitor, the values for CPU_BUSY and IDLE are coming from @@CPU_BUSY and @@IDLE using @@TIMETICKS to convert ticks to seconds. I think you could use these values to see how many seconds the service has been running (on the downside, if I remember right, the number of ticks will eventually overflow if you have decent uptime).
That being said, it might be easier to use the system session time...
Chad
January 28, 2008 at 1:29 pm
Thank you Chad, this is worth trying out. It's always a good practice to get to a piece of data from multiple routes, especially as a sanity check. Such an important piece of info needs to be rock-solid.
I tried Gail's suggestion, and it worked perfectly. I knew from looking at the SQL Log when the instance was last restarted, and on trying the following query, I was able to get it confirmed:
select login_time, * from sys.dm_exec_sessions
where session_id < 51
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 28, 2008 at 1:37 pm
I usually just grab the login time for session_id 1. iirc it's the resource monitor. It will always be there.
Chad: How do the CPU_BUSY and IDLE relate to real time if there are multiple CPUs on the server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2008 at 3:10 pm
Both @@IDLE and @@CPU_BUSY sum idle and busy across all CPUs. @@TIMETICKS enables you to scale to the processor, but you have to divide by the number of processors to convert to a "time" measure. (Hyperthreading appears as double too, so two hyperthreaded CPUs would need to be divided by 4).
Chad
April 1, 2008 at 11:36 am
can get it by checking tempdb create date:
SELECT crdate FROM master..sysdatabases WHERE name = 'tempdb'
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply