How to monitor database file space usage in SQL Server

Undoubtedly, when it comes to monitoring database file space usage we need a simple way to do it. Fortunately, SQL Server has considerable quantity of DMV and System Functions to allows us to get closely related information about it. Today's post has the intention of providing a practical way of warning us in case the free database file space is less than the threshold of 20%. With this alert in mind, we will be able to make a preventive decision in order to prevent the performance of in-flight transactions from being impacted.

I am coming up with a simple query to achieve that. Firstly, we need to create a physical temporary table 'FileSpace' which will hold related information for each database. Having successfully creating the table, the following step is to execute the adequate logic, in the context of each database user via 'sp_msforeachdb', to collect file space usage which will be reported in the end. I am getting space usage information of each database file by using FILEPROPERTY system function.

The execution of the entire script (including creation of the table) may be automated via a SQL Job in order to send yourself an alert with format HTML by using database mail. Unluckily, this code does not include the logic to send that email but it is not something difficult to complete, therefore, just do it. That is all for now. Let me know any remarks you may have. Thanks for reading.

Here is the whole script.

USE [master]
GO
CREATE TABLE [dbo].[FileSpace](
    [DatabaseName] [nvarchar](128) NULL,
    [FileName] [sysname] NOT NULL,
    Type varchar(10) not null,
    [FileSizeGB] [decimal](10, 2) NULL,
    [SpaceUsedGB] [decimal](10, 1) NULL,
    [SpaceFreeGB] [decimal](10, 1) NULL,
    [SpaceFree%] [decimal](10, 1) NULL,
    Physical_Name varchar(max)
) ON [PRIMARY]
GO
 
EXEC sp_MSforeachdb '
USE [?]
insert into master.DBO.[FileSpace]([DatabaseName] ,    [FileName] , Type,    [FileSizeGB],    [SpaceUsedGB] ,    [SpaceFreeGB] ,    [SpaceFree%],Physical_Name )
select db_name() DatabaseName, name FileName, Type_Desc ,
cast(size/128.0/1024.0 as decimal(10,2)) SizeGB, 
cast(FILEPROPERTY(name,''SpaceUsed'') /128.0/1024.0  as decimal(10,1)) SpaceUsedGB, 
cast((size - FILEPROPERTY(name,''SpaceUsed'') ) /128.0/1024.0 as decimal(10,1)) SpaceFreeGB, 
cast(((size - FILEPROPERTY(name,''SpaceUsed'') )/(size*1.0)) *100 as decimal(10,1)) [SpaceFree%], physical_name
FROM sys.database_files'

SELECT * FROM FileSpace
WHERE [SpaceFree%]<=20
DROP TABLE FileSpace

Creating alerts for monitoring proactively SQL Server AlwaysOn Availability Groups

When it comes to monitoring databases we have to do it proactively by using alerts implemented natively or via T-SQL code inside SQL Jobs running recurrently (I know we do have the AlwaysOn Dashboard, but personally I do not have time to check it all the time). As a result of this situation, so rapidly do we have databases working on production environment that we may need to have alerts working as well. In this post I am going to show how to have the most important AlwaysOn Availability Group alerts implemented. To begin with, I would basically suggest creating alerts only for these error messages.



Undoubtedly, we may need more alerts. Therefore, we will need to look into ‘sys.messages’ system object and look for error messages closely related to AlwaysOn Availability Group feature. Only after identifying them will we be able to create particular alerts taking the ‘message_id’ number.  To illustrate, I will query ‘sys.messages’ by using keywords like ‘Availability’, ‘suspended’, ‘failed’, ‘disconnected’, etc.

SELECT * FROM sys.messages  
WHERE (  [text] LIKE '%Availability%Disconnected%' OR   [text] LIKE '%Availability%not%synchroni%'
OR   [text] LIKE  '%Availability%suspended%'    OR   [text] LIKE '%Availability%failed%' OR   [text] LIKE '%Availability%chang%role%'
OR   [text] LIKE '%Availability%resumed%' )  AND language_id=1033

