Enabling Service Broker in SQL Server

Today while creating a database for one of our new projects came across with an error:

“The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications”

Now, the question I had in front of me was………

How do I enable Service Broker in SQL server 2008 R2 database? 

I tried executing the below query…..

ALTER DATABASE SET ENABLE_BROKER but it went into an infinite loop.

Again executed the query and got error- ‘database is in use.’

Use Master

ALTER DATABASE [DBNAME] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

And after a long R&D, I killed the entire session id connected to the database, in which I was suppose to enable the broker.

So there are two options to enable the broker.  

Option 1-

Just run this script, it will kill all the process's that a database is using and then set the broker

USE master

go

DECLARE @dbname sysname

SET @dbname = 'YourDBName'

DECLARE @spid int

SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)

WHILE @spid IS NOT NULL

BEGIN

EXECUTE ('KILL ' + @spid)

SELECT @spid = min( spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid

END

ALTER DATABASE @dbname SET ENABLE_BROKER

Option 2 –

·         Connect SQL server management studio

·         Right click on your database

·         Go to  Properties->  Options ->  Service Broker

 Reference taken from stackoverflow.com

Repair a suspect database In MS SQL

Incident:

Yesterday I got a mail from database alert (Automated mail alert) informing me that one of our databases located on the test server went into Suspect Mode. The version used was SQL Server 2005 Service Pack 3.

Solution:

The first step was to identify why this incident occurred and after investigation it was found that it was due to the corruption of the transnational log file of the database.

I connected to SSMS using the sa login credentials and located the SUSPECT database:

Other Possible Causes

• The database could have become corrupted.
• The database cannot be opened due to inaccessible files or insufficient memory or disk space.
• There was an unexpected SQL Server Shutdown, power failure or a hardware failure.

After some RND, I found a set of queries to get database back in normal mode.

EXEC sp_resetstatus 'DBname';

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb('DBname')

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

Below script will help you to get email notification for 'Suspected Database:

All you have to do is schedules this script in SQL Agent or maintenance plan.

if(select count(*) from sys.databases where state=4)>0

Begin

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SGDBA',--replace with your profine name

    @recipients = 'admi@spluspl.com', --Replace with your email for group email

    @query = '  set nocount off

    select name, state from sys.databases where state = 4',

    @subject = 'Suspected Database',

    @Body = 'Body' ,

    @importance='high',

    @attach_query_result_as_file = 0 ;

END

Else Print 'no suspect databases'

Reference taken from codeproject  and support managed.

Rebuilding all table indexes

Index fragmentation can occur when any large data modification operations happen (INSERT, UPDATE, or DELETE statements) on tables.

For DBAs to maintain multiple databases and monitoring the disk fragmentation and rebuilding the indexes for individual database is quite time consuming task. Rebuilding an index drops and re-creates the index. This removes fragmentation and reorders the index rows in contiguous pages.

So we can use DBCC DBREINDEX statement to rebuild all the indexes on all the tables in database.

DBCC DBREINDEX is efficient over dropping and recreating indexes.

This script can be used in a maintenance plan and can be executed in a scheduled job.

--Option 1

USE YourDBName

GO

EXEC sp_MSforeachtable@command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

GO

EXEC sp_updatestats

GO

--Option 2

USE YourDBName

GO

CREATE PROCEDURE ReIndexDB_N_UpdateStats

AS

DECLARE @Table VARCHAR(255)

DECLARE IXCursor

CURSOR FOR

SELECT table_name

FROM information_schema.tables

WHERE table_type = 'base table'

OPEN IXCursor

FETCH NEXT

FROM IXCursor INTO @MyTable

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'Reindexing Table:  ' + @MyTable

DBCC DBREINDEX(@Table, '', 80)

FETCH NEXT

FROM IXCursor INTO @Table

END

CLOSE IXCursor

DEALLOCATE IXCursor

EXEC sp_updatestats

GO

Execution of Stored Procedure sp_updatestats at the end of the Indexes process ensures updating stats of the database.

Click Here for more details 

SQL SERVER Monitoring Script

Problem

As  a SQL Server DBA, dedicated to monitoring and administering many busy OLTP production environments, I always find myself trying to answer questions like:

What was SQL Server doing last night when the end users were experiencing slow application response times? 
How much time the ABC job taken for execution?
Why the A job get failed?
Are the SQL services running?
What is the OLAP DB size?
Is the backup plan executed properly and what the size of the BKP files.

The answers to such questions are never easy to identify. So how can I figure this out? 


Solution


The following script comes in handy because it captures the code the SQL Server engine is processing at any point in time and delivers it on email with following information.  

Here is that query:

       

CREATE PROC ServerInfo         

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)  ,@tableHTML VARCHAR(8000)       

DECLARE @dbcount int   

   

select @dbcount=count (*) FROM sys.databases WHERE state_desc <> 'ONLINE'       

   

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+' : DB Monitor Alert'         

SET @HTML=       

N'<style type="text/css">       

#box-table       

{       

font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;       

font-size: 12px;       

text-align: center;       

border-collapse: collapse;       

border-top: 7px solid #9baff1;       

border-bottom: 7px solid #9baff1;       

}       

