Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I determine when the state of a specific database switched to online?

Background: I want to determine how long the database is in use to find out how reliable sys.dm_db_index_usage_stats currently is.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

As @SQLLearner specificed in his answer, the easiest way without auditing in place or an XE session running would be to just look in the SQL Server Log. The below code will get the most recent startup of a particular database (using YourDatabaseName in this example, but obviously change it for your environment):

create table #error_log
(
    LogDate datetime not null,
    ProcessInfo nvarchar(128) not null,
    Text nvarchar(512) not null
)
go

insert into #error_log
exec xp_readerrorlog 0, 1, N'Starting', N'YourDatabaseName'
go

select top 1
    LogDate,
    Text
from #error_log
order by LogDate desc

As per @usr's comment an Connect link, in SQL Server 2012 Unicode parameters are required.

share|improve this answer
    
I received a "severe error" in Denali because this script did not use unicode strings. I edited this post. Reference: connect.microsoft.com/SQLServer/feedback/details/684695/… –  usr Jul 7 '12 at 14:25
    
@usr Looks like you are correct. I originally wrote the code with SQL Server 2008 R2, and testing it on 2012 you are correct. Thanks for the edit! –  Thomas Stringer Jul 7 '12 at 14:28
add comment

You can look into sys.dm_os_sys_info DMV and sqlserver_start_time to determine when SQL Server service was started. This was also start point for sys.dm_db_index_usage_stats DMV.

If you want to find out when specific database was bought onine, you need to look into SQL Server ErrorLog for something like "Starting up Database"

share|improve this answer
2  
Database online and SQL-service running are not the same thing. Am I missing something? –  ypercube Jul 7 '12 at 12:10
1  
I agree with the above comment. The OP is looking for when a database came online, and that doesn't necessarily mean when SQL Server was started. –  Thomas Stringer Jul 7 '12 at 12:21
    
I understand, but AFAIK no easy way to find out. OP: Look into SQL Server ErrorLog when database was bought ONLINE. –  SQL Learner Jul 7 '12 at 12:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.