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

Get statistics of Stored Procedures execution using SQL Trace


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


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

Automate Manual Update of Statistics


SQL Server by default has the Boolean database properties “Auto Update Statistics” and “Auto Create Statistics” turned on and as the name implies SQL Server self maintains database statistics and does a pretty good job as well. But, there are cases where the “Auto Update Statistics” database option is just not good enough and the DBA has to take some action to improve on the current state and before I proceed here is a brief explanation I have copied from an older post of mine The source to the problem is that statistics are updated when 20% (+ 500...

Get statistics of stored procedures executions from sys.dm_exec_procedure_stats


sys.dm_exec_procedure_stats can provide some interesting and useful statistics data about stored procedures executions. I feel this DMV is underused and I have to admit I also seem to forget of it’s existence but from time to time when I visit a customer for the first time and I need to generate a baseline report of executions I use this DMV (where as my default action would be to use SQL trace and set a stop time of 60 minutes). An alternative would be to capture SQL Trace or use XE. These 2 methods would provide accurate statistics data but...
no comments

Synchronize AlwaysOn Replicas Configuration and Server Level Objects (Logins, Permissions, Jobs….)


AlwaysOnSynchronizer is a free .NET console application that is designed to be run by an SQL Server Agent job and address the issue of synchronizing “server level objects” from the primary replica to the secondary replica(s). Here is a direct download link to AlwaysOnSynchronizer from the sqlserverutilities site. The utility comes in to address the need where environments that have implemented AlwaysOn Availability Groups need to synchronize “server level objects” that are beyond the scope of the Availability Databases that actually synchronize the databases only but do not synchronize objects outside the scope of the database. This leaves objects...
no comments