| Author |
Topic |
|
netslave
Starting Member
9 Posts |
 Posted - 2007-12-28 : 12:54:49
|
| I'm trying to find out how long a specific database has been running. I can check the create date of the tempdb to see how long SQL Server has been running, but that's not exactly what I need. Is there a way to find when a database was brought online?Thanks! |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-12-28 : 13:15:09
|
Look at the created date output from this:exec sp_helpdb CODO ERGO SUM |
 |
|
|
netslave
Starting Member
9 Posts |
Posted - 2007-12-28 : 14:26:41
|
| Hmm, well, that will tell me the create date, but how about how long it has been running since it's last shut down?Say for instance, I bring the server down over night. The next day, I'd like to be able to see that the database has been running since 12:00 am or something like that. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-12-28 : 15:46:52
|
| It will be the same as when SQL Server was started. When SQL Server starts, it brings up all databases during initial recovery.CODO ERGO SUM |
|
|
|
netslave
Starting Member
9 Posts |
Posted - 2007-12-28 : 15:57:03
|
| What if one of the databases went offline for some reason (manual or something else), but SQL server was still running? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-28 : 22:59:01
|
| Then have to check sql server log, there is entry when you take db online/offline. |
|
|
|
netslave
Starting Member
9 Posts |
Posted - 2007-12-31 : 10:28:15
|
| Is there any way to do this programatically say with a query or something? I've thought about writing a trigger or something to write into a history table the datetime of when the database is brought online. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-31 : 23:25:14
|
| But you can't create trigger on system table. |
|
|
|
netslave
Starting Member
9 Posts |
Posted - 2008-01-02 : 15:21:47
|
| What else can I do other then manually look at the server log? Are there no tables or views that contain this information? I sure can't find them if so. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-01-02 : 16:17:45
|
This may help you find what you want:exec master.dbo.xp_readerrorlog CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-01-02 : 16:28:17
|
| But what happens if you cycle the error log on a scheduled basis? The information may be in an old log that has been deleted already. You could of course save more log files, but there's always that possibility that the database server hasn't been restarted in a very long time and you don't have all of the error log since the restart.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
netslave
Starting Member
9 Posts |
Posted - 2008-01-02 : 16:41:07
|
THANKS!! |
|
|
|
netslave
Starting Member
9 Posts |
Posted - 2008-01-02 : 16:46:34
|
| tkizer, Maybe periodically copy the log over to the table that I'll eventually query to get the data? |
|
|
|
|
How long has a database been running