A user DB restarting every hour? Any idea why?

Question

Post reply

A user DB restarting every hour? Any idea why?

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
December 3, 2015 at 8:22 am

#322949

One of the user db went offline yesterday.
I looked at the error message and I saw this...
Date12/2/2015 3:49:36 PM
LogSQL Server (Archive #1 - 12/3/2015 3:23:00 AM)
Sourcespid17s
Message
The log for database 'Database_Name' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Also, I noticed that this particular DB is starting up every hour with the below message....not sure why.
Message
Starting up database 'Database_Name'.
Any idea how to troubleshoot this?.....I checked the event log as well, and I see the same errors.
Regards,
SQLisAwe5oMe.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 1

Take a look at event viewer, see if you can see any warnings or errors related to that drive.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

please post the results of this query when the problem next happens

select name, database_id, is_auto_close_on, state_desc,

is_cleanly_shutdown, recovery_model_desc, log_reuse_wait_desc

from sys.databases where database_id = DB_ID('yourdb')

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 3

See of the database is set to AUTO_CLOSE. When closed, SQL Server releases its exclusive lock on the database files and log files. If some other process decides to back up (copy, read, or check) the log file while the database is closed, then next attempted use of the database will require SQL Server to gain exclusive locks on its files, which fail with that message (when some other process has a lock on the file). If AUTO_CLOSE is enabled, best practice is to not use it.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 4

Thanks SoHelpMeCodd.

The auto close is set to true....I think this is the main reason.

Also, there is a backup happening every hour, so this probably forcing it to restart the db.

Regards,
SQLisAwe5oMe.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 5

What exactly means by Auto Close when its set to true/false?

Regards,
SQLisAwe5oMe.

mark.williams 37494 SSCommitted Points: 1661 More actions · Answer 6

The AUTO_CLOSE option is probably enabled for the databases that SQL Server is regularly restarting. AUTO_CLOSE closes the database when the last user closes his or her connection and all processes in the database have completed. When a user then tries to connect to the database, SQL Server reopens the database, generating the message you describe.

Its bad practice

_________________________________________________________________

"The problem with internet quotes is that you cant always depend on their accuracy" -Abraham Lincoln, 1864

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 7

SQLisAwe5oMe

SSChampion

Points: 14052

December 3, 2015 at 9:18 am

#1843814

Thanks Mark, appreciate it.

Regards,
SQLisAwe5oMe.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 8

Quick question.

What would be a scenario where you would want to enable auto-close?

Regards,
SQLisAwe5oMe.

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 9

If a database is "rarely" used, if you want the database's data files and log files to usually be included in the system's regular OS backups (as opposed to SQL Server backups), if you can accept the risk that a data file or a log file can be accidentally deleted while it is offline, and if you are comfortable with ignoring the messages that prompted you to post your question :).

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 10

Thank you so much SoHelpmeCodd.

Regards,
SQLisAwe5oMe.

Wayne West SSC-Insane Points: 22586 More actions · Answer 11

AutoClose and AutoShrink are relics from when developers had much more limited resources and were running SQL Server on their local box. These days, they are not considered just bad practices, but VERY bad practices. It's the first thing (plus backups and recover model) that I'd look at when I am given a new box to administer.

AutoClose is risky, as previously pointed out, as the log can be deleted. AutoShrink is very bad. Personally I wish MS would remove them from SQL Server, or maybe make them an option that can only be enabled with a T-SQL script.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]