HELP - My Distribution Database Is HUGE, But I Don't Have A Lot Of Commands In Queue

I recently ran across an issue where the Distribution database on the Distributor was almost 150 GB, but when I looked at the Publications in Replication Monitor, I found that there were less than 50,000 commands in queue across all Publications.

I immediately thought, "Why on earth is the Distribution database bigger than this truck?"

I started my investigation by running this query to see how many Transactions were in the Distribution database (there were several million):
SELECT PD.Publisher_DB, COUNT(RT.xact_id) AS #TransactionsInDistributionDB
FROM distribution.dbo.MSrepl_transactions RT
JOIN distribution.dbo.MSpublisher_databases PD ON PD.id = RT.publisher_database_id
GROUP BY PD.Publisher_DB;

However, Replication Monitor tells you how many Commands are in queue (not Transactions), so I ran this query and found that there were almost 500 million commands total in queue (NOTE: Use this query with caution, it will take a long time to run if there are a lot of commands in queue - in my case it took almost 45 minutes):
SELECT PD.Publisher_DB, COUNT(RC.command_id) AS #CommandsInDistributionDB
FROM distribution.dbo.MSrepl_commands RC
JOIN distribution.dbo.MSpublisher_databases PD ON PD.id = RC.publisher_database_id
GROUP BY PD.Publisher_DB;

We also observed a lot of Disk IO on the Distributor, and the Distribution clean up: distribution job was taking a lot longer than normal (1-4  hours instead of 2-5 minutes).

