SQL Server Information – backups not compliant with the backup naming convention

Goal

To obtain a list of backups that have a name different than the backup naming convention and the space occupied by these backups

My way of achieving it

Using tsql, xp_cmdshell and xp_fileexists. From time to time, at least in my case, I am doing backups as part of a ticket or a request and I am naming them in 100% of the cases something that is different than what the scheduled backups uses for those backups. In time I realized the my footprint is quite big and if among the team there are other colleagues doing the same then the footprint is even bigger. Because of this I created this script that will tell us which backups looks like NOT being compliant or backups that are not taken by the scheduled jobs or using the right naming convention and provides us information about those files like:

  • path to the backup
  • if the file is still on disk
  • size of the file
  • delete commands

The assumptions that I made were that usually we are taking full backups, differential backups, tlog backups and on some servers we might have log shipping configured. Because of these we might have 4 naming conventions for our backups or more. The script starts by creating a table containing the naming conventions discovered based on the backup name length, number of numbers in the backup name and the number of occurrences. Based on this table and based on the fact that if occurrences is smaller than 10 is deciding if the backup is compliant or not.

By default the script is dealing with backup history for the last 14 days. If you want to go back in time more than 14 days then you must modify the line below

SET @days = 14 — enter here the number of days for which you want to search among backup history

in the script.

Please be aware that this setting together with how often you take the backups increases the execution time.

The output of the script is bellow

backupsnotcompliant1

The output of the script from a server where we have some backups jobs configured looks like below

backupsnotcompliant2

 

The only thing that I want to mention here is that this script is providing information only and the decision of doing something with the output is entirely something that you must decide and is very dependent of the each infrastructure environment. All the results set were left in the output because by seeing the output you can easily confirm that indeed those type of backups are for sure not compliant with the backup policy from your environment.

The script 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