SQL SERVER – Starting Up Database – Why Multiple Times in Errorlog?

Fixing error messages is something which keeps me energized because there is something challenging that is put in front of me. I know these can be frustrating times working on errors, but they also make you learn more about the product and make you better. My day life includes creating demo’s for sessions and learning new techniques that will enhance my knowledge. One of the days I started my machine and immediately connected to my SSMS environment to just see that it was taking unusually longer than expected time to get the instance up and ready. Let us see blog post about Starting Up Database.

My instinct was to scan through the ERRORLOG, I found the below series of messages.

2016-02-15 16:37:55.330 spid53       Starting up database ‘SQLAuthority’.
2016-02-15 16:37:55.830 spid54       Starting up database ‘SQLAuthority’.
2016-02-15 16:39:12.350 spid53       Starting up database ‘SQLAuthority’.
2016-02-15 16:39:46.460 spid54       Starting up database ‘SQLAuthority’.
2016-02-15 16:39:47.490 spid54       Starting up database ‘SQLAuthority’.
2016-02-15 16:39:48.240 spid54       Starting up database ‘SQLAuthority’.
2016-02-15 16:39:48.850 spid54       Starting up database ‘SQLAuthority’.
2016-02-15 16:39:49.370 spid54       Starting up database ‘SQLAuthority’.
2016-02-15 16:39:49.930 spid54       Starting up database ‘SQLAuthority’.
2016-02-15 16:39:50.890 spid54       Starting up database ‘SQLAuthority’.
2016-02-15 16:39:51.500 spid54       Starting up database ‘SQLAuthority’.

If we look closely at the timestamp of the message, they are very close – roughly twice per second – all day long. The event log filled up, so I was not able to see what happened beyond yesterday evening.

While finding the root cause for this, I found that AUTOCLOSE setting was ON for the database. Due to this setting, SQL Server would “close” the database and release all resources once last connection is closed. As soon as first connection is made, it would “open” or “start” the database and make an entry in SQL Server ERRORLOG.

Here is the quick script to demonstrate the behavior. Please don’t do it on production server.

-- recycle errorlog
EXEC sp_CYCLE_ERRORLOG
GO
-- create database and set the autoclose property to ON
CREATE DATABASE [SQLAuthority] 
GO
USE [master] 
GO
ALTER DATABASE [SQLAuthority] SET AUTO_CLOSE ON WITH NO_WAIT
GO
-- --------------------------------------------------
-- Below is to change database context 4 times.
WAITFOR DELAY '00:00:01'
GO
USE [SQLAuthority] 
GO
USE MASTER
GO
WAITFOR DELAY '00:00:01'
GO
USE [SQLAuthority] 
GO
USE MASTER
GO
WAITFOR DELAY '00:00:01'
GO
USE [SQLAuthority] 
GO
USE MASTER
GO
WAITFOR DELAY '00:00:01'
GO
USE [SQLAuthority] 
GO
USE MASTER
GO
-- --------------------------------------------------
-- read error log
EXEC sp_READERRORLOG

Once you run above script, you should see “Starting up database…” message in output as shown below

First “Starting up” message is when database was created, then we changed the property in script.

If you wanted to turn off these message in event log.

• Open SQL Server Management Studio and connect to SQL instance.
• Right click on your database and click on properties.
• Select Options (from left pane)
• Look into “Automatic” section, and change “Auto Close” to “False”
• Click OK

Here is the T-SQL to turn off auto close for one database. Please change the database name.

ALTER DATABASE [SQLAuthority] SET AUTO_CLOSE OFF WITH NO_WAIT
GO

If you want to do for all databases, then use below

EXECUTE sp_MSforeachdb 'IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))
EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'')'

I personally found that this was something I did by accident but it caused some great learning for me which I personally felt was worth a share.

Reference: Pinal Dave (https://blog.sqlauthority.com)