SQL Server Error: 9001, Severity: 21, State: 5 – The log for database `database_name` is not available. Check the event log for related error messages. Resolve any errors and restart the database. | Error: 9001, Severity: 21, State: 5. | Matched: %Error%,

Upon being greeted with the following logs, I started investigating the issue.

I found the issue to be caused by the database being closed, and not any major underlying disk issue.

Check if your database has auto_close enabled.


select @@SERVERNAME AS server_name,getutcdate() as report_date_utc,name as database_name, is_auto_close_on , state_desc, user_access_desc
from sys.databases
where is_auto_close_on = 1
order by name asc

If autoclose is on, switch this database to no longer use auto_close.

Before making any changes, check the integrity of the database. If there are no errors generated by this command, then  move on to changing the auto_close option.

dbcc checkdb('database_name')

alter database [database_name] set offline with rollback immediate;
go
alter database [database_name] set online;

alter database [database_name] set AUTO_CLOSE OFF;

The file ‘myDatabase_LOG2’ cannot be removed because it is not empty. Server: Msg 5042, Level 16, State 2, Line 1

When doing some re-indexing on a server, I had to create an additional log file so that the disk drive would not fill up and stop the database / re-index operation.

Subsequent to doing my re-index, no matter how many times I attempted to do a dbcc shrinkfile with the emptyfile option, the file would not delete.

The file ‘Database_Log_File’ cannot be removed because it is not empty.


USE [myDatabase]
GO
-- EMPTY TRANSACTION LOG FILE
DBCC SHRINKFILE(myDatabase_LOG2,EMPTYFILE)
GO
-- TO CLEAR OUT TRANSACTION LOG FILE -- PERFORM A TRANSACTION LOG BACKUP - Standard SQL Backup

BACKUP LOG [myDatabase] TO DISK = '\\myBackupShare\myDatabase_yyyymmdd.trn',INIT, PASSWORD= 'myPasword';

-- TO CLEAR OUT TRANSACTION LOG FILE -- PERFORM A TRANSACTION LOG BACKUP - Red Gate
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXEC MASTER..sqlbackup N'-SQL "BACKUP LOG [myDatabase]
TO DISK = ''\\myBackupShare\''
WITH NAME = '''', DESCRIPTION = '''',
INIT, PASSWORD = ''myPasword''
, MAXDATABLOCK = 65536,KEYSIZE = 256, COMPRESSION = 1, THREADS = 1"'
, @exitcode OUTPUT, @sqlerrorcode OUTPUT
GO

-- REMOVE TRANSACTION LOG FILE
ALTER DATABASE [myDatabase] REMOVE FILE [myDatabase_LOG2]
GO

Best Practices – Alerts

As a DBA inevitably processes fail from time to time. I have used the 6W’s concept to ensure that enough information is provided to the message recipient to be effective in their job.

Use the Six W’s (Five W’s one H) in the Alert message. While the use of the 6 W’s seems to be verbose, it provides your team members enough context to understand how they need to respond to an incident or alert.

  • Why
  • Why was the job scheduled?
  • What
  • What are the inputs of the job?
  • What does the job do?
  • What are the outputs of the job?
  • What are the warnings / errors encountered?
  • How
  • How is the job implemented
  • How does one validate the inputs for the job?
  • How does one validate the outputs for the job?
  • Where
  • Where is the job scheduled?
  • Who
  • Who requested the Job?
  • Who is the user account / proxy account which is used by the job?
  • When
  • When should the job be executed and what is the logic used for scheduling?
  • Task Chain / Dependency
  • Should this process be implemented before or after another process?
  • Specific Time

Another method of validating that your alerts contains enough information is to consider Horstman’s Law of Project Management.
If the alert is assumed to provide one with information which is actionable, we can use the following as a guide to determine how what happened differed from client expectations.

Provide a succinct summary of the client expectations in the terms of:
Detail the Client Expectations

  • Who is responsible for completing a task (Person / User Account / Server) ?
  • What is this task item?
  • When is the task item to be completed? (SLA)

Detail how expectations were not met and what to do next

  • Who attempted to perform a task (Person / User Account / Server) ?
  • What errors/ events happened when attempting to complete task?
  • What needs to be done based on the errors/events which occured?
  • When was the task attempted?

