Error: Server Principal Already Exist

Sometimes you will face error like: Server Principal already exist while creating a Login.

1.Run the below query

SELECT SUSER_SID('dev')
GO

2.it will give an hexadecimal number like:

0x06EB61AB7AF713448F09FA5103282D43

3.Execute the following query
Select * from sys.server_principals where SID=0x06EB61AB7AF713448F09FA5103282D43

It wil give an out put like:

dev1 266 0x06EB61AB7AF713448F09FA5103282D43 S SQL_LOGIN 0 2012-10-17 18:02:05.353 2012-10-17 18:47:58.287 master us_english NULL

Remove the login "dev1: and corresponding database users and recreate the login "dev" once again

To find all logins in SQL Server that have been denied access

select  name, denylogin from sys.syslogins where denylogin ='1'

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

Query To Find Out Full Details of Databases

SELECT database_id,CONVERT(VARCHAR(25), DB.name) AS dbName,CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],user_access_desc AS [User access],recovery_model_desc AS [Recovery model],CASE compatibility_levelWHEN 60 THEN '60 (SQL Server 6.0)'WHEN 65 THEN '65 (SQL Server 6.5)'WHEN 70 THEN '70 (SQL Server 7.0)'WHEN 80 THEN '80 (SQL Server 2000)'WHEN 90 THEN '90 (SQL Server 2005)'WHEN 100 THEN '100 (SQL Server 2008)'END AS [compatibility level],CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],-- last backupISNULL((SELECT TOP 1CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +' (' + CAST(DATEDIFF(second, BK.backup_start_date,BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup],CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],page_verify_option_desc AS [page verify option],CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown]FROM sys.databases DBORDER BY dbName, [Last backup] DESC, NAME

Script To Identify Blocking Query

SELECTdb.name DBName,tl.request_session_id,wt.blocking_session_id,OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,tl.resource_type,h1.TEXT AS RequestingText,h2.TEXT AS BlockingTest,tl.request_modeFROM sys.dm_tran_locks AS tlINNER JOIN sys.databases db ON db.database_id = tl.resource_database_idINNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_addressINNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_idINNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_idINNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_idCROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

Query to Keep CPU Busy for 60 Seconds

DECLARE @T DATETIME, @F BIGINT;SET @T = GETDATE();WHILE DATEADD(SECOND,60,@T)>GETDATE()SET @F=POWER(2,30);

Finding Memory Pressure – External and Internal

The following query will provide details of external and internal memory pressure. It will return the data how much portion is assigned to what kind of memory type.


SELECT TYPE, SUM(single_pages_kb) InternalPressure, SUM(multi_pages_kb) ExtermalPressureFROM sys.dm_os_memory_clerksGROUP BY TYPE
ORDER BY SUM(single_pages_kb) DESC, SUM(multi_pages_kb) DESCGO

Find Most Expensive Queries Using DMV

SELECTTOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs
.execution_count,
qs
.total_logical_reads, qs.last_logical_reads,
qs
.total_logical_writes, qs.last_logical_writes,
qs
.total_worker_time,
qs
.last_worker_time,
qs
.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs
.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs
.last_execution_time,
qp
.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time