Use this VB script to check SQL Server connectivity. You can add N number of SQL Server instances to verify it’s status.
Tag Archives: Scripts
Change \ Transfer Schema for all Objects
Posted by Vidhya Sagar
on December 21, 2009
No comments
In my working environment we will be using different schema for production and development environments. Our development apps are configured to Dev schema and Production apps are configured to Prod schema, so when we restore the db from production to development environment, all our dev apps stopped working since the objects \ securable will be using prod schema. Here I need to change the schema to dev from prod, so the syntax will be like below
Script to find SQL Job activity details
Posted by Vidhya Sagar
on September 17, 2009
15 comments
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 »
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.