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

Rollin’ On!

May 23, 2012 · Posted in General Silliness · Comment 

In honor of the 4chan RickRoll’D Internet Meme that was taken offline for around 24 Read more

HowTo: Grow a very large MS-SQL Log File

May 4, 2011 · Posted in SQL · Comment 

Microsoft SQL Server 2005 has a nasty habit of creating thousands or hundreds of thousands of logical log files even though you set Autogrowth to a large size, say 8GB. The following SQL code automates manually growing your log file by 8GB increments. When using the ALTER DATABASE command to modify log file size manually like this, SQL Server seems to properly create logical log files of around 1GB each resulting in far less logical log files overall. This should increase log file performance. Read more

Next Page »