Logreader timeouts...
Mood:  hungry
In a previous entry, I talked about instructing Logreader to break huge transactions into smaller chunks, using the -MaxCmdsInTran parameter. This still didn't help in a specific case, where a process ran for a long time generating millions of commands within a transaction. As a result, the log reader timed out with the error: "The process could not execute sp_replcmds". If you drill down into the agent history, you will see a "Timeout expired" error message. To overcome this situation, you need to increase the Query Timeout for logreader, which is set to a default of 300 seconds. For example, you'd add the following parameter to log reader's "Run Agent" step, to increase the query timeout:

-QueryTimeOut 1000

After adding this parameter, restart the logreader agent. You'll probably see status messages like "Initializing...", "The process is waiting for a response from one of the backend connections..." etc. But the logreader will eventually pickup all the transactions from the published database's transaction log, provided the timeout value is sufficient.

Also make sure there is no blocking going on in the publishing and distribution servers. Blocking often results in timeouts as well. Sometimes, log reader gets blocked by the distribution clean up job. So, use sp_who or sp_who2 to make sure there's no blocking, and if there is, then clear it. If the distribution clean up job is blocking logreader, then stop the distribution clean up job.

Replicating huge transactions...
Mood:  cool
Are you replicating a table that is undergoing data modifications, that affect several thousands or even millions of rows? In that case, you could be seeing blocking on the subscribing tables, as the distribution agent tries to apply all those commands to subscriber in one big transaction. This could also affect your transaction log backup sizes on the subscribging database. Well, I faced a similar situation today.

There's hardly any information on SQL Server replication on the net. So, I resorted to SQL Server Books Online, and found this new parameter for logreader (introduced in SQL Server 2000 SP1), called MaxCmdsInTran. Using this parameter, you can specify the maximum number of commands that constitute a transaction. For example, if you specify a value of 1000 for this parameter, even if you do an UPDATE on the published table, that affects 100,000 rows, it will be stored in the distribution database as 100 transactions, with 1000 commands each. As a result, the distribution agent applies shorter transactions to the subscriber, and results in minimal or no blocking. This will also help reduce the transaction log backup sizes.

One thing to be aware of though. This new parameter will not guarantee transactional consistency. For example, one transaction on the publisher affects 100,000 rows, but if the distribution agent fails while applying these transactions, it will not rollback all those changes on the subscriber. It will only rollback those changes that are part of the smaller (sub-divided) transaction that failed. In my case it didn't really matter, so I am going for it.

See "Replication Log Reader Agent Utility" in SQL Server Books Online for more information.

Another long day!
Mood:  don't ask
6.5 hours overtime, just to figure out there's nothing wrong with my SQL Server database and replication, but it is the application that is causing performance issues. Lesson learnt: Don't always believe that it is the database that is causing performance issues, though people around you force you to think so!

Here are some books that talk in detail about database as well as application performance tuning.

Subscribing partially to a publication, that is setup with 'concurrent snapshot option'
Mood:  surprised
It's replication time again! Just wanted to add a new table to an existing publication. Added it successfully using sp_addarticle. So far so good. When I tried to subscribe to that newly added article from an existing subscription, using sp_addsubscription, I got the following error:

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

So, that means, there's no point in adding an article to an existing publication (that is setup with concurrent snapshot option), as you cannot synchronize subscriptions for those publications, without a complete resynch. Doesn't make sense! Well, I will try to find a workaround, and update this post.

Deadlocks!!
Mood:  lazy
Big day today! Working this Sunday to fix some deadlocking issues. Must fix them today and get the application live tomorrow.

I use the procedure explained here, to log detailed information about deadlocks into SQL Server error log.

And now for the coolest bit. Do you know how to get a count of deadlocks from the current errorlog? Try this:

EXEC sp_readerrorlog 1, 'g:\mssql\log\errorlog', 'Deadlock'

Be sure to edit your errorlog path in the above command. If you get 10 rows as output, that means you have 10 deadlocks logged in the errorlog. For more information about this undocumented functionality of sp_readerrorlog, read this article

End of day update: Got rid of most of the deadlocks, mostly by changing the order in which tables are accessed, converting some unnecessary outer joins to inner joins, changing the isolation level from serializable to read committed etc. I'm a happy chappy for now :-)

Welcome!
Mood:  bright
Hello there! Welcome to my SQL Server blog, and this happens to be the first entry in the blog! I hope to find some time to add SQL Server tips and tricks to this page, on a regular basis. Of course, not everything I post here will be about SQL Server!