USE [master] GO /****** Object: StoredProcedure [dbo].[sp_who3] Script Date: 24/06/2016 15:29:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_who3] AS ——————————————————————————————————– — mimics sp_who2 with query text — SPID , Status , Login , HostName , BlkBy , DBName , Command , CPUTime , DiskIO , LastBatch , ProgramName , SPID…

Read More »

DBCC TRACESTATUS (-1 ); ————————————– —–TRACEON ————————————– DBCC TRACEON (1117, -1 ); DBCC TRACEON (1118, -1 ); DBCC TRACEON (1222, -1 ); DBCC TRACEON (3226, -1 ); DBCC TRACEON (4199, -1 ); ————————————– —–TRACEOFF ————————————– –DBCC TRACEOFF — (1117, -1 — ); –DBCC TRACEOFF — (1118, -1 — ); –DBCC TRACEOFF — (1222, -1 —…

Read More »

USE [msdb]; GO /****** Object: Job [USER Integrity Check main] Script Date: 19/04/2016 14:06:30 ******/ BEGIN TRANSACTION; DECLARE @ReturnCode INT; SELECT @ReturnCode = 0; /****** Object: JobCategory [Database Maintenance] Script Date: 19/04/2016 14:06:30 ******/ IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N’Database Maintenance’ AND category_class = 1 ) BEGIN EXEC @ReturnCode…

Read More »

USE [master]; GO /****** Object: StoredProcedure [dbo].[DatabaseIntegrityCheck] Script Date: 19/02/2016 09:58:06 ******/ –DROP PROCEDURE [dbo].[DatabaseIntegrityCheck]; –GO /****** Object: StoredProcedure [dbo].[DatabaseIntegrityCheck] Script Date: 19/02/2016 09:58:06 ******/ SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck] @Databases NVARCHAR(MAX), @CheckCommands NVARCHAR(MAX)= ‘CHECKDB’, @PhysicalOnly NVARCHAR(MAX)= ‘N’, @NoIndex NVARCHAR(MAX)= ‘N’, @ExtendedLogicalChecks NVARCHAR(MAX)= ‘N’, @TabLock NVARCHAR(MAX)= ‘N’, @FileGroups NVARCHAR(MAX)=…

Read More »

USE [msdb]; GO /****** Object: Job [USER INDEXES] Script Date: 18/02/2016 16:54:31 ******/ BEGIN TRANSACTION; DECLARE @ReturnCode INT; SELECT @ReturnCode = 0; /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 18/02/2016 16:54:31 ******/ IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]’ AND category_class = 1 ) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category…

Read More »

/* Back up the master, msdb, and model databases to ensure that you can roll back to a previous configuration if problems occur. master: This database hosts system-level information. Back up this database regularly. model: You need to back up the template database only after you have made modifications. Because changes to the template database…

Read More »

USE [MASTER]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO CREATE PROCEDURE dbo.CommandExecute @Command NVARCHAR(MAX), @CommandType NVARCHAR(MAX), @Mode INT, @Comment NVARCHAR(MAX)= NULL, @DatabaseName NVARCHAR(MAX)= NULL, @SchemaName NVARCHAR(MAX)= NULL, @ObjectName NVARCHAR(MAX)= NULL, @ObjectType NVARCHAR(MAX)= NULL, @IndexName NVARCHAR(MAX)= NULL, @IndexType INT= NULL, @StatisticsName NVARCHAR(MAX)= NULL, @PartitionNumber INT= NULL, @ExtendedInfo XML= NULL, @LogToTable NVARCHAR(MAX), @Execute NVARCHAR(MAX) AS…

Read More »

USE [msdb]; GO IF ( SELECT 1 FROM [dbo].[sysoperators] WHERE name = ‘DBA_Alerts’ ) IS NULL BEGIN EXEC msdb.dbo.sp_add_operator @name = N’DBA_Alerts’, @enabled = 1, @pager_days = 0, @email_address = N’DBA_Alerts@dwf.co.uk’; END; ELSE BEGIN PRINT ‘Operator Already Exists’; END; GO USE [msdb]; GO IF ( SELECT 1 FROM [dbo].[sysalerts] WHERE name = ‘Corruption Alert 823…

Read More »

USE [msdb]; GO EXEC master.dbo.xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’, N’UseDatabaseMail’, N’REG_DWORD’, 1; GO EXEC master.dbo.xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’, N’DatabaseMailProfile’, N’REG_SZ’, N’SQL_Alerting’; GO EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 1; GO –RESTART SQL SERVER AGENT

Read More »

–Add TempDB Files to number of prcessors (up to 8) DECLARE @dbname VARCHAR(200); DECLARE @datafilename VARCHAR(200); DECLARE @logfilename VARCHAR(200); DECLARE @cpucount SMALLINT; DECLARE @datafilepath VARCHAR(200); DECLARE @logilepath VARCHAR(200); DECLARE @datasizeMB INT; DECLARE @logsizeMB INT; DECLARE @datafilegrowthMB INT; DECLARE @logfilegrowthMB INT; DECLARE @sqlstring VARCHAR(8000); DECLARE @loop SMALLINT; SELECT @dbname = ‘tempdb’; SELECT @datasizeMB = 1024; SELECT…

Read More »

— Focus on these settings: — backup compression default (should be 1 in most cases) — clr enabled (only enable if it is needed) — cost threshold for parallelism (depends on your workload) 50 is good — lightweight pooling (should be zero) — max degree of parallelism (depends on your workload) 4-8 — max server…

Read More »

USE [msdb]; GO /****** Object: Job [(00:00) Maintenance – CycleErrorLogs] Script Date: 06/10/2014 11:19:43 ******/ BEGIN TRANSACTION; DECLARE @ReturnCode INT; SELECT @ReturnCode = 0; /****** Object: JobCategory [DBA_Maintenance] Script Date: 06/10/2014 11:19:43 ******/ IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N’DBA_Maintenance’ AND category_class = 1 ) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category…

Read More »

USE [master]; GO — setup ALTER DATABASE [msdb] MODIFY FILE (NAME = N’MSDBData’, SIZE = 51200 KB, FILEGROWTH = 204800 KB ); GO ALTER DATABASE [msdb] MODIFY FILE (NAME = N’MSDBLog’, SIZE = 51200 KB, MAXSIZE = UNLIMITED, FILEGROWTH = 204800 KB ); GO

Read More »