Home Dashboard Directory Help
Search

SSMS Intellisense causes startup of all auto closed databases by Jerry-HdL


Status: 

Active


2
0
Sign in
to vote
Type: Bug
ID: 790341
Opened: 6/17/2013 6:01:19 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

In SSMS (2008R2), using Intellisense causes the following query to be run:
SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit) AS [IsSystemObject],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

The end result is that any time Intellisense runs this query (at least every couple minutes), it causes all auto-closed databases to start up. In our situation, we have hundreds of databases, most of which are used only a couple times a year at random, unscheduled intervals (an ideal fit for auto close). Thankfully the object browser in SSMS no longer causes startup of auto-closed databases when enumerating the database list. However, this side effect of Intellisense also effectively makes auto-close a non-option.

The problem is caused by the "has_dbaccess()" function call. Limiting records using "WHERE is_auto_close_on = CAST(0 AS bit)" prevents the databases from starting up (similar to restricting columns returned from sys.databases - doing "SELECT *" from that system view will also cause all auto-closed databases to start up). Perhaps the query could be turned into a union query that returns NULL (in this case meaning "unknown" instead of "empty" or "no value") in the IsAccessible column for auto-closed databases, with the using code updated to handle the NULL values appropriately. *Edit - it looks like "case when is_auto_close_on = 1 then CAST(NULL AS bit) else ... end" will short-circuit, preventing startup of auto-closed databases.*
Details
Sign in to post a comment.
Posted by Microsoft on 6/20/2013 at 9:35 AM
Hello Jerry. Thank you for bringing this to our attention. We really do appreciate the feedback. We’ll investigate and get back to you.

-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Sign in to post a workaround.