SQLTeam.com | Weblogs | Forums

SQL 2012 Express Databases not starting intermittently

#1

Hi all,

Long time no post..... Anyway we're having some bizarre issue here. We have thousands of Win7 workstations that have SQL2012 express installed. They each have 2 instances with 4 databases each. The problem we're seeing is that the instances are coming up, most databases are starting but one particular database fails to start in some cases.
The database in question (if it makes any difference) is the last alphabetically, and if we do a windows logoff/logon it starts (although I suspect this is more to do with the client app trying to re-connect).

Anyone seen this before? I have a feeling it's application related but can't prove it, and there's nothing else in the SQL Logs that looks like it's related....

Cheers,

Tim

#2

If you run the following query, what do you see as the state_desc of the database?
SELECT * FROM sys.databases

#3

Currently they're all showing as "ONLINE" but it is intermittent. Generally the problem occurs after a software update, but that may just be coincidence.

#4

Large log files, not backed up, perhaps? (Taking a while to checkpoint, or whatever, on startup?)

The [recovery_model_desc] column in

SELECT * FROM sys.databases

will show FULL if logging is enabled for the database.

This will show amount of Log File Used (for eeach DB):

SELECT	[Name]=LEFT(instance_name, 50),
	[Used(MB)]=cntr_value/1024	-- Convert KB to MB
FROM	sys.dm_os_performance_counters 
WHERE	    counter_name = 'Log File(s) Used Size (KB)'
	AND instance_name != '_Total'
--
ORDER BY [Name]