After some Googling I found a great article by Paul Ibison (http://www.replicationanswers.com/TransactionalOptimisation.asp) that reveals that the immediate_sync option causes the Distributor to queue all Transactions for a Publication until the retention period is reached, regardless of whether or not the Transaction has been delivered or not.

I ran this query at the Distributor to see what Publications had the immediate_sync option enabled:
SELECT SS.Name AS PublisherName, Pubs.Publisher_DB, Pubs.Publication, Pubs.Immediate_Sync
FROM distribution.dbo.MSpublications AS Pubs
JOIN master.sys.servers AS SS ON SS.server_id = Pubs.publisher_ID
WHERE Pubs.Immediate_Sync = 1
ORDER BY PublisherName, Publisher_DB, Publication;

This revealed 2 Snapshot Publications that had that option enabled.  Yes, you read that correctly, the immediate_sync option also causes the Distributor to queue all Transactions for Snapshot Publications.  Even though they're not necessary for the Publication.  

How Do I Fix It?
To correct the issue, you need to run a couple scripts in the Published database (update PublicationName as appropriate):
EXEC sp_changepublication
@publication = 'PublicationName',
@property = 'allow_anonymous', 
@value = 'FALSE';

EXEC sp_changepublication 
@publication = 'PublicationName',
@property = 'immediate_sync', 
@value = 'FALSE';

The next execution of the Distribution clean up: distribution job will take much longer, but it will then clean up all the unnecessary transactions.  In my case, there is now less than 10 GB of data in the Distribution database.

These scripts can be executed on the fly without impact as they do not interrupt or affect the Publication (aside from telling the Distributor that it no longer needs to queue commands that it doesn't really need).

How Did It Happen?
This option is a result of checking a very innocent looking box in the New Publication Wizard:

This setting is very BAD

If you have scripted out the Publication creation, edit your scripts and look for this snippet:
EXEC sp_addpublication ... @immediate_sync = N'true'

This will need to be changed to:
EXEC sp_addpublication ... @immediate_sync = N'false'

This option can greatly impact the performance and storage of your Distributor.  If you suspect this an issue on a Distributor you manage, I recommend you run the scripts above to check for and correct the issue.

Thanks again Paul Ibison for your invaluable assistance.

Red Gate Releases SQL Monitor v4 - One Small Step For Man, One Giant Leap for SQL Server Monitoring

When I attended Red Gate's SQL In The City in Charlotte, I spent a great deal of time chatting with the SQL Monitor project manager, Daniel Röthig.  He let me in on a little secret about SQL Monitor v4 that really excited me.  It's no longer a secret and I recently upgraded from SQL Monitor v3 to v4.  Overall, I was very happy with the upgrade process and the new features.  

So without further ado...

The Upgrade Process
The upgrade to v4 was very straightforward.  While it didn't initially give any initial indication that it had discovered that v3 was installed, it did carry over all of my settings and at the end of the process it warned that a downgrade was not possible due to the changes that the upgrade made to the structure of the repository database.

The upgrade was quick and painless.  My only wish here would be for it to check (or indicate) that it found another version already installed.  This would improve confidence level through the install process (I ended up going back and forth a couple times between the install and the current version to verify folder path and configured ports).

New Features
Within moments of launching the new version, I immediately noticed two new features.

1. DBs list shortened to first 5 databases in SQL Instance view 
• When you select an instance of SQL, SQL Monitor v4 only lists the first 5 databases on that server.  I have several servers with 500-2000 databases and by the time SQL Monitor v3 listed them all, it's time to refresh the page (making the SQL Instance page nearly useless as it was constantly refreshing/loading).  This feature greatly improves loading speed of the main page for SQL Instance monitoring.
• The issue still exists if you expand all the DBs on the server, but you can just click the Pause button (or rewind time 1 minute) to suspend the page refreshes.  Once you've drilled into the specific database you're interested in, you can click the Return to present button to resume real-time monitoring (and page refreshes).
2. SQL Waits Monitoring
• Top 10 SQL Server wait stats (sorted by Wait time, Waiting tasks, Average Wait time, or Signal wait time) with a brief description of each wait state.
• Each wait state breaks out the top 10 queries that generated those waits (sortable by Execution count, Duration, CPU time, Physical or Logical reads, or Logical writes)
• This is the best new feature of SQL Monitor v4, and this is what Daniel and I spent so much time discussing.
• Just knowing the query resource utilization only reveals part of the story.  By monitoring the wait stats, you can better focus your tuning efforts and have a much clearer understanding of what SQL Server is doing.

Existing features that I love

SQL Monitor does a great job in multiple areas:

• Analysis
• Easy access the performance graphs, just click on a metric on the main page to drill into the analysis graph
• Baselines (Regions View) - Awesome for tracking problems that occur regularly or comparing performance over multiple time periods
• Overlay additional stats (you can stack multiple metrics on the same graph to help pinpoint trouble areas)
• Alerts
• Many default alerts are active out of the box to give you a good idea of what your SQL Server is doing that might not be best practice
• Deadlocks appear here and you can drill into the detail of the deadlock participants.

Wish list

SQL Monitor does a great job monitoring many aspects of SQL Server (and the underlying OS), but there are a few new features I'd love to see added to make the product even more robust:

• Charts for SQL waits and query resource usage
• You can currently view charts for server resource utilization (OS and SQL resources) with baselines on the Analysis tab, but charts for wait types and query waits/resources would also be beneficial.  
• Charts should be clickable to drill into detail for a specific wait type, resource, or query.
• Charts help to reveal queries that are more bursty in nature.  I would be more likely to tune a poorly performing query that completely pegs system resources for a couple minutes than one that consumes the same amount of resources over a larger timeframe.
• # Blocked sessions (clickable to see the blocking tree - including query detail for all queries involved)
• Additional Deadlock detail (graphical deadlock graph would be great, but I did find that the text is available under Alerts -> {specific alert} -> Output)
• Additional Memory stats monitored
• Free list stalls/sec
• Lazy writes/sec
• Page Life Expectancy only reveals so much information and it's value decreases greatly when running on multiple NUMA nodes (PLE is an average across all NUMA nodes.  If one NUMA node is crushed, but the others are fine, PLE will not reveal the a true measure of memory pressure).  These stats are additional ways to measure memory utilization/pressure on an instance of SQL.
• Signal wait time % (total Signal Wait time/Total Wait Time)
• This is the percentage of time that SQL Server is waiting on the CPU to be ready to process a query (10% = low, 15% = medium, 20% = high and anything over 20% indicates high CPU resource contention)
• This is available as a custom metric (instructions here: http://sqlmonitormetrics.red-gate.com/cpu-signal-wait-time/), but I think this is important enough to include by default

In conclusion, SQL Monitor is maturing into a robust product and the v4 upgrade brings it one step closer to being the best SQL monitoring tool on the planet.

Replication Monitor Not Able to Display Publications - I Love The Smell of CPUs Burning In The Morning

Recently, I was working on load-balancing multiple replication Publications across two Distributors.  I had dropped the Publications and Subscriptions on one Publisher that was being migrated, and then registered the Publisher with a newer Distributor that had less load.

Quick refresher of replication terms:
  Publisher - Source DB server that contains the database you want to replicate
  Publication - Record of articles (tables, views, etc...) to be replicated
  Subscription - Specifies destination server (Subscriber) and database for the Publication
  Distributor - Intermediary DB server that collects the data that needs to be replicated and pushes it to the Subscriber
  Subscriber - Destination DB server, contains the database to which the replicated data is pushed

I was able to recreate the Publications and Subscriptions without issue and they appeared properly in SQL Server Management Studio.  However, none of the Publications appeared in Replication Monitor giving the impression that nothing was happening.  The Snapshot Agents did appear in Replication Monitor and I was able to successfully generate snapshots, but other than that, I was flying blind.

We ended up rolling back to the old, overloaded distributor until we could determine the issue.  Initially, I assumed it was something with the Publisher I was migrating as other Publications on other Publishers were working properly.  However, it worked perfectly with the old Distributor before the migration and after rolling it back, so the root cause was initially a mystery.

When I discussed the issue with another DBA, I found out that this has been an occasional problem with any new Publishers/Publications using the newer Distributor.  We then changed our focus to the Distributor and found that the newer Distributor server was experiencing CPU contention.  

We knew that there was some CPU contention, but we thought it was minor as average CPU utilization was only 50%.  After finding an excerpt from the excellent Red-Gate book Performance Tuning with SQL Server Dynamic Management Views, we were able to find that the signal waits time percentage was pretty high (~25%), clearly indicating that our CPU contention was a greater issue than we originally thought.  Ideally, this percentage should be less than 10-15%.  If you're pushing over 20% like we were, you've got CPU contention issues.
  SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) 
  AS NUMERIC(20,2)) AS [% Signal (CPU) Wait Time]
  FROM sys.dm_os_wait_stats;

These status are cumulative since instance startup, but you can run this script after you make your CPU changes to reset the stats and observe the effects (note, it will take some time for the stats to stabilize):
  DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

We were able to add additional CPUs to the newer distributor and our signal wait time percentage dropped to about 8% and all issues with Replication Monitor are now a thing of the past.

Many thanks to Glenn Berry, Louis Davidson, and Tim Ford for your contributions to this fantastic book and to Red-Gate for publishing it.

SQL Server 2008 R2 - Gandalf Edition - How To Address A Locked sa Account With No Other Means To Connect To SQL

I had a conversation with a client the other day that went something like this:

Client> Hey Matt, we can't currently use {name of software package} because it says that the sa account on {insert DB server name here} is locked out, can you help?
Me> Sure, let me take a look
I then attempt to connect with Windows Authentication with every domain and local user I can think of.
Me> How does the software connect to SQL Server (thinking that the login for the software may have enough rights)
Client> It uses the sa account
Me> Ummm, why?
Client> Oh that's just the way the vendor set it up back in 2007
Me> Do you have another SQL or Windows account that has SQL SysAdmin rights?
Client> {cricket, cricket}
I then check the documentation for the server (I surprisingly actually found something, albeit not much at all) and the only account documented was the sa account.

The client follows the best practice and either remove (SQL 2005) or do not add (SQL 2008 and up) the local Administrators group, so I was left with no means to connect to the server.  

This is when I realized that SQL Server was going all Gandalf on me:

I then flashed back to the last time I had to address this situation on a server running SQL Server 2000.  With SQL 2000 a locked sa account meant you needed to "rebuild" your system databases.  Which is to say that you're replacing them with fresh new copies completely void of any configuration information (logins, connected databases, scheduled jobs, etc...).

I was not looking forward to that prospect.

However, after a bit of Googling, I found that there was a feature introduced in SQL 2005 to allow access without "rebuilding" your system databases (this server was running SQL Server 2008 R2, and this process worked perfectly).  If you start SQL Server in Single User mode (also known as Maintenance Mode, hence the parameter of -m), any member of the local Administrators group can connect to SQL Server with full SysAdmin rights.

I fired up SQL Server Configuration Manager and stopped the SQL Server (MSSQLSERVER) service.  I then brought up the properties of the service and added "; -m" to the end of the Startup parameters on the Advanced tab (in my testing adding this Startup parameter through the Windows Services console did not work).  

Once I started up the SQL Server service, I was able to connect with a local administrator account and unlock the sa account.  I also created a dedicated SQL login for the software to use.

Finally, I added a couple other domain administrator accounts (that normally have full access to all of the client's database servers) and granted them SysAdmin rights.

Phew, bullet dodged.

Many thanks to Raul Garcia for your blog post describing this process on SQL 2005 (coincidentally written around the same the vendor installed the software package that used the sa account): http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

Database Migrations Made Easy (Or At Least Easier)

I do quite a few database migrations where I work.  How many, you ask?  Just this  year I've performed around 18 customer database migrations (and I set up almost half that number of new SQL Servers to support the migrations).  Several of these migrations included high profile customers with compressed time-frames.  

Along the way, I've learned several tricks that I hope will help.

We have a couple core databases for our application that are critical to the total operation of our software.  The recovery model for these databases are set to Full and we do transaction log backups every 15 minutes.  We just restore these databases to the new server using NORECOVERY so that we can continue to apply the transaction log backups.

The rest (and bulk) of our customer databases are generated monthly through bulk loading processes.  Once the monthly cycle has closed out, the databases are cleaned and set to read-only for reporting purposes.  Obviously the databases that have aged out and are currently read-only can just be backed up and restored to the new server.

To reduce the outage window during the migration, we schedule a suspension of the bulk loading process so that all the open and future databases can be moved without taking down the entire system.  Obviously your mileage may very here depending on the database model you are working with.

The Full Recovery Model and Transaction log backups are your friends in a tight time-frame as you can get the vast bulk of the data restored on the destination server before the outage window begins.

SQL logins can be easily migrated to the new server using sp_HelpRevLogin.  You don't even need to know the password, it will extract the password hash so that the password on the new server will match the old server.  The script for this SPROC can be found here: http://support.microsoft.com/kb/918992/en-us

Once the database is online, you can use sp_abFixUserLogins to sync database users to the logins on the new server (thus preserving the exact same rights).  The code for this SPROC can be found here: http://www.sqlservercentral.com/Forums/Topic232234-257-1.aspx#bm238983

Overall Tips:

1. Prepare a Project Plan
• Well before the migration, write out a project plan so that you know what you need to do when and you don't miss any critical steps along the way
2. Script EVERYTHING!  
• Clicking through the GUI is OK, but it takes time and it's easy to miss that one little option that will cause you huge headaches down the road when you miss it.  If you're not familiar with how to script the backup or restore, don't worry!  Just go through the GUI and set the options once, but don't click OK, instead click the Generate Script button at the top of the GUI.  As the name implies, this will open a new query window with the script to perform the actions you just specified in the GUI.  Find/Replace is much faster than going through the GUI many times and you can be sure you didn't miss the options if you made sure to specify them the first time through.
3. Test your project plan and your scripts
• Do a dry run of your project plan and make sure that it is solid.  It's easier (and far less stressful) to find and correct mistakes when you don't have an impatient customer breathing down your neck (or worse yet, your boss' neck).
• Be sure to test all of your scripts as you work through the dry run.  Just like your project plan, it's much easier and less stressful to test and correct everything ahead of time.
4. Save your scripts and your project plan in a safe and well known place
• You need to know exactly where your scripts and project plan are (yes, I've walked into a migration and totally blanked on where I put those pesky scripts that I worked so hard on).
• Save your scripts and project plan to a location that is secure (i.e. redundant storage) and backed up.  You should NOT save them only to your desktop.  If your hard drive crashes, all your efforts are in vain (yes, I've also walked into a migration and had my PC crap out on me, but my scripts were in a safe place, so I could just use another PC and move on).
• Having a common location for your scripts and project plan is also handy if you have a peer/code review and others need to access them.
5.  Keep your scripts and project plan after the migration is complete
• You may be questioned after the migration to ask if you completed a specific task.  By keeping your plan and scripts, you can easily reference your documentation to confirm exactly what actions you did (or did not) perform.
• You never know when you may need to do another migration and Find/Replace makes quick work of adapting the existing scripts and plan to a new project.  I never delete any of my scripts (unless they are total rubbish and I've already rewritten them).

Happy database migrating!

SQL Replication - Monitoring Tips When Delivering Snapshots for Transactional Publications

Monitoring the delivery of a snapshot for Transactional Replication can be a bit tedious, especially if it is a large publication or you are delivering large tables.  However, the more you know about what to expect, the less likely you are to panic if things don't quite seem to be going the way they should.

The key steps in snapshot delivery are:

• Run PRE scripts (drop the existing published table/article on the Subscriber)
• Run SCH scripts (recreate the published table/article on the Subscriber)
• Push BCP files (BULK INSERT the raw data from the published table/article)
• Create Indexes
• Push queued transactions (technically this step is post snapshot delivery)

Monitoring the first three steps is pretty simple as Replication Monitor gives you plenty of feedback during this process.  Unfortunately, index creation does not report any progress (and you may even get the dreaded message "The replication agent has not logged a progress message in 10 minutes" if it takes a while to create the indexes).

Note: If you get that message a lot and you know it's rogue (i.e. the Distribution Agent is still running and there is no blocking of the ALTER TABLE or CREATE INDEX processes on the Subscriber) you can change the heartbeat interval on the Distributor by following the instructions here (Thanks Amit!): http://troubleshootingsql.com/2010/05/28/replication-agent-has-not-logged-a-message-in-10-minutes/  We set ours to 20 minutes by running this script on the distributor:

EXEC sp_changedistributor_property @property = N'heartbeat_interval', @value = 20

By default, SQL Server will only create the clustered indexes for tables pushed to the Subscriber.  When the clustered index creation process is complete, the Distribution Agent will report "Delivering replicated transactions".  If you are using the default settings (i.e. only recreating the clustered indexes at the Subscriber), this message means exactly what is says, queued transactions are now being pushed to the Subscriber.  

This message always appears after the clustered indexes are created.  Thus, if you also replicate the non-clustered indexes (like we do in our environment), this message means that the creation of the non-clustered indexes has started, not that it is actually delivering the queued transactions.  In some cases, the Distribution Agent may even report that it "Delivered snapshot from the unc\{path} sub-folder in ##### milliseconds" before the non-clustered index creation is complete.  The only way to know for sure is to either query the Subscriber or wait for the Distribution Agent to report ### transaction(s) with ### command(s) were delivered in Replication Monitor.

There are just a few nuances to monitoring replication and I hope that this helps to shed some light on the process.

Maintaining SQL Replication Publications -or- How To Add An Article To A Publication AND Generate A Snapshot

For quite some time, I was stumped as to how to properly script adding an article to an existing publication.  It's a somewhat straightforward process using the sp_addarticle command, but for some reason when I would start the Snapshot Agent, a snapshot would never be generated for the table I added to the publication (and thus it would not be synced to the Subscriber).  

In my case the sp_addarticle command looks like this (the PublicationName and TableName were changed to protect the innocent):
EXEC sp_addarticle @publication = N'PublicationName', @article = N'TableName', @source_object = N'TableName', @type = N'logbased', @pre_creation_cmd = N'drop', @destination_table = N'TableName';

Simply running sp_addarticle with the correct options was not enough to allow a snapshot to be generated for that table.  When I started the Snapshot Agent, it would complete successfully with the message [0%] A snapshot was not generated because no subscriptions needed initialization.  Perplexed, I decided to perform the same actions through the GUI (I knew it worked properly there) and then run SQL Server Profiler and filter by my local HostName.  

Note: If you have enabled the @immediate_sync option (it is off by default), you will not be able to drop individual tables/articles from the publication.  However, you can turn it back off without recreating the publication by issuing the following command:
EXEC sp_changepublication @publication = N'PublicationName', @property = N'immediate_sync', @value = N'FALSE'

In SQL Server Profiler, I saw sp_addarticle executed and I also observed several help SPROCs that the GUI uses for validation, but then the diamond in the rough appeared.  There was an additional SPROC that was executed against the published database: sp_refreshsubscriptions.   Executing sp_refreshsubscriptions will refresh the subscription metadata for any new articles added to the publication, which the Snapshot Agent subsequently uses to determine what articles need a new snapshot.  The syntax is very simple (unlike sp_addarticle):
EXEC sp_refreshsubscriptions @publication = N'PublicationName';

Just replace the PublicationName with the name of the publication to which you have just added article(s) (you can add multiple articles and then execute sp_refreshsubscriptions only once). Once you execute it in the context of the published database, the next time the Snapshot Agent runs, it will generate a snapshot for the article(s).

Note: You do not need to run the sp_refreshsubscriptions SPROC when creating a new Subscription/Publication, as creating the Subscription after the Publication has been created populates all the article Subscription metadata from the Publication.

While I'm on the topic, I also want to quickly touch on how to drop a table from a Publication.  There are two SPROCs to run to remove a table from the publication, sp_dropsubscription and sp_droparticle:
EXEC sp_dropsubscription @publication = N'PublicationName', @article = N'TableName', @subscriber = N'all', @destination_db = N'all';

EXEC sp_droparticle @publication = N'PublicationName', @article = N'TableName', @force_invalidate_snapshot = 1;

Just update the PublicationName and the TableName as appropriate.  

The final tip for today is that you can script starting the Snapshot Agent.  It is another simple SPROC that you run against the published database:
EXEC sp_startpublication_snapshot N'PublicationName';

We use sp_startpublication_snapshot extensively when setting up or modifying a Publication (it sure beats finding the appropriate SQL Agent job on the Distributor, and we don't have to bring up Replication Monitor).