Today I needed to initialize a pull subscription from a server that had been in the pool of subscribers for a published database, and had been out for sometime. In that time the distribution database had moved, so it was a case of re-creating the subscriptions all over. Once the pull subscription and distribution jobs created, I ran the snapshot agent, which uses a file share. When the snapshot agent had completed, I stared up the agent job on the subscriber. I had done this many times before, and was surprised to see an error not too dissimilar from the one below in the subscriber logs on Replication Monitor:
The process could not read file ‘\\Server1\SQLShareFiles\unc\publication\2050530095003\slmnc.pre’ due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21022)
Get help: http://help/MSSQL_REPL21022
Access is denied.
(Source: MSSQL_REPL, Error number: MSSQL_REPL5)
Get help: http://help/MSSQL_REPL5
Oh. Access is denied? Quickly Googling the error I found a useful post at DBAStackExchange. Although we do use SQL Agent Authentication, what was missing was the permissions to the file share. Browsing to the file share and giving the Agent account full control of the file share resolved the issue for me. And though using Agent accounts does not follow best practice for the Replication Agent Security Model, it got the snapshot pushing out.
In my previous post I shared some SQL scripts which would give you the number of undistributed commands by publication, by publication and article, and by distribution database. So you can measure undistributed commands at a very high granularity (articles), or at a very low granularity (the entire distribution database.) How granular you go depends entirely on where your pain is: you could of course filter out to only a few articles, or a particular publication. Continue reading
When it comes to monitoring replication, using the Replication Monitor.exe gives you a good view of what is happening now. But unless you have someone watching Replication Monitor 24/7, it’s generally not a good idea to rely on it. In fact, its best to avoid running Replication Monitor too often. Instead, you can use the information stored in your distribution database to get information such as agents hitting latency thresholds and a raise in undistributed transactions. Using the information stored in the distribution database ensures that monitoring is centralised. (I’m aware that there is a built in check for replication exceeding a latency threshold, however I have seen a high number of undistributed commands where in fact there were none, and false positives can be frustrating.) Continue reading
This has been done many times before by many other people, but I set this challenge to a friend of mine who was interested in programming, and then realised that I had no solution. So I spent the train journey home writing my effort below. Like a lot of things in computer programming, there’s more than one correct way, but many more incorrect ways! I like this way because it’s quite small and readable.
DECLARE @COUNT TINYINT = 1
DECLARE @FizzBuzz VARCHAR(8)
WHILE @COUNT < 101
SELECT @FizzBuzz = CASE
WHEN @COUNT % 3 = 0
AND @COUNT % 5 = 0
WHEN @COUNT % 3 = 0
WHEN @COUNT % 5 = 0
ELSE CAST(@Count AS VARCHAR(3))
SELECT @COUNT = @COUNT + 1
In yesterdays post I mused what surprises replication would have for me after resolving an issue around the error message “Could not execute sp_replcmds”. Today’s replication issue comes from an error message that greeted me this morning:
“Cannot insert explicit value for identity column in table [table] when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544″ Continue reading
Replication always brings up new surprises, and the most recent surprise I encountered was this error message on every single publication from one database:
“could not execute sp_replcmds on server [servername]” Continue reading
Recently we had an issue with some 3rd part databases that had hit the max INT on some of the identity columns. Potentially the fix for this is easy enough, but you have to be able to anticipate how the application will deal with resetting the identity value back down. Continue reading
Today I am going to share with you a SQL Script that can help monitor the status on your distribution agents in a snapshot/transactional replication topology.
Despite their being some built-in monitoring for replication agents in the Replication Monitor, one that seems to be missing is checking for the agents updating the distributor. Sure there’s ones for agents shutting down for whatever reason, but sometimes latency can be reported as “excellent” for a subscriber despite not having updated the distributor for some time. And because it has not updated the distributor, there’s no calcs for transactional latency, which means that whilst on the surface everything is fine, you are slowly heading towards a real headache with replication. Continue reading
Recently I needed to write a stored procedure that would update a row if it existed in a queue table, and if it did not then to insert the row. Thinking from a pseudo code thought process, you’d run a “IF EXISTS (SELECT Id FROM TABLE) UPDATE, ELSE INSERT INTO TABLE..” Whilst this approach does work, it would be inefficient. This will do a table/index scan twice: once for the SELECT statement and the UPDATE statement.
Depending on your version of SQL Server, there are more efficient ways to do this:
- In 2005, the efficient approach would be to attempt an UPDATE first, and if the @@rowcount returned from the previous statement is 0, then INSERT. This reduces the number of scans, however for inserts it is still two IO operations. As I was developing on a 2005 box, this is the approach I went for.
- In 2008 onwards you can reduce the INSERT to one operation by using the MERGE keyword.
Recently I needed to find a stored procedure that was creating a lock on a table and updating the table regularly. So my starting point was the table name, but I needed to find where the commad was coming from that was locking the table, and what stored procedure was doing the update, if any. As I could not be sure that it was a sproc, I wanted to make sure that I also got the program name. Continue reading