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....
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.
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]