Balance tables across SQL Server database files in the primary filegroup or other filegroups

Goal

To have an overview of the tables that can be moved across the database files in a database and see if they can be moved or not

My way of achieving it

Using tsql. The script is checking the edition of SQL Server and the biggest tables from our database and will check if these tables contains LOB data and how much from the table is LOB and how much is not. Tests showed that even if you have a column that is of LOB type, if the value can fit in the page then you can still move that page to other locations in the database files. I tried to find a way to map the table to database files but I couldn’t find a solution that will work in a fashionable time. Searching on internet revealed that I can use:

  • DBCC IND(0,’tablename’,1)
  • SELECT sys.fn_PhysLocFormatter(%%physloc%%) as rowlocation FROM tablename

The problem with first one is that for huge tables is taking a lot of time which makes it unusable while the second one although is faster for a 100GB table it is still takes minutes to run. I would have wanted to have this information in order to estimate how much space a table occupies on each of the database files. Also the script is ignoring the tables that are partitioned already.

The script is nothing more than a procedure that can help us to provide a quick answer to the question – Can I move some tables to other files or not?

Like with everything else from the internet please try it first and check if the information provided are accurate or not. Below is an example of the script output.

The script can be downloaded from here

Initialize, labeling, formatting and mounting disks in windows 2012 R2 for a SQL Server installation

Goal

To check for the available disks and the server /node and to choose the one that will be used for the SQL Server instance.

My way of achieving it

Using powershell. The script requires 2 arguments, one is the instance name and the other is the installation type. I am using Get-Disk to check all available disks which have a partition style of RAW or which don’t have any partitions on them. After the discovery the script is prompting the user to choose the disks and inform him how the disks will be used.

Select the disks for your instance:

– first disk will be used for sql_data_drive

– second disk for the Data01 MP

– third one for Log01 MP

– the fourth one for the Data02 MP

– the fifth one for the Data03 MP and so on

– Example: 1,2,3,4

: 2,3,4,5

Disk 2 with the size of 0.5 GB will be used for SQL Data Drive

Disk 3 with the size of 0.9765625 GB will be used for Data01 MP

Disk 4 with the size of 4.8828125 GB will be used for Log01 MP

Disk 5 with the size of 6.8359375 GB will be used for Data02 MP

After that the disks will be formatted and configured as mount points. Script will check for first available letter and will use the first disk as the base drive for all the other disks that will be mounted on that drive.

The script and a log of an execution are attached here and here

SQL Server troubleshooting – first steps in troubleshooting performance of some TSQL statements (part 4)

Goal

To split the code of a SP or ADHOC query into pieces in order to find out the number and names of temporary tables used, variable tables, dynamic code, calls of the stored procedures, dynamic tsql, usage of the IN, parameters used in the stored procedures, if code is using index hints, cursors, outer joins, set commands.

My way of achieving it

My approach is to split the code, one word on every line and looping to the lines and searching for specific sql keywords. Is not 100 percent accurate but it will give me a 50% or more accurate view of how the code was written and how we can improve it or how we can spot the bottlenecks. Each table has a some comments columns that can be used to add the things that we must be aware when using SP or dynamic code or DDL statements (create) with DML statemetns (insert) in a SP or when using table variables or IN operator and so on.

Below is an example of the output

split_the_code_perf_troubleshooting

First result set is the query displayed as one word per row. The second result set is the actual SP or adhoc query that we troubleshoot because here we will check the code that is point to us by the other result sets where we have locationincode. For example the fifth result set shows us that our stored procedure is using parameters and that parameter is used on line 37,  select @dbname = parsename(@objname, 3). And this can help us to find out if a conversion will take place or not which is helpful in the overall troubleshooting process. Also we can see some of the tables that are used and if by experience or because we are familiar with the server and database e know that the table has millions of rows then we can start troubleshooting from there.

The script can be downloaded from here

Mapping databases to their backup files that are still on disk

Goal

To obtain a list of backups for the databases that are on our instance whcih are still on disk and to count them.

My way of achieving it

Using tsql and xp_cmdshell. From time to time we end up having free space issues even after the backup cleanup job that we have in place executed successfully. This situation might happen because of how our backup cleanup logic works. Most of the backup cleanup logic is using dates to delete files and not numbers of the backups or a combination of these two. Because of this I created the below script in order to know how many backup files are still on disk for each of my databases. If we know that we should keep only 2 full backups and we have 3 on disk then we can delete one. The same applies for differential backups. The script works well for the environment for which I created it but with some small modifications I believe it will suit other environments need.  The script is expecting the backup file name in this form

I:\sqldata\backup\diff\SQLServer01\03\mysite_regional_02_p_20160903_042243_ab_diff.bak

  • the path contains the database name
  • the path has a timestamp in the form above
  • the path contains the type of the backup

By default the script will output

  • the database name and how many backups on disk exists for that database
  • the database names and their path and their backup date for all the databases that have more than 2 backups on disk.
  • the delete commands for a backup file that was taken for a database that is not anymore on the instance
  • some information in case the backup path contains also a folder named ‘restore’

The script provides only information and decision to delete something is in the hands of the person that interprets the results and knows how many full backups we should have, how many differentials and so on.

Below you can see the output of the script fir diff.bak and a local path

mappingdbtobakfiles

 

The script can be downloaded from here

 

SQL Server Administration through SQL Server Management Studio

Goal

To create an interface that contains the up to date information and know how when it comes to manage SQL Server databases using SSMS and to prepare for further certification or just to refresh the knowledge or improve them with something new.

