By: Tim Smith | Comments | Related: More > Database Administration
Problem
We use several file system tools and discovered through an outage that one of our file system tools caused an entire folder path to become write-protected. None of our developers or customers could write to (and in a few cases, read from) the file path. Unfortunately, we had database-related files in this path and the error caused all the writes during this window to be denied. This ending up causing other failures and we've contacted the third party in charge of the software tool for preventing of this problem and possibly an alert they have. However, independent of the vender's solution, we wanted to know what type of alerts we could create for this situation to quickly identify if this issue is happening and prevent it from continuing.
Solution
When we see this error, it's also possible that a user lost permission to the file path (or file group) - such as a folder path where the files are stored - and that this loss of permissions carries . In this tip, we'll look at a couple of ways showing how we can track this information and alert on the information, as file write-protection can quickly cause an outage if it's not stopped quickly provided that we know that we want the user to have this protection.
Monitor the SQL Server error log
When this error occurs, we will see an error in the error log with a message that includes "media is write protected" with an operating system error. In general, we can get the latest SQL Server error log information by executing the below script:
EXEC sp_readerrorlog 0,1
We can save the output of this information to a table, provided the table matches the schema of the error output - which returns LogDate (a datetime column), ProcessInfo (a varchar column) and Text (another varchar column). In the below example, we'll use a variable table with these columns for the insert and apply the appropriate filters on the text we save (with the extended stored procedure sp_readerrorlog, we could use the third and fourth variables not seen above to filter for text as well, such as EXEC sp_readerrorlog 0,1,'operating system','media is write protected').
If we verify that the file or file group is set to read-only and (or) prevents any types of writes, we can revert this change for the appropriate user or users. In the below code, we read the error log every few minutes (five in this example) for this message in the error log. Provided that we validate that this error is present, we would send the alert. If we determine that other information is needed before we send the alert (to avoid alert spam), we would want to include that. The below example assumes that any presence of this error is indicative of a problem.
DECLARE @errorcount TINYINT DECLARE @filealert TABLE( LogDate DATETIME, ProcessInfo VARCHAR(250), Text VARCHAR(MAX) ) INSERT INTO @filealert EXEC sp_readerrorlog 0,1 SELECT @errorcount = COUNT(*) FROM @filealert WHERE [Text] LIKE '%media is write protected%' AND LogDate > DATEADD(MINUTE,-5,GETDATE()) IF (@errorcount > 0) BEGIN DECLARE @message VARCHAR(MAX) = 'Media protected disk detected; error count ' + CAST(@errorcount AS VARCHAR(3)) EXEC msdb.dbo.sp_send_dbmail @profile_name = 'mailadmin' , @recipients = '[email protected]' , @body = @message , @subject = 'Error: Disk(s)' END
Use PowerShell ISE to test writing text to a file and sending an alert if an error is detected on the attempted write
First, we'll create a blank text file (.txt) in a folder path, in this case C:\import\protect, however, you can test this on any custom path. In the below image under the file's properties, we will set a deny permission of full control on a folder path in the security tab - in this case C:\import\protect\. As we see when we update the path's options and reload the properties, the path is set to Read-Only (image two shown below this) on the General tab and this applies to everything within the folder.
Next, we'll try to write to the text file that we created in this path using PowerShell's Add-Content:
Add-Content "C:\import\protect\exampletext.txt" "My dog has flees"
We get several errors, but the first error is key:
Add-Content : Access is denied At line:1 char:1 + Add-Content "C:\import\protect\exampletext.txt" "My dog has flees"
As an alternative, in the properties selection under security, we could also simply deny the write permission and get the same error as the above message. Similar to the SQL Server error log message, this message tells us that we can't write to any file in the path. Our next step will be wrapping our attempt in a try-catch, so that we can catch the error, rather than have the script fail with an error message in the PowerShell ISE window. We'll set the -ErrorAction option to stop and in our catch, catch the exception, outputting the exception message.
try
{
Add-Content "C:\import\protect\exampletext.txt" "My dog has flees" -ErrorAction Stop
}
catch [Exception]
{
$_.Exception.Message
}
The message:
Access to the path 'C:\import\protect\exampletext.txt' is denied.
From here, we can send an email alert within PowerShell:
try
{
Add-Content "C:\import\protect\exampletext.txt" "My dog has flees" -ErrorAction Stop
}
catch [Exception]
{
$message = $_.Exception.Message
Send-MailMessage -SmtpServer "smtp.ourserver.ourdomain" -To "[email protected]" -From "[email protected]" -Subject "Error: Disk(s)" -Body $message
}
Specific to our environment, we would make the appropriate adjustments to the smtpserver, to email and from email. If we're executing the job from a SQL Server Agent using PowerShell as an alternative, we could have the job set to fail without the try catch and alert on any job failures - which is generally an option DBAs have running anyway, as other priority jobs may be failing throughout the day and alerts for them should be sent when they fail. Both Windows Task Scheduler and SQL Server Job Agent can run scripts and both can email within scripts, but we may already have other alerting set up for either of these and use the built-in alerting we have - the key is that we want the job to fail, so we remove the try-catch.
Next Steps
- This tip assumes that write-protection is an error. There are situations where we want this, such as during a restore operation of any kind to a disk path, we don't want any users touching the path while the restore is taking place. This would be a valid use-case of preventing any reads or writes except the one user account performing the restore operation. Also, we may prevent some users from reading, writing or performing any operation on some paths for security. Unless our settings change, such as updating a path which includes database files to be secure, this shouldn't affect existing databases.
- If we determine these messages or attempts at writing to a file are problems, we should act immediately, as it means are files cannot get, update or remove data. In the error log T-SQL script, I use a time window of five minutes, however, depending on the urgency, we may want to know as soon as possible, so checks of every fifteen seconds would be superior.
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
