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

AlwaysOnSyncronizer version 1.5.0 is now available for download


AlwaysOnSynchronizer is a free .NET console application that synchronizes “server level objects” from an AlwaysOn Availability Group primary replica to the secondary replica(s). For additional details about AlwaysOnSyncronizer see this post. To download AlwaysOnSyncronizer click here.   The original release had a problem with the initial deployment which is now fixed with this release as well as some minor bug fixes. In addition this release handles jobs that were deleted at the primary replica. In the original release new jobs and jobs that got modified were synchronized to the secondary replica(s) but a job that was deleted at the...
no comments

Rule “Not clustered or the cluster service is up and online” failed


When installing SQL Server 2014 sp2 I faced the above error. I tried to install the service pack on the passive node of a clustered sql instance while pausing the node first as a best practice to prevent cluster issues when the node goes down for a restart. The windows cluster (WSFC) is on top of Windows Servers 2012R2. To overcome the issue I had to NOT pause the cluster node, this did the change and allowed the setup program to pass the rule validation that it failed.  
no comments

Build the RESTORE command from a backup file


  One way to build the RESTORE command is from sys.database_files which is a good and handy option. This of course is only relevant when the path to be restored on the target server is different than the source server. In cases where the source server and target server database files paths are the same then there is no need to specify the files location using the WITH MOVE option of the RESTORE command. Build the RESTORE command based on sys.database_files USE MyDB;-- Use original pathSELECT ',MOVE ''' + name + ''' TO ''' + physical_name + '''' FROM...
no comments

Monitor Future Partitions


In organizations where there partition tables are in use it is a good practice to monitor the available partitions for future use. For example you may be excepting to have xx partitions a head but for some reason these partitions do not exist resulting in the current partition growing large and possibly leading to a performance problem. In some places the DBA team creates the future partitions in the beginning of the year for the entire year a head and when reaching the year’s end arrives some things can be forgotten etc.. So monitoring this part of the system...
no comments