[ACCEPTED]-Why does SQL Server say "Starting Up Database" in the event log, twice per second?-sql-server-2012

Accepted answer

Score: 26

With AUTO_CLOSE ON the database will be 21 closed as soon as there are no connections 20 to it, and re-open (run recovery, albeit 19 a fast paced one) every time a connection 18 is established to it. So you were seeing 17 the message because every 2 second your 16 application would connect to the database. You 15 probably always had this behavior and never noticed 14 before. Now that your database crashed, you 13 investigated the log and discovered this 12 problem. While is good that now you know 11 and will likely fix it, this does not address 10 you real problem, namely the availability of 9 the database.

So now you have a database 8 that won't come out of recovery, what do 7 you do? You restore from you last backup 6 and apply your disaster recovery plan. Really, that's all there 5 is to it. And there is no alternative.

If 4 you want to understand why the crash happened 3 (it can be any of about 1 myriad reasons...) then 2 you need to contact CSS (Product Support). They 1 have the means to guide you through investigation.

Score: 21

If you wanted to turn off this message in 2 event log. Just goto SQL Server Management 1 Studio,

Right click on your database
Select Options (from left panel)
Look into "Automatic" section, and change "Auto Close" to "False"
Click okay

That's All :)

Score: 2

I had a similar problem with a sql express 9 database stuck in recovery. After investigating 8 the log it transpired that the database 7 was starting up every couple of minutes. Running 6 the script

select name, state_desc, is_auto_close_on from sys.databases where name = 'mydb'

revealed that auto close was set 5 to on.

So it appears that the database is 4 in always in recovery but is actually coming 3 online for a brief second before going offline 2 again because there are no client connections.

I 1 solved this with following script.

Declare @state varchar(20)
while 1=1
   begin
     Select @state = state_desc from sys.databases where name='mydb';
     If @state = 'ONLINE'
        Begin
           Alter database MyDb
                 Set AUTO_CLOSE_OFF;
           Print 'Online'
           break;
        End
        waitfor delay '00:00:02'
   end

Source: stackoverflow.com

[ACCEPTED]-Why does SQL Server say "Starting Up Database" in the event log, twice per second?-sql-server-2012

How do I import a .bak file into Microsoft SQL Server 2012?

What is the meaning of the following SQL Server declaration: datetime2(7)?

SQL Server 2012 installation Reporting Services Catalog error

SQL Server 2012 Install or add Full-text search

Convert Image DataType To String in SQL Server

Why CTE (Common Table Expressions) in some cases slow down queries comparing to temporary tables in SQL Server

smo restore database

Using WITH (NOLOCK) and Transactions

Stored procedure EXEC vs sp_executesql difference?

catastrophic failure trying to select from linked server