Recently I ran into an issue with SSIS Catalog database while enabling “Always ON support”. The only way to fix it was to let Always ON wizard ( select option : Full database and log backup option) create SSIS DB on secondary. I will post more detail but in the meantime, this blog has the problem and solution in detail:
Report for job failures
I am sure most of the jobs you have scheduled have some kind of notification in the advanced job properties. You may also have SQL Server alerts setup to send email to DBA team when a job fails. Honestly, I don’t think it’s necessary to get alerts for each and every job. Some job failure notifications are just notorious inbox-filling alerts. So, I created this routine to alert me once in the morning for all jobs that have failed last run as failed on all my SQL Servers.
This consolidated report helps reduce number of alerts. I should mention though, if a job fails and next run is let’s say not for a week, every day the job will appear in the failure report until the next run of job is finished successfully.
One more thing, some jobs get stuck and never finish. Hence they don’t appear in this email even though there might be a problem and the job needs to be looked at by a DBA. I suggest you use ‘check failed job’ script I have (it should be in the jobs section). It has small TSQL to get jobs that are currently running. A DBA should look at if the job shows up in the query output and start time doesn’t look right. Fortunately, not everything can be automated, that’s why companies need DBAs ehh !
For the job failure report, you need to create a table in let’s say a repository database. A powershell script will gather information about SQL Jobs from all the servers mentioned in a config file and load this data into the table you just created in repository database. Here is how it’s done:
STEP 1: Create table in a repository database on monitoring server using following TSQL Script:
USE [DatabaseRepository]
GO
CREATE TABLE [dbo].[DB_Job_Info]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](25) NULL,
[JobName] [varchar](25) NULL,
[LastRunDate] [varchar](25) NULL,
[LastRunOutCome] [varchar](25) NULL,
[DateRecordWasAdded] [datetime] NULL,
PRIMARY KEY CLUSTERED ( [ID] ASC)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DB_Job_Info] ADD CONSTRAINT [DF_CurrentDate_JobInfo] DEFAULT (getdate()) FOR [DateRecordWasAdded]
GO
STEP 2: Create a SQL Agent job on monitoring server with three job steps and schedule it to run at let’s say 7 AM every morning.
job step1 – TSQL-
Truncate table DatabaseRepository.dbo.Db_Job_Info
job step 2 – PowerShell –
#add-pssnapin sqlservercmdletsnapin100
#add-pssnapin sqlserverprovidersnapin100
$date = ( get-date ).ToString(‘yyyy/MM/dd’)
$DestServer = “abcMonitorSQLserver1”
$DestDB = “DatabaseRepository”
foreach ($instance in get-content “\\abcMonitorSQLServer1\D$\DBA_DoNotDelete\PowerShellScripts\Config\servers.txt”)
{
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
# Create an SMO connection to the instance
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance
$jobServer = $s.JobServer
$jobs = $JobServer.Jobs
ForEach ($job in $jobs)
{ if ($job.IsEnabled -eq $True) #-and ($db.name -ne “model”))
{ $Server = $instance
$JobName = $job.Name
$LastRunDate = $job.LastRunDate
$LastRunOutcome = $job.LastRunOutCome
$InsertQuery = “spInsertDB_JobInfo ‘$Server’, ‘$JobName’, ‘$LastRunDate’, ‘$LastRunOutCome'”
invoke-sqlcmd -Database $DestDB -Server $DestServer -query “$InsertQuery” -SuppressProviderContextWarning;
}
}
}
job step 3 – TSQL –
Declare @TotalJobs int
set @TotalJobs = (select COUNT (ID) from DatabaseRepository.dbo.DB_Job_Info)
DECLARE @count int
set @count = (select COUNT (ID) from DatabaseRepository.dbo.DB_Job_Info WHERE LastRunOutCome like ‘failed’ )
IF @count >= 1
BEGIN
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<html>This is an automated email from SQL Server monitoring server abcSvr1.
<br>Total number of Jobs monitored are = ‘+ cast( @TotalJobs as varchar(10)) +
‘<br><br>Following is the status of SQL Server Jobs.</html>’ +
N'<H1>SQL Server DB Job Failures:</H1>’ +
N'<table border=”1″>’ +
N'<tr><th>Sr Number</th>’ +
N'<th>Server Name</th>’ +
N'<th>Job Name</th>’ +
N'<th>Last Run Time</th>’ +
N'<th>Last Run Outcome</th></tr>’ +
CAST ( ( SELECT td = ROW_NUMBER() over(order by ID), ”,
td = ServerName, ”,
td = JobName, ”,
td = LastRunDate, ”,
td = LastRunOutcome, ”
FROM DatabaseRepository.dbo.DB_Job_Info
WHERE LastRunOutCome like ‘failed’
FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>’ +
N'<html><br><br>The above table shows <font color=”red”>list of jobs with last run-outcome as failed since previous email alert</font>.
For more information please log-on to the server(s) and look at the job history.</html>’;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘YourDBmailProfileName’,
@recipients = ‘DBA Team<xyzDBATeam@abc.com>’,
–@copy_recipients = ‘1@2.com;2@3.com’,
@subject = ‘SQL Server Job status’,
@body = @tableHTML,
@body_format = ‘HTML’;
END
ELSE
Begin
SET @tableHTML =
N'<html>This is an automated email from SQL Server monitoring server abcSvr1.
<br>Total number of Jobs monitored are = ‘ + cast (@TotalJobs as varchar (10)) +
‘<br><br><i><font color=”green”>All Jobs are completed successfully.</font></i></html>’
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘DBProfileName’,
@recipients = ‘DBA Team<xyzDBATeam@abc.edu>’,
@subject = ‘SQL Server Job status’,
@body = @tableHTML,
@body_format = ‘HTML’;
End
Alert for SQL Service down
This alert is similar to low disk space alert. It’s basically SQL Agent job running on a monitoring SQL Server, reading server names from a text file, running PowerShell script on remote servers to check if one of the SQL Servcies is down and send an email to DBA team’s email distribution list if a SQL Service is down.
I have another PowerShell script for people who need more in-depth monitoring with servers that may not necessarily be in same domain. You may request it in the comment section below. I am not including that since the script below should be sufficient for most of the DBAs in most situations.
You can see in ‘script logic’ section, script is reading status of all services that have ‘SQL’ in their name and excluding ‘SQL Browser’ service. The script also ignores SQL Services that are disabled.
As always, I am not including sample alert email but rest assured it’s beautiful and meaningful.
POWERSHELL SCRIPT:
######################### DEFINING VARIABLES ################################
$TO = "DBA Team<dbateamDL@abc.com"
$From = "SQL Server DB Alert<xyzMonitoring@abc.com"
$SMTPServer = "smtp@abc.com"
######################### DEFINING FUNCTIONS ################################
# (FuncMail) Function to send email
function FuncMail {
param($To, $From, $Subject, $Body, $smtpServer)
$msg = new-object Net.Mail.MailMessage #creating a mail object
$smtp = new-object Net.Mail.SmtpClient($smtpServer) #creating SMTP server object
#Email Structure
$msg.From = $From
#$msg.ReplyTo = "xyz@abc.com"
$msg.To.Add($To)
$msg.Subject = $Subject
$msg.IsBodyHtml = 1
$msg.Body = $Body
#$mailmessage.Priority = [system.net.mail.mailpriority]::high
$smtp.Send($msg)
}
######################### Script Logic ################################
$servers=get-content "\\abcSQL01_ServerName\D$\DBA_DoNotDelete\PowerShellScripts\Config\Servers.txt"
foreach($server in $servers)
{
# go to each server and return the name and state of services
# that are like "SQLAgent" and where their state is stopped
get-wmiobject win32_service -computername $server | where-object {$_.name -match "SQL*" -and $_.startmode -ne "disabled" -and $_.name -ne "SQLBrowser"} | `
#select name,state,startmode |
Foreach { if ($_.state -ne "running") {`
$subject = ":( !!! " + $_.name +"service down on " + $server
$body = "The state of SQL service " + $_.name + " has changed to" + $_.state + ". Please look into the issue"
FuncMail -To $TO -From $From -Subject $subject -smtpServer $smtpserver -body $body
}
}
}
######################### Script Logic ################################
Alert for low disk space
If you have been reading my blog, you know by now that I like to get alerts for failures or warnings. Who wants to go and check things on individual servers, am I right? Also, I do not like to use monitoring software – if you are a DBA you possibly know what I mean.
I use a dedicated instance of SQL Server to run my monitoring scripts etc. I schedule a SQL Agent job on this monitoring instance named Alert_LowDiskSpace which runs following PowerShell script every hour (feel free to change frequency depending on you needs) during week days between 6 AM to 11 PM only. I run different schedule for weekends if required, or keep it same as weekdays – depends on your needs.
The script basically reads through a text file with list of servers that need to be monitored, checks free space available and sends alerts if thresholds are reached ( I used different thresholds for system and non-system drives).
I am not pasting a sample email alert here, but rest assured the email is going to look beautiful and will be meaningful – just try it.
POWERSHELL SCRIPT:
######################### DEFINING VARIABLES ################################
$TO = “DBA Team<xyzabc@abc.com>”
$From = “SQL Server DB Alert<dbamonitoring@abc.com>”
$SMTPServer = “xyzsmtpserver.abc.com”
$PercentFreeThreshold = 10
$PercentFreeThresholdForC = 5
######################### DEFINING FUNCTIONS ################################
# (FuncMail) Function to send email
function FuncMail {
param($To, $From, $Subject, $Body, $smtpServer)
$msg = new-object Net.Mail.MailMessage #creating a mail object
$smtp = new-object Net.Mail.SmtpClient($smtpServer) #creating SMTP server object
#Email Structure
$msg.From = $From
$msg.To.Add($To)
$msg.Subject = $Subject
$msg.IsBodyHtml = 1
$msg.Body = $Body
#$mailmessage.Priority = [system.net.mail.mailpriority]::high
$smtp.Send($msg)
}
######################### Script Logic ################################
foreach ($ServerName in get-content “\\abcServerName\D$\DBA_DoNotDelete\PowerShellScripts\Config\Servers.txt”)
{
Get-WMIObject Win32_LogicalDisk -filter “DriveType=3” -computer $ServerName | where-object {$_.deviceID -ne “C:”}`
| Select SystemName,DeviceID,VolumeName,@{Name=”GBTotal”; Expression={“{0:N1}” -f($_.size/1gb)}},@{Name=”GBFree”;Expression={“{0:N1}” -f($_.freespace/1gb)}},@{Name=”PercentFree”;Expression={“{0:N2}” -f(($_.freespace/$_.Size)*100)}}, @{Name=”PercentFull”;Expression={“{0:N2}” -f((100)-(($_.freespace/$_.Size)*100))}} `
| Foreach {
if ([decimal]$_.PercentFree -lt [decimal]$PercentFreeThreshold -and [decimal]$_.GBTotal -gt 0)
{
$subject = “:( !!! ” + $_.DeviceID +” Drive – ” + $_.PercentFull + “% Full on server $servername”
$body = “The server ” + $_.SystemName + ” has only ” + $_.GBFree + ” GB free space remaining on ” + $_.DeviceID + ” Drive out of total ” + $_.GBTotal + ” GB available space. Please attend to the issue immediately.”
FuncMail -To $To -From $From -Subject $subject -smtpServer $smtpserver -body $body
}
}
}
foreach ($ServerName in get-content “\\abcServerName\D$\DBA_DoNotDelete\PowerShellScripts\Config\Servers.txt”)
{
Get-WMIObject Win32_LogicalDisk -filter “DriveType=3” -computer $ServerName | where-object {$_.deviceID -eq “C:”}`
| Select SystemName,DeviceID,VolumeName,@{Name=”GBTotal”; Expression={“{0:N1}” -f($_.size/1gb)}},@{Name=”GBFree”;Expression={“{0:N1}” -f($_.freespace/1gb)}},@{Name=”PercentFree”;Expression={“{0:N2}” -f(($_.freespace/$_.Size)*100)}}, @{Name=”PercentFull”;Expression={“{0:N2}” -f((100)-(($_.freespace/$_.Size)*100))}} `
| Foreach {
if ([decimal]$_.PercentFree -lt [decimal]$PercentFreeThresholdForC -and [decimal]$_.GBTotal -gt 0)
{
$subject = “:( !!! ” + $_.DeviceID +” Drive – ” + $_.PercentFull + “% Full on server $servername”
$body = “The server ” + $_.SystemName + ” has only ” + $_.GBFree + ” GB free space remaining on ” + $_.DeviceID + ” Drive out of total ” + $_.GBTotal + ” GB available space. Please attend to the issue immediately.”
FuncMail -To $To -From $From -Subject $subject -smtpServer $smtpserver -body $body
}
}
}
PREREQUISITES FOR FAIL-OVER CLUSTER INSTALL FOR SQL SERVER 2008
OVERVIEW
This document is only intended for SQL Server 2008/2008 R2 cluster Install on Windows Server 2008/ 2008 R2 cluster using SQL Server Integrated Install method. (It is assumed that we are only using 64-bit versions)
PREREQUISITES
- Microsoft Cluster Server (MSCS) on windows server 2008/2008 R2 (Enterprise Edition) must be installed and working properly. The Cluster Validation Report must show all the tests are successful.
- Quorum disk should be on a separate cluster disk only used for quorum (> 500 MB size). No other resource should share the same disk
- .Net framework5 SP1 must be installed/enabled
- Microsoft Windows Installer5 or later version must be installed/enabled
- A combination of one or more cluster disks in Microsoft Cluster Service (MSCS) cluster group, aka a resource group is required. Each resource group can contain at most one Instance of SQL Server. If management decides to use only one cluster disk for all data/log/tempdb files, a minimum of one cluster disk is required per SQL Server clustered Instance. If the disks are to be separated one resource group should have in it – all the clustered disks related to the one SQL Server instance.
Notes from MSDN: SQL Server supports mount points; the clustered installations of SQL Server are limited to the number of available drive letters. Assuming that you use only one drive letter for the operating system, and all other drive letters are available as normal cluster drives or cluster drives hosting mount points, you are limited to a maximum of 25 instances of SQL Server per failover cluster.
A mounted volume, or mount point, allows you to use a single drive letter to refer to many disks or volumes. If you have a drive letter D: that refers to a regular disk or volume, you can connect or “mount” additional disks or volumes as directories under drive letter D: without the additional disks or volumes requiring drive letters of their own.
Additional mount point considerations for SQL Server failover clustering:
i. SQL Server Setup requires that the base drive of a mounted drive has an associated drive letter. For failover cluster installations, this base drive must be a clustered drive. Volume GUIDs are not supported in this release.
ii. The base drive, the one with the drive letter, cannot be shared among failover cluster instances. This is a normal restriction for failover clusters, but is not a restriction on stand-alone, multi-instance servers
iii. Take extra care when setting up your failover cluster to ensure that both the base drive and the mounted disks or volumes are all listed as resources in the resource group. SQL Server Setup validates drive configuration as part of a failover cluster installation.
SQL Server Setup automatically sets dependencies between the SQL Server cluster group and the disks that will be in the failover cluster. Do not set dependencies for disks before Setup.
- Service account and password on which the SQL Services will run is required. This account needs to be a general domain service account with no elevated permissions. Our naming standard for SQL Server service accounts is: sql_<server_name>_svc (In case of cluster use cluster_name instead of server_name). (This must be different from the account MSCS is running under. Also note the account for MSCS should have permissions to create computer objects on AD. Please make sure passwords for all accounts are saved in the password repository)
- Domain Admin Group for DBA with Admin privileges on both physical nodes. If domain group for DBAs do not exist on the domain-of which the server is part of, Server Team needs to create a group on the domain. Additionally TSS Server Team will need to assign Administrator privileges on the server to the DBA domain group.
- A unique name (network name) is required to connect to a clustered instance of SQL Server (For stand-alone instance machine name is used). This network name appears on the network as if it were a single computer and should be decided upon before SQL Server failover cluster install.
- A minimum of one IP address is required for the clustered SQL Server Instance aka virtual IP address. Clustered SQL Server instance doesn’t listen on IP addresses of the local servers but the Virtual IP, hence the IP should be unique to the domain.
- Instance name (Named Instance) of the instance to be installed if it is a Named Instance and not a Default Instance. The Instance name should be unique across all nodes taking part in the cluster.
- Microsoft Distributed Transaction Coordinator (MSDTC) cluster resource must be created before installing SQL Server failover cluster if the requirement is to use MSDTC for distributed queries it.
- SQL Server TCP port number is required. All named instances of SQL Server by default listen on dynamic ports. It is one of the best practices to make SQL Server instance listens on a static TCP port. TCP 1433 is reserved for Default instances and UDP 1434 is reserved for SQL Server browser service. Carefully choose a TCP port which is not default port for any other application and is not currently in use by any other application. (In some cases changing the SQL Server port to a non-default static port will require additional steps to change registry on SAN)
- Antivirus software used must be supported for Cluster services and should be cluster aware. Some antivirus vendors also have cluster-aware versions. Check with the manufacturer of the antivirus software. Please note that antivirus software can cause issues of resource failure if it does not support MSCS and disabling the antivirus software will not make issues go away. The antivirus software will have to be un-installed if it is identified as cause of resource failures.
Notes from MSDN: Additionally, you should exclude the following file system locations from virus scanning on a server that is running Cluster Services:
i. The path of the \mscs folder on the quorum hard disk. For example, exclude the Q:\mscs folder from virus scanning.
ii. The %Systemroot%\Cluster folder.
iii. The temp folder for the Cluster Service account. For example, exclude the \clusterserviceaccount\Local Settings\Temp folder from virus scanning.
- The disk where SQL Server will be installed must be uncompressed. SQL Server setup will fail if you attempt to install it on a compressed drive. There are additional requirements for the install drive which are not listed here like for example the drives need to be formatted using NTFS. Since these things are standard they are not included. Please refer MSDN for details. Other standard points such as the cluster group names must not contain special characters are also not listed assuming these are normal practices.
- Copy the SQL Server media on the local drive of the node (make sure the media is of correct version and edition) SQL Server needs to be installed on. Please note that integrated method of install for SQL Server cluster requires running ‘add a node’ setup on all secondary nodes. Please use media copied on local drives on the nodes for installation.
- Information regarding which SQL Server services needs to be installed. The document does not include specific steps to install SSIS, SSAS and SSRS. Note that SSRS installation on cluster differs from standard install and since SSRS is not cluster aware, SSRS on each node has to be joined to the cluster farm. Most requirement if not all are to install only DB Engine, replication and full text search and not SSIS, SSRS or SSAS on DB Servers, hence that discussion is not included.
- Ask server team to verify that NetBIOS is disabled for all private network cards before beginning SQL Server cluster setup.
- The network name and IP address of SQL Server should not be used for any other purpose, such as file sharing. If you want to create a file share resource, use a different unique network name and IP address for the resource. Microsoft recommends that you do not use file shares on data drives, as they can affect SQL Server behavior and performance. Please make sure a file share resource is created (with its own IP address and cluster disk for backup if you are going to backup locally and you need the backup drive to be cluster aware too)
- To ensure correct failover cluster functionality, add exceptions to firewall configuration settings for the SQL Server port, SQL Browser port, File and Printer Sharing (TCP 139/445 and UDP 137/138), and Remote Procedure Call (TCP port 135). Only applies if there is a firewall between nodes or windows firewall is enabled on nodes.
- The LooksAlive and IsAlive polling intervals can be changed in MSCS Cluster Administrator from the Advanced tab for the SQL Server resource or by using the Cluster.exe command prompt utility. The default values are 5 seconds and 60 seconds respectively. The values should be left default unless otherwise required for a particular setup. (Make sure that the account that Cluster Service is running under should at least have public rights to SQL Server (DBA responsibility)
- Make sure ISA Server is not used. It’s not supported on either Windows clustering or SQL Server failover clusters.
- Remote Registry Services must be running.
- Remote Administration must be enabled
- To use encryption, there are separate steps listed on MSDN. Follow the steps (get and install certificate for cluster virtual name and install it on both nodes and enable Force protocol encryption in SQL Server Configuration Manger to configure failover cluster for encryption. Encryption discussion is not included in this document.
Additional considerations before installing SQL Server failover cluster on MSCS is listed under following MSDN Article:
http://msdn.microsoft.com/en-us/library/ms189910(v=sql.105).aspx
Stand-alone instance install check list
It’s best to keep a checklist somewhere in your documentation area which can be used by all your DBAs in order to make the SQL Server Installs Standard.
Following is a generic checklist for stand-alone installs only.
Sr.No. SQL Server Install tasks
1 Create SQL Server data and backup directories on server
2 Make sure correct version of .net framework is installed on server
3 Get correct edition and version of SQL Server media staged on the server
4 Request a domain service account to be used to run SQL Services
5 Request Domain group for DBAs to be added as administrator on the server
6 Make sure correct version of windows operating system is installed on the server
7 Make sure all the disk group partitions for data, log, tempdb and backup are available
8 Get a SQL Server Instance Name approved if it’s not a default instance
9 Get information on which services will be needed (i.e. SSRS/SSIS/SSAS etc.)
10 If SSRS is to be installed get information on which mode i.e SharePoint or Native
11 Get infromation on which port will SQL Services need to listen on
12 Make sure browser service is running and UDP port for it is open in case of named instance
13 Get infromation on how users plan to connect to SQL Server
14 Enable Fast File Initialization
15 Enable Lock Pages in memory
16 Add SQL Server file extensions to Antiviurs scan exceptions
17 Install SQL Server using stadard installation procedures and information gathered.
18 Create SQL Server operators (DBA operator in specific) using only distribution lists
19 Configure SQL Server max and min server memory
20 Change SQL Server log retention number to 99
21 Tweak default settings of auto growth and recover model for model database
22 Enable database mail
23 Create public profile to get the notifications
24 Enable Agent Mail and Agent failsafe operator
25 Create SQL Server alerts for major severity levels and error numbers
26 Create maintenance plan for backing up databases and make sure it’s working fine
27 Create Rebuild index job
28 Create a trigger for getting notification of database creation
29 Limit SQL server logs to 99 before recycling in registry
30 Create application users or any other users/logins required
31 Install latest service pack available for the version installed
32 Add server to daily monitoring configuration file
33 Add password for ‘sa’ and any other passwords to password repository
Running CheckDB commands regularly
All of us know CheckDB commands need to run on all the databases regularly to make sure there is no corruption. This regular check must have 2 parts. One to check if there are any consistency and second to notify DBA about it so that DBA can take action before it’s too late ergo a ‘clean backup file’ is cleaned up. Initially I tried PowerShell to do this regularly, but trust me it’s so much simpler to use T-SQL. It’s almost surprisingly dumb. Here is the script:
USE MASTER
SET NOCOUNT ON
SELECT ROW_NUMBER() OVER(ORDER BY name) Seq, name Bank
INTO #Databases
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
AND compatibility_level > 80
ORDER BY Bank
DECLARE
@Loop INT = 1,
@Qt INT = (SELECT COUNT(1) FROM #Databases),
@Bank VARCHAR(50);
WHILE @Loop <= @Qt
BEGIN
SET @Bank = (SELECT Bank FROM #Databases WHERE Seq = @Loop);
EXEC(
'USE ' + @Bank + '; ' +
'PRINT ''Database in use: '' + db_name();
DBCC CHECKDB with no_infomsgs, physical_only');
SET @Loop = @Loop + 1;
END
DROP TABLE #Databases;
Schedule this script to run as agent job. I schedule one for user databases (weekly schedule) and one for system databases (daily schedule). Also, I only do physical check since I believe that’s enough for my environment, feel free to modify script according to your need.
If there are any consistency errors, the job will fail. Add a notification to the job to notify you if the job fails, and there you have it: you will know when there is a consistency error in one of your databases.
I plan to write a script to check ‘last good time’ i.e. to check when did last successful checkdb run, to notify DBA that everything is fine. This way you have two checks – 1. you will be notified all checkdbs successfully finished and 2. you will be notified when there is an error. I always want both success and failure message. Also, I like to consolidate success message in one email for all servers. You will see this approach in all monitoring scripts I post.
See you in next post & keep checking logs! 😉
ABOUT THIS BLOG
I thought of writing this blog to help myself and other SQL Server DBAs out there. It really helps me because all the important scripts I need are online and I can access them from anywhere. Also, other DBAs can take something if they like it.
– Mohammed Shaikh
Rebuild all fragmented indexes in all databases on an instance
Index Rebuild is one of the most important recurring DBA task, am I right? Only if there was a way to do it programmatically (out of box with maintenance plans) such that only fragmented indexes are rebuilt and not all at once ! I am sure you all know about http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Great solution, I just don’t like to create custom stored procedures on servers though. I have to say, the solution by Ola Hallegren is the best if you can maintain it and don’t mind creating stored procedures or a tools database where you keep all your custom stored procs etc.
I just want a simple T-SQL Script which I can schedule as a job. I got this code from some site I cannot find now, to give due credit to the author! Anyway, I have done a little modifications of my own. The script works great, rebuilds indexes one by one based on fragmentation percentage. I can also exclude some databases if I want. And best of all it doesn’t use cursor ( I just don’t like cursors, sorry!).
Note: Please change the code to include ‘online’ or ‘sort in tempdb’ options depending on the edition you are using. Also, this will not work on SQL 2000 since you would need to look up ‘scan density’ from the results of DBCC SHOWCONTIG in 2000.
USE MASTER
GO
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER(ORDER BY name) Seq, name Bank
INTO #Databases
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB')
AND compatibility_level > 80
ORDER BY Bank;
DECLARE
@Loop INT = 1,
@Qt INT = (SELECT COUNT(1) FROM #Databases),
@Bank VARCHAR(50);
WHILE @Loop <= @Qt BEGIN SET @Bank = (SELECT Bank FROM #Databases WHERE Seq = @Loop); EXEC( 'USE [' + @Bank + ']; ' + 'PRINT ''Database in use: '' + db_name(); SELECT ROW_NUMBER() OVER(ORDER BY p.object_id, p.index_id) Seq, t.name TableName, h.name SchemaName, i.name IndexName, p.avg_fragmentation_in_percent Frag INTO #Consult FROM sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null) p join sys.indexes i on (p.object_id = i.object_id and p.index_id = i.index_id) join sys.tables t on (p.object_id = t.object_id) join sys.schemas h on (t.schema_id = h.schema_id) where p.avg_fragmentation_in_percent > 10.0
and p.index_id > 0
and p.page_count >= 50
ORDER BY SchemaName, TableName;
DECLARE
@Loop INT = 1,
@Total INT = (SELECT COUNT(1) FROM #Consult),
@CMD VARCHAR(500)
WHILE @Loop <= @Total BEGIN SELECT @CMD = ''ALTER INDEX ['' + IndexName + ''] ON ['' + SchemaName + ''].['' + TableName + ''] '' + ( CASE WHEN Frag > 30.0 THEN '' REBUILD'' ELSE '' REORGANIZE'' END)
FROM #Consult
WHERE Seq = @Loop;
EXEC(@CMD);
PRINT ''executed: '' + @CMD;
SET @Loop = @Loop + 1;
END;
PRINT DB_NAME() + '' Qty affected indexes: '' + CONVERT(VARCHAR(5),@Total);
PRINT ''========'';
DROP TABLE #Consult;');
SET @Loop = @Loop + 1;
END;
DROP TABLE #Databases;
SQL Agent Jobs failed
One of the things we DBAs look for is if there were any failed jobs. Although each failed job will send notification, at times depending on where you are working, we may or may not get failed job alerts. To double check, all jobs finished fine, run the script below to see if there are any jobs with last run status as ‘failed’.
I haven’t worked out a PowerShell script for this yet which will send an HTML email alert of all the jobs that failed every morning. The second part of the script is to check the jobs which are running currently. You may notice a few jobs which are not supposed to be running (for example they are nightly jobs and should have been finished when you run this script in the morning).
Run this on a center management server or as multi-server query on your registered servers.
--Check jobs with last run status as 'failed'
use msdb
go
select h.server as [Server],
j.[name] as [Name],
h.message as [Message],
h.run_date as LastRunDate,
h.run_time as LastRunTime
from sysjobhistory h
inner join sysjobs j on h.job_id = j.job_id
where j.enabled = 1
and h.instance_id in
(select max(h.instance_id)
from sysjobhistory h group by (h.job_id))
and h.run_status = 0
go
--Check jobs with status as 'executing'
exec msdb..sp_help_job @execution_status = 1
go
ONESQL 