How to monitor blocking in SQL Server 2005 and in SQL Server 2000 -- sp_blocker_pss80

How to monitor blocking in SQL Server 2005 and in SQL Server 2000 -- Microsoft document (sp_blocker_pss80)
-----------------------------------------------------------------------------:
http://support.microsoft.com/kb/271509/

Follow the steps in the link to create and test and resolve the blocking problem

Use DBCC OPENTRAN -- When -- Why

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.

Reading SQL Trace Files using fn_trace_gettable function

Reading SQL Trace Files using fn_trace_gettable function:
--------------------------------------------------------
http://kalpeshshirodker.wordpress.com/2008/02/15/reading-sql-trace-files-using-fn_trace_gettable-function/

Notes(Important):
----------------
1): Run SQL Profiler to record events you want to check.
2): Be sure that the result file will be saved to a disk with '.trc'.
3): after stop recording search in the result file by using the following query:

SELECT Count(*) as CountOfEvents,
AVG(Duration) AS AvgDuration,
SUM(Duration) AS [SumDuration],
SUBSTRING(TextData, 1, 30) AS [Text Data]
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_41.trc',default)
WHERE EventClass in (10,12) -- BatchCompleted, RPC Completed
GROUP BY SUBSTRING(TextData, 1, 30)
ORDER BY SUM(Duration) DESC

This will give a list with all queries runs on the database at the record time.

Job: Logshipping Copy Transaction Logs

Job Steps:
=========
Step 1:)Delete old transaction backup
------------------------------------
del C:\WinSupport\Logshipping\transaction\*.* /Q


Step 2:)Backup Log:
--------------------
BACKUP LOG bam TO logshipping_bam_transaction WITH INIT, NO_TRUNCATE

WAITFOR DELAY '00:00:05'



Step 3:)Copy to Uitwijk:
-----------------------
xcopy C:\WinSupport\Logshipping\transaction\*.* \\srvsql-2\c$\winsupport\logshipping\transaction\ /c /Y



Step 4:)Copy to Standby:
------------------------
echo xcopy C:\WinSupport\Logshipping\transaction\*.* \\psqlweb4\c$\winsupport\logshipping\transaction\ /c /Y



Step 5:)Apply on both:
--------------------
EXEC [srvsql-2].master.dbo.restore_log_backups
--EXEC [psqlweb4].master.dbo.restore_log_backups




Step 6:)Dummy (Op master):
-------------------------
sp_who

Backup Databases to another Disck

SET NOCOUNT ON
DECLARE @dbs_nam AS varchar(50) -- Database naam
DECLARE @opd AS varchar(2048) -- Opdracht
-- Creëer cursus met select statement voor welke databases gedumpt moeten worden
DECLARE dmp_dbs_cur CURSOR FOR
SELECT [name]
FROM [master].[dbo].[sysdatabases]
WHERE [dbid] <> 2
-- Open cursor
OPEN dmp_dbs_cur
-- Pak de volgende uit de resultset van de cursor en plaats deze in de variabele @dbs_nam
FETCH NEXT
FROM dmp_dbs_cur
INTO @dbs_nam
-- Voer de volgende serie acties uit zolang de resultset nog niet geheel doorlopen is
WHILE @@FETCH_STATUS = 0
BEGIN
SET @opd = 'BACKUP DATABASE [' + @dbs_nam + ']
TO DISK = N''\\psms2\Install\Back-up\' + RTRIM(CONVERT(varchar(64), SERVERPROPERTY('servername'))) + '\20070903_1045uur_' + @dbs_nam + '.BAK'' WITH NOFORMAT
, NOINIT
, NAME = N''' + @dbs_nam + '-Full Database Backup''
, SKIP
, NOREWIND
, NOUNLOAD
, STATS = 10'
PRINT @opd
EXEC (@opd)
FETCH NEXT
FROM dmp_dbs_cur
INTO @dbs_nam
END
-- Sluit de cursor
CLOSE dmp_dbs_cur
-- Geeft geheugenruimte weer vrij
DEALLOCATE dmp_dbs_cur