SSIS Error Handling with Email Alerts and Details.

Presented with the need for a consistent method of alerting users of Errors or Warning Conditions in SQL Server Integration Services; I went about the creation of a standard methodology to use in all packages across the enterprise.

The requirements were:

  • Alert Specific Users of Errors within a Package
  • Alerts are to provide sufficient detail for rapid troubleshooting
  • Alert Process is to be consistent across all servers

I therefore set about using the SSIS Script task, so that I could increment my counters and customize my error messages as each task in the package completed.
I implemented 2 counters, ones for warnings and one for errors as well as an error message for tracking each step of my package.
The User Defined Variables for error handling are

  • alertsubscribers – string
  • errorcount – integer
  • errormessage – string
  • mailhost – string
  • warningcount – integer

Read Only Variables (All System Provided)


System::LocaleID
,System::TaskID
,System::TaskName
,System::TaskTransactionOption
,System::MachineName
,System::PackageID
,System::PackageName
,System::ProductVersion
,System::VersionBuild
,System::VersionComments
,System::VersionGUID
,System::VersionMajor
,System::VersionMinor

Read Write Variables (User Variables at Package)


User::errorcount
,User::errormessage
,User::warningcount
,User::alertsubscribers
,User::mailhost

Script Task at Begining of Package to Initialize Variables


String errormessage = "";
errormessage = errormessage + "MachineName: " + Dts.Variables["MachineName"].Value.ToString() + System.Environment.NewLine;
errormessage = errormessage + "PackageID: " + Dts.Variables["PackageID"].Value.ToString() + System.Environment.NewLine;
errormessage = errormessage + "PackageName: " + Dts.Variables["PackageName"].Value.ToString() + System.Environment.NewLine;
errormessage = errormessage + "ProductVersion: " + Dts.Variables["ProductVersion"].Value.ToString() + System.Environment.NewLine;
Dts.Variables["errormessage"].Value = errormessage;
Dts.Variables["errorcount"].Value = 0;
Dts.Variables["warningcount"].Value = 0;

For each Error In a Script Task, if there is an Exception I simply increment the errorcount or the warning count via a line of code such as:

String errormessage = "";
int errorcount = 0;
int warningcount = 0;
errorcount = errorcount + 1;
errormessage = errormessage + "My Custom Error or Warning Message " + System.Environment.NewLine;

Dts.Variables["errormessage"].Value = Dts.Variables["errormessage"].Value.toString() + errormessage;
Dts.Variables["errorcount"].Value = (int)Dts.Variables["errorcount"].Value + errorcount;
Dts.Variables["warningcount"].Value = (int)Dts.Variables["warningcount"].Value + warningcount;

If I am running a Component, and want to track the error. I use an On Failure Script Task.

Subsequently at the end of the package there are two options.
1. Log the results to table (My preferred method)
2. Send an Email to the requisite parties. (the method I describe below)


using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text;
using System.Net.Mail;

int x = 0
x = (int)Dts.Variables["errorcount"].Value + (int)Dts.Variables["warningcount"].Value ;

// send alert only if errorcount + warning count > 0
if (x >0) {

string mailhost ="mailservername";
string alertsubscribers =Dts.Variables["alertsubscribers"].Value.ToString() ;
string subject = "" ;
string body = Dts.Variables["errormessage"].Value;

subject = Dts.Variables["PackageName"].Value.ToString() + " " + Dts.Variables["errorcount"].Value.ToString() + " Errors Encountered, " + Dts.Variables["warningcount"].Value.ToString() + " Warnings Encountered at "+ System.Datetime.Now.toString("yyyy-MM-dd HH:mm:ss")

//create the mail message
MailMessage mail = new MailMessage();

//set the addresses
mail.From = new MailAddress("genericEmailAddress@domain.com","SSIS Alert System");
mail.To.Add(alertsubscribers);

//set the content
mail.Subject = subject;
mail.Body = body;
mail.IsBodyHtml = false;
//send the message
SmtpClient smtp = new SmtpClient();
smtp.Host = Dts.Variables["mailhost"].Value.ToString() ;
smtp.Port = 25;
smtp.UseDefaultCredentials = true;
smtp.Send(mail);
}