Posts

SQLDBA - finding out dated statics

-
 -- Script - Find Details for Statistics of Whole Database SELECT DISTINCT OBJECT_NAME(s.[object_id]) AS TableName, c.name AS ColumnName, s.name AS StatName, STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated, DATEDIFF(d,STATS_DATE(s.[object_id], s.stats_id),getdate()) DaysOld, dsp.modification_counter, s.auto_created, s.user_created, s.no_recompute, s.[object_id], s.stats_id, sc.stats_column_id, sc.column_id FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id JOIN sys.partitions par ON par.[object_id] = s.[object_id] JOIN sys.objects obj ON par.[object_id] = obj.[object_id] CROSS APPLY sys.dm_db_stats_properties(sc.[object_id], s.stats_id) AS dsp WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1 AND (s.auto_created = 1 OR s.user_created = 1) ORDER BY DaysOld; --Script 2: Update Statistics for Database --EXEC sp_updatestats; --GO
Post a Comment
Read more

SQLDBA - High tempdb Usage: Queries Exceeding 100 MB in the Past 24 Hours

-
 USE [msdb] GO /****** Object:  Job [BTCH - High TEMPDB Usage Details]    Script Date: 2/20/2026 2:42:10 AM ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object:  JobCategory [[Batch - H]]    Script Date: 2/20/2026 2:42:10 AM ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Batch - H]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Batch - H]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'BTCH - High TEMPDB Usage Details',  @enabled=1,  @notify_level_eventlog=0,  @notify_level_email=0,  @notify_level_netsend=0,  @notify_level_page=0,  @delete_level=0,  @description=N'High TEMPDB Usage Details',  @category_name=N'[Batch - H]',  @owner_login_name=...
Post a Comment
Read more

SQLDBA_ Shrink tempDB

-
   create proc dbo.sp_chi_shrinkdata   (    @dbname  sysname = null,    @freed_space  int = null, --  the portion of free space (MB) to reclaim, eg. 90% of 1 gb is 900 mb    @stats   int = null --  Display status message every @stats MB.   )   as      /*  Author:  **  Created on: **  Modified on:  **  Purpose: Shrinks data file (not log).   **  Features:   **    1. Can shrink data size down to a specified value (optional). If desired size not provided, it    **       will shrink all the way down until there's 200 MB free space left;   **    2. Can report time, data size and cycle number after each run. If echo interval not supplied,    **       it will report at every 20 MB interval.   **  3. No n...
Post a Comment
Read more

SQLDBA- Large Temp Trans Notification

-
USE [msdb] GO /****** Object:  Job [ADM - Large Temp Trans Notification]    Script Date: 2/20/2026 2:35:31 AM ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object:  JobCategory [Database Maintenance]    Script Date: 2/20/2026 2:35:31 AM ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ADM - Large Temp Trans Notification',  @enabled=1,  @notify_level_eventlog=2,  @notify_level_email=0,  @notify_level_netsend=0,  @notify_level_page=0,  @delete_level=0,  @description=N'No description available.',  @category_name=N'Database Maint...
Post a Comment
Read more

SQLDBA - TempDB usage details

-
 TempDB usage details that you can use for monitoring or troubleshooting in SQL Server. I’ll include the most common DMV queries and what each one tells you. 🔍 TempDB Usage – Key Details & Diagnostic Queries 1. TempDB Space Usage (By File) Shows how much space is used/free in each TempDB data file. SQLSELECT    name AS FileName,    size/128 AS TotalMB,    CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128 AS UsedMB,    (size - FILEPROPERTY(name, 'SpaceUsed'))/128 AS FreeMBFROM tempdb.sys.database_files;Show more lines 2. TempDB Usage by Session Helps identify which sessions are consuming TempDB. SQLSELECT     session_id,    user_objects_alloc_page_count AS UserAllocatedPages,    internal_objects_alloc_page_count AS InternalAllocatedPages,    user_objects_dealloc_page_count AS UserDeallocatedPages,    internal_objects_dealloc_page_count AS InternalDeallocatedPagesFROM sys.dm_db_session_...
Post a Comment
Read more

SQLDBA- Troubleshoot replication issue.

-
While encounter the error with replication. We can find which table  name and  which record having an issue  with replication with below commands.  we need to  run these below commands in distribution database.     -- run below command in distribution data base  USE <DB name> GO SELECT TOP(10)* FROM MSrepl_errors ORDER BY TIME DESC -- Take Xact_Seqno & command _id from above resulut set and pass in below command SELECT * FROM Msrepl_commands WHERE xact_seqno =  AND Command_id = We need to pass below values from above result set.  EXEC SP_BROWSEREPLCMDS @xact_seqno_start = '', xact_seqno_end = '', @publisher_database_id= 1, @article_id=1, @command_id=1
Post a Comment
Read more

SQLDBA-Longer TempDB OpenTran

-
  Get alert long open transaction on Temp DB. USE [msdb] GO /****** Object:  Job [ADM - Longer TempDB OpenTran]    Script Date: 12/15/2025 10:17:01 AM ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object:  JobCategory [[On Demand]]    Script Date: 12/15/2025 10:17:01 AM ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[On Demand]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[On Demand]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA - Longer TempDB OpenTran',  @enabled=1,  @notify_level_eventlog=0,  @notify_level_email=2,  @notify_level_netsend=0,  @notify_level_page=0,  @delete_level=0,  @description=N'No description available.',  @category_name=N'...
Post a Comment
Read more