Sql Server Materials

 -- 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

 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=...

   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...

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...

 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_...