As you have seen, they are about 63 messages (depending on the SQL Server version you are working on). I do believe that these messages are the be-all and end-all so we might not need to implement additional alerts. Put differently, they are enough. Nevertheless, we might filter out some messages we do not consider important and include others to monitor AlwaysOn Failover Cluster instead.

SELECT * FROM sys.messages  
WHERE (  [text] LIKE '%Availabiliy%replica%' OR   [text] LIKE '%Database%replica%' OR   [text] LIKE '%primary%role%'
    OR   [text] LIKE '%secondry%role%' OR   [text] LIKE '%availability%group%' OR   [text] LIKE '%WSFC%'
    OR   [text] LIKE '%primary%replica%' OR   [text] LIKE '%secondary%replica%' OR   [text] LIKE '%alwayson%')
    AND    [text] NOT LIKE '%No user action is required.%'
    AND severity<>10
    AND language_id=1033

After having got the error number, the next step is to create the alert for the error by using ‘sp_add_alert’ system stored procedure which accepts basic parameters like the name for the alert and ‘message_id’ (taken from ‘sys.messages’). The rest of parameters will be the same for each alert. So, you can create the other alerts by replacing the name and ‘message_id’, for instance, the following example is based on the error number 41418. (We also create the notification to send an email to us when the alert is raised. Obviously, not until having successfully configured SQL Agent service to use Database Mail will you be able to receive the email.)

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'41418 HADR Alert', 
        @message_id=41418, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=1, 
        @notification_message=N'Contact your Database Administrator urgently.', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'41418 HADR Alert', @operator_name=N'DBA', @notification_method = 1

To try out to see whether or not the alert is working, we will need to raise the error manually via RAISERROR command with ‘LOG’ parameter (to see the message logged in the SQL Server error log).

RAISERROR(41418,16,1) WITH LOG;

Having done that, we can see the following message in error log:

Likewise, you will get the email.

To sum up, I am convinced that these sorts of alert will always work for monitoring Availability Groups and others. You just need to spend some time on figuring out about the error messages and then go ahead and create the right alert for you. I hope you enjoy this post and let me know any remark you may have. Thanks for reading again.

Using ‘sp_browsereplcmds’ to diagnose SQL Server Replication issues

While diagnosing SQL Server transactional replications issues, we may need to look into some pending commands inside the Distribution database. In other words, not only do we have to monitor pending commands, but also take some actions so as to keep working the replication. For instance, at times we may have to kill some specific commands because of errors which do not allow other commands to be moved to subscribers. Before doing that, we firstly need to identify which commands have to be removed from the queue by using ‘sp_browsereplcmds’ system stored procedure. This basically accepts many input parameters like article_id:

EXEC SP_BROWSEREPLCMDS @article_id = 1

After executing it, we are going to filter only the pending commands for the Article in question. (Remember that an article in replication is directly related to a table. You can query ‘sysarticles’ system table inside the published database.)


Another parameter we can use in order to get more specific information is the transaction sequence number which is essentially the identifier for the transaction. Luckily, when reading some errors, we can see the sequence number and command ID which allow us to identify exactly the root cause we need to work on with ease.

EXEC SP_BROWSEREPLCMDS @xact_seqno_start = '0x00000027000000B50008',@xact_seqno_end = '0x00000027000000B50008' 

There are other parameters like command ID to get only the command we need to look into, and also the database ID to get all commands for that database.

EXEC SP_BROWSEREPLCMDS @xact_seqno_start = '0x00000027000000B50008',@xact_seqno_end = '0x00000027000000B50008' ,
@publisher_database_id = 33, @article_id = 1,@command_id= 1

Be cautious, do not execute ‘sp_browsereplcmds’ without any parameter on production database environments as they can have millions of commands inside Distribution database and as a result of this we will not get what we need rapidly and at the same time we will affect the database server performance. I hope you can find this post interesting when it comes to troubleshooting replication issues. Let me know any remark you may have. Thanks for reading.

Why is the .txt history detail file of SQL Backup Job not created?

