![]() SQL Server ERRORLOG would have information about the closing of this connection. No attempt was made to restore the connection. The connection is marked by the server as unrecoverable. The connection is broken and recovery is not possible. If we come back to the connection which was running query 1 and try to execute anything, we would get this error: Msg 0, Level 20, State 0, Line 9 (Please note that ALTER INDEX waited for 1 minute because there was a session blocking it.) We would notice that the command got succeed after waiting for 1 minute. We can run the same commands as above, but now using this option. Option 3: ABORT_AFTER_WAIT = BLOCKERS (Kill all blockers)Īs explained earlier, due to this option, SQL Server is going to kill all blockers. You can now cancel query 2 in the earlier example and run it again with this new option.Īs we can see the time, exactly after 1 minute we got the error shown below: Msg 1222, Level 16, State 56, Line 4 When this option is provided, ALTER INDEX would exit by itself if the lock is not acquired. Option 2: ABORT_AFTER_WAIT = SELF (Exit DDL after wait) This confirms that online index rebuild switched to normal queue and would wait. If we look at blocking using the query below: select session_id, blocking_session_id, wait_resource, wait_time, wait_typeįrom sys.dm_exec_requests where session_id = 58 - this is the SPID from query 2Īfter 1 minute, which we have specified in alter index query, we will see this: In my case, query 1 was SPID 54 and query 2 was SPID 58. We need to make a note of the SPID in each query. Online = on ( wait_at_low_priority ( max_duration = 1 minutes, abort_after_wait = none ) ) Query 2 select 'spid'Īlter index pk_mydemotable on mydemotable Once query 1 is started, we will run query 2 in another query window. Query 1 - query 1: taking shared lock and holding it. You can run the queries shown below to have a look at the behavior. Option 1: ABORT_AFTER_WAIT = NONE (Switch to normal queue) ![]() The above script would take around 60 seconds. ![]() create a table in database create earlierĬonstraint pk_mydemotable primary key (id) Let’s look at the following example to understand the low priority wait queue and the behavior with all three options. So unless we have the transaction log drive space to support 49 days worth of transactions, we should keep the MAX_DURATION lower.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |