Tuesday, June 17, 2014

Last good dbcc checkdb to all databases in sql server

 CREATE TABLE #tempTotal
(
DatabaseName varchar(255),
Field VARCHAR(255),
Value VARCHAR(255)
)
 CREATE TABLE #temp
 (
 ParentObject VARCHAR(255),
 Object VARCHAR(255),
 Field VARCHAR(255),
 Value VARCHAR(255)
 )
 EXECUTE sp_MSforeachdb '
 INSERT INTO #temp EXEC(''DBCC DBINFO ( ''''?'''') WITH TABLERESULTS'')
 INSERT INTO #tempTotal (Field, Value, DatabaseName)
 SELECT Field, Value, ''?'' FROM #temp
 TRUNCATE TABLE #temp';
 ;WITH cte as
 (
 SELECT
 ROW_NUMBER() OVER(PARTITION BY DatabaseName, Field ORDER BY Value DESC) AS rn,
 DatabaseName,
 Value
 FROM #tempTotal t1
 WHERE (Field = 'dbi_dbccLastKnownGood')
 )
 SELECT
 DatabaseName,
 Value as dbccLastKnownGood
 FROM cte
 WHERE (rn = 1)
 DROP TABLE #temp
 DROP TABLE #tempTotal

Friday, June 13, 2014

Long Running Queries notification jobn


Create this stored procedure in MSDB Database : this will fetch the queries which are running more than 5 min. Long running queries information will stored in MSDB.dbo.LongRunningQuery_History table and send notification to configured mail team.

  USE MSDB
GO
CREATE  PROCEDURE sp_longRunningQueryNotification
AS
IF (OBJECT_ID('MSDB.[dbo].[LongRunningQuery_History]') IS NULL)
BEGIN
CREATE TABLE DBAAdmin.[dbo].[LongRunningQuery_History](
[session_id] [smallint] NOT NULL
,[start_time] [datetime] NOT NULL
,[total_elapsed_time] [int] NOT NULL
,[status] [nvarchar](30)
,[command] [nvarchar](30) NOT NULL
,[database_id] [VARchar](100) NOT NULL
,[last_wait_type] [nvarchar](100)
,[wait_resource] [nvarchar](256)
,[cpu_time] [int] NOT NULL
,[reads] [bigint] NOT NULL
,[writes] [bigint] NOT NULL
,[logical_reads] [bigint] NOT NULL
,[cmdText] [varchar] (MAX)
,[user_name] [varchar] (100)
, [date] datetime
) ON [PRIMARY]
END

Declare @count varchar(100)
SELECT @count =  count(*) FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS p WHERE r.total_elapsed_time > 300000
IF (@count)>= 1
BEGIN

DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Long Running Queries on '+ cast (@@servername as varchar(30)) +'</H1>' +
N'<table border="1">' +
N'<tr><th>Session_ID</th><th>Elapsed_Time_in_Mins</th><th>Command</th><th>Login_Name</th>' +
N'<th>Database_Name</th>' +
CAST ( ( SELECT td = CAST(r.session_id AS VARCHAR(10)), '',
td = CAST((r.total_elapsed_time)/60000 AS VARCHAR(10)), '',
td = CAST(t.[text] AS VARCHAR(1000)) ,'',
td = CAST(s.loginame AS VARCHAR(1000)) ,''
, td = CAST(DB_Name(r.database_id) AS VARCHAR(100) ),''
--, r.wait_type
--, r.last_wait_type
--, r.wait_resource
--, r.cpu_time
--, r.reads
--, r.writes
--, r.logical_reads
--, t.[text] AS [executing batch]
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY
sys.dm_exec_query_plan(r.plan_handle) AS p
Left Join sys.sysprocesses s on s.spid = r.session_id
WHERE r.total_elapsed_time > 300000
ORDER BY
r.total_elapsed_time DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

DECLARE @vcsubject varchar(1000)
Set @vcsubject ='Long Running Queries in ' + cast (@@servername as varchar(30))+ ', Please start monitoring'

EXEC msdb.dbo.sp_send_dbmail @recipients='dbateam@gmail.com',
   @profile_name = 'SQLDBA_Profile',
@subject = @vcsubject,
@body = @tableHTML ,
@body_format = 'HTML' ;

INSERT INTO dbo.LongRunningQuery_History SELECT r.session_id, r.start_time, TotalElapsedTime_ms = r.total_elapsed_time, r.[status], r.command, DatabaseName = DB_Name(r.database_id), r.last_wait_type, r.wait_resource, r.cpu_time, r.reads, r.writes, r.logical_reads, t.[text] , s.loginame, GETDATE() FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS p Left Join sys.sysprocesses s on s.spid = r.session_id WHERE r.total_elapsed_time > 300000

END

DELETE FROM dbo.LongRunningQuery_History WHERE [Date] < (GETDATE() - 30)



Below is the script for creating a scheduled SQL job to monitor Long running queries.

USE [msdb]
GO

/****** Object:  Job [M_SQLDBA_LongRunningQueries]    Script Date: 06/13/2014 14:10:59 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 06/13/2014 14:10:59 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'M_SQLDBA_LongRunningQueries', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'No description available.', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [running]    Script Date: 06/13/2014 14:10:59 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'running', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'Exec sp_longRunningQueryNotification', 
@database_name=N'MSDB', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'LongRunningQueries', 
@enabled=1, 
@freq_type=4, 
@freq_interval=1, 
@freq_subday_type=4, 
@freq_subday_interval=15, 
@freq_relative_interval=0, 
@freq_recurrence_factor=0, 
@active_start_date=20140612, 
@active_end_date=99991231, 
@active_start_time=0, 
@active_end_time=235959, 
@schedule_uid=N'e7b3e53a-e7b4-4d98-b040-1ba256b7af5a'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...