/* The Code */ --If you want to get a list of current owners who aren't sa SELECT suser_sname(owner_sid) as DBOwner, Name as DBName FROM...

--Script to find autogrowth event from the default trace. DECLARE @filename NVARCHAR(1000); DECLARE @bc INT; DECLARE @ec INT; DECLARE...

--Script to find number of VLF. SET NOCOUNT ON; /* declare variables required */ DECLARE @DatabaseId INT; DECLARE @TSQL varchar(MAX);...

Use msdb go select distinct j.Name as "Job Name", --j.job_id, case j.enabled when 1 then 'Enable' when 0 then 'Disable' end as "Job...

SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS...

--Collect all index stats if object_id('index_estimates') is not null drop table index_estimates go create table index_estimates (...

-- Show data in table dbtest02 that do not exists in dbtest01 select * from dbtest02.dbo.article except select * from dbtest01.dbo.article

/*Perform a 'USE <database name>' to select the database in which to run the script.*/ -- Declare variables SET NOCOUNT ON; DECLARE...

--Below script will change the number of SQL Server errorslogs to 10 USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',...

USE [msdb] GO /****** Object: Job [sp_cycle_errorlog] Script Date: 08/26/2014 10:44:11 ******/ BEGIN TRANSACTION DECLARE @ReturnCode...

USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=10000, @jobhistory_max_rows_per_job=1000

-- Ensure a USE <databasename> statement has been executed first. SET ANSI_NULLS ON; SET ARITHABORT ON; SET QUOTED_IDENTIFIER ON; SET...

-- Ensure a USE <databasename> statement has been executed first. SET ANSI_NULLS ON; SET ARITHABORT ON; SET QUOTED_IDENTIFIER ON; SET...

-- Ensure a USE <databasename> statement has been executed first. SET ANSI_NULLS ON; SET ARITHABORT ON; SET QUOTED_IDENTIFIER ON; SET...

-- Ensure a USE <databasename> statement has been executed first. SET ANSI_NULLS ON; SET ARITHABORT ON; SET QUOTED_IDENTIFIER ON; SET...

The below script rebuild all the indexes that are fragmented higher than 30%. Reorg indexes that are fragmented between 10%-30% -- Ensure...

--Buffer Pool Extension Alter server configuration set buffer pool extension on (filename ='D:\extrabuffer.bpe', size = 32GB) GO

SELECT TOP (50) sp.database_id, dbname= DB_NAME (qt.dbid), so.name AS StoredProcName, sp.total_worker_time, sp.execution_count AS...

SELECT TOP (10) sp.database_id, so.name AS StoredProcName, sp.total_worker_time, sp.execution_count, sp.total_logical_reads,...

SELECT TOP (5) a.database_id, so.object_id, so.name AS TableName, a.page_latch_wait_count , a.page_latch_wait_in_ms,...