Archive for February, 2011

How to figure out IIS Load in NLB

February 18, 2011 Leave a comment


As a good practice always collect data on a non-production system.

  • Create a User Defined Performance Collector with below counters for all the web servers added in NLB.
    • Web Server
      • Current Connections
      • Connection Attempts / Sec
      • Login Attempts / Sec

You can save the collection data onto SQL Server, text files, Binary files etc. I mostly save the data in .blg format (Binary).As it is convenient to open the file in Performance Monitor.

Once the collection set is created you can schedule it to run continuously every sec or time you like to monitor, I mostly do it every 1 min.

Let’s say you ran the Performance collection set for one day and collected enough data to analyze. Stop the Collector Set as opening the in-use file can corrupt it.

Open the file in performance Monitor tool.

Performance monitor will do a graph representation for all the data you collected for the duration, but still it give us much information to analyze. Select the counter for each server and see the Average Load of the server.

Performance monitor will give a consolidated Average for the duration data has been collected.

To do deeper analyses on Web Server Load (Average Connection per Hour /Per Day, Peak Load etc.) I load the data into SQL Server using windows performance monitor relog.exe.

Loading the data using relog.exe require ODBC Data Source for SQL Server.

Uploading the Data into SQL server

As u can see from the above picture, I loaded the data into database named Perfmon using ODBC SUFIAN_XYZ_1 successfully.

  • Tables Created on successful data load
    • CounterData
    • CounterDetails
    • DisplayToID    

Stage the data as required; I used the data to analyze all Web Server Load Daily / Hourly.

SQL Query:

SELECT cd.MachineName,cd.CounterName,


sum(c.CounterValue) TotalConnections,

round(sum(c.CounterValue)/count(SUBSTRING(SUBSTRING(c.counterDateTime,12,len(counterDateTime)),1,2)),0,0) Avg_Connection_PerHour

FROM CounterData c INNER
JOIN CounterDetails cd

ON c.CounterID=cd.CounterID

By cd.MachineName,cd.CounterName,


by cd.MachineName,cd.CounterName,SUBSTRING(c.CounterDateTime,0,12)




Copy the Data with head and paste I to Excel and create a PivotTable and chart as below


Categories: IIS

How to Setup iSCSI on Windows Storage Server 2008

February 16, 2011 Leave a comment

Categories: Uncategorized

Forcing the clearing of persistent reservations on a disk formerly used by the cluster

February 7, 2011 1 comment


It happens most of the time that while Cluster Failover occurs or due to some issue one of the disk or more went off line and on trying to being the resource online we get error as below.


The Cluster service failed to bring clustered service or application ‘SQL Server’ completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered service or application.

Cluster resource ‘Disk E:’ in clustered service or application ‘SQL Server’ failed.


If disk failover occurs but the server that takes ownership of a disk cannot read it, the cluster cannot maintain availability of the disk. If information written to the disk is changed during the process of failover, it could cause issues for users or software that requires this information. In either case, if the affected disk is a disk witness (a disk that stores cluster configuration data and participates in quorum), such issues could cause the cluster to lose quorum and shut down.


Open the Disk Management under storage of Computer management .Get the disk number which failed.

Open Command Prompt with “Run as Administrator”

Run the command in command prompt

Cluster node Server name /clearpr:DiskNo(ex:5)

Ex: cluster node cpnftsdtdb1 /clearpr:5

Once clearing of persistent reservations on the disk is done. Try to bring the resource online.

Categories: Windows Cluster

Log Reader Agent Status Notification

February 6, 2011 1 comment

While setting up critical Transactional Replication I came across this scenario of alerting the L3 DBA support team with Log Reader Agent Status and writing it to SQL Server Error Log.

When Log Reader Agent is Retrying to read the Database Log and sending it to Distribution Database and when it fails.

To achieve this I have written an after insert trigger on table
MSlogreader_history in Distribution Database.

To get the last status of the Log Reader Agent execute the below code on distributor database.

SELECT comments,runstatus FROM distribution.dbo.MSlogreader_history
where timestamp=(SELECT
MAX(timestamp) from MSlogreader_history)

—Trigger Code

ON MSlogreader_history
                 DECLARE @LogReaderStatus int
DECLARE @LogReaderComments varchar(255)
                 DECLARE @LogReader            varchar(255)
                 DECLARE @@Status            varchar(255)SELECT @LogReaderComments=lrh.comments,@LogReaderStatus=lrh.runstatus,

FROM distribution.dbo.MSlogreader_history lrh INNER JOIN MSlogreader_agents lra

and lrh.timestamp=(SELECT
MAX(i.timestamp) from INSERTED i)
                          IF @LogReaderStatus=5
SET @@Status=‘Log Reader Agent : ‘+ @LogReader+‘ is retrying…..’
EXEC xp_logevent 60000, @@Status, informational
IF @LogReaderStatus=6
SET @@Status=‘Log Reader Agent : ‘+ @LogReader+‘ Failed…….’
EXEC xp_logevent 60000,@@Status, informational


Transactions waiting to be replicated to Subscription Database (Transactional Replication)

February 2, 2011 5 comments

While troubleshooting Replication issues it’s always a question till what point transaction has been replicated to subscription and from which point transaction are in distribution database waiting to be replicated to subscription database.

Replication Architecture

Let’s say distributor agent failed to replicate transaction from distributor to subscriber due to some reason and now after restarting the distributor agent it’s still not replicating the transactions.
There could be n number of reason due to which distributor agent may fail.

In this post we will focus on how to get last transaction replicated to subscriber database and what all transaction are waiting in the distributor database to be replicated.

Every time distributor agent replicates the transaction from distributor to subscriber, it does an entry in a system table dbo.MSreplication_subscriptions which exists in subscriber database.
The MSreplication_subscriptions table contains one row of replication information for each Distribution Agent.

Code: SELECT publisher,publisher_db,publication,transaction_timestamp
FROM dbo.MSreplication_subscriptions

Transaction_timestamp value is same as xact_seqno and till this sequence number all the transaction are replicated to subscription database.

In case u does not have access to the subscription database and to find out the last replicated xact_seqno to subscriber database from distributor database

sp_MSget_last_transaction Publicationid,Publication_database
sp_MSget_last_transaction 2,TestDB

Connect to the Distributor

Code: select * from MSrepl_commands Where xact_seqno>Transaction_timestamp value

This will give you those records which are in distributor and not been replicated to subscriber database after the last replicated xact_seqno
in subscriber database (dbo.MSreplication_subscriptions)

sp_browsereplcmds : @xact_seqno_start, @xact_seqno_end

The stored procedure will use to view pending commands in the distributor database.|
It will display the commands in the readable format.


Replication monitor will also give the xact_seqno at which distributor agent failed


You can also check the error on browsing MSrepl_errors table in distribution database.

From MSrepl_errors


In this case error is :”The row was not found at the Subscriber when applying the replicated command:

To resolve the same I have xact_seqno and command id , I simple deleted the record from the MSrepl_commands at the distributor.Once done distributor agent will start replicating pending transaction to subscriber.

DELETE from MSrepl_commands where command_id=1295 and xact_seqno=0x000260AD000000100021