Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

t-sql Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 8:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
How can track database server startups/shutdowns via t-sql and save this information in a table.
Post #1374115
Posted Wednesday, October 17, 2012 10:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 5,565, Visits: 24,707
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
Posted Thursday, October 18, 2012 12:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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
Posted Thursday, October 18, 2012 12:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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
Posted Monday, October 29, 2012 1:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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
Posted Monday, October 29, 2012 2:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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
Posted Monday, October 29, 2012 4:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579

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 »

Add to briefcase

Permissions Expand / Collapse