TSQL Scripts & Snippets
Reusable diagnostic and administrative scripts covering wait statistics analysis, DMV queries, index maintenance, blocking chains, memory pressure, and more.
first add these counters in performance monitor
first add these counters in performance monitor
first add these counters in performance monitor
1) Failed Logins option should be enabled for tracking error in the SQL Server Error logs.
1) Find the space utilization
Transactional Replication:
No description available.
it can be -2 or -3 or -4.
Ask some basic questions:-
There can be 2 variants in SSPI errors:
TEMPDB Data File Full:-
1. first add the table to the publication and then disable the settings
for unique values
we need to add (waitfor delay 'hh:mm:ss')
Create Master Key in all Replicas
Step 1: Buffer Pool Utilization by Database
diagnostic script for automation operations.
diagnostic script for automation operations.
Step 1: Check if the full backup job is running
full backup
Execute the below script on the secondary server
diagnostic script for security-audit operations.
for single database
diagnostic script for database operations.
it will give you an approx. value of how much sql server is using memory in mb.
encryption_state = 3 encrypted; =2 in progress
diagnostic script for architecture operations.
********************************************************************************************************
diagnostic script for architecture operations.
diagnostic script for architecture operations.
for a specific database
use this on principal and mirror
diagnostic script for database operations.
diagnostic script for backup-restore operations.
diagnostic script for high-availability operations.
diagnostic script for security-audit operations.
COMMIT saves all changes made in a transaction permanently, while ROLLBACK undoes those changes. For example, COMMIT finalizes a new record, while ROLLBACK discards it.
Create Endpoints in all the Replicas and in all the Nodes With LISTENER_IP = ALL
diagnostic script for security-audit operations.
diagnostic script for security-audit operations.
if table contains <=1000 rows, don't create an index.
this one will only work from sql server 2014
enable contained database feature in instance settings
diagnostic script for automation operations.
diagnostic script for database operations.
to connect another sql server
windows authentication:
Creates NonClustered Index on the table.
local temporary table:
diagnostic script for automation operations.
to see the list of endpoints
Missing Indexes Script
most common wait types:
diagnostic script for security-audit operations.
No description available.
to block a permission:
Drop all orphaned users from custom SP
for dropping single database
diagnostic script for security-audit operations.
diagnostic script for database operations.
diagnostic script for automation operations.
No description available.
No description available.
No description available.
No description available.
diagnostic script for high-availability operations.
diagnostic script for backup-restore operations.
diagnostic script for index-maintenance operations.
for a specific database
diagnostic script for database operations.
for logins
use the following rules only with small tables
Method 1_Auto Fix:
Generate the Attach and Detach Script
diagnostic script for database operations.
diagnostic script for automation operations.
modify the command accordingly
diagnostic script for database operations.
diagnostic script for database operations.
diagnostic script for high-availability operations.
diagnostic script for database operations.
diagnostic script for database operations.
diagnostic script for database operations.
diagnostic script for automation operations.
diagnostic script for automation operations.
diagnostic script for automation operations.
diagnostic script for automation operations.
diagnostic script for automation operations.
This code will trigger an alert to your email when recovery model got change.
diagnostic script for automation operations.
diagnostic script for automation operations.
diagnostic script for automation operations.
diagnostic script for automation operations.
diagnostic script for high-availability operations.
No description available.
diagnostic script for backup-restore operations.
diagnostic script for automation operations.
diagnostic script for security-audit operations.
No description available.
old method
diagnostic script for backup-restore operations.
diagnostic script for database operations.
for simplified view, use the following script
Unused Index Script
if the query is small, use the following
diagnostic script for security-audit operations.
Finding out members of Active Directory Group that is already in SQL Server
diagnostic script for security-audit operations.
diagnostic script for security-audit operations.
diagnostic script for database operations.
diagnostic script for database operations.
diagnostic script for architecture operations.
Step 1: Set up an Extended Event session to capture session activity.
diagnostic script for database operations.
diagnostic script for automation operations.
identify where the master database files are located
diagnostic script for database operations.
identify where the temp database files are located
identify the database files location
Steps to Setup Automatic Database Refresh between Two Servers:
No description available.
to identify the drop transaction
Configure min and max memory
to set the database online
No description available.
diagnostic script for security-audit operations.
diagnostic script for database operations.
No description available.
Requirement:whenever data is inserterd,updated,or deleted in tablea that has to replicate to the tableb in same database.
Master is the most crucial database in an instance, if it is corrupt entire instance gets affected.
Steps:
Verify the reason of failure in the error logs and troubleshoot accordingly. If database is really corrupt then look out for a available valid backup. If backup is available restore MSDB as a normal u
Steps to Resolve Suspect State:
for full backup
No description available.
diagnostic script for architecture operations.
add this line in the beginning of the all the commands
diagnostic script for database operations.
Scripting Out the Logins, Server Role Assignments, and Server Permissions
sent mails:
'add this line in the beginning of the command query'
Deploys Brent Ozar's sp_Blitz — the industry-standard SQL Server health check stored procedure from the First Responder Kit.
Deploys Adam Machanic's sp_WhoIsActive — the industry-standard real-time SQL Server activity monitoring stored procedure.
diagnostic script for database operations.
create credential
first create a table to collect the data in master or desired database
1) Read fn_dblog (try to get data from the log file) OR fn_dump_dblog (try to get data from the log backup).
No description available.
diagnostic script for security-audit operations.
run this query in all the machines to view the permissions
diagnostic script for database operations.
for specific database
diagnostic script for backup-restore operations.
this is useful to findout the modified extents
diagnostic script for architecture operations.
diagnostic script for architecture operations.
diagnostic script for index-maintenance operations.
for basic settings:
diagnostic script for automation operations.
diagnostic script for index-maintenance operations.
diagnostic script for security-audit operations.
diagnostic script for database operations.
No description available.
diagnostic script for security-audit operations.
diagnostic script for automation operations.
diagnostic script for security-audit operations.
diagnostic script for architecture operations.
diagnostic script for security-audit operations.
diagnostic script for architecture operations.
diagnostic script for architecture operations.
diagnostic script for database operations.
diagnostic script for database operations.
diagnostic script for database operations.
diagnostic script for database operations.
diagnostic script for database operations.
diagnostic script for index-maintenance operations.
diagnostic script for database operations.
Get Avg Task Count and Avg Runnable Task Count
diagnostic script for security-audit operations.
diagnostic script for installation operations.
for a specific database