To find all logins that have been disabled in SQL 2005/2008/2008R2

select name,* from sys.server_principals where is_disabled = '1'

FInd Current Isolation Level

dbcc useroptions

Kill Negative SPID

1. Find the negative SPID number.

2. For e.g. is SPID number is -2 then run the below T-SQL
use master
go
select req_transactionUOW from syslockinfo where req_spid = -2
go
3. You will get a hexadecimal value with the above syntax and then run the below command.

KILL 'FE4A57F2-28C5-44F9-8416-B08760DFE7E9'

By this process you will be able to kill negative SPID.

Syntax to create Database Snapshot using T-SQL

Syntax to create Database Snapshot using T-SQL

Syntax :-

create database SnapshotName  on ( Name = Logical file name of DB , filename = 'C:\snapshotname.ss' ) as snapshot of Database name

Example :-

create database  test_snapshot on ( Name = test_new , filename = 'C:\test_new_snapshot.ss' ) as snapshot of test_new

How to change the collation of the server

Run the below command in CMD and it will change the collation of the server. Master database will be rebuild.

setup.exe /Quiet /ACTION=REBUILDDATABASE /INSTANCENAME=Instance2 /SQLSYSADMINACCOUNTS=server\Administrator /SAPWD=sa123 /SQLCOLLATION=SQL_Latin1_General_CP1_CS_AS

How to find the owner of the maintenance plans in SQL Server

Run the below script and it will tell you the owner of the maintenance plan.

For SQL 2000.
select plan_name,owner from dbo.sysdbmaintplans
For SQL 2005/2008/2008R2
select * from dbo.sysmaintplan_plans


If you want to change the maintenance plan owner then please use the below command.
For SQL 2000

update dbo.sysdbmaintplans set owner ='sa' where owner ='LoginName'
For SQL 2005

UPDATE
msdb.dbo.sysdtspackages90
SET
OWNERSID = SUSER_SID('YOUR_DOMAIN\APPROPRIATE_ACCOUNT')
WHERE
OWNERSID = SUSER_SID('YOUR_DOMAIN\OLD_ACCOUNT')

For SQL 2008/2008R2

update msdb.dbo.sysssispackages set [ownersid] = suser_sid('sa') where [name] = 'MaintenancePlan'

How to check status of SQL services using TSQL

EXEC xp_servicecontrol N'querystate',N'MSSQLServer'

EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'

Identifying orphaned or long-running transactions using the DMVs

-- SQL 2000 sysprocess query

SELECT spid ,
status ,
hostname ,
program_name ,
loginame ,
login_time ,
last_batch ,
( SELECT text
FROM ::
fn_get_sql(sql_handle)
) AS [sql_text]
FROM sysprocesses
WHERE spid =

-- SQL 2005/2008 DMV query

SELECT s.session_id ,
s.status ,
s.host_name ,
s.program_name ,
s.login_name ,
s.login_time ,
s.last_request_start_time ,
s.last_request_end_time ,
t.text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id =

Events collected by the default trace

SELECT DISTINCT
e.trace_event_id ,
e.name
FROM sys.fn_trace_geteventinfo (1) t
JOIN sys.trace_events e
ON t.eventID = e.trace_event_id

Querying sys.traces for the default trace characteristics

SELECT *
FROM sys.traces
WHERE is_default = 1 ;

Checking the recovery model

-- SQL Server 2000

SELECT name ,
DATABASEPROPERTYEX(name, 'Recovery')
FROM sysdatabases

-- SQL Server 2005/2008

SELECT name ,
recovery_model_desc
FROM sys.databases

Calculate The values for MAX Memory for SQL Serve

PRINT ' '
PRINT ' ** Calculate the values for MAX Memory for SQL Server **'
PRINT ' '
PRINT ' Calculating the SQL Server MAX Memory setting, as general base configuration for a *dedicated* SQL Server machine.'
PRINT ' *dedicated* means that only SQL Server and related services are running on the server. NO other application is install'
PRINT ' and running on the server. Therefore it is only dedicated to run SQL Server.'
PRINT ' '
PRINT ' Below is the formula used to calculate the value to be used by SQL Server.'
PRINT ' '
PRINT ' 1. Reserve 1 Gigabyte (GB) of RAM for the Operating System (OS)'
PRINT ' 2. Reseeve 1GB of RAM for each 4GB of RAM installed from 4 - 16 GB'
PRINT ' 3. Add 1GB of RAM for every 8GB of RAM above 16GB'
PRINT ' '
PRINT ' This is a good starting point, then monitoring "Memory\Available Mbytes" performance counter to fine tune your value.'
PRINT ' '
-- Setting up variables for script
DECLARE
@TotalMEMORYinBytes NUMERIC, -- Intial memory value of physical server in bytes
@TotalMEMORYinMegaBytes NUMERIC, -- Converted value of physical server memory in megabytes
@SQLMaxMemoryMegaByte NUMERIC, -- Value to use for SQL server MAX memory value in megabytes
@RamOver16GB NUMERIC -- Used to check if physical memory is over 16 gigabytes
-- Read physical memory on server
SET @TotalMEMORYinBytes = (select physical_memory_in_bytes from sys.dm_os_sys_info)
-- Coverting value from bytes to megabytes
SET @TotalMEMORYinMegaBytes = (@TotalMEMORYinBytes /(1024*1024))
-- OS need mim 1GB of RAM. Add 1 gigabyte to final value of MAX memory
SET @SQLMaxMemoryMegaByte = 1024
-- If Total Memory is great thatn 16 GB of RAM then add 4 GB of RAM
IF @TotalMEMORYinMegaBytes > 16384
BEGIN
 SET @SQLMaxMemoryMegaByte = (@SQLMaxMemoryMegaByte + 4096) -- Add 4 gigabytes to final value of MAX memory
 SET @RamOver16GB = ((@TotalMEMORYinMegaBytes - 16384)/8) -- Determine how much memory of over the 16GB of RAM
 SET @SQLMaxMemoryMegaByte = (@SQLMaxMemoryMegaByte + @RamOver16GB) -- Add 1GB of RAM for every 8GB of RAM above 16GB to sub total
