I’ve written a script to find the job activity details, this script will give the output similar to job activity monitor in SQL2K5 or SQL2K8. In my working place we had a request to collect the job details from all the servers which includes all version of SQL Server. I’ve tried sp_help_jobactivity for SQL2K5 \ SQL2k8, however it doesn’t provide the columns needed. So I’ve written a script to get customized output, this script will also work with SQL2K. The script will fetch the following details Read more »
Category Archives: Scripts - Page 2
Script to get row count for all the tables in all the databases
Posted by Vidhya Sagar
on June 13, 2009
2 comments
I’ve written this script to get details of row count in all the tables in all the database including the system database. You can modify the script to exclude system database if you don’t need details for that. You can see that I haven’t use COUNT() function to get these values, If I run using this function for all the db then it will take more time and resource.
Startup procedure to check SQL Server status when restarted
Posted by Vidhya Sagar
on April 23, 2009
Comments Off
I’ve written a startup procedure to check SQL Server status when the SQL Server gets restarted. Most of the time as a DBA when a Server gets rebooted or SQL Services is restarted we need to make sure everything is fine in SQL server to make sure SQL server is running healthy. Some companies will have budget to buy a monitoring tool, so that all these things will be taken care by that tool. This small procedure will help the DBA’s to know the status of SQL Server in HTML format through mail when it’s restarted.
Applies to:
- SQL Server 2005
- SQL Server 2008
Pre-Requisites
- Xp_cmdshell procedure should be in enabled state
- A database mail profile should be configured as default profile (else you need to provide this profile information inside the procedure)
- Script
- This script will monitor the below parameters in SQL Server.
- SQL Server Uptime
- SQL Server Service Status
- SQL Server Agent Status
- Database(s) status
- Authentication mode
/*
Procedure Name : serverstartup
Written By : Vidhya Sagar
www.sql-articles.com
*/
USE master
GO
CREATE PROC serverstartup
AS
SET NOCOUNT ON
IF EXISTS(SELECT 1 FROM tempdb..sysobjects where name ='##sqlservice')
DROP TABLE ##sqlservice
CREATE TABLE ##sqlservice (details VARCHAR(100))
IF EXISTS(SELECT 1 FROM tempdb..sysobjects where name ='##agentservice')
DROP TABLE ##agentservice
CREATE TABLE ##agentservice (details VARCHAR(100))
WAITFOR DELAY '00:02'
DECLARE @sname VARCHAR(100), @starttime VARCHAR(30)
DECLARE @authmode VARCHAR(25), @subject VARCHAR(250)
DECLARE @insname VARCHAR(50),@agentname VARCHAR(50)
DECLARE @sqlstatus VARCHAR(100), @agentstatus VARCHAR(100)
DECLARE @dbstatus VARCHAR(100), @dbdetail VARCHAR(2000)
DECLARE @sctsql VARCHAR(200), @HTML VARCHAR(8000)
SELECT @sname = @@SERVERNAME
SELECT @authmode=case SERVERPROPERTY('IsIntegratedSecurityOnly') when 1 then 'Windows' else 'Mixed' end
SELECT @starttime=CONVERT(VARCHAR(30),create_date,109) from sys.databases where database_id=2
IF (serverproperty('InstanceName')) IS NOT NULL
BEGIN
SET @insname='mssql$'+CONVERT(VARCHAR(40),serverproperty('InstanceName'))
SET @agentname='sqlagent$'+CONVERT(VARCHAR(40),serverproperty('InstanceName'))
END
ELSE
BEGIN
SET @insname='mssqlserver'
SET @agentname='sqlserveragent'
END
SELECT @sctsql='EXEC master.dbo.xp_cmdshell ''sc query '+@insname+' | FIND "STATE"'''
INSERT ##sqlservice
EXEC (@sctsql)
SELECT @sctsql='EXEC master.dbo.xp_cmdshell ''sc query '+@agentname+' | FIND "STATE"'''
INSERT ##agentservice
EXEC (@sctsql)
IF EXISTS(SELECT 1 FROM ##sqlservice WHERE details LIKE '%RUNNING%')
SET @sqlstatus = 'Running'
ELSE
SET @sqlstatus = '<font color="red">Not Running</font>'
IF EXISTS(SELECT 1 FROM ##agentservice WHERE details LIKE '%RUNNING%')
SET @agentstatus = 'Running'
ELSE
SET @agentstatus = '<font color="red">Not Running</font>'
IF EXISTS (SELECT 1 FROM sys.databases WHERE state_desc<>'ONLINE')
BEGIN
SET @dbstatus= '<font color="red">Some of the database(s) are offline</font>'
SELECT @dbdetail = '<table border="1"><tr><th>Database Name</th><th>Database Status</th></tr><tr>'
SELECT @dbdetail = @dbdetail + '<td align="Center">' + name + '</td><td align="Center">'+state_desc+'</td></tr></table>' FROM sys.databases WHERE state_desc<>'ONLINE'
END
ELSE
BEGIN
SET @dbdetail = ''
SET @dbstatus='All databases are online'
END
SET @subject=@sname+' : SQL Server is restarted. Please Check'
SET @HTML='<h3>'+@sname+'</h3><br>'+'<table border="1"><tr><th>SQL Server Startup time</th><th>SQL Server Service</th><th>SQL Agent Service</th> <th>Database(s) Status</th><th>Authentication Mode</th></tr><tr><td align="Center">'+
@starttime+'</td><td align="Center">'+@sqlstatus+'</td><td align="Center">'+@agentstatus+'</td><td align="Center">'+@dbstatus+'</td><td align="Center">'+@authmode+'</td></tr></table><br><br>'+@dbdetail
EXEC msdb.dbo.sp_send_dbmail
@recipients ='kvs1983@indiamvps.net',
@subject=@subject,
@body =@HTML,
@body_format ='HTML'
GO
---Enabling startup parameter for that procedure
EXEC sp_procoption 'serverstartup','startup',True
GO
In the script above there are two parameters that need to be taken care by the implementer.
- WAITFOR – I’ve used this command since this is the time for the database to get into steady state i.e. roll back and roll forward time. If the database is in huge size then the roll back or roll forward will take some time, you need to adjust this value based on the time its taking. By default I’ve set this value to 2 min.
- Mail Recipients – You need to provide the email address to whom the alert mail need to be delivered. In the script above I’ve used my hotmail ID. Please change this to your mail ID. If you don’t have a default mail profile, then use @profile_name parameter in the mail procedure to use the correct mail profile.
Testing
Let’s test the script developed. Since the script is marked for startup when ever SQL Server is restarted this procedure will run and provide the health check status for SQL Server to the mail recipients given in the script.
- When Everything is normal and running fine.
- In this case I’ve stopped SQL Agent service, lets check the alert mail. You can find SQL Agent status as not running.
- Now lets make a database to emergency mode. I’ve created a new database named EMERGENCY_TEST and made it to emergency mode. From the mail below you can check that you can get the db details too.
I’m sharing this procedure since some of the DBA’s might use this procedure to simplify their process.
Your comments are suggestions are always welcomed to improve me better. You can either post your queries @ Forums sections else mail me.
Trigger to Check Job failures – SQL 2000
Posted by Vidhya Sagar
on March 2, 2009
Comments Off
I was trying to find a alert which could fire immediately upon any of the job failure in the server to send an email. Unfortunately I couldn’t find any the alert related to this. So I thought of writing a DML trigger which should fire immediately when an entry is inserted in sysjobhistory table on msdb database. The next step is that the trigger should send a mail when there is failure in the job, hence I’ve used run_status column in the table, as all the DBA’s know when this column has a value 0 then it states the job has been failed. I’ve tried this script in SQL Server greater than version 2000 however it’s not working on it. This script works only on SQL Server 2000, still some of them are still working on SQL 2000 so I thought this script has still value. This DML trigger will send an email upon any of the job failure.
Applies To
- SQL Server 2000
- Script
- In the script below make sure to change the mail address used in the recipients field. You also need SMTP mail to be configured in SQL Server to work however you can modify the script to include CDO messaging.
/*
Written By : Vidhya Sagar
www.sql-articles.com
*/
USE MSDB
GO
CREATE TRIGGER jobfailurenotify
ON sysjobhistory
FOR INSERT
AS
DECLARE @failed TINYINT, @subject VARCHAR(300), @body VARCHAR(500)
SELECT @failed = COUNT(*) from inserted where run_status=0
IF @failed > 0
BEGIN
SELECT @subject=name from msdb.dbo.sysjobs where job_id in (select distinct(job_id) from inserted where run_status=0)
SET @subject=@subject + ' job failed on ' + CONVERT(VARCHAR(25), GETDATE())
SET @body=@subject + '. Please look in to this. Server Name : ' + CONVERT(VARCHAR(25),SERVERPROPERTY('SERVERNAME'))
EXEC master.dbo.xp_sendmail
@recipients = N'kvs1983@gmail.com',
@subject = @subject,
@message = @body
END
ELSE
BEGIN
PRINT 'No job failures found'
END
Sample Mail Output
From: testing@gmail.com
Sent: Monday, March 02, 2009 6:50 PM
To: kvs1983@gmail.com
Subject: TESTING job failed on Mar 2 2009 6:50PM
TESTING job failed on Mar 2 2009 6:50PM. Please look in to this. Server Name : SAGARSYS
If you have any concerns or queries regarding this script. Just post it out in our Forums Section.
Disk space (including mounted drive) Check via SQL Server
Posted by Vidhya Sagar
on February 5, 2009
No comments
In my previous article on “Diskspace Check via SQLServer” will find only physical \ logical drives that is attached to the system i.e the drive which you see under my computer. I’ve used this script in one of my client machine and found that this script is not capable to check disk space for mounted drives. Hence I’ve written an WMI script and SQL Script (like version 1) to fetch both physical disk & mounted disk space details. I hope this would help most of the DBA’s to monitor their mounted voulmes where they have hosted their database files. Read more »