Sql Server Materials

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

If we want  analyze the blocking information, we can use sp_whoisactive for on going blocking. if we want  analyze the pas happed blocking information. we can relay  on third party toools (monitoring tools.). if we  don't have any monitoring tool one of the method logging the sp_whoisactive.  Below  is script for logging the sp_whoisactive.  Here  we have created two table  and creating one job. In that job there  are  4 steps, first  two step for loading data into above tables. remaining two steps for deleting the data older than 72 hours. if you want you change this.  Here place the DB ANME with your DBname  USE [DB ANME] GO /****** Object:  Table [dbo].[whoisactive_log]    Script Date: 12/3/2025 1:31:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[whoisactive_log]( [whoisactive_id] [bigint] IDENTITY(1,1) NOT NULL, [dd_hh_mm_ss_mss] [nvarchar](50) NULL, [sessio...

 Databases can use a lot of storage space. It is always good to know how the space is distributed among the tables and indexes. SQL Server allows us to query all relevant data and create our desired reports. In this article I will show different queries and alternative options to access the storage sizes at various levels of detail. What requires storage space? A database has a total size which includes the actual data, transaction log data and reserved storage space, since the database is expected to grow. The data is stored in one primary data file with the file extension .mdf, optional user-defined data files with the file extension .ndf and at least one transaction log file with the file extension .ldf. See this article about database files and filegroups to learn more about it. The data is basically separated into two types: tables and indexes. A table has either a clustered index with all data or it is a heap table. Indexes are copies of the data and need space too. Note that...

  To solve slowness of SQL Server database, you should find which wait events exists in the database. You can find wait events of database with following query. with waits as (select wait_type, wait_time_ms / 1000.0 as waits, (wait_time_ms - signal_wait_time_ms) / 1000.0 as resources, signal_wait_time_ms / 1000.0 as signals, waiting_tasks_count as waitcount, 100.0 * wait_time_ms / sum (COALESCE (wait_time_ms,1)) over() as percentage, row_number() over(order by wait_time_ms desc) as rownum from sys.dm_os_wait_stats where wait_type not in ( N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH', N'SLEEP_TASK', N'SLEEP_SYSTEMTASK', N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN', N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'BROKER_TASK_STOP',...

  -- Alter  script set  compatibility y to sql server  2022 DECLARE @DBName SYSNAME; DECLARE @SQL NVARCHAR(MAX); -- Table variable to hold database names DECLARE @DBList TABLE (DBName SYSNAME); -- Insert all user databases except system ones INSERT INTO @DBList (DBName) SELECT name FROM sys.databases WHERE database_id > 4 -- Exclude system DBs AND state_desc = 'ONLINE'; -- Initialize loop WHILE EXISTS (SELECT 1 FROM @DBList) BEGIN     -- Get one database name     SELECT TOP 1 @DBName = DBName FROM @DBList;     -- Build and execute the ALTER statement     SET @SQL = 'ALTER DATABASE [' + @DBName + '] SET COMPATIBILITY_LEVEL = 160;';     PRINT 'Updating compatibility for: ' + @DBName; PRINT @SQL     --EXEC sp_executesql @SQL;     -- Remove processed DB     DELETE FROM @DBList WHERE DBName = @DBName; END --Set  compatability  to  SQL server  2022  SELECT 'ALTER D...

  Script to Loop through all the transaction log file and restore them automatically. Below script is generating the restoring the  transactional log backups.  Initially  we have inserted transaction files into  temp table and then we have generating restoring the tractional log backup. Method1: USE master; DECLARE @t AS TABLE (fname VARCHAR(max)); INSERT @t EXEC xp_cmdshell 'dir /b /S E:\dirname\*.trn'; SELECT 'RESTORE LOG [DBNAME] FROM DISK = ''' + fname + ''' WITH NORECOVERY;' FROM @t WHERE fname IS NOT NULL Method2: Powershell  Script; To automatically loop through and restore all SQL Server transaction log files ( .trn ), you can use a PowerShell script or T-SQL script depending on your environment and preferences. Here's a PowerShell script that reads all .trn files from a folder and restores them in sequence. # Define variables $serverName = "YourSQLServerInstance" $databaseName = "YourDatabase" $backupFolder ...