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


t-sql


t-sql

Author
Message
sqlnewbie17
sqlnewbie17
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1199
How can track database server startups/shutdowns via t-sql and save this information in a table.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 25280
This might be what you need.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info



Returns :
2012-10-17 23:03:26.687

So create your table and then insert the result of the T-SL, it will give you at least half of what you asked for (that is the startup time)

other wise read:
http://msdn.microsoft.com/en-us/library/ms181720(v=sql.100).aspx

Please note the following
Startup procedures must be in the master database and cannot contain INPUT or OUTPUT parameters. Execution of the stored procedures starts when the master database is recovered at startup.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
sqlnewbie17
sqlnewbie17
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1199
SQL Server 2008 has start time.Where can I find similar data in 2005 and 2000
I would like to have the servername,starttime,login that started the server,login that stopped the server in 2000,2005,2008

SQLserver 2008:
Columns:
cpu_ticks   ms_ticks   cpu_count   hyperthread_ratio   physical_memory_in_bytes   virtual_memory_in_bytes   bpool_committed   bpool_commit_target   bpool_visible   stack_size_in_bytes   os_quantum   os_error_mode   os_priority_class   max_workers_count   scheduler_count   scheduler_total_count   deadlock_monitor_serial_number   sqlserver_start_time_ms_ticks   sqlserver_start_time
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38938
you could also read the sql error logs; the first entry is whent he server was started, i believe;

my server has the default 10 transaction files:

EXEC [sp_readerrorlog] 0
EXEC [sp_readerrorlog] 1
EXEC [sp_readerrorlog] 2
--...etc



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlnewbie17
sqlnewbie17
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1199
On SQLServer 2005:
I am in the Test database which has Test table.

insert into Table
SELECT   @@servername as Servername,   name as DBName,[crdate] AS [LastStartupDate]
FROM   [master].[dbo].[sysdatabases]
WHERE   [name] = 'tempdb'
and [Test].[dbo.Table].LastStartUpTime < [master].[dbo].[sysdatabases].crdate

Error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Test.dbo.Table.LastStartUpTime" could not be bound.

Where am I going wrong?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38938
this is wrong...it's looking for a table that happens to have a dot in it's name:

[Test].[dbo.Table].LastStartUpTime
--should be
[Test].[dbo].[Table].LastStartUpTime



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlnewbie17
sqlnewbie17
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1199
insert into Table
SELECT   @@servername as Servername,   name as DBName,[crdate] AS [LastStartupDate]
FROM   [master].[dbo].[sysdatabases]
WHERE   [name] = 'tempdb'
and [Test].[dbo].[Table].LastStartUpTime < [master].[dbo].[sysdatabases].crdate

It gives me this error now:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Test.dbo.Table.LastStartUpTime" could not be bound.
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