Clearly, we always need to have some kind of history for SQL Backup jobs in order to check whether or not they were executed correctly. I personally use a .txt file to write on it all details of each step executed so that I can use it to diagnose any problem or error behind it. Nevertheless, at times set it up may become something not so easy as the .txt file might not be created during the SQL Backup job execution.


There are some reasons why this may be happening. The first one is because the directory where the .txt file does not exist. It should have been created manually before executing the SQL Backup job. The second reason is because the backup directory is blocked. Surprisingly, you will realise it is with blocked access when you try to open it. One way to get it unblocked is by simply opening the directory and then clicking on “continue” via Windows Explorer.



The third one is because the SQL Agent account does not have permissions on that directory. There must have given Read and Write permission on it and there are some cases where we will need to give explicit permissions to the SQL Agent account on that directory via CMD windows command tool. Undoubtedly, it would be no problem if we are working on only one directory but what would it happen whether we are implementing many SQL Backup jobs? it would become a very tedious job to manage one by one. So, in this case we need some manner to automatize and get them done rapidly. For instance, here I am going to show you a technique to achieve it:

icacls "H:\SQLBackup\FinancialDB\Full" /grant MyDomain\sqlagentAccount:(OI)(CI)F

Now for creating the script to give permission on all necessary directories we can create the code by using this T-SQL (based on reading the directory from Backup Devices whose names are like 'FinancialDB-Full_Database_Backup'):

DECLARE @sn NVARCHAR(128);
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',  'ObjectName',   @sn OUTPUT;
select 'icacls "'+SUBSTRING(physical_name,1, LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name)))+'" /grant '+@sn+':(OI)(CI)F'
from sys.backup_devices where name
ORDER BY name 

All the output results must be executed on CMD tool (as Administrator) and finally after executing the SQL Backup job we will verify that the .txt file was created:

And the history details are inside:


I hope this tip is useful and practical for you. Let me know any remark you may have. Thanks for reading!

The 'SkipErrors' parameter for the Replication Distribution Agent

Having seen many errors in SQL Server Replication, I can say that the vast majority of them are reported at replication agents level like Distribution Agent, Log Reader Agent, Snapshot Agent, Queue Reader Agent, and so on. Unfortunately, some of  them are mostly related to Primary and Foreign Key conflicts, and to solve them either one by one or by reconfiguring the replication we may need much time, therefore, we need to look for an option to allow the operation to continue while we work to fix them as soon as possible. Luckily, this option is the 'SkipErrors' parameter which I have used many times to deal with them. Likewise, you can use it for skipping other errors. 
Today's post will show how to use the' SkipErrors' parameter which allows to skip specific errors so that the data synchronization process is not stopped. This parameter is configurable in the profile of the Distribution Agent and has as input the error number we may want to skip.
The following picture shows an error (with code 547 related to an Foreign Key issue) in the Distribution Agent process and we see how the transactions are being queued due to this error, consequently, there is a need to fix it so as to allow the rest of pending transactions are moved on. (Distribution Agent reads sequentially the table 'msrepl_commands' to get the command to execute in the subscribers, this means that First in the Queue is the First Out to be moved to subscribers.)



Other common errors where you can use 'SkipErrors' parameter is when there are not some rows in the subscriber to apply the changes (The row was not found at the Subscriber when applying the replicated command). The error code for this case is 20598.
 

I mentioned before that the 'SkipErrors' parameter is configurable inside the Distribution Agent profile and that is what we are going to do right now. Firstly, we need to create a customized profile based on the Default profile and write in the 'Value' column the numbers of the errors (to be skipped)  separated by colons as we can see in the following picture 20598:547.



Having done that, we may have to restart the Distribution Agent. Next time the Distribution Agent starts up, it will load the new customized profile with the error codes to be skipped (ignored). Finally, not only will we verify that many errors are skipped, but also the Distribution Agent is running with no problem.
 

Just to finish writing this post, keep in mind that the transactions with these errors were missed, which means that you will not be able to recover them and may affect your data consistency. In other words, we must use 'SkipErrors' parameter with extreme caution. Let me know any remark you may have. Thanks for reading.