Automate Partitions Management Sliding Window

15/08/2016

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

12/08/2016

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

06/08/2016

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.  
tags:
no comments

Build the RESTORE command from a backup file

12/04/2016

  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

03/04/2016

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

03/01/2016

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

25/08/2015

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...
2 comments

Script attach and detach commands for all databases

23/08/2015

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

Get statistics of Stored Procedures execution using SQL Trace

19/08/2015

Today I was assigned the task of generating a report showing statistics of Stored Procedures execution. My customer needed that data because they have heavy processes preparing data to be ready for quick retrieval by user reports. There are many reports and we wanted to know if there are reports that are not being used and so we can stop preparing data for these reports. We can get statistics information of stored procedures executions from the sys.dm_exec_procedure_stats DMV which has valuable information but has some limitations, for example a stored procedure defined using WITH RECOMPILE will not have any...
no comments

Using SQL IO to test InfiniBox performance – Updated

12/08/2015

Following the IO test results I published in this post the guys from Infinidat have contacted me and made me aware that the test result do not really reflect the performance capability of the machine and this is so because the numbers I got indicate that the limitation was at the host side and not at the SAN. They investigated the SAN logs from the time of my test and showed that we have reached the limitation of the HBA card at the host. The host we tested had a dual port 4gb HBA card which means it can...
no comments