troubleshooting Question

Blocking issue+SQL Server 2012

Avatar of mssql_v2005
mssql_v2005 asked on
Microsoft SQL ServerDatabasesMicrosoft SQL Server 2008
7 Comments1 Solution344 ViewsLast Modified:
Hi All,

We are facing blocking issue  with below command while restoring full DB backup with NORECOVERY option (while configuring DB mirroring). We are trying to kill below process but again and again below command is causing blocking issue.

Can you please advise how to fix this blocking issue.

Command that is causing blocking issue:
(@_msparam_0 nvarchar(4000))SELECT
ISNULL((case dmi.mirroring_redo_queue_type when N'UNLIMITED' then 0 else dmi.mirroring_redo_queue end),0) AS [MirroringRedoQueueMaxSize],
ISNULL(dmi.mirroring_connection_timeout,0) AS [MirroringTimeout],
ISNULL(dmi.mirroring_partner_name,'') AS [MirroringPartner],
ISNULL(dmi.mirroring_partner_instance,'') AS [MirroringPartnerInstance],
ISNULL(dmi.mirroring_role,0) AS [MirroringRole],
ISNULL(dmi.mirroring_safety_level + 1, 0) AS [MirroringSafetyLevel],
ISNULL(dmi.mirroring_state + 1, 0) AS [MirroringStatus],
ISNULL(dmi.mirroring_witness_name,'') AS [MirroringWitness],
ISNULL(dmi.mirroring_witness_state + 1, 0) AS [MirroringWitnessStatus],
CAST(case when dmi.mirroring_partner_name is null then 0 else 1 end AS bit) AS [IsMirroringEnabled],
ISNULL(dmi.mirroring_guid,'00000000-0000-0000-0000-0000000000000000') AS [MirroringID],
ISNULL(dmi.mirroring_role_sequence,0) AS [MirroringRoleSequence],
ISNULL(dmi.mirroring_safety_sequence,0) AS [MirroringSafetySequence],
ISNULL(dmi.mirroring_failover_lsn,0) AS [MirroringFailoverLogSequenceNumber],
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_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.is_db_chaining_on AS [DatabaseOwnershipChaining],
dtb.is_auto_update_stats_async_on AS [AutoUpdateStatisticsAsync],
dtb.is_date_correlation_on AS [DateCorrelationOptimization],
dtb.is_trustworthy_on AS [Trustworthy], AS [Name],
dtb.database_id AS [ID],
dtb.create_date AS [CreateDate],
dtb.is_auto_create_stats_on AS [AutoCreateStatisticsEnabled],
dtb.is_auto_update_stats_on AS [AutoUpdateStatisticsEnabled],
dtb.is_parameterization_forced AS [IsParameterizationForced],
dtb.is_read_committed_snapshot_on AS [IsReadCommittedSnapshotOn],
dtb.is_auto_close_on AS [AutoClose],
dtb.is_broker_enabled AS [BrokerEnabled],
CAST(isnull(dtb.source_database_id, 0) AS bit) AS [IsDatabaseSnapshot],
ISNULL(DB_NAME(dtb.source_database_id), N'') AS [DatabaseSnapshotBaseName],
dtb.is_fulltext_enabled AS [IsFullTextEnabled],
dtb.service_broker_guid AS [ServiceBrokerGuid],
dtb.snapshot_isolation_state AS [SnapshotIsolationState],
(dtb.is_published*1+dtb.is_subscribed*2+dtb.is_merge_published*4) AS [ReplicationOptions],
suser_sname(dtb.owner_sid) AS [Owner],
ISNULL(dtb.log_reuse_wait,0) AS [LogReuseWaitStatus],
drs.recovery_fork_guid AS [RecoveryForkGuid],
drs.database_guid AS [DatabaseGuid],
CAST((case when drs.last_log_backup_lsn is not null then 1 else 0 end) AS bit) AS [HasFullBackup],
CAST(case when in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit) AS [IsSystemObject],
CAST(case when ctb.database_id is null then 0 else 1  end AS bit) AS [ChangeTrackingEnabled],
CAST(ISNULL(ctb.is_auto_cleanup_on,0) AS bit) AS [ChangeTrackingAutoCleanUp],
ISNULL(ctb.retention_period,0) AS [ChangeTrackingRetentionPeriod],
CAST(ISNULL(ctb.retention_period_units,0) AS tinyint) AS [ChangeTrackingRetentionPeriodUnits],
dtb.containment AS [ContainmentType],
dtb.default_language_lcid AS [DefaultLanguageLcid],
dtb.default_language_name AS [DefaultLanguageName],
dtb.default_fulltext_language_lcid AS [DefaultFullTextLanguageLcid],
ISNULL(dtb.default_fulltext_language_name,N'') AS [DefaultFullTextLanguageName],
CAST(dtb.is_nested_triggers_on AS bit) AS [NestedTriggersEnabled],

Open in new window

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 7 Comments.
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>


Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 7 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004