Use DBCC PAGE to identify rows involved in blocking operations in SQL Server

February 8, 2015 · Posted in SQL · Comment 

Recently, one of my customers complained about a BizFlow database that was “running slowly”. BizFlow uses SQL Server to store data used in business processes – in this implementation there is a main BizFlow database, and an “archive” database.

The system I was looking at has a Windows Service that moves old rows from the main Read more

A chance to be mentored by one of the premiere members of the SQL Server Community, @PaulRandal ? I’m in!

February 2, 2015 · Posted in SQL · Comment 

@PaulRandal is offering to mentor people over the next several months. I’d love the opportunity to receive some guidance from one of the most respected members of the world-wide SQL Server community.

I’ve been using SQL Server since early 2003, and have a pretty wide level of understanding of the features and principles of SQL Server; however I feel I could use some of Paul’s very valuable time to understand how I could provide better service to my customers and provide better assistance to the rest of the SQL Server community.

I’ve used Paul’s `DBCC PAGE` details to help developers understand details surrounding strange behavior in their products. I’ve read many other blog posts by him, at SQLSkills.com, that have been extremely helpful.

He regularly sends subscribers the invaluable SQL Skills Insiders email that is very informative, and interesting since he tends to share quite a bit more than *just* SQL Server info.

Do yourself a favor, and get informed on SQL Server internals by reading his material!

Tool for scripting SQL Server Agent Jobs

December 4, 2013 · Posted in SQL, Technology · Comment 

I recently created an open-source tool that will create a T-SQL script file containing code to recreate whatever jobs you presently have in SQL Server Agent for any given SQL Instance, up to SQL Server 2012 SP1.

The tool requires SQL Server 2012 SP1 Server Management Objects and SQL Server System CLR Types:

SQL Server System CLR Types (x86)
SQL Server Shared Management Objects (x86)

The tool itself is named MVCTSQLJobScripter and can be downloaded from:

MVCTSQLJobScripter-v1.0-Setup.zip

The utility takes several command-line parameters, as below, which necessitates running it from the command prompt, `cmd.exe`.

MVCTSQLJobScripter.exe /server:localhost /outfile:C:\temp\AgentJobs.sql

The GitHub repository contains the source code for the tool at:

https://github.com/MaxVernon/MVCTSQLJobScripter

As expected, this tool comes with no warranties, express or implied, and is use at-your-own-risk.

SQL Server 2005 Mirroring – disable/enable T-SQL Scripts

October 10, 2012 · Posted in SQL · Comment 

USE [MagellanSQLMonitor]
GO
/****** Object: StoredProcedure [dbo].[EnableMirroring] Script Date: 05/16/2012 13:59:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[EnableMirroring]
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX);

DECLARE CUR CURSOR FOR
SELECT 'ALTER DATABASE ' + D.NAME + ' SET PARTNER = ''TCP://machine1.domain.inet:5022'''
FROM machine2.MASTER.SYS.DATABASES D
WHERE STATE=1;
OPEN CUR;
FETCH NEXT FROM CUR INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR (@SQL,0,1) WITH NOWAIT;
EXEC machine2.master.sys.sp_executesql @SQL;
FETCH NEXT FROM CUR INTO @SQL;
END
CLOSE cur;
DEALLOCATE cur;

DECLARE CUR CURSOR FOR
SELECT 'ALTER DATABASE ' + D.NAME + ' SET PARTNER = ''TCP://machine2.domain.inet:5022'''
FROM machine2.MASTER.SYS.DATABASES D
WHERE STATE=2;
OPEN CUR;
FETCH NEXT FROM CUR INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR (@SQL,0,1) WITH NOWAIT;
EXEC machine1.master.sys.sp_executesql @SQL;
FETCH NEXT FROM CUR INTO @SQL;
END
CLOSE cur;
DEALLOCATE cur;

DECLARE CUR CURSOR FOR
SELECT 'ALTER DATABASE ' + D.NAME + ' SET WITNESS = ''TCP://witness.domain.inet:5022'''
FROM machine2.MASTER.SYS.DATABASES D
WHERE STATE>0;
OPEN CUR;
FETCH NEXT FROM CUR INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR (@SQL,0,1) WITH NOWAIT;
EXEC machine1.master.sys.sp_executesql @SQL;
FETCH NEXT FROM CUR INTO @SQL;
END
CLOSE cur;
DEALLOCATE cur;

DECLARE CUR CURSOR FOR
SELECT 'ALTER DATABASE ' + D.NAME + ' SET PARTNER TIMEOUT 30'
FROM machine2.MASTER.SYS.DATABASES D
WHERE STATE=1;
OPEN CUR;
FETCH NEXT FROM CUR INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR (@SQL,0,1) WITH NOWAIT;
EXEC machine1.master.sys.sp_executesql @SQL;
FETCH NEXT FROM CUR INTO @SQL;
END
CLOSE cur;
DEALLOCATE cur;

--SELECT 'ALTER DATABASE ' + D.NAME + ' SET PARTNER = ''TCP://machine2.domain.inet:5022''' FROM SYS.DATABASES D WHERE STATE=1
--SELECT 'ALTER DATABASE ' + D.NAME + ' SET WITNESS = ''TCP://witness.domain.inet:5022''' FROM SYS.DATABASES D WHERE STATE=1
--SELECT 'ALTER DATABASE ' + D.NAME + ' SET PARTNER TIMEOUT 30' FROM SYS.DATABASES D WHERE STATE=1
--select * from sys.database_mirroring
SELECT d.Name AS [Database], dm.mirroring_state_desc AS State, dm.mirroring_role_desc AS Role, dm.mirroring_partner_name AS Partner, dm.mirroring_witness_name AS Witness, dm.mirroring_connection_timeout AS Timeout
FROM sys.database_mirroring dm
INNER JOIN sys.databases d ON dm.database_id = d.database_id
WHERE dm.MIRRORING_STATE > 0
ORDER BY d.Name
--ALTER DATABASE DatabaseName SET PARTNER TIMEOUT 30
END

USE [DatabaseMonitor]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BreakMirroring]
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX);

DECLARE CUR CURSOR FOR
SELECT 'ALTER DATABASE ' + D.NAME + ' SET PARTNER OFF'
FROM machine2.MASTER.SYS.DATABASES D
WHERE STATE=1;
OPEN CUR;
FETCH NEXT FROM CUR INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR (@SQL,0,1) WITH NOWAIT;
EXEC machine2.master.sys.sp_executesql @SQL;
FETCH NEXT FROM CUR INTO @SQL;
END
CLOSE cur;
DEALLOCATE cur;

END

How to audit Database Design Modifications using a DDL Trigger

September 27, 2012 · Posted in SQL, Technology · 2 Comments 

Recently we identified the need to create a system for tracking changes made to our databases by developers and internal systems. Although the database itself is using FULL logging, which allows you to view changes made, we found that we’d like to see all modifications made over the entire history of the database in question. This prompted me to develop a table containing all changes Read more

Next Page »