AlwaysOnSyncronizer version 1.6.0 is now available for download


This updated release includes the following major changes: 1. Add support for Operators 2. Add support for SQL Server 2016 3. Improve logging for the execution of script files on secondary replicas 4. Fix minor issues related to the initial deployment For additional details about AlwaysOnSyncronizer see this post. To download AlwaysOnSyncronizer click here.   Following the installation before running the program for the first time it is recommended to run the sql script file named drop_procedures.sql located under the AlwaysOnSyncronizer_1.6_Setup\Uninstall folder. Doing so you assure that you are using the latest version of the stored procedures....

Shorten Replication failover time with AlwaysOn Availability Groups


Recently, on a production system where we had implemented AlwaysOn Availability Groups not too long a go we experienced a situation where following a planned failover we noticed a latency in the delivery of commands to our Transactional Replication subscribers. Looking into this issue I found that the reason for the latency experienced is that the log reader was not harvesting transactions from the published database transaction log. When querying MSlogreader_history I noticed that the log reader was just scanning the VLFs looking for the LSN where it should start delivering transactions.  So executing sp_replcounters simply returned 0 for...

Query msdb backupset to get backup information


Check out this useful query over msdb..backupset to get all backup information needed and also useful when need to restore transaction logs Note that the query returns the most recent backup per each database but if you comment out the line that comes just after this remark /* Get the most recent backup of type FULL */  you get all rows --SELECT DB_NAME(database_id) AS , name, physical_name, size /128 AS size_mb, state_desc FROM sys.master_files WHERE database_id > 4-- SELECT LEFT( physical_name, 3) drive ,SUM(size)/128 size FROM sys.master_files GROUP BY LEFT( physical_name, 3)USE msdb; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;...
no comments

Implement SQL Server Agent Jobs with AlwaysOn Availability Groups


In this post I talk about SQL Server Agent jobs when implementing AlwaysOn Availability Groups. AlwaysOn Availability Groups synchronizes the databases that are part of an Availability Group but any other object that is not part of these databases do not get synchronized (i.e. logins, linked servers, operators etc.) and this includes SQL Server Agent jobs. For a tool that synchronizes all the objects that are beyond the scope of AlwaysOn Availability Groups see this post.   Our requirement is to have all jobs that exist on the Primary replica to exist also on the Secondary replica(s) and to...

An update to Partition Management is available


Partition Management is a generic piece of code designed to automate the maintenance of a partition sliding window. For more details about Partition Management see this post. For a direct download from the sqlserverutilities web site   This update includes the following content: 1. Add SET LOCK_TIMEOUT to prevent a case where the procedure waits to get a schema stability lock (LCK_M_SCH_S) required for the MERGE and SPLIT commands and while it is waiting in line to acquire that lock on the required resource in order to proceed it blocks all other processes waiting also to acquire a lock...
no comments

Using a start up stored procedure in SQL Server


SQL Server offers the option to define a stored procedure that will get executed at service start up. I have found this very useful and been making usage of this option over the years. An alternative can be using SQL Server Agent job which is not as good in my opinion. The procedure you set gets executed in the master database when the database completes it’s recovery phase at service startup and has the limitation that it cannot accept input parameters.   You will need to use sp_configure and sp_procoption first to allow startup execution of procdures USE...
no comments

An new update to sp_helpindex2 is now released – 20161101


This update adds support for columnstore and xml indexes and support for schemas. For direct download of sp_helpindex2 click here For more info on sp_helpindex2 go to the original post here   Usage examples USE AdventureWorks2014;EXEC sp_helpindex2 N'ProductDescription'; USE AdventureWorks2014;EXEC sp_helpindex2 @Table = N'ProductDescription' ,@Schema = N'Production' ,@IndexExtendedInfo = 0 ...
no comments

Create a Performance Baseline Repository


The scripts can be downloaded here. Long a go I blogged about this topic here. The post talks about and walks through the steps needed to create a baseline repository holding key metrics for each execution (Stored Procedure or SQL Batch) of an instance of SQL Server. The process is based on a few stored procedures that create a server side trace, load the trace files to a table and manipulate the data resulting in a summarized (aggregated) data per each execution. The idea behind is that it is always useful to have a baseline as a...
no comments

Index REBUILD with Transactional Replication


In a very busy environment the OLTP server has recently entered a state in which the log reader had a large queue of transactions not yet read from the transaction log. We new it is related to the index maintenance job that runs on the weekend and this has started a discussion, or may I say the old discussion, of “to rebuild or not to rebuild” (see this post) . In other words the current index maintenance job causes a log reader latency that we cannot afford and something needs to be changed, but what? Do we...
no comments

Automate Partitions Management Sliding Window


Working with partitions at different environments has led me to think that a generic unified code to manage partitions can be very useful and practical. Think about it, if you look at a code that manages a partition sliding window implemented by 10 different DBAs you are going to see 10 different variations of the same thing. It’s a little like reinventing the wheel over and over again. A generic piece of code can be easier to maintain since you get to be familiar with it and cuts of the development time to 0. No more development time needed...
no comments