We do database creation through an application,i am seeing below error when running below DMV(connection context is master)

query ran :

SELECT session_id as SPID,wait_type,wait_resource,last_wait_type,
 command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r with(nolock)  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 

Error thrown :

Msg 927, Level 14, State 6, Line 5
Database 'XXXXXX' cannot be opened. It is in the middle of a restore.

Please Note this works some times and it fails some times with above error...when it works wait type is PREEMPTIVE_OS_CREATEFILE if this helps

Any ideas on why DMV throws this error.I expected DMV to work.

SQl version:

Microsoft SQL Server 2014 (SP2-CU10-GDR) (KB4052725) - 12.0.5571.0 (X64) 
    Jan 10 2018 15:52:08 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

SSMS related info:

   Microsoft SQL Server Management Studio                       12.0.5571.0
    Microsoft Analysis Services Client Tools                    12.0.5571.0
    Microsoft Data Access Components (MDAC)                     6.3.9600.17415
    Microsoft MSXML                     3.0 6.0 
    Microsoft Internet Explorer         9.11.9600.19541
    Microsoft .NET Framework            4.0.30319.42000
    Operating System                    6.3.9600

screenshot for error: enter image description here

restore command ran:

RESTORE DATABASE [somename] FROM DISK = N'M:\path\Shares\55543f86-b817-4bc2-aaa3-baff0fc632c8.bak' WITH FILE = 1,
MOVE N'xxx' TO N'M:\path\somename.mdf',
MOVE N'xxx_1' TO N'M:\path\FTIftrow_somename_1.ndf',
MOVE N'xxx_log_1' TO N'M:\path\somename_log.ldf', NOUNLOAD, STATS = 5

some more details on this restore, this restore also takes a awful lot of time, which is being asked here ..

Restore database stuck at 100% progress

  • What version? Include the output of select @@version. – David Browne - Microsoft Jan 19 at 19:16
  • 1
    What is the database context of your connection? Try adding "USE master" at the start of your script to ensure you're not accidentally in the DB context of the database you're restoring – HandyD Jan 20 at 0:01
  • 1
    The problem is that in your SSMS - you will have a default database set. Just change it to master and you should be good. – Kin Shah Jan 20 at 1:06
  • @DavidBrowne-Microsoft ,added version info – TheGameiswar Jan 20 at 2:46
  • 1
    The CROSS APPLY is probably to blame. If you remove the CROSS APPLY, and just select from sys.dm_exec_requests I think you'll have better luck. – AMtwo Jan 20 at 5:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.