Counting Unreplicated Commands and Transactions

Counting Unreplicated Commands and Transactions Someone asked me a question via Twitter today that I couldn’t possible answer 40 characters at a time. The question was about how to get the count of unreplicated transactions and commands from teh distribution server without using system views. Well, I do use one system view, but only because replication relies on the same view, sys.servers. The Question @SQLSoldier – Was told you were the guru … lookin for query to get undelivered cmds
read more

SQLU HA/DR Week – Database Mirroring Performance Counters

SQLU HA/DR Week – Database Mirroring Performance Counters Database mirroring debuted in SQL Server 2005 and had eleven performance counters for monitoring mirroring. There were ten new performance counters released with SQL Server 2008. We now have twenty-one performance counters to use for monitoring database mirroring. A list with a brief description of each can be found in Books Online or online at http://msdn.microsoft.com/en-us/library/ms189931.aspx. Many people find themselves without a clear understanding of what the counters mean and how to
read more

SQLU VLDB Week – Archiving and Purging Data

SQLU VLDB Week – Archiving and Purging Data Welcome back for the final day of VLDB week at SQL University. Today, we will talk about one of the big challenges that all administrators must eventually tackle when working with a VLDB: archiving and purging data. This particular problem has a very simple solution; however, if you can’t take the easy way out, there are other things you can do. Before we go any further, here is a recap of VLDB
read more

SQLU VLDB Week – Integrity Checks

SQLU VLDB Week – Integrity Checks Welcome back to day four of VLDB week at SQL University. Today, we will talk about running integrity checks – a.k.a. DBCC CHECKDB. In factuality, there are serveral DBCC CHECK commands for checking database integrity, not just CHECKDB. CHECKDB is the main function, the grandaddy of all DBCC CHECK commands. It is the one that performs the full gamut of integrity checks on a database. Almost everything I know about CHECKDB I learned from
read more

When Does Trustworthy Get Reset on a Mirror?

When Does Trustworthy Get Reset on a Mirror? When I’ve done sessions on automating database mirroring processes and in my book, I talk about incorporating steps to set properties that get reset during the restore process in the mirroring setup such as database owner and the Trustworthy property. When you perform the initial restores of the database to set up mirroring, the database owner is set to the account of the person performing the restore, and if Trustworthy is enabled,
read more

Does a Mirroring Failover Clear the Procedure Cache?

Does a Mirroring Failover Clear the Procedure Cache? I saw a question on the MSDN forums asking what happens to the procedure cache (also known as the plan cache) on the principal and mirror when a failover occurs. My reply was starting to get long, so I decided to move it out to a blog post. The original question poses the theory that the procedure cache is flushed on both mirror and principal when a failover occurs. How Mirroring Transfers
read more

Getting Fast Counts of Large Service Broker Queues

This question regarding getting a fast count from a service broker queue came in through a discussion group. Question: Fast way to get a count of records from an SSB queue Subject: Fast way to get a count of records from an SSB queue I’m looking for tips or pointers on how to optimize for reading the count of records in an SSB queue. We’re seeing as the queue grows to 1M records a simple SELECT COUNT(*) WITH(NOLOCK) is taking
read more

What Does Redone Mean?

What Does Redone Mean? Recently, I was honored by having a fellow DBA I’ve known for many years ask me to be his mentor. Earlier this week, he was reading a TechNet article by Paul Randal (blog | @PaulRandal) on Understanding Logging and Recovery in SQL Server. He didn’t quite understand what Paul meant by the term redone. His Questions via Email I was reading recovery by Paul and just came across the below sentence: For a log record from
read more

T-SQL Tuesday #011 – Top 5 Myths of Database Mirroring

T-SQL Tuesday #011- Top 5 Myths of Database Mirroring This blog entry is participating in T-SQL Tuesday #011, hosted this month by Sankar “@SankarReddy13” Reddy(Blog|Twitter). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Misconceptions in SQL Server. For my part in this month’s T-SQL Tuesday, I will be discussing my top 5
read more

T-SQL Tuesday #010: Indexes: To Be or Not To Be (a B-tree)

T-SQL Tuesday #010: Indexes: To Be or Not To Be (a B-tree) This blog entry is participating in T-SQL Tuesday #010, hosted this month by Michael J Swart (Blog|@MJSwart). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s topic: indexes. For my part in this month’s T-SQL Tuesday, I’m going to talk about index
read more