END
-- Check if Total Memory is less than 16 GB but more than 12 GB
IF (@TotalMEMORYinMegaBytes < 16384 and @TotalMEMORYinMegaBytes > 12288 ) SET @SQLMaxMemoryMegaByte = (@SQLMaxMemoryMegaByte + 4 )
-- Check if Total Memory is less than 12 GB but more than 8 GB
IF (@TotalMEMORYinMegaBytes < 12288 and @TotalMEMORYinMegaBytes > 8192) SET @SQLMaxMemoryMegaByte = (@SQLMaxMemoryMegaByte + 3 )
-- Check if Total Memory is less than 8 GB but more than 4 GB
IF (@TotalMEMORYinMegaBytes < 8192 and @TotalMEMORYinMegaBytes > 4096) SET @SQLMaxMemoryMegaByte = (@SQLMaxMemoryMegaByte + 2 )
-- Check if Total Memory is less than 4 GB
IF @TotalMEMORYinMegaBytes < 4096 SET @SQLMaxMemoryMegaByte = (@SQLMaxMemoryMegaByte + 0 )
-- Calculate Maximum Memory settings in megabytes
SET @SQLMaxMemoryMegaByte = (@TotalMEMORYinMegaBytes - @SQLMaxMemoryMegaByte)
-- Show final value to use for MAX memory in SQL server. Value is set to megabytes because interface as for value in megabytes
SELECT @TotalMEMORYinMegaBytes AS 'Total Server Memory in Megabytes ***', @SQLMaxMemoryMegaByte AS 'SQL Server MAX Memory Value in Megabytes ***'
GO
 

Finding the size of the TokenAndPermUserStore cache

SELECT SUM(single_pages_kb + multi_pages_kb) / 1024.0 AS CacheSizeMB
FROM sys.dm_os_memory_clerks
WHERE [name] = 'TokenAndPermUserStore'

Finding the scheduler queues

SELECT scheduler_id ,
current_tasks_count ,
runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

Finding the top ten CPU-consuming queries.

SELECT TOP ( 10 )
SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset ) / 2 ) + 1)
AS statement_text ,
execution_count ,
total_worker_time / 1000 AS total_worker_time_ms ,
( total_worker_time / 1000 ) / execution_count
AS avg_worker_time_ms ,
total_logical_reads ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_elapsed_time / 1000 AS total_elapsed_time_ms ,
( total_elapsed_time / 1000 ) / execution_count
AS avg_elapsed_time_ms ,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC

SQL Server execution statistics.

SELECT TOP 10
execution_count ,
statement_start_offset AS stmt_start_offset ,
sql_handle ,
plan_handle ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_logical_writes / execution_count AS avg_logical_writes ,
total_physical_reads / execution_count AS avg_physical_reads ,
t.text
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
ORDER BY avg_physical_reads DESC

Clearing the wait statistics on a server

DBCC SQLPERF('sys.dm_os_wait_stats', clear)

Finding The Top Ten Cumulative Wait Events.

SELECT TOP

10

wait_type

,

max_wait_time_ms wait_time_ms

,

signal_wait_time_ms

,

wait_time_ms

- signal_wait_time_ms AS resource_wait_time_ms ,

100.0

* wait_time_ms / SUM(wait_time_ms) OVER ( )

AS

percent_total_waits ,

100.0

* signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )

AS

percent_total_signal_waits ,

100.0

* ( wait_time_ms - signal_wait_time_ms )

/

SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits

FROM

sys.dm_os_wait_stats

WHERE

wait_time_ms > 0 -- remove zero wait_time

AND

wait_type NOT IN -- filter out additional irrelevant waits

(

'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',

'SQLTRACE_BUFFER_FLUSH'

,'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',

'LAZYWRITER_SLEEP'

, 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',

'BROKER_EVENTHANDLER'

, 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',

'CHECKPOINT_QUEUE'

, 'FT_IFTS_SCHEDULER_IDLE_WAIT',

'BROKER_TRANSMITTER'

, 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',

'LOGMGR_QUEUE'

, 'ONDEMAND_TASK_QUEUE',

'REQUEST_FOR_DEADLOCK_SEARCH'

, 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',

'DBMIRROR_EVENTS_QUEUE'

, 'BROKER_RECEIVE_WAITFOR',

'PREEMPTIVE_OS_GETPROCADDRESS'

, 'PREEMPTIVE_OS_AUTHENTICATIONOPS',

'WAITFOR'

, 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',

'RESOURCE_QUEUE'

)

ORDER BY

wait_time_ms DESC

Finding Count of Logical CPU

SELECT cpu_countFROM sys.dm_os_sys_info
GO-- Identify Virtual Processors in for SQL Server 2000CREATE TABLE #TempTable([Index] VARCHAR(2000),[Name] VARCHAR(2000),[Internal_Value] VARCHAR(2000),[Character_Value] VARCHAR(2000)) ;INSERT INTO #TempTableEXEC xp_msver;SELECT Internal_Value AS VirtualCPUCountFROM #TempTableWHERE Name = 'ProcessorCount';DROP TABLE #TempTableGO

Last or Recently Ran Query

SELECT
deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC