Tagged: Navnit Shukla

How to bypass restart computer error message while installing SQL Patch

If you ever encounter restart computer error message while performing SQL Server patching and you don’t have opportunity\permission to restart your machine.

You can bypass this situation by deleting value from PendingFileRenameOperations key.

Here are the steps which you need to follow –>

  1. To Open Registry Editor. Click Start  ( win + R ) –> Run, input “regedit” and press Enter.
  2. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager, and delete any value in “PendingFileRenameOperations” key.

restart

regedit

rerun.PNG

Replication – Snapshot is not getting generated

Replication issues and resolutions

Today I have encountered very strange issue –

Scenario – Snapshot is not getting generated for newly added articles

or

Error when setting up Transactional Replication via SQL scripts.

Msg  14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Step performed to resolved the issue-

  1. Re snap with reinitialize all subscription option no luck.
  2. Removed the newly added articles and add back and ran the snapshot but NO Luck.
  3. Drop the subscriber and add back and generate the snapshot but no luck ( Most of time it resolve the issue )
  4. Script out publication and drop the publication and when I try to add

While adding Publication i am getting error message –

Error Message –

Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Resolution —

There are 2 unofficial workarounds:

(a) You can circumvent the check by specifying @reserve = ‘internal’ when you add the subscription for the new article and the snapshot agent should generate snapshot for the new article

(b) You could change the immediate_sync property in syspublications to 0 (see sp_changepublication).

Other more official workarounds —

Including changing the sync_method from ‘concurrent’ to either ‘database snapshot’ (enterprise edition only) and ‘native’ (which locks table during snapshot generation). Change the sync_method will force a reinitialization of all your subscriptions at this point. Alternatively you could create another publication and use this instead.

Before —

use [Publisher db]

exec sp_addpublication @publication = N’Publicatoin name’,

@description = N’Transactional publication of database ”dirtrbuter name” from Publisher ”publisher name”.’,

@sync_method = N’concurrent’,

@retention = 0,

@allow_push = N’true’,

 

After —

use [Publisher db]

exec sp_addpublication @publication = N’Publicatoin name’,

@description = N’Transactional publication of database ”dirtrbuter name” from Publisher ”publisher name”.’,

@sync_method = N’native’,

@retention = 0,

@allow_push = N’true’,

 

  1. I have change the @sync_method = N’concurrent’ to @sync_method = N’native’ and ran the script to create publication.
  1. Ran the snapshot but snapshot not getting generated for newly added articles

7. Remove all article (from console) and added again … and ran the snapshot resolve my issue

Create Blocking Alert – SQL 2005 , SQL 2008 R2 and SQL 2012

We often need to create blocking alert in our Production server to capture existing blocking on server and keep record for future analysis.

Here are steps which help us to create blocking alerts-

Either you can receive blocking details through e-mail or you can log event id in application log and configure your monitoring tool to capture from application log.

STEP 1 –  Create SP to capture blocking details and generate e-mail\ event id in application log.

Note –  and datediff(minute, last_batch, getdate()) > 15, through which you can define blocking duration to generate e-mail or event, I am using 15 min

USE [db]

go

/****** Object: StoredProcedure [dbo].[usp_checkForLongRunningBlockers_log] Script Date: 5/18/2015 9:22:50 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[usp_checkForLongRunningBlockers_log]

as

declare @@leadblocker_spid smallint

declare @@dbname nvarchar(128)

declare @@login_time datetime

declare @@last_batch datetime

declare @@open_tran smallint

declare @@status nchar(30)

declare @@hostname nchar(128)

declare @@program_name nchar(128)

declare @@cmd nchar(16)

declare @@loginame nchar(128)

declare @@net_address nchar(12)

declare @@net_library nchar(12)

declare @@string varchar(2000)

select

@@leadblocker_spid = sp.spid,

@@dbname = db_name(sp.dbid),

@@login_time = login_time,

@@last_batch = last_batch,

@@open_tran = open_tran,

@@status = sp.status,

@@hostname = hostname,

@@program_name = program_name,

@@cmd = cmd,

@@loginame = loginame,

@@net_address = net_address,

@@net_library = net_library

FROM master.dbo.sysprocesses sp

LEFT JOIN sys.dm_exec_requests er

ON er.session_id = sp.spid

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

AND blocked = 0

and datediff(minute, last_batch, getdate()) > 15

set @@string =

‘Blocking Details -Date: ‘ + convert(varchar(20), getdate(), 9) + ‘

@@leadblocker_spid = ‘ + convert(varchar(5),@@leadblocker_spid) + ‘

@@dbname = ‘ + @@dbname + ‘

@@login_time = ‘ + convert(varchar(20), @@login_time, 9) + ‘

@@last_batch = ‘ + convert(varchar(20), @@last_batch, 9) + ‘

@@open_tran = ‘ + convert(varchar(5), @@open_tran) + ‘

@@status = ‘ + @@status + ‘

@@hostname = ‘ + @@hostname + ‘

@@program_name = ‘ + @@program_name + ‘

@@cmd = ‘ + @@cmd + ‘

@@loginame = ‘ + @@loginame + ‘

@@net_address = ‘ + @@net_address + ‘

@@net_library = ‘ + @@net_library + ”

if @@leadblocker_spid is null

return

else

–exec dbo.sp_SQLSMTPMail_html

— @vcTo = ‘nshukla@xyz.com’,

— @vcBody = @@string,

— @vcSubject = ‘Long Running Blocking’,

— @vcAttachments = null,

— @vcQuery = null,

— @vcFrom = ‘noreply@xyz.com’,

— @vcCC = ”,

— @vcBCC = ”,

— @vcSMTPServer = ‘nav-mail2.xyz.com’, — put local network smtp server name here

— @cSendUsing = ‘2’,

— @vcPort = ’25’,

— @cAuthenticate = ‘0’,

— @vcDSNOptions = ‘0’,

— @vcTimeout = ’30’,

— @vcSenderName = null,

— @vcServerName = null

EXEC xp_logevent 60000, @@string, error

GO

STEP 2 – Create table to capture blocking history –

USE [db]

GO

/****** Object:  Table [dbo].[BLOCKING_HISTORY]    Script Date: 5/18/2015 9:26:40 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[BLOCKING_HISTORY](

[spid] [smallint] NULL,

[kpid] [smallint] NULL,

[blocked] [smallint] NULL,

[waittype] [varbinary](5000) NULL,

[waittime] [bigint] NULL,

[lastwaittype] [nvarchar](500) NULL,

[waitresource] [nvarchar](500) NULL,

[dbid] [smallint] NULL,

[uid] [smallint] NULL,

[cpu] [int] NULL,

[physical_io] [bigint] NULL,

[memusage] [int] NULL,

[login_time] [datetime] NULL,

[last_batch] [datetime] NULL,

[ecid] [smallint] NULL,

[open_tran] [smallint] NULL,

[status] [nvarchar](500) NULL,

[hostname] [nvarchar](500) NULL,

[program_name] [nvarchar](500) NULL,

[hostprocess] [nvarchar](500) NULL,

[cmd] [nvarchar](500) NULL,

[nt_domain] [nvarchar](500) NULL,

[nt_username] [nvarchar](500) NULL,

[net_address] [nvarchar](500) NULL,

[net_library] [nvarchar](500) NULL,

[loginame] [nvarchar](500) NULL,

[context_info] [varbinary](5000) NULL,

[sql_handle] [varbinary](5000) NULL,

[stmt_start] [int] NULL,

[stmt_end] [int] NULL,

[request_id] [int] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

STEP 3 – Create SP to INSERT  blocking details in Blocking History table.

USE [db]

GO

/****** Object:  StoredProcedure [dbo].[usp_blockingEntry]    Script Date: 5/18/2015 9:25:52 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE Procedure [dbo].[usp_blockingEntry]

as

begin

SET NOCOUNT ON

declare @blockingcount int  =   ( select count(1) from sys.sysprocesses where blocked 0 )

if @blockingcount > 0

INSERT INTO [dbo].[BLOCKING_HISTORY]

(      spid

,      kpid

,      blocked

,      waittype

,      waittime

,      lastwaittype

,      waitresource

,      dbid

,      uid

,      cpu

,      physical_io

,      memusage

,      login_time

,      last_batch

,      ecid

,      open_tran

,      status

–,    sid

,      hostname

,      program_name

,      hostprocess

,      cmd

,      nt_domain

,      nt_username

,      net_address

,      net_library

,      loginame

,      context_info

,      sql_handle

,      stmt_start

,      stmt_end

,      request_id

)

select

spid

,      kpid

,      blocked

,      waittype

,      waittime

,      lastwaittype

,      waitresource

,      dbid

,      uid

,      cpu

,      physical_io

,      memusage

,      login_time

,      last_batch

,      ecid

,      open_tran

,      status

–,    sid

,      hostname

,      program_name

,      hostprocess

,      cmd

,      nt_domain

,      nt_username

,      net_address

,      net_library

,      loginame

,      context_info

,      sql_handle

,      stmt_start

,      stmt_end

,      request_id

from sys.sysprocesses  where blocked 0

end

GO

STEP – 4 – Create SP to get lead blocker –

USE [db]
GO

/****** Object: StoredProcedure [dbo].[usp_leadblocker] Script Date: 5/18/2015 9:44:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[usp_leadblocker]
as

select *
from
(
select
‘Blocking’ as State,
hostname as host,
db_name(dbid) as dbname,
SPID,
blocked,
lastwaittype,
dbid,
login_time,
last_batch,
open_tran,
status,
hostname,
program_name,
cmd,
nt_domain,
nt_username

from sys.sysprocesses (nolock)
where
spid in (
select distinct blocked
from sys.sysprocesses (nolock)
where
blocked 0
and blocked spid
)
and blocked = 0
and blocked spid

union all

select
‘Blocked’ as State,
hostname as host,
db_name(dbid) as dbname,
SPID,
blocked,
lastwaittype,
dbid,
login_time,
last_batch,
open_tran,
status,
hostname,
program_name,
cmd,
nt_domain,
nt_username
from sys.sysprocesses (nolock)
where
blocked 0
and blocked spid
) dt
order by 1 desc

GO

STEP 5 – T- SQL to check SPID text.

DECLARE @sqltext VARBINARY(128)

SELECT @sqltext = sql_handle

FROM sys.sysprocesses

WHERE spid = 64

SELECT TEXT

FROM sys.dm_exec_sql_text(@sqltext)

GO

***************************************

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 64
SELECT TEXT
FROM ::fn_get_sql(@sqltext)
GO

*****************************

DBCC INPUTBUFFER(64)
GO

*************************

SP_WHO2 64

go

SSRS – How to convert in Day/Hour/Minute format in SSRS report

Scenario – In my SSRS report i have calculate Age field of Tickets like in 1 Day 2 Hours 20 min format and we are getting values in minute .

Solution –

I have created filed called Age(Minutes) in my SP or query

IN SQL –

datediff(minute,date_created, getdate()) as ‘Age (Minute)’

IN SSRS write expression

=  ( Fields!Age__Minute_.Value ) \ 1400 & ” days ” &  ((( Fields!Age__Minute_.Value ) MOD 1400) \ 60 ) & ” Hours ” & ((( Fields!Age__Minute_.Value ) MOD 1400) MOD 60 ) & ” Minutes “

SQL Server 2012 Always On Step by Step Configuration

SQL Server 2012 always on Step by step configuration –

Prerequisite to configure always on –

1. Domain accounts for SQL Server services should have local admin   access
2. Two IP addess
One is for Cluster
Other one is for always ON listener
Note – These IP address should be in customer facing sub-net
3. UNC share on any non-cluster server and non-domain controller.
Cluster computer account and each node computer account should have  R\W access to this share.

if you are not having domain controller access ask your Window Admin team

– Pre-staged cluster computer account in Active Directory. Account can be pre-staged according to the following article: http://technet.microsoft.com/en-us/library/cc731002.aspx#BKMK_steps_precreating

– Pre-staged AlwaysOn listener computer account in Active Directory. Account can be pre-staged according to the following article: http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx#BKMK_steps_precreating2

Windows cluster installation: 

1) Install Failover Cluster feature using Server Manager on both/all nodes.

2) Open Failover Cluster Manager and create a new cluster:

Cluster1.PNG
3) In “Select Servers” step add all your cluster nodes. Click Next.

4) Pass the validation, all test. Warnings regarding storage are OK – you’re setting up AlwaysOn, so you don’t have shared storage.

5) Specify cluster name and IP address on the next screen. If cluster account was pre-staged make sure to specify correct name (same as the pre-staged computer account name). Make sure to use IP, that was provided by customer and is 100% not in use.

6) Finish cluster installation.

7) Configure quorum or the newly created cluster:

How to configure Quorun –

a. Right Click on Cluster Name –> More action –>Configure Cluster Quorum setting

b) On the “Select Quorum Configuration Option” window select “Select the quorum witness”. Click “Next”.

c) On the “Select Quorum Witnes” window select “Configure a file share witness”. Click “Next”.

d) Specify a file share path, provided by the customer. Click “Next”

e) Click “Next”, then “Finish”

Installing SQL Server:

1) Install SQL Server as a stand-alone instance.

2) Following components are required and recommended for basing SQL Server installation:

SQLInstallation1.PNG

3) Complete the installation.

4) Make sure to add SQL Server service account to sysadmins at each instance. This is required.

Enabling AlwaysOn on instance level:

1) Open SQL Server configuration manager.

2) Select SQL Server Services in the left-side menu.

3) Right click SQL Server service and select “Properties”:

SQLConfiguration1.PNG
4) Go to “AlwaysOn High Availablity Tab” and check “Enable AlwaysOn Availability Groups”:

5) Click “OK”. Make sure to restart SQL Server service.

6) Repeat 1-5 on each node.

Configuring AlwaysOn high availability group (single interface option)

Please skip to the next part if you want to use separate NICs for mirroring traffic and user connections.

1) Open SQL Server Management studio and connect to the first node.

2) Open AlwaysOn High Availablity -> Availablity groups and select “New Availability Group” (make sure to use 2012 Management Studio):

3) In the new window enter availability group name (it may be any name you wish), click “Add” and specify secondary replica name:

alwayson_3

4) You can use default settings or customize them.

5) Click “OK”.

6) Go to SQL Server Management Studio, expand AlwaysOn High Availablity, expand your Availability Group, expand “Availability Replicas”, right click your second server and select “Join Availablity Group” (if it exists).
7) In new Window connect to it and click “Next” and “Finish”.

Configuring AlwaysOn availability group listener

1) Go to SQL Server Management Studio, expand AlwaysOn High Availablity, expand your Availability Group, created earlier, right click “Availabilty Group Listeners” and select “Add Listener”:

2) Specify Listener DNS Name (if AlwaysOn listener computer account was pre-staged, this should match the name of the computer), enter 1433 into “Port”, for “Network mode” select Static IP and click “Add” to add an IP address:
alwayson_1

3) In the “Add windows” select the correct subnet and enter an IP address provided by customer:

always_2

4) Click “OK” and “OK”.

Adding/Removing databases to/from AlwaysOn availability group.

1) To add a database to availability group you select “Add database” in “Availability databases” and follow the Wizard.

– You can use fully automated addition. In this case you will need network share, accessible from both servers and with RW rights for SQL Server service account.

SQL Server will perform backup and restore of the database automatically.

– You can use “Join only” addition. In this case you will need to perform full backup and t-log backup and restore (as when configuring Mirroring, WITH NORECOVERY) and then use the Wizard.

2)  To remove a database from availability group just right click the database and select “Remove database from availablity group…”:

Please note that before deleting database, that is part of AlwaysOn HA, you must delete it from Availability group.