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

  -- Find the job names with user name with running as job owner.  SELECT j.name AS JobName,        j.owner_sid,        s.name AS JobOwner,        a.start_execution_date FROM msdb.dbo.sysjobs AS j INNER JOIN msdb.dbo.sysjobactivity AS a     ON j.job_id = a.job_id INNER JOIN sys.server_principals AS s     ON j.owner_sid = s.sid WHERE a.run_requested_date IS NOT NULL   AND a.stop_execution_date IS NULL  -- Job is still running   AND s.name LIKE  '%SVC_%';       -- Replace with the specific user -- Find the job name, with command context .  SELECT j.name AS JobName,        js.step_id,        js.step_name,        js.database_name,        js.command,        js.subsystem FROM msdb.dbo.sysjobs AS j INNER JOIN msdb.dbo.sysjobsteps AS js     ON j.job_id =...

How to  verify the CDC is enabled data bases or tables The following query  will get list  of  CDC enabled data bases  SELECT     name AS DatabaseName,     is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1; The following query  will get list  of  CDC enabled  tables  with in the  database.  USE <DB Name>;  -- Replace with actual DB name GO SELECT      s.name AS SchemaName,     t.name AS TableName,     c.capture_instance,     c.supports_net_changes,     c.start_lsn,     c.end_lsn FROM      cdc.change_tables c JOIN      sys.tables t ON c.source_object_id = t.object_id JOIN      sys.schemas s ON t.schema_id = s.schema_id; Below will get the list of tables which  are enabled CDC from all data bases.  DECLARE @dbName NVARCHAR(128) DECLARE @sql NVARCHAR(MAX) -- Cursor to loop ...

  Below Query  help  bring the  server  login name , data base level login name  along with their roles/members.  This query will help us to validate the roles/ members are mapped correctly or not.  USE master  GO CREATE TABLE #temp ( id INTEGER  IDENTITY(1,1), DatabasseName VARCHAR(100), login_name VARCHAR(500), database_user VARCHAR(500), role_name VARCHAR(500), user_type VARCHAR(500), authentication_type_desc VARCHAR(500) ) EXEC sp_foreachdb  ' USE ? INSERT INTO #temp (DatabasseName,login_name,database_user,role_name,user_type,authentication_type_desc) SELECT ''?'' AS DatabasseName,     sp.name AS login_name,     dp.name AS database_user,     dr.name AS role_name,     dp.type_desc AS user_type,     dp.authentication_type_desc FROM      sys.server_principals sp  JOIN       sys.database_principals dp ON sp.sid = dp.si...

 Hello,  To determine the filegroup where an index is created in SQL Server, you can use the following methods: 1. Using  sys.indexes  and  sys.filegroups  System Views You can query the system views to find the filegroup of an index: Sql Copy code SELECT i.name AS IndexName, o.name AS TableName, f.name AS FileGroupName FROM sys.indexes i JOIN sys.objects o ON i.object_id = o.object_id JOIN sys.data_spaces f ON i.data_space_id = f.data_space_id WHERE o.type = 'U' ; -- Only user tables This query will list all indexes, their associated tables, and the filegroups they belong to. 2. Using SQL Server Management Studio (SSMS) Open SSMS and connect to your database. Navigate to the table where the index is created. Expand the  Indexes  folder under the table. Right-click the index and select  Properties . In the  Storage  section, you can see the filegroup where the index is stored. 3. Using...

  Below help us to get the list of indices  on  table with columns and include columns also. SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) AS SchemaName,     QUOTENAME(t.name) AS TableName,     QUOTENAME(i.name) AS IndexName, i.type_desc,     i.is_primary_key,     i.is_unique,     i.is_unique_constraint,     STUFF(REPLACE(REPLACE((         SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()]         FROM sys.index_columns AS ic         INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id         WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0         ORDER BY ic.key_ordinal         FOR XML PATH     ), '<row>', ', '), '</row>', ''), 1, 2, '') AS KeyC...