#box-table th       

{       

font-size: 13px;        

font-weight: normal;       

background: #b9c9fe;       

border-right: 2px solid #9baff1;       

border-left: 2px solid #9baff1;       

border-bottom: 2px solid #9baff1;       

color: #039;       

}       

#box-table td       

{       

border-right: 1px solid #aabcfe;       

border-left: 1px solid #aabcfe;       

border-bottom: 1px solid #aabcfe;       

color: #669;       

}       

tr:nth-child(odd) { background-color:#eee; }       

tr:nth-child(even) { background-color:#fff; }         

</style>'+       

       

'<h3><font color="Green"> -:: '+@sname+' ::- </h3><br>'+'       

<table id="box-table">       

<tr><font color="Green"       

<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>'    

if (@dbcount >0 )   

begin      

set @HTML=@HTML + N'<H3><font color="Red"> -:: Off-Line Database Details ::- </H3>' +       

N'<table id="box-table" >' +       

N'<tr><font color="Green"><th>Database Name</th><th>Status</th><th>Created Date</th>       

</tr>' +       

CAST ( (        

SELECT td = CAST([name] AS VARCHAR(200)),'',       

td = [state_desc],'',       

td = CONVERT(VARCHAR(30),[create_date],120)        

FROM sys.databases WHERE state_desc <> 'ONLINE'        

ORDER BY [name]       

FOR XML PATH('tr'), TYPE        

) AS NVARCHAR(MAX) ) +       

N'</table>' +   

    

'<h3><font color="Green"> -:: Job Notification ::- </h3><br>'+'       

<table id="box-table">       

<tr><font color="Green"       

<th>Server Name</th>       

<th>Job Name</th>       

<th>Job Status</th>        

<th>Last Run Status</th>       

<th>Last Date Run</th>       

<th>Run Duration</th>       

</tr>' +        

CAST ( (        

SELECT td = CAST(CAST(Serverproperty('servername') as varchar(100)) AS VARCHAR(200)),'',      

td = j.name,'',       

td = CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END,'',       

td = CASE jh.run_status WHEN 0 THEN 'Error Failed'     

    WHEN 1 THEN 'Succeeded'     

    WHEN 2 THEN 'Retry'     

    WHEN 3 THEN 'Cancelled'     

    WHEN 4 THEN 'In Progress' ELSE     

    'Status Unknown' END ,'',     

         

td = isnull(convert(varchar(12),ja.run_requested_date,107),'-'),'',       

td = isnull(CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108),'-')     

     

FROM(msdb.dbo.sysjobactivity ja      

LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)     

join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id     

WHERE ja.session_id=(SELECT MAX(session_id)  from msdb.dbo.sysjobactivity)  and j.job_id in ( select job_id from msdb.dbo.sysjobs where enabled=1)     

ORDER BY j.name     

FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +       

N'</table>'        

end   

else   

set @HTML=@HTML + N'<H3><font color="Green"> -:: Off-Line Database Details ::- </H3>' +   

N'<table id="box-table" >' +       

N'<tr><font color="Green"><th>Database Name</th><th>Status</th><th>Created Date</th>       

</tr>'+       

CAST ( (        

SELECT td = 'No Database Available','',    

td = '-','',          

td = CONVERT(VARCHAR(30),getdate(),120)        

FOR XML PATH('tr'), TYPE        

) AS NVARCHAR(MAX) ) +       

N'</table>'  +   

    

'<h3><font color="Green"> -:: Job Notification ::- </h3><br>'+'       

<table id="box-table">       

<tr><font color="Green"       

<th>Server Name</th>       

<th>Job Name</th>       

<th>Job Status</th>        

<th>Last Run Status</th>       

<th>Last Date Run</th>       

<th>Run Duration</th>       

</tr>' +       

CAST ( (        

SELECT td = CAST(CAST(Serverproperty('servername') as varchar(100)) AS VARCHAR(200)),'',      

td = j.name,'',       

td = CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END,'',       

td = CASE jh.run_status WHEN 0 THEN 'Error Failed'     

    WHEN 1 THEN 'Succeeded'     

    WHEN 2 THEN 'Retry'     

    WHEN 3 THEN 'Cancelled'     

    WHEN 4 THEN 'In Progress' ELSE     

    'Status Unknown' END ,'',     

         

td = isnull(convert(varchar(12),ja.run_requested_date,107),'-'),'',       

td = isnull(CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108),'-')     

     

FROM(msdb.dbo.sysjobactivity ja      

LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)     

join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id     

WHERE ja.session_id=(SELECT MAX(session_id)  from msdb.dbo.sysjobactivity)  and j.job_id in ( select job_id from msdb.dbo.sysjobs where enabled=1)     

ORDER BY j.name     

FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +       

N'</table>'        

       

print @HTML        

--print @tableHTML       

       

EXEC msdb.dbo.sp_send_dbmail         

@recipients ='dbaadmin@abc.com', --replace with your email id          

@subject=@subject,         

@body =@HTML,

@body_format ='HTML'