t-sql

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » t-sql

t-sql

Rate Topic

Display Mode

Topic Options

Author

Message

sqlserver12345

Posted Wednesday, October 17, 2012 8:46 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 01, 2013 12:42 PM
Points: 139, Visits: 447

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

Post #1374115

bitbucket-25253

Posted Wednesday, October 17, 2012 10:00 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 5,099, Visits: 20,190

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

Post #1374129

sqlserver12345

Posted Thursday, October 18, 2012 12:34 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 01, 2013 12:42 PM
Points: 139, Visits: 447

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

Post #1374544

Lowell

Posted Thursday, October 18, 2012 12:44 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:55 AM
Points: 11,605, Visits: 27,643

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1374549

sqlserver12345

Posted Monday, October 29, 2012 1:58 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 01, 2013 12:42 PM
Points: 139, Visits: 447

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?

Post #1378449

Lowell

Posted Monday, October 29, 2012 2:08 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:55 AM
Points: 11,605, Visits: 27,643

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1378457

sqlserver12345

Posted Monday, October 29, 2012 4:12 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 01, 2013 12:42 PM
Points: 139, Visits: 447

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.

Post #1378501

« Prev Topic | Next Topic »

Permissions