SQL Server for Starters

Check this important link from Microsoft:
=========================================
Incomplete transaction may hold large number of locks and cause blocking:
------------------------------------------------------------------------
http://support.microsoft.com/kb/295108/

Check open transactions in the database:
========================================
1): Create table 'oldtransactions'
----------------------------------:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[oldtransactions](
[db] [varchar](255) NULL,
[urenoud] [int] NULL,
[changed] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
----------------------------
2): Create Job ,on Master database "CheckOpenTran":
--------------------------------------------------
DECLARE map_cursor CURSOR FOR select name from master..sysdatabases where not name in ('master','model','msdb','tempdb','distribution')
OPEN map_cursor
FETCH NEXT FROM map_cursor INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN

create table #temp(field varchar(50),inhoud varchar(50))
insert into #temp
exec ('dbcc opentran('+@db+') with tableresults')
set @urenoud=0
select @urenoud=datediff(n, inhoud, getdate()) from #temp where field='OLDACT_STARTTIME'
drop table #temp
set @urenoud=isnull(@urenoud, 0)
insert oldtransactions(db, urenoud, changed) values (@db, @urenoud, getdate())
FETCH NEXT FROM map_cursor INTO @db
END
CLOSE map_cursor
DEALLOCATE map_cursor
------------------------
3): Schedule to run the job each 15 minutes of the day(Occurs every day every 15 minute(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 1/31/2006.)

---------------------------------------------
4): Create Stored procedure 'CheckOpenTran':
--------------------------------------------
USE [master]
GO
/****** Object: StoredProcedure [dbo].[CheckOpenTran] Script Date: 12/12/2008 13:20:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[CheckOpenTran](
@maxtime int=1
)
as
declare @db varchar(200)
declare @snapshotage int
declare @oldest int

declare @ret int
set @ret=0
declare @description varchar(200)
set @description=''

select @snapshotage=max(datediff(n, changed, getdate())) from oldtransactions

if @snapshotage<17
begin
select top 1 @db=db, @oldest=urenoud from oldtransactions where urenoud>=@maxtime order by urenoud
set @oldest=isnull(@oldest, 0)
if @oldest>0
begin
set @ret=1
set @description=@description+@db+' (transactie '+ cast(@oldest as varchar(5)) + ' minuten oud)'
End
else
begin
set @ret=0
set @description='Transacties ok'
End
end
else
begin
set @ret=1
set @description='Open Transactie SQL Job draaid niet'
end

select ret=@ret, message=@description
---------------------------
5): Execute 'CheckOpenTran' stored procedure when yoe need to check for an open transactions.

USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[checksqljobs] Script Date: 12/09/2008 12:23:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[checksqljobs]
As
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_type VARCHAR(12)
DECLARE @owner_login_name sysname
DECLARE @subsystem NVARCHAR(40)
DECLARE @category_id INT
DECLARE @enabled TINYINT
DECLARE @execution_status INT
DECLARE @date_comparator CHAR(1)
DECLARE @date_created DATETIME
DECLARE @date_last_modified DATETIME
DECLARE @description NVARCHAR(512)

DECLARE @is_sysadmin INT
DECLARE @job_owner sysname

SET NOCOUNT ON

-- Setting om alleen enabled jobs te monitoren
set @enabled =1

-- By 'composite' we mean a combination of sysjobs and xp_sqlagent_enum_jobs data.
-- This proc should only ever be called by sp_help_job, so we don't verify the
-- parameters (sp_help_job has already done this).

-- Step 1: Create intermediate work tables
CREATE TABLE #job_execution_state (job_id UNIQUEIDENTIFIER NOT NULL,
date_started INT NOT NULL,
time_started INT NOT NULL,
execution_job_status INT NOT NULL,
execution_step_id INT NULL,
execution_step_name sysname COLLATE database_default NULL,
execution_retry_attempt INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL)
CREATE TABLE #filtered_jobs (job_id UNIQUEIDENTIFIER NOT NULL,
date_created DATETIME NOT NULL,
date_last_modified DATETIME NOT NULL,
current_execution_status INT NULL,
current_execution_step sysname COLLATE database_default NULL,
current_retry_attempt INT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
last_run_outcome INT NOT NULL,
next_run_date INT NULL,
next_run_time INT NULL,
next_run_schedule_id INT NULL,
type INT NOT NULL)
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

-- Step 2: Capture job execution information (for local jobs only since that's all SQLServerAgent caches)
SELECT @is_sysadmin = 1
SELECT @job_owner = SUSER_SNAME()

IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
ELSE
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

INSERT INTO #job_execution_state
SELECT xpr.job_id,
xpr.last_run_date,
xpr.last_run_time,
xpr.job_state,
sjs.step_id,
sjs.step_name,
xpr.current_retry_attempt,
xpr.next_run_date,
xpr.next_run_time,
xpr.next_run_schedule_id
FROM #xp_results xpr
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
msdb.dbo.sysjobs sjv
WHERE (sjv.job_id = xpr.job_id)

-- Step 3: Filter on everything but dates and job_type
IF ((@subsystem IS NULL) AND
(@owner_login_name IS NULL) AND
(@enabled IS NULL) AND
(@category_id IS NULL) AND
(@execution_status IS NULL) AND
(@description IS NULL) AND
(@job_id IS NULL))
BEGIN
-- Optimize for the frequently used case...
INSERT INTO #filtered_jobs
SELECT sjv.job_id,
sjv.date_created,
sjv.date_modified,
ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in #job_execution_state (NOTE: 4 = STATE_IDLE)
CASE ISNULL(jes.execution_step_id, 0)
WHEN 0 THEN NULL -- Will be NULL if the job is non-local or is not in #job_execution_state
ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
END,
jes.execution_retry_attempt, -- Will be NULL if the job is non-local or is not in #job_execution_state
0, -- last_run_date placeholder (we'll fix it up in step 3.3)
0, -- last_run_time placeholder (we'll fix it up in step 3.3)
5, -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
jes.next_run_date, -- Will be NULL if the job is non-local or is not in #job_execution_state
jes.next_run_time, -- Will be NULL if the job is non-local or is not in #job_execution_state
jes.next_run_schedule_id, -- Will be NULL if the job is non-local or is not in #job_execution_state
0 -- type placeholder (we'll fix it up in step 3.4)
FROM msdb.dbo.sysjobs sjv
LEFT OUTER JOIN #job_execution_state jes ON (sjv.job_id = jes.job_id)
END
ELSE
BEGIN
INSERT INTO #filtered_jobs
SELECT DISTINCT
sjv.job_id,
sjv.date_created,
sjv.date_modified,
ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in #job_execution_state (NOTE: 4 = STATE_IDLE)
CASE ISNULL(jes.execution_step_id, 0)
WHEN 0 THEN NULL -- Will be NULL if the job is non-local or is not in #job_execution_state
ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
END,
jes.execution_retry_attempt, -- Will be NULL if the job is non-local or is not in #job_execution_state
0, -- last_run_date placeholder (we'll fix it up in step 3.3)
0, -- last_run_time placeholder (we'll fix it up in step 3.3)
5, -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
jes.next_run_date, -- Will be NULL if the job is non-local or is not in #job_execution_state
jes.next_run_time, -- Will be NULL if the job is non-local or is not in #job_execution_state
jes.next_run_schedule_id, -- Will be NULL if the job is non-local or is not in #job_execution_state
0 -- type placeholder (we'll fix it up in step 3.4)
FROM msdb.dbo.sysjobs sjv
LEFT OUTER JOIN #job_execution_state jes ON (sjv.job_id = jes.job_id)
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON (sjv.job_id = sjs.job_id)
WHERE ((@subsystem IS NULL) OR (sjs.subsystem = @subsystem))
AND ((@owner_login_name IS NULL) OR (sjv.owner_sid = SUSER_SID(@owner_login_name)))
AND ((@enabled IS NULL) OR (sjv.enabled = @enabled))
AND ((@category_id IS NULL) OR (sjv.category_id = @category_id))
AND ((@execution_status IS NULL) OR ((@execution_status > 0) AND (jes.execution_job_status = @execution_status))
OR ((@execution_status = 0) AND (jes.execution_job_status <> 4) AND (jes.execution_job_status <> 5)))
AND ((@description IS NULL) OR (sjv.description LIKE @description))
AND ((@job_id IS NULL) OR (sjv.job_id = @job_id))
END

-- Step 3.1: Change the execution status of non-local jobs from 'Idle' to 'Unknown'
UPDATE #filtered_jobs
SET current_execution_status = NULL
WHERE (current_execution_status = 4)
AND (job_id IN (SELECT job_id
FROM msdb.dbo.sysjobservers
WHERE (server_id <> 0)))

-- Step 3.2: Check that if the user asked to see idle jobs that we still have some.
-- If we don't have any then the query should return no rows.
IF (@execution_status = 4) AND
(NOT EXISTS (SELECT *
FROM #filtered_jobs
WHERE (current_execution_status = 4)))
BEGIN
TRUNCATE TABLE #filtered_jobs
END

-- Step 3.3: Populate the last run date/time/outcome [this is a little tricky since for
-- multi-server jobs there are multiple last run details in sysjobservers, so
-- we simply choose the most recent].
IF (EXISTS (SELECT *
FROM msdb.dbo.systargetservers))
BEGIN
UPDATE #filtered_jobs
SET last_run_date = sjs.last_run_date,
last_run_time = sjs.last_run_time,
last_run_outcome = sjs.last_run_outcome
FROM #filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (CONVERT(FLOAT, sjs.last_run_date) * 1000000) + sjs.last_run_time =
(SELECT MAX((CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time)
FROM msdb.dbo.sysjobservers
WHERE (job_id = sjs.job_id))
AND (fj.job_id = sjs.job_id)
END
ELSE
BEGIN
UPDATE #filtered_jobs
SET last_run_date = sjs.last_run_date,
last_run_time = sjs.last_run_time,
last_run_outcome = sjs.last_run_outcome
FROM #filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
END

-- Step 3.4 : Set the type of the job to local (1) or multi-server (2)
-- NOTE: If the job has no jobservers then it wil have a type of 0 meaning
-- unknown. This is marginally inconsistent with the behaviour of
-- defaulting the category of a new job to [Uncategorized (Local)], but
-- prevents incompletely defined jobs from erroneously showing up as valid
-- local jobs.
UPDATE #filtered_jobs
SET type = 1 -- LOCAL
FROM #filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
AND (server_id = 0)
UPDATE #filtered_jobs
SET type = 2 -- MULTI-SERVER
FROM #filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
AND (server_id <> 0)

-- Step 4: Filter on job_type
IF (@job_type IS NOT NULL)
BEGIN
IF (UPPER(@job_type) = 'LOCAL')
DELETE FROM #filtered_jobs
WHERE (type <> 1) -- IE. Delete all the non-local jobs
IF (UPPER(@job_type) = 'MULTI-SERVER')
DELETE FROM #filtered_jobs
WHERE (type <> 2) -- IE. Delete all the non-multi-server jobs
END

-- Step 5: Filter on dates
IF (@date_comparator IS NOT NULL)
BEGIN
IF (@date_created IS NOT NULL)
BEGIN
IF (@date_comparator = '=')
DELETE FROM #filtered_jobs WHERE (date_created <> @date_created)
IF (@date_comparator = '>')
DELETE FROM #filtered_jobs WHERE (date_created <= @date_created)
IF (@date_comparator = '<')
DELETE FROM #filtered_jobs WHERE (date_created >= @date_created)
END
IF (@date_last_modified IS NOT NULL)
BEGIN
IF (@date_comparator = '=')
DELETE FROM #filtered_jobs WHERE (date_last_modified <> @date_last_modified)
IF (@date_comparator = '>')
DELETE FROM #filtered_jobs WHERE (date_last_modified <= @date_last_modified)
IF (@date_comparator = '<')
DELETE FROM #filtered_jobs WHERE (date_last_modified >= @date_last_modified)
END
END

-- Return the result set (NOTE: No filtering occurs here)
Declare @aantaltotaal int
Set @aantaltotaal=0
SELECT
@aantaltotaal=isnull(count(*),0)
FROM #filtered_jobs fj
LEFT OUTER JOIN msdb.dbo.sysjobs sjv ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id = so3.id)
LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id)

-- Return the result set (NOTE: No filtering occurs here)
Declare @aantalfout int
Set @aantalfout=0
SELECT
@aantalfout=isnull(count(*),0)
FROM #filtered_jobs fj
LEFT OUTER JOIN msdb.dbo.sysjobs sjv ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id = so3.id)
LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id)
where
(fj.last_run_time>0 and fj.last_run_outcome<>1)

--print @aantalfout
Declare @message varchar(200)
Declare @ret int

set @ret=0
set @message='No failed SQL Jobs'

If @aantalfout=1
Begin
SELECT
@ret=1, @message='Op ' + sjv.originating_server + ' is *'+ sjv.name + '* gefailed op ' + cast(fj.last_run_date as varchar(8)) + ''
FROM #filtered_jobs fj
LEFT OUTER JOIN msdb.dbo.sysjobs sjv ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id = so3.id)
LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id)
where
fj.last_run_time>0 and fj.last_run_outcome<>1
ORDER BY sjv.job_id
End
If @aantalfout>1
begin
set @ret=1
set @message='Multible Failed SQL jobs, check SQL Agent'
end
If @aantaltotaal=0
begin
set @ret=1
set @message='Not a single SQL job detected, check this check'
end

-- Clean up
DROP TABLE #job_execution_state
DROP TABLE #filtered_jobs
DROP TABLE #xp_results
--SET NOCOUNT OFF
select ret=@ret, message=@message

SQL Server for Starters

SQL Server for Starters Archief

Over mij

vrijdag 12 december 2008

Use DBCC OPENTRAN -- When -- Why

Reading SQL Trace Files using fn_trace_gettable function

donderdag 11 december 2008

Job: Logshipping Copy Transaction Logs

Backup Databases to another Disck

dinsdag 9 december 2008

Check Faild Jobs