locked
Deadlock in DB while creating DB SQL 2008 R2 express win7 64 bit RRS feed

  • Question

  • I am creating a DB using SMO object and while creating the DB i see a deadlock in the event viewer. This is a random issue and does not occuer every time i create the DB:

    I managed to log the dead lock from the DB using Trace 1222 and Trace 1204 turned on.

    this is the log from the ERRORLOG i recieve:

    2012-07-30 10:12:20.85 Server      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
    Apr  2 2010 15:48:46 
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

    2012-07-30 10:12:21.43 Server      (c) Microsoft Corporation.
    2012-07-30 10:12:21.43 Server      All rights reserved.
    2012-07-30 10:12:21.43 Server      Server process ID is 1324.
    2012-07-30 10:12:21.46 Server      System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
    2012-07-30 10:12:21.53 Server      Authentication mode is MIXED.
    2012-07-30 10:12:21.57 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAMEI\MSSQL\Log\ERRORLOG'.
    2012-07-30 10:12:21.92 Server      This instance of SQL Server last reported using a process ID of 1296 at 7/30/2012 9:03:20 AM (local) 7/30/2012 4:03:20 PM (UTC). This is an informational message only; no user action is required.
    2012-07-30 10:12:21.95 Server      Registry startup parameters: 
    -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAMEI\MSSQL\DATA\master.mdf
    -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAMEI\MSSQL\Log\ERRORLOG
    -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAMEI\MSSQL\DATA\mastlog.ldf
    2012-07-30 10:12:33.25 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2012-07-30 10:12:33.89 Server      Detected 1 CPUs. This is an informational message; no user action is required.
    2012-07-30 10:12:38.35 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2012-07-30 10:12:40.40 Server      Node configuration: node 0: CPU mask: 0x0000000000000001:0 Active CPU mask: 0x0000000000000001:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2012-07-30 10:12:41.06 spid7s      Starting up database 'master'.
    2012-07-30 10:12:41.45 spid7s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2012-07-30 10:12:41.90 spid7s      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'DBNAMEI'.
    2012-07-30 10:12:42.56 spid7s      SQL Trace ID 1 was started by login "sa".
    2012-07-30 10:12:42.64 spid7s      Starting up database 'mssqlsystemresource'.
    2012-07-30 10:12:42.70 spid7s      The resource database build version is 10.50.1600. This is an informational message only. No user action is required.
    2012-07-30 10:12:49.29 spid10s     Starting up database 'model'.
    2012-07-30 10:12:49.40 spid7s      Server name is 'SYSTEM-777\DBNAMEI'. This is an informational message only. No user action is required.
    2012-07-30 10:12:50.39 spid7s      Informational: No full-text supported languages found.
    2012-07-30 10:12:50.49 spid7s      Starting up database 'msdb'.
    2012-07-30 10:12:51.76 Server      A self-generated certificate was successfully loaded for encryption.
    2012-07-30 10:12:51.82 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\DBNAMEI ].
    2012-07-30 10:12:51.82 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$DBNAMEI\sql\query ].
    2012-07-30 10:12:52.04 spid10s     Recovery completed for database model (database ID 3) in 1 second(s) (analysis 187 ms, redo 0 ms, undo 375 ms.) This is an informational message only. No user action is required.
    2012-07-30 10:12:52.57 Server      Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
    2012-07-30 10:12:52.85 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
    2012-07-30 10:12:52.99 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2012-07-30 10:12:54.92 spid7s      Recovery completed for database msdb (database ID 4) in 1 second(s) (analysis 62 ms, redo 0 ms, undo 906 ms.) This is an informational message only. No user action is required.
    2012-07-30 10:12:57.42 spid10s     Clearing tempdb database.
    2012-07-30 10:13:09.95 spid10s     Starting up database 'tempdb'.
    2012-07-30 10:13:14.43 spid10s     Recovery completed for database tempdb (database ID 2) in 1 second(s) (analysis 359 ms, redo 0 ms, undo 281 ms.) This is an informational message only. No user action is required.
    2012-07-30 10:13:19.00 spid13s     The Service Broker protocol transport is disabled or not configured.
    2012-07-30 10:13:19.37 spid13s     The Database Mirroring protocol transport is disabled or not configured.
    2012-07-30 10:13:21.87 spid7s      Recovery is complete. This is an informational message only. No user action is required.
    2012-07-30 10:13:21.87 spid13s     Service Broker manager has started.
    2012-07-30 10:18:31.39 spid52      DBCC TRACEON 1204, server process ID (SPID) 52. This is an informational message only; no user action is required.
    2012-07-30 10:18:31.65 spid52      DBCC TRACEON 1222, server process ID (SPID) 52. This is an informational message only; no user action is required.
    2012-07-30 10:18:33.28 spid54      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
    2012-07-30 10:18:33.90 spid54      Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    2012-07-30 10:19:16.07 spid51      SQL Trace ID 2 was started by login "system-777\Administrator".
    2012-07-30 10:35:42.59 spid54      Starting up database 'DBNAME'.
    2012-07-30 10:35:43.31 spid6s      Deadlock encountered .... Printing deadlock information
    2012-07-30 10:35:43.31 spid6s      Wait-for graph
    2012-07-30 10:35:43.31 spid6s      
    2012-07-30 10:35:43.31 spid6s      Node:1

    2012-07-30 10:35:43.31 spid6s      KEY: 1:281474978545664 (59855d342c69) CleanCnt:2 Mode:U Flags: 0x1
    2012-07-30 10:35:43.31 spid6s       Grant List 0:
    2012-07-30 10:35:43.31 spid6s         Owner:0x00000000801433C0 Mode: S        Flg:0x40 Ref:3 Life:00000000 SPID:54 ECID:0 XactLockInfo: 0x0000000080EA97A0
    2012-07-30 10:35:43.31 spid6s         SPID: 54 ECID: 0 Statement Type: SELECT Line #: 1
    2012-07-30 10:35:43.31 spid6s         Input Buf: Language Event: (@_msparam_0 nvarchar(4000))SELECT
    dtb.is_ansi_null_default_on AS [AnsiNullDefault],
    dtb.is_ansi_nulls_on AS [AnsiNullsEnabled],
    dtb.is_ansi_padding_on AS [AnsiPaddingEnabled],
    dtb.is_ansi_warnings_on AS [AnsiWarningsEnabled],
    dtb.is_arithabort_on AS [Ari
    2012-07-30 10:35:43.31 spid6s      Requested by: 
    2012-07-30 10:35:43.31 spid6s        ResType:LockOwner Stype:'OR'Xdes:0x0000000080DA4E80 Mode: X SPID:21 BatchID:0 ECID:0 TaskProxy:(0x00000000806E03C8) Value:0x80143100 Cost:(0/220)
    2012-07-30 10:35:43.31 spid6s      
    2012-07-30 10:35:43.31 spid6s      Node:2

    2012-07-30 10:35:43.31 spid6s      DATABASE: 5                    CleanCnt:2 Mode:X Flags: 0x1
    2012-07-30 10:35:43.31 spid6s       Grant List 0:
    2012-07-30 10:35:43.31 spid6s         Owner:0x0000000080142EC0 Mode: X        Flg:0x40 Ref:1 Life:00000000 SPID:21 ECID:0 XactLockInfo: 0x0000000080DA41B0
    2012-07-30 10:35:43.31 spid6s         SPID: 21 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 1
    2012-07-30 10:35:43.31 spid6s         Input Buf: No Event: 
    2012-07-30 10:35:43.31 spid6s      Requested by: 
    2012-07-30 10:35:43.31 spid6s        ResType:LockOwner Stype:'OR' SPID:54 BatchID:0 ECID:0 TaskProxy:(0x0000000080E9A510) Value:0x80143400 Cost:(0/10000)
    2012-07-30 10:35:43.45 spid6s      
    2012-07-30 10:35:43.45 spid6s      Victim Resource Owner:
    2012-07-30 10:35:43.45 spid6s       ResType:LockOwner Stype:'OR'Xdes:0x0000000080DA4E80 Mode: X SPID:21 BatchID:0 ECID:0 TaskProxy:(0x00000000806E03C8) Value:0x80143100 Cost:(0/220)
    2012-07-30 10:35:43.48 spid21s     Error: 1205, Severity: 13, State: 51.
    2012-07-30 10:35:43.48 spid21s     Transaction (Process ID 21) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    2012-07-30 10:35:43.49 spid21s     Error: 1205, Severity: 13, State: 51.
    2012-07-30 10:35:43.49 spid21s     Transaction (Process ID 21) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    2012-07-30 10:35:43.78 spid22s     deadlock-list
    2012-07-30 10:35:43.78 spid22s      deadlock victim=process8035fb88
    2012-07-30 10:35:43.78 spid22s       process-list
    2012-07-30 10:35:43.78 spid22s        process id=process8035fb88 taskpriority=0 logused=220 waitresource=KEY: 1:281474978545664 (59855d342c69) waittime=515 ownerId=2068 transactionname=CLEAN DB lasttranstarted=2012-07-30T10:35:42.937 XDES=0x80da4e80 lockMode=X schedulerid=1 kpid=2704 status=background spid=21 sbid=0 ecid=0 priority=0 trancount=0
    2012-07-30 10:35:43.78 spid22s         executionStack
    2012-07-30 10:35:43.78 spid22s         inputbuf
    2012-07-30 10:35:43.78 spid22s        process id=process8035f948 taskpriority=0 logused=10000 waitresource=DATABASE: 5  waittime=515 schedulerid=1 kpid=2888 status=suspended spid=54 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2012-07-30T10:35:42.670 lastbatchcompleted=2012-07-30T10:35:42.670 lastattention=2012-07-30T10:35:41.810 clientapp=.Net SqlClient Data Provider hostname=SYSTEM-777 hostpid=1568 loginname=system-777\Administrator isolationlevel=read committed (2) xactid=2067 currentdb=1 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2012-07-30 10:35:43.78 spid22s         executionStack
    2012-07-30 10:35:43.78 spid22s          frame procname=adhoc line=1 stmtstart=56 sqlhandle=0x02000000ae7bf604eee28d4e060a95789e0872a3a624ee2f
    2012-07-30 10:35:43.78 spid22s     SELECT
    dtb.is_ansi_null_default_on AS [AnsiNullDefault],
    dtb.is_ansi_nulls_on AS [AnsiNullsEnabled],
    dtb.is_ansi_padding_on AS [AnsiPaddingEnabled],
    dtb.is_ansi_warnings_on AS [AnsiWarningsEnabled],
    dtb.is_arithabort_on AS [ArithmeticAbortEnabled],
    dtb.is_auto_close_on AS [AutoClose],
    dtb.is_auto_shrink_on AS [AutoShrink],
    dtb.is_cursor_close_on_commit_on AS [CloseCursorsOnCommitEnabled],
    dtb.is_concat_null_yields_null_on AS [ConcatenateNullYieldsNull],
    dtb.is_numeric_roundabort_on AS [NumericRoundAbortEnabled],
    dtb.is_quoted_identifier_on AS [QuotedIdentifiersEnabled],
    dtb.is_read_only AS [ReadOnly],
    dtb.is_recursive_triggers_on AS [RecursiveTriggersEnabled],
    dtb.is_local_cursor_default AS [LocalCursorsDefault],
    dtb.page_verify_option AS [PageVerify],
    dtb.recovery_model AS [RecoveryModel],
    dtb.user_access AS [UserAccess],
    dtb.snapshot_isolation_state AS [SnapshotIsolationState],
    dtb.is_read_committed_snapshot_on AS [IsReadCommittedSnapshotOn],
    dtb.is_db_chaining_on AS [DatabaseOwnershipChaining],
    dtb.is_auto     
    2012-07-30 10:35:43.78 spid22s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
    2012-07-30 10:35:43.78 spid22s     unknown     
    2012-07-30 10:35:43.78 spid22s         inputbuf
    2012-07-30 10:35:43.78 spid22s     (@_msparam_0 nvarchar(4000))SELECT
    dtb.is_ansi_null_default_on AS [AnsiNullDefault],
    dtb.is_ansi_nulls_on AS [AnsiNullsEnabled],
    dtb.is_ansi_padding_on AS [AnsiPaddingEnabled],
    dtb.is_ansi_warnings_on AS [AnsiWarningsEnabled],
    dtb.is_arithabort_on AS [ArithmeticAbortEnabled],
    dtb.is_auto_close_on AS [AutoClose],
    dtb.is_auto_shrink_on AS [AutoShrink],
    dtb.is_cursor_close_on_commit_on AS [CloseCursorsOnCommitEnabled],
    dtb.is_concat_null_yields_null_on AS [ConcatenateNullYieldsNull],
    dtb.is_numeric_roundabort_on AS [NumericRoundAbortEnabled],
    dtb.is_quoted_identifier_on AS [QuotedIdentifiersEnabled],
    dtb.is_read_only AS [ReadOnly],
    dtb.is_recursive_triggers_on AS [RecursiveTriggersEnabled],
    dtb.is_local_cursor_default AS [LocalCursorsDefault],
    dtb.page_verify_option AS [PageVerify],
    dtb.recovery_model AS [RecoveryModel],
    dtb.user_access AS [UserAccess],
    dtb.snapshot_isolation_state AS [SnapshotIsolationState],
    dtb.is_read_committed_snapshot_on AS [IsReadCommittedSnapshotOn],
    dtb.is_db_chaining_on AS [DatabaseOw    
    2012-07-30 10:35:43.78 spid22s       resource-list
    2012-07-30 10:35:43.78 spid22s        keylock hobtid=281474978545664 dbid=1 objectname=master.sys.sysdbreg indexname=clst id=lock800c9580 mode=U associatedObjectId=281474978545664
    2012-07-30 10:35:43.78 spid22s         owner-list
    2012-07-30 10:35:43.78 spid22s          owner id=process8035f948 mode=S
    2012-07-30 10:35:43.78 spid22s         waiter-list
    2012-07-30 10:35:43.78 spid22s          waiter id=process8035fb88 mode=X requestType=convert
    2012-07-30 10:35:43.78 spid22s        databaselock subresource=FULL dbid=5 dbname=DBNAME id=lock800c8d80 mode=X
    2012-07-30 10:35:43.78 spid22s         owner-list
    2012-07-30 10:35:43.78 spid22s          owner id=process8035fb88 mode=X
    2012-07-30 10:35:43.78 spid22s         waiter-list
    2012-07-30 10:35:43.78 spid22s          waiter id=process8035f948 mode=S requestType=wait
    2012-07-30 10:35:43.92 spid18s     Error: 9001, Severity: 21, State: 5.
    2012-07-30 10:35:43.92 spid18s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 10:35:46.53 spid54      Error: 9001, Severity: 21, State: 1.
    2012-07-30 10:35:46.53 spid54      The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 10:35:46.98 spid18s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 10:35:46.98 spid18s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 10:35:48.40 spid53      Error: 9001, Severity: 21, State: 1.
    2012-07-30 10:35:48.40 spid53      The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 10:35:48.65 spid53      Error: 9001, Severity: 21, State: 1.
    2012-07-30 10:35:48.65 spid53      The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 10:35:48.65 spid53      Error: 9001, Severity: 21, State: 1.
    2012-07-30 10:35:48.65 spid53      The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 10:35:49.07 spid53      Error: 9001, Severity: 21, State: 1.
    2012-07-30 10:35:49.07 spid53      The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 10:35:49.07 spid53      Error: 9001, Severity: 21, State: 1.
    2012-07-30 10:35:49.07 spid53      The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 10:35:55.79 Logon       Error: 18456, Severity: 14, State: 38.
    2012-07-30 10:35:55.79 Logon       Login failed for user 'KBUser'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    2012-07-30 10:35:56.06 spid16s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 10:35:56.06 spid16s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 10:40:21.01 spid18s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 10:40:21.01 spid18s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 11:11:24.51 spid22s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 11:11:24.51 spid22s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 11:34:46.17 Logon       Error: 18456, Severity: 14, State: 38.
    2012-07-30 11:34:46.17 Logon       Login failed for user 'KBUser'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    2012-07-30 11:34:46.32 spid18s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 11:34:46.32 spid18s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 11:35:04.18 Logon       Error: 18456, Severity: 14, State: 38.
    2012-07-30 11:35:04.18 Logon       Login failed for user 'KBUser'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    2012-07-30 11:35:04.29 spid18s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 11:35:04.29 spid18s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 11:35:14.25 Logon       Error: 18456, Severity: 14, State: 38.
    2012-07-30 11:35:14.25 Logon       Login failed for user 'KBUser'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    2012-07-30 11:35:14.35 spid19s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 11:35:14.35 spid19s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 11:35:34.26 Logon       Error: 18456, Severity: 14, State: 38.
    2012-07-30 11:35:34.26 Logon       Login failed for user 'KBUser'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    2012-07-30 11:35:34.37 spid19s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 11:35:34.37 spid19s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 11:36:14.28 Logon       Error: 18456, Severity: 14, State: 38.
    2012-07-30 11:36:14.28 Logon       Login failed for user 'KBUser'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    2012-07-30 11:36:14.39 spid19s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 11:36:14.39 spid19s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 11:41:27.76 spid22s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 11:41:27.76 spid22s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 12:12:33.64 spid24s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 12:12:33.64 spid24s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 12:42:35.91 spid17s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 12:42:35.91 spid17s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 13:12:36.50 spid22s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 13:12:36.50 spid22s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 13:42:38.12 spid23s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 13:42:38.12 spid23s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 14:12:43.05 spid22s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 14:12:43.05 spid22s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 14:42:47.83 spid23s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 14:42:47.83 spid23s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 15:12:52.66 spid23s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 15:12:52.66 spid23s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 15:42:57.34 spid18s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 15:42:57.34 spid18s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 16:13:02.09 spid19s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 16:13:02.09 spid19s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 16:43:06.66 spid21s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 16:43:06.66 spid21s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 17:13:11.17 spid20s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 17:13:11.17 spid20s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 17:43:15.69 spid16s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 17:43:15.69 spid16s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 18:13:20.37 spid18s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 18:13:20.37 spid18s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 18:43:25.11 spid16s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 18:43:25.11 spid16s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 19:13:29.62 spid18s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 19:13:29.62 spid18s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 19:43:34.27 spid21s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 19:43:34.27 spid21s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 20:13:39.02 spid16s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 20:13:39.02 spid16s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 20:43:43.53 spid22s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 20:43:43.53 spid22s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 21:13:47.95 spid22s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 21:13:47.95 spid22s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 21:43:52.47 spid17s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 21:43:52.47 spid17s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 22:13:57.33 spid20s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 22:13:57.33 spid20s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 22:44:02.66 spid20s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 22:44:02.66 spid20s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2012-07-30 23:14:02.75 spid17s     Error: 9001, Severity: 21, State: 1.
    2012-07-30 23:14:02.75 spid17s     The log for database 'DBNAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

    <deadlock-list>
      <deadlock victim="process8035fb88">
        <process-list>
          <process id="process8035fb88" taskpriority="0" logused="220" waitresource="KEY: 1:281474978545664 (59855d342c69)" waittime="515" ownerId="2068" transactionname="CLEAN DB" lasttranstarted="2012-07-30T10:35:42.937" XDES="0x80da4e80" lockMode="X" schedulerid="1" kpid="2704" status="background" spid="21" sbid="0" ecid="0" priority="0" trancount="0">
            <executionStack/>
            <inputbuf>
            </inputbuf>
          </process>
          <process id="process8035f948" taskpriority="0" logused="10000" waitresource="DATABASE: 5 " waittime="515" schedulerid="1" kpid="2888" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2012-07-30T10:35:42.670" lastbatchcompleted="2012-07-30T10:35:42.670" lastattention="2012-07-30T10:35:41.810" clientapp=".Net SqlClient Data Provider" hostname="SYSTEM-777" hostpid="1568" loginname="system-777\Administrator" isolationlevel="read committed (2)" xactid="2067" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
              <frame procname="adhoc" line="1" stmtstart="56" sqlhandle="0x02000000ae7bf604eee28d4e060a95789e0872a3a624ee2f">
                SELECT
                dtb.is_ansi_null_default_on AS [AnsiNullDefault],
                dtb.is_ansi_nulls_on AS [AnsiNullsEnabled],
                dtb.is_ansi_padding_on AS [AnsiPaddingEnabled],
                dtb.is_ansi_warnings_on AS [AnsiWarningsEnabled],
                dtb.is_arithabort_on AS [ArithmeticAbortEnabled],
                dtb.is_auto_close_on AS [AutoClose],
                dtb.is_auto_shrink_on AS [AutoShrink],
                dtb.is_cursor_close_on_commit_on AS [CloseCursorsOnCommitEnabled],
                dtb.is_concat_null_yields_null_on AS [ConcatenateNullYieldsNull],
                dtb.is_numeric_roundabort_on AS [NumericRoundAbortEnabled],
                dtb.is_quoted_identifier_on AS [QuotedIdentifiersEnabled],
                dtb.is_read_only AS [ReadOnly],
                dtb.is_recursive_triggers_on AS [RecursiveTriggersEnabled],
                dtb.is_local_cursor_default AS [LocalCursorsDefault],
                dtb.page_verify_option AS [PageVerify],
                dtb.recovery_model AS [RecoveryModel],
                dtb.user_access AS [UserAccess],
                dtb.snapshot_isolation_state AS [SnapshotIsolationState],
                dtb.is_read_committed_snapshot_on AS [IsReadCommittedSnapshotOn],
                dtb.is_db_chaining_on AS [DatabaseOwnershipChaining],
                dtb.is_auto
              </frame>
              <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
                unknown
              </frame>
            </executionStack>
            <inputbuf>
              (@_msparam_0 nvarchar(4000))SELECT
              dtb.is_ansi_null_default_on AS [AnsiNullDefault],
              dtb.is_ansi_nulls_on AS [AnsiNullsEnabled],
              dtb.is_ansi_padding_on AS [AnsiPaddingEnabled],
              dtb.is_ansi_warnings_on AS [AnsiWarningsEnabled],
              dtb.is_arithabort_on AS [ArithmeticAbortEnabled],
              dtb.is_auto_close_on AS [AutoClose],
              dtb.is_auto_shrink_on AS [AutoShrink],
              dtb.is_cursor_close_on_commit_on AS [CloseCursorsOnCommitEnabled],
              dtb.is_concat_null_yields_null_on AS [ConcatenateNullYieldsNull],
              dtb.is_numeric_roundabort_on AS [NumericRoundAbortEnabled],
              dtb.is_quoted_identifier_on AS [QuotedIdentifiersEnabled],
              dtb.is_read_only AS [ReadOnly],
              dtb.is_recursive_triggers_on AS [RecursiveTriggersEnabled],
              dtb.is_local_cursor_default AS [LocalCursorsDefault],
              dtb.page_verify_option AS [PageVerify],
              dtb.recovery_model AS [RecoveryModel],
              dtb.user_access AS [UserAccess],
              dtb.snapshot_isolation_state AS [SnapshotIsolationState],
              dtb.is_read_committed_snapshot_on AS [IsReadCommittedSnapshotOn],
              dtb.is_db_chaining_on AS [DatabaseOw
            </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="281474978545664" dbid="1" objectname="master.sys.sysdbreg" indexname="clst" id="lock800c9580" mode="U" associatedObjectId="281474978545664">
            <owner-list>
              <owner id="process8035f948" mode="S"/>
            </owner-list>
            <waiter-list>
              <waiter id="process8035fb88" mode="X" requestType="convert"/>
            </waiter-list>
          </keylock>
          <databaselock subresource="FULL" dbid="5" dbname="DBNAME" id="lock800c8d80" mode="X">
            <owner-list>
              <owner id="process8035fb88" mode="X"/>
            </owner-list>
            <waiter-list>
              <waiter id="process8035f948" mode="S" requestType="wait"/>
            </waiter-list>
          </databaselock>
        </resource-list>
      </deadlock>
    </deadlock-list>

    Tuesday, July 31, 2012 10:29 AM

All replies

  • Error

    Error: 9001, Severity: 21, State: 1

    means that log file is missing.check the log file of the dataabse, restart the SQL Server, check the size of the tempdb


    Ramesh Babu Vavilla MCTS,MSBI

    Tuesday, July 31, 2012 11:11 AM
  • I know that restart the sql instance will solve the issue and i can continue working with my DB

    I don't understand what is that deadlock all i am doing is creating a new DB using SMO and this deadlock occurs and all the operations against the DB fail 

    Wednesday, August 1, 2012 7:54 AM
  • Can you show us the code you create a database? Make sure that Auto Close setting is set to OFF

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Wednesday, August 1, 2012 8:17 AM
    Answerer
  • Yes this is the code i use

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;

    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Common;

    ...

    public void Create(string i_DataFile, string i_LogFile)
    {
    if (this.Exists)
    {
    throw new InvalidOperationException(Resources.DatabaseAlreadyExistsErrorMessage);
    }

    FileGroup group = new FileGroup(this.Database, Settings.Default.PrimaryDatabaseFileGroupName);

    string logicalDataFileName = Path.GetFileNameWithoutExtension(i_DataFile);

    DataFile dataFile = new DataFile(group, logicalDataFileName, i_DataFile);
    dataFile.Growth = Settings.Default.InitialDataFileGrowthPercentage;
    dataFile.GrowthType = FileGrowthType.Percent;
    group.Files.Add(dataFile);

    this.Database.FileGroups.Add(group);

    if (!string.IsNullOrEmpty(i_LogFile))
    {
    string logFileName = Path.GetFileNameWithoutExtension(i_LogFile);
    LogFile logFile = new LogFile(this.Database, logFileName, i_LogFile);
    logFile.Growth = Settings.Default.InitialDataFileGrowthPercentage;
    logFile.GrowthType = FileGrowthType.Percent;

    this.Database.LogFiles.Add(logFile);
    }

    this.Database.Create();

    bool dbExists = this.Exists;

    if (!dbExists)
    {
    throw new InvalidOperationException(string.Format(Resources.DatabaseFailedToCreate,
    m_DatabaseName,
    dbExists));
    }
    }

    ...

    Wednesday, August 1, 2012 10:03 AM