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

Logging to Event Viewer


SQL Server exposes 2 methods of Logging to the Windows Application log using T-SQL out of the box: xp_logevent and RAISEERROR. Both are simple straight forward and convenience to use but lack the option to control the Event Id passed to the log entry we add. Both xp_logevent and raiseerror add entries with EevntIDs 17061 and 17063 for Information and Error respectively and you have no control over the EevntId value. In order to unify all messages from the various sources into a single centralized location which in this case is the Windows Event Viewer being monitored by...
no comments

Script entire server permissions


This script is very useful when you have to copy permissions from one instance of SQL Server to another. For example when you perform a side by side upgrade or when you replace the server HW and need to duplicate all stuff. Run the script on the source server (the server you are about to replace) and copy save the output so that you can run it on the target instance (the new instance that will replace the current instance). Before running the Permissions script you need to handle logins and the best option for that is to use...

Script attach and detach commands for all databases


This script (coded by a colleague DBA Boaz Goldstein) generates the attach and detach commands for all databases on the instance in an xml format to overcome the SSMS limitation of results to Text (limited to 8mb) or results to Grid (limited to 64mb). When returning a result in the format of xml SSMS limits you to 2gb (not really a limitation) which is the max LOB size. You come across this limitation when working with large databases spanning multiple file groups and data files and then your script becomes big and gets cut in the middle Copy...
no comments