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 troubleshooting – filegroup warning alerts

Goal

To obtain information that will help me troubleshoot, solve these type of alerts

My way of achieving it

Using tsql and xp_cmdshell. From time to time and depending of the monitoring solution in place alerts related to filegroup for a database is something common throughout the day of the dba and I wanted to have something that will help me while solving those type of alerts. The script that I am using for solving these alerts doesn’t mean that we shouldn’t take further actions anymore is just an way to tell me if a deeper investigation or look should be made. The output of the script is below and the script requires the database name and the threshold for which you want the script to calculate the new values. In most of the cases the threshold is 80 but some implementations can configure the threshold to 90 which means that the alerts will come when the used percentage of the database is >=90.

These are the places that needs to be modified to reflect your database and your threshold

SET @dbname = ‘AdventureWorks’ — database name
SET @threshold = 50 — threshold

As you can see below

 

fg_warning

the script shows the current info and increases the maxsize for database files belonging to the PRIMARY filegroup with at least 2%. The main goal of the increase is to have a maxsize that will make the used space in percent to be smaller than threshold. When the comment for each database file specifies that the maxsize is bigger than the free space on that drive we need to have a look and possible to start the increase disk procedure for that location.

The script can be downloaded from here

SQL Server Instance – generate tsql to meet tempdb best practices and display the current configuration

Goal

Generate tsql code required to meet tempdb best practices configuration and display the current configuration

My way of achieving it

Using SSMS, tsql, xp_cmdshell

The backbone for this script was a script made a while ago by VijayRod, and reviewed by AkshayM that was checking tempdb configuration by comparing the current configuration to best practices recommended by Microsoft at that time. Because sometime we need if configuration is not compliant to modify the tempdb settings I decided to use their script and add the code that will generate the tsql commands used to configure tempdb database to meet the requirements. This version of the script has some limitations and assumes the following
– when it comes to tempdb databases that have files created in multiple locations ( different drives ), by default the script will take in consideration only the biggest drive when it comes to create the files.
– it assumes that the free space left on the drive where the files will be created will be only 1 GB from the drive capacity. If you want to leave more space then replace the 1024 in the script in this line – SELECT @availablespace=(MAX(diskspacefreeinmb)-1024)/@suggestedDataFilecount FROM @dbfilesdrivemountpoint WHERE filetype = ‘DATA’ –
– it assumes that the maximum size will be the initial size + 10MB. If you want to have a maximum size bigger then replace 10 in the script in this line – SELECT @maxsize = CONVERT(nvarchar,(@availablespace+10))+’MB’ — assumes a maximum size of initial size + another 10 MB

The output of the script looks like below depending on the current tempdb database configuration

tempdbbpgencomm1

The script can be downloaded from here