top of page

Search
Deadlock Priority SQL Server
Specifies the relative importance that the current session continue processing if it is deadlocked with another session. --Deadlock...
Kunal Ranpura
Jan 12, 20201 min read
24 views
0 comments
Script to Remove Replication SQL Server
--Set deadlock priority to high to make sure the script is not stopped due to deadlock SET DEADLOCK_PRIORITY HIGH --<numeric-priority>...
Kunal Ranpura
Jan 12, 20201 min read
373 views
0 comments
Script to Check log reuse wait type in sql server
--Find log_reuse_wait_desc wait type for the database: DB_Name select log_reuse_wait_desc from sys.databases where name='DB_Name'
Kunal Ranpura
Jan 12, 20201 min read
97 views
0 comments
Script to Remove Data or Log File
--Note: Check is required if you want to remove log file. Only Secondary log file and data file can be removed. checkpoint go USE...
Kunal Ranpura
Jan 12, 20201 min read
12 views
0 comments
Change Data Capture
--List of tables with CDC enabled SELECT s.name AS Schema_Name, tb.name AS Table_Name , tb.object_id, tb.type, tb.type_desc,...
Kunal Ranpura
Jan 12, 20201 min read
33 views
0 comments
Script to Fix Orphan Users in SQL Server
--Find Orphan Users in the DB EXEC sp_change_users_login 'Report' --If you already have a login created, map the user to login using the...
Kunal Ranpura
Dec 24, 20191 min read
84 views
0 comments
SQL Server Change DB Owner to SA
SQL Server Script to Change DB Owner to SA for all the user DBs in the Instance. --Script:17 Change DB Owner to SA for all the User DBs...
Kunal Ranpura
Dec 24, 20191 min read
55 views
0 comments

SQL Server Configure Distributed Transaction Coordinator (DTC)
Learn how to configure DTC
Kunal Ranpura
Dec 13, 20191 min read
79 views
0 comments

SQL Server Log Shipping Using T-SQL
T-SQL Script to Log Shipping Database. Pre-req. Check List: Database should be in Full recovery mode. Take Full database back on the...
Kunal Ranpura
Dec 7, 20193 min read
950 views
0 comments

SQL Server Database Mirroring
Learn how to configure SQL Server database mirroring using T-SQL Script.
Kunal Ranpura
Nov 30, 20192 min read
748 views
0 comments

SQL Server 2019 Accelerated Database Recovery ADR
SQL 2019 has a new feature name: ADR Accelerated Database Recovery. To enable ADR at database level: alter database wideworldimportersdw...
Kunal Ranpura
Nov 16, 20192 min read
689 views
0 comments
SQL Server Log LSN Details
select top 10 * from sys.fn_dblog(null,null) order by [Current LSN] desc go
Kunal Ranpura
Nov 16, 20191 min read
14 views
0 comments
Find Object associated with SQL Server Latch Wait Type
--Script:16 SQL Server 2019 and Above. Find DB and Object name having Page_latch Wait type. --You can use the following script as an...
Kunal Ranpura
Nov 10, 20191 min read
15 views
0 comments

PowerShell Function to Automate Monitoring
clear #Install-Module ImportExcel #output file location: c:\powershell\Report_PROD_.xlsx #Input File C:\powershell\sqlservers.txt...
Kunal Ranpura
Oct 23, 201910 min read
140 views
0 comments
SQL Server: Find Available Space in Data & Log files for all DBs in an instance
SET ANSI_NULLS ON; SET ARITHABORT ON; SET QUOTED_IDENTIFIER ON; SET NOCOUNT ON; Declare @dbname varchar(max) DECLARE @command...
Kunal Ranpura
Oct 23, 20191 min read
13 views
0 comments

5 - Scripts I use every single day
1). Script to see what is running in your SQL Server. select * from sys.sysprocesses where status = 'suspended' and spid >10 and hostname...
Kunal Ranpura
Aug 13, 20194 min read
51 views
0 comments
SQL Server Index Read/Write stats (all tables in current DB) ordered by Writes
SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName], i.name AS [IndexName], i.index_id, s.user_updates AS [Writes], user_seeks + user_scans...
Kunal Ranpura
Aug 12, 20191 min read
15 views
0 comments
SQL Server Index Read/Write stats (all tables in current DB) ordered by Reads
SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName], i.name AS [IndexName], i.index_id, user_seeks + user_scans + user_lookups AS [Reads],...
Kunal Ranpura
Aug 12, 20191 min read
9 views
0 comments
Find when index statistics where last updated
-- When were Statistics last updated on all indexes? SELECT o.name, i.name AS [Index Name], STATS_DATE(i.[object_id], i.index_id) AS...
Kunal Ranpura
Aug 12, 20191 min read
11 views
0 comments
SQL Server Find what Objects are using space in buffer cache
-- Breaks down buffers used by current database by object (table, index) in the buffer cache SELECT OBJECT_NAME(p.[object_id]) AS...
Kunal Ranpura
Aug 12, 20191 min read
22 views
0 comments
bottom of page