My way of achieving it

By duplicating the SQL Server Management Studio interface using static information and using Tool Tips. Before anything else I want to mention that:

  • I am not a programmer
  • The information is taken from other DBAs that were kind to share it online

When I started to learn SQL Server it was obvious ( and I am speaking only about the database engine) that it will be hard to always remember anything and this became more clear throughout my jobs since every company has its own vision and they are doing things in their own way. On top of these, most of the time, depending on the position and the job description we are loosing the knowledge and the know how that we learned during a SQL Server certification or during a job where we had to do only performance troubleshooting or during a job which required only database maintenance and user creation and so on. Because of this I decided to start making this simple application in C sharp in order to use it as a repository of knowledge and best practices when it comes to SQL Server.

The main purpose of this application is to

  • help someone that just started to learn SQL Server by having some of the information at one click
  • for more experienced DBA it might be just a repository containing the experience he accumulated throughout his jobs. Every ToolTip can have a legend section  where we can put  how we solved a similar issue or what was the root cause.
  • for others might be just a place containing the answers that we have to provide when speaking, solving SQL Server problems.

Below is a video that I made showing some of the things. Ignore how the application looks it is just to show how good would have been if Microsoft would have provided an argument when starting SSMS that will allow some ToolTips and some best practices.

Below you can watch some of the things that I manged to create and do till now.

 

SQL Server clustered instance dependencies – verification – using tsql, xp_cmdshell

Goal

To check and verify if the existent dependencies for sql server resources are in place

My way of achieving it

Using tsql, xp_cmdshell. From time to time due to adding more disks to an instance or performing other activities we end up having a resource that is not tied to the parent resource and when it comes to sql server disks some of the databases residing on those disks are not accessible. In order to check if we have any of the resources not being tied to the right parent resource I have created the script below that will tell us which resources are not tied. The output of the script looks like below

sqldiskdep

The code can be downloaded from here

Display folder size in a graphical way using SQL Server

Goal

To obtain the size of folders and their path in graphical way. To have the output of tree command, dir command and the folder size in only one output.

My way of achieving it

Using tsql, xp_cmdshell in order to call the tree, dir and powershell.exe command s.  The desire to have something like that came when I have to troubleshoot and see who is the biggest consumer in case of free space issues. Of course that multiple approaches and solutions can solve this question but I stopped on using the tree command because I liked the look of the output. The first output shows the information as the tree command does while the other tries to sort them by size in order to easy see the biggest consumer. One problem with this is that for locations where sql server doesn’t have access the size returned is 0. Even so I decided to finish the script and use it like this because of the output. The script has also a deep switch that will let you decide how deep you want to traverse the folders, maximum is 3.

The output of the script looks like this

sqlservertreelikeoutput

The code can be downloaded from here

Load balancing for database files among the mount points

Goal

To load balancing the database files among the number of mount points or paths that we have available for this database.

My way of achieving it

Using tsql, xp_cmdshell and powershell command. From time to time, because databases grow, more databases are created we need more and more space for storage and because of this we end up adding more disks or mount points (the case that this script, post will address) but these are not used evenly by the databases from our instance. Some of the databases that were created  in the beginning will use only the mountpoints available when the database was created. Because of this from time to time we need a cleaning method or a method to load balancing the files among the mountpoints. The script below is my attempt to load balancing those files. By default is displaying information for databases that have 4 database files. You must be aware that the script is not checking for the available free space at destination. The script is only displaying the information without executing anything. The output of the script for a test database will look like below

loadbalancingdbfiles_17July2016

The code can be downloaded from here

Duplicate objects belonging to a schema to another schema

Goal

To duplicate all the tables, views, storedprocedures, userdefinedfunctions that belongs to a schema to another schema.  Of course that there are more objects that may need to be duplicated but as a start, those are the main ones that the script does. Other objects can be added with little modification to the script.

My way of achieving it

Using powershell and scripting

The script tries to do what generate script option at the database level does but on top is trying to address the following

 

  • It tries to resolve the name of the objects inside of all the generated code to point to the objects in the right schema( new schema) – this is where the script might have an advantage over the generate script option at the database level.
    • Meaning that if the object definition was in dbo schema – SELECT * from software_definition – it will transform it in SELECT * from testuser.software_definition. As you can guess this string replacement might take a lot of time if we have a lot of objects that are scripted and the object definition is long. More than that, that string replacement might be wrong sometimes. This is why some more tests needs to be conducted.
  • It tries to decrease the need of the manual work

The output of the script looks like below

duplschema2 duplschema1

Please use it first on your virtual machines or your test instances, like adventure works sample databases or others. As I said it is not a replacement for generate script option it is just an attempt to avoid some

– clicking operations

– selecting some scripting options

– finding and replacing some strings, schema.name to newschema.name

– adding new schema name for the objects that are missing the schema prefix ( this needs to be adjusted to work as good as possible)

The code can be downloaded from here

SQL Server clustered instance dependencies – verification

Goal

To check and verify if the existent dependencies for sql server resources are in place

My way of achieving it

Using powershell. From time to time due to adding more disks to an instance or performing other activities we end up having a resource that is not tied to the parent resource and when it comes to sql server disks some of the databases residing on those disks are not accessible. In order to check if we have any of the resources not being tied to the right parent resource I have created the script below that will tell us which resources are not tied. The output of the script looks like below

disk_dep_powershell

The code can be downloaded from here