SQL Server – breaking bad habits, improving knowledge and awareness

The hardest part of being a DBA is to keep up with all the things that appear in connection to our job.

  • SQL Server new features
  • SQL Server best practices
  • SQL Server new DMVs, DMFs
  • SQL Server procedures specific to each company
  • …..

Although this information is available and reachable by checking Microsoft web sites, SQL Server specific blogs, your corporate e-mail or corporate intranet, most of the time due to the daily workload or other distractions we miss it. The answer to this will be a reminder or even better a notification. I found the notification a better option because I always like the way in which Microsoft Outlook notifies you when a new e-mail arrives.  While trying to duplicate that behavior I found out that is very hard without a good understanding of .NET programming. In order to still have what I wanted I had to accept that

  • the notification will still consume resources, it will still appear although you cannot see it – the idea behind was to check if you have more than one monitor and if yes to check if any application is running in full screen mode or covers a specific portion of the last monitor. If there will be no application that have windows that covers the lower left corner of the screen then the notification will appear otherwise the notification will not appear since you cannot see it.

Because these days especially in the office we have at least 2 displays the idea of having a notification on the third one that runs every 10 seconds and stays there for another 10 seconds reminding me about a new DMV or reminding me about something else is beneficial. Most of the time will be ignored but from time to time while contemplating the screen or waiting for a task to complete we can peek to that notification and in time we will know the new feature or what is new or other things that you want your notification to display.

Below is an example of how it looks like.

In order to close the notifications we need to double click it.

The script can be downloaded from here

Compare 2 SQL Server instances

During sql server installations or migrations of old instances to new instances we need to gather information quick in order to be sure that we are installing or configuring the instances in the same way. Sometimes when it comes to performance issues we need to compare instances settings to spot any differences that might cause it. One of the methods to spot differences while comparing 2 instances is to put them side by side and to check the property that you want using one of the built in tools, SSMS, tsql code, SQL Server Configuration Manager, Services management console, SQL Server Facets and others that might expose the setting that you want. Unfortunately this method is sometimes

  • time consuming
  • ineffective when the properties that you want to check are accessible using different tools
  • not appropriate when you want to share your results with someone else

Fortunately we can overcome all the above using Powershell and SMO to build a basic GUI ( inspired from this script # A GUI WMI explorer and WMI Method Help generator available on www.ThePowerShellGuy.com) that displays some of the information that we need. The script attached to this post is displaying properties for

  • server collections
  • jobs collections
  • logins collections
  •  users collections
  • databases collections

For other collections you can check the SMO Object Model Diagram and add them to the script together with the modifications that might be required.

Below is an example of the script output

Some of the properties are at their turn collections and for those when selected the output will show sometime only the powershell object type and not the collection.

As with almost all of the scripts provided here, this script is only something basic that can help us to provide specific type of answers faster.

The script can be downloaded from here

Tracking activity to word document for audit purpose or to prove the compliance

More than once we need to provide a prove of some of the steps, if not all, that we took while implementing a change or when solving a ticket. Sometimes this can be accomplished easily using the screen capture software or just the Microsoft built in print screen while in other cases depending on the length of the activity that we need to track it becomes heavily intrusive.

Because of this I started to search how I can do it easier by the mean of automation and powershell. The problems/questions to solved were:

  • how often I should take a capture
  • what the capture size should be
  • which capture I should write to the document

The answers to those questions were

  • every second I am checking if a mouse movement occurred and if yes a capture will be taken at the position where the mouse was
  • I chose a size that will show me enough area surrounding the mouse. This area can be changed to fit your requirements but it will affect the document size.
  • all captures taken when the mouse moves, movement of the mouse means we did something. I am not using the keyboard too much while performing my day to day tasks.

We need of course to have Word and before saving the document we need to stop the script execution.

Below is how the document look like

The script can be downloaded from here

SQL Server SPID – tracking how spid affects sql server

SQL Server Management Studio offers a lot of tools to track what a spid is doing and how it affects the sql server and other spids. Some of the tools can offer information at a very granular level while others to an wider level. Because sometimes we just want something between we need to use what Microsoft offers and use it appropriately. To have an overview of a specifc spid we can use:

  • sp_who2
  • sp_lock
  • built in dmv
  • Activity Monitor
  • extended events

Or we can use custom script that will aggregate information using Microsoft dmvs and SPs and present them. The biggest advantage of using custom scripts is that we can choose

  • to display results to text
  • to display columns as rows
  • to create a include client statistics look like

Microsoft is already doing this when we choose Query – Include Client Statistics which is why I decided to duplicate that and show status,blocking_session_id,wait_type,wait_duration_ms,cpu_time,memory_usage,total_scheduled_time,reads,writes,logical_reads,arithabort,transaction_isolation_level,lock_timeout,deadlock_priority

The output looks like below and I want to mention before anything else that the script used is providing a broad overview and was created on spot only to test how the output look like. Because of this

  • The output should be read and understood from this perspective.
  • The script and the way was written should be seen from this perspective also.

The values that I choose to show are not the most important and they were choose only to show that by choosing to display columns like status we decrease the amount of info that can fit but we can code some of the columns using numeric values, 1 for running, 2 for sleeping and so on. The biggest advantage of this type of display is that we can see how specific spid behave up and down by having only one look throughout of execution time.

Depending on the things that you want to track you can create your own script using the script below as an example. The only thing that you need to check is Results to Text in SSMS.

You can download this script from here

SQL Server full backups – size, space required

SQL Server backups started to fail now and then due to space issues. There is something that I can do to make it work?

The answer to that question is obvious and we just need to allocate more space to the location where we save the backups. But how much space we need to allocate? The answer is to check the space used by the last full backup for all the databases as a starting point and multiply it with the retention period.


In order to have that value we can use the below script to see the sum of all the latest full backups taken on our instance. Because of the reasons below the size that we see might not be the total size that we need but it is still a good estimation of what we might need for a successfull backup.

  • databases were created and for them we haven’t taken yet a backup
  • databases were backed up in the past but the file is not available on the disk to check the size

The script give us a real number when the last full backup of all the databases was successfully and the files are still on disk.

In our environment that full backup file receives _full while in other environments the naming convention might be different which is why the script must be modified a little bit. Also if we want to have an estimation about latest differential we need to modify the script. In red below you can see where you need to modify for the desired results.

SELECT bmf.physical_device_name as path FROM
(select media_set_id = MAX(bs.media_set_id) FROM sys.databases d LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = d.name AND bs.is_copy_only = 0 AND bs.type =’D‘ GROUP BY d.Name, bs.type) r
LEFT OUTER JOIN msdb.dbo.backupmediafamily bmf ON r.media_set_id = bmf.media_set_id where bmf.physical_device_name is not null and bmf.physical_device_name like ‘%_full%’

The script output looks like below

and you can download it from here

Check and Register SPN for our SQL Server Instances


To check if our SQL Server instance registered its SPN and to register if not.

My way of achieving it

Using powershell.

Sometimes when troubleshooting connection issues we end up realizing that it was caused due to Kerberos and the solution is to check for SPN and to create it in case is not there. Because the creation requires the knowing of the port under which SQL Server instance is listening and also the service account under which the service is running I found useful the usage of a script that will generate and check the existence and the creation of this SPN. The script can be executed in powershell console and I prefer the one that you can open while you are using SSMS and from contextual menu you choose Start Powershell.

The output of the script looks like below

The script can be downloaded from here

SQL Server – map drives to disk numbers


To obtain the disk number of all the drives together with drive size and free space.

My way of achieving it

Using powershell and CIM_StorageExtent.

For a long period now I tried different methods and approaches in order to map the drives in Windows to their respective disk numbers. Today starting from what Granger Godbold said here, https://rvdnieuwendijk.com/2012/05/29/powershell-function-to-get-disk-scsi-lun-number/ , I finally managed to have that information. The class that I used is CIM_StorageExtent. I started with this Get-WmiObject CIM_StorageExtent  and after I filtered the output to not show me the Memory like objects I noticed that maybe the objects there appears in a specific order and I checked my assumption

Disk #1, Partition #0|214748364800|
Disk #6, Partition #0|214748364800|
Disk #7, Partition #0|214748364800|
Disk #8, Partition #0|214748364800|
Disk #9, Partition #0|214748364800|
Disk #10, Partition #0|214748364800|
Disk #11, Partition #0|268435456000|
Disk #12, Partition #0|53684994048|
Disk #13, Partition #0|53684994048|
Disk #14, Partition #0|53684994048|
Disk #15, Partition #0|53684994048|
Disk #2, Partition #0|429498826752|
Disk #16, Partition #0|53684994048|
Disk #17, Partition #0|53684994048|
Disk #18, Partition #0|53684994048|
Disk #19, Partition #0|53684994048|
Disk #20, Partition #0|161059176448|
Disk #21, Partition #0|53684994048|
Disk #22, Partition #0|161059176448|
Disk #23, Partition #0|53684994048|
Disk #24, Partition #0|53684994048|
Disk #25, Partition #0|53684994048|
Disk #3, Partition #0|644249288704|
Disk #26, Partition #0|53684994048|
Disk #27, Partition #0|53684994048|
Disk #28, Partition #0|107372085248|
Disk #29, Partition #0|53684994048|
Disk #30, Partition #0|161059176448|
Disk #31, Partition #0|53684994048|
Disk #32, Partition #0|53684994048|
Disk #33, Partition #0|161059176448|
Disk #34, Partition #0|53684994048|
Disk #35, Partition #0|966376030208|
Disk #4, Partition #0|214748364800|
Disk #36, Partition #0|966376030208|
Disk #37, Partition #0|1234814631936|
Disk #38, Partition #0|966376030208|
Disk #39, Partition #0| 1070596096|
Disk #5, Partition #0|214748364800|
Disk #0, Partition #0|209715200000|
Disk #0, Partition #1| 90248839168|


It looks like all the drives, the ones containing “\” appears in the following order:

  • disk order , partition 0
    • disk 0, partition 0
    • disk 1, partition 0
    • disk 39, partition 0
  • disk order, partition order
    • disk 0, partition 1
    • disk 0, partition 2
    • disk 0, partition n
    • disk 1, partition 1
    • disk 1, partition n
    • …..

This is the order that I noticed when I had ONLY SIMPLE VOLUMES. Maybe the order will be differently in case we have spanned volumes.

But because usually the servers in my environment are not using spanned volumes I decided to make a script to take advantage of what I just saw. The output of the script looks like below

The script can be downloaded from here

Update datatype of primary key in SQL Server table


To generate the tsql commands that we can use to alter the primary key datatype.

My way of achieving it

Using powershell to check some of the relations between the tables. As with all other things that I am posting here, this, is far from being a final script. It is just something that you can use in order to check the level of work required to change the data type of a primary key. Sometimes in our job we are asked to perform this type of activity and although it seems like a trivial task there are hidden things that make it very difficult. I tried to show as many things that I could for which the change of datatype might fail but I am sure that depending on the database structure the script will still fail even after we will perform the steps described in the output of the script.  The output of the script must be followed and changed accordingly to your requirements. In the script I changed the data type of InqNo column from int to numeric(18,0).

The script requires the instance name, database name and table name

$instancename = “UCPHMA”
$databasename = “Inquiry2012”
$tablename = “Inquiries”

The output of the script can be seen below and as you can see asks for dropping and recreating some indexes among other things.

IMP – Those actions needs to be very good planned when it comes to the production servers and big tables due to all the problems that dropping an index creates and all the problems that creating an index creates. But in case the tables are small then the impact will not be big. As with every script, first needs to be tested in a controlled environment and used only after the operation was successful there.

Very important is to check the script output before executing it.

The script can be downloaded from here

Migrate, re-create login(s) in SQL Server using Powershell and script method


To generate the sql server login with a single click in case we want to migrate it or just want to see what permissions it has on our instance.

My way of achieving it

Using powershell and the work of other people over the internet :). My desire was to have the code generated while I am right clicking on the Logins folder or a Login in SSMS and start powershell and paste a script. Although over the internet I found a lot of examples, the one that caught my eyes was this post (http://jongurgul.com/blog/sql-server-instance-security-scripting-permissions/) . It seems that the author wanted also the same thing but he changed his approach.  — This script was written initially to allow permissions to be scripted out with a single click,  —

Because I still wanted to create and to make a script for that approach I inspired myself and came with the script that will just give me that information. Far from being a final script, this version has the advantage that you can generate the info for all of the logins or only for one login and the information is in this order

  • create login
  • server roles
  • server permissions
  • object permissions at server level
  • create user for that login for the mapped databases
  • database roles
  • database permissions for that login
  • object permissions for that login

An output of running the script looks like

The script can be downloaded from here.

Rename a computer name that serves as a SQL Server – how much work it requires


To know up front the work that needs to be performed when we need to rename a computer that has SQL Server installed on it.

My way of achieving it

Using some of the code that other people already wrote and modifying it together with the Microsoft recommendations when it comes to renaming the computer name. I tried to have this overview in order to estimate how long or how hard it will be to modify the computer name depending on how many instances we have there and what features are already enabled. The script is far from being complete but as I built on top of other scripts we can use it as base for other checks and tests and so on.

The output of the script will look similar to this


The script can be downloaded from here