This Month

First add the variables

The folders are strings and the number of days to keep the ... more »

Leave Comment | Permanent Link | Cosmos

Wednesday, March 26

Server Info

by Administrator on Wed 26 Mar 2008 18:56 GMT

Generates information abour SQL Server and the version installed more »

Leave Comment | Permanent Link | Cosmos

File Locations

by Administrator on Wed 26 Mar 2008 18:25 GMT

Gives the location of the database files works with SQL Server 2000 and 2005 more »

Leave Comment | Permanent Link | Cosmos

List Databases on Server

by Administrator on Wed 26 Mar 2008 18:18 GMT

Lists the databases available on a server more »

Leave Comment | Permanent Link | Cosmos

Database Properties

by Administrator on Wed 26 Mar 2008 18:12 GMT

Script to list database properties for use on SQL Server 2000 and 2005 servers. more »

Leave Comment | Permanent Link | Cosmos

Friday, January 18

SQL Server 2005 DDL Triggers

by Administrator on Fri 18 Jan 2008 13:59 GMT

See article http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ddl-TRIGGER-workbench/ which describes 
SQL Server 2005 DDL Triggers.

I have implemented these with some amendments:- 




CREATE TABLE [dbo].[DDLChangeLog]...   more »

Leave Comment | Permanent Link | Cosmos

Thursday, January 3

SQL Agent Error

by Administrator on Thu 03 Jan 2008 10:55 GMT

Have you ever seen the following error in the SQL Server Agent error log:-

Message
[425] delay_between_response attribute (10 sec) ... more »

Leave Comment | Permanent Link | Cosmos

Saturday, December 8

Restore the Master Database

by Administrator on Sat 08 Dec 2007 15:24 GMT

On SQL Server 2000 to restore the Master database, copy the instal disk to the server being restored and alter ... more »

Leave Comment | Permanent Link | Cosmos

Wednesday, November 14

Script to add alerts and notifications to DBA User - SQL 2005

by Administrator on Wed 14 Nov 2007 14:12 GMT

This script adds an operator 'DBA' and reports all job failures and alerts to this Operator. Database Mail or SQL Mail needs to be enabled. more »

Leave Comment | Permanent Link | Cosmos

MOM reporting backp failure, when backups succesful

by Administrator on Wed 14 Nov 2007 09:59 GMT

Today the MOM agent reported backup failure on one of my servers:-

Description:

BACKUP failed to complete the command ... more »

Leave Comment | Permanent Link | Cosmos

Wednesday, October 31

Alter the collation to database default

by Administrator on Wed 31 Oct 2007 22:34 GMT

/*---------------------------------------------------------------------------------------------------

Purpose: This script generate scripts to alter the collation

Author: Carolyn Richardson

Date: 05/10/2007

---------------------------------------------------------------------------------------------------*/

USE{databasename}

GO... more »

Leave Comment | Permanent Link | Cosmos

Identify tables on Server with collation issue

by Administrator on Wed 31 Oct 2007 22:26 GMT

This script will identify tables on all databases on a server that have differing collations, collation differences have probably occured between upgrades to SQL Server versions or where the server default differs more »

Leave Comment | Permanent Link | Cosmos

Find tables with large logical fragmentation

by Administrator on Wed 31 Oct 2007 22:12 GMT

--Quick script to find tables with large logical fragmentation and generate a script to alter the fill factor

CREATE TABLE... more »

Leave Comment | Permanent Link | Cosmos

Monday, October 22

Upgrading to SQL Server 2005

by Administrator on Mon 22 Oct 2007 13:55 BST

Upgrading to SQL Server 2005 more »

Leave Comment | Permanent Link | Cosmos

Friday, October 19

How to transfer logins and passwords between instances of SQL Server

by Administrator on Fri 19 Oct 2007 14:04 BST

How to transfer logins and passwords between instances of SQL Server more »

Leave Comment | Permanent Link | Cosmos

Thursday, October 18

Alerts for SQL Server 2000

by Administrator on Thu 18 Oct 2007 22:37 BST

-- These are our preferred Alerts for SQL Server 2000/2005

-- Author: SQL Server Specialists (www.SQLServerSpecialists.co.uk)

USE MSDB

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 19 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 19 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 20 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 20 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Demo: Sev. 21 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 21 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 22 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 22 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 23 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 23 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 24 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 24 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 25 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 25 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Full msdb log'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Full msdb log'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Demo: Full tempdb'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Full tempdb'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Backup Failure'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Backup Failure'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Backup Failure', @message_id = 3201, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Deadlock Event'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Deadlock Event'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Deadlock Event', @message_id = 1205, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Driver or Hardware problem with the I/O system'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Driver or Hardware problem with the I/O system'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Driver or Hardware problem with the I/O system', @message_id = 845, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Fatal Error in current Proccess - 020'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error in current Proccess - 020'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error in current Proccess - 020', @message_id = 0, @severity = 20, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Fatal Error in Database Processes - 021'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error in Database Processes - 021'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error in Database Processes - 021', @message_id = 0, @severity = 21, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Fatal Error In Resource - 019'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error In Resource - 019'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error In Resource - 019', @message_id = 0, @severity = 19, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Fatal Error: Database Integrity Suspect - 023'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error: Database Integrity Suspect - 023'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error: Database Integrity Suspect - 023', @message_id = 0, @severity = 23, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Fatal Error: Hardware Error - 024'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error: Hardware Error - 024'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error: Hardware Error - 024', @message_id = 0, @severity = 24, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Fatal Error: Table Integrity Suspect - 022'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error: Table Integrity Suspect - 022'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error: Table Integrity Suspect - 022', @message_id = 0, @severity = 22, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Fatal Errror - 025'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Errror - 025'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Errror - 025', @message_id = 0, @severity = 25, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Full Log - 9002'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Full Log - 9002'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Full Log - 9002', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Full msdb log - 9002'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Full msdb log - 9002'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Full msdb log - 9002', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @database_name = N'msdb', @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Full tempdb - 9002'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Full tempdb - 9002'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Full tempdb - 9002', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @database_name = N'tempdb', @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Lock Allocation'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Lock Allocation'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Lock Allocation', @message_id = 17125, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Lock request timeout period exceeded'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Lock request timeout period exceeded'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Lock request timeout period exceeded', @message_id = 1222, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Misc Table Error'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Misc Table Error'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Misc Table Error', @message_id = 2511, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Non Logged SQL Server Shutdown'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Non Logged SQL Server Shutdown'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Non Logged SQL Server Shutdown', @message_id = 20531, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF (EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Potentially Fatal Error Insufficient Resources'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Potentially Fatal Error Insufficient Resources'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Potentially Fatal Error Insufficient Resources', @message_id = 0, @severity = 17, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 4, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Unexpected failure acquiring application lock.'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Unexpected failure acquiring application lock.'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Unexpected failure acquiring application lock.', @message_id = 21414, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Unexpected failure releasing application lock'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Unexpected failure releasing application lock'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Unexpected failure releasing application lock', @message_id = 21415, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

Leave Comment | Permanent Link | Cosmos

Save DTS packages as Structured Storage Files

by Administrator on Thu 18 Oct 2007 22:25 BST

CREATEPROCEDURE dbo.usp_SaveDTS

@DTSname varchar(256)='',

@Applicationpath varchar(700)='',

@destinationpath varchar(700)='\\Serverpath\Software\SQL 2000\DTS\',

@switches varchar(200)=' -E -!X '

/*=============================================================================================================================

Stored Proc: usp_SaveDTS

Purpose: Saves DTS Packages as Structured Storage File

Author: Carolyn Richardson

Date: 25/09/2007

NOTES: Alter the @destinationpath to point to the correct UNC path to directory

=============================================================================================================================*/

SET quoted_identifier off

SET nocount on

SET concat_null_yields_null off

DECLARE @Count int

,@folderexist int ,@maxcount int ,@query varchar(1000) ,@date varchar(10) ,@versionid varchar(40) ,@createdate varchar(25)

SET @date =convert(varchar(10),getdate(),112)

SET @Count =1

PRINT 'Saving DTS packages - Started'

PRINT getdate()

SET @Applicationpath = @Applicationpath +'DTSRUN.exe'

CREATE TABLE #DTSTABLE

(id intidentity(1,1), DTSname varchar(256),versionid varchar(40), createdate varchar(25))

IF @dtsname =''

BEGIN

INSERT INTO #DTSTABLE

(dtsname,versionid,createdate)

SELECT name,versionid,replace(replace(convert(varchar(25),createdate,109),':',' '),' ','_')

FROM msdb..sysdtspackages

END

ELSE

BEGIN

INSERTINTO #DTSTABLE

(dtsname,versionid,createdate)

SELECT name,versionid,replace(replace(convert(varchar (25),createdate,109),':',' '),' ','_')

FROM msdb..sysdtspackages
WHERE name= @DTSname

END

IF(SELECTcount(*)FROM #dTStable)= 0

BEGIN

SET @date =convert(varchar(100),getdate(),109)
PRINT 'Error: No valid DTS package found for saving'

END

ELSE

BEGIN

-- Check folder for Server Exists
SET @destinationpath = @destinationpath +@@Servername CREATETABLE #Files (Files int, Folder int, parent int) INSERT #Files EXEC master.dbo.xp_fileexist @destinationpath

SELECT @folderexist = Folder FROM #Files

IF @folderexist <>1 BEGIN

SET @query ='