t-sql

Question

Post reply

t-sql

mtz676

SSCrazy Eights

Points: 9214
More actions
October 17, 2012 at 8:46 pm

#268761

How can track database server startups/shutdowns via t-sql and save this information in a table.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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[/url]
Before posting a performance problem please read[/url]

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 2

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_ticksms_tickscpu_counthyperthread_ratiophysical_memory_in_bytesvirtual_memory_in_bytesbpool_committedbpool_commit_targetbpool_visiblestack_size_in_bytesos_quantumos_error_modeos_priority_classmax_workers_countscheduler_countscheduler_total_countdeadlock_monitor_serial_numbersqlserver_start_time_ms_tickssqlserver_start_time

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 4

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 SSC Guru Points: 323518 More actions · Answer 5

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