Today at Day of Data Orlando, I moderated a panel discussion with Bob Ward, Celia Brusatori, Kevin Feasel, Rob Volk, and Tim Radney talking about AI in your career, and whether it’s your sidekick, hero, or villain. A few really… Read the full article at Brent Ozar
It’s a cold, gloomy day on the very last Alaskan cruise of the season. Yes, I picked this on purpose! While I talk about why this is a fun cruise to be on, let’s take your top-voted questions from https://pollgab.com/room/brento.… Read the full article at Brent Ozar
One script that checks the whole server and returns a single list, worst first: CRITICAL (fix now), WARNING (look soon), OK and INFO. It covers backups, CHECKDB, disk space, failed jobs, waits, memory, tempdb, settings and more. Health Check 01 to 12 show the detail behind each line. Needs SQL Server 2016 SP2 or later….
Version, patch level, edition, uptime, CPUs, memory and the key server settings in one place. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 01_Server_Overview.sql – version, uptime,…
Every database with its state, size, recovery model, what is holding up log reuse, and settings worth knowing (auto close, auto shrink, page verify, Query Store, RCSI, encryption). Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes…
The last full, differential and log backup of every database, hours since the last full, and the last 7 days of backup history. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server…
Free space on every drive that holds a database file, and each file’s size, space used and growth setting. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. –…
SQL Agent jobs that failed in the last 7 days, what is running now, and the last outcome of every job. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and…
What SQL Server has spent its time waiting for since it started, with the harmless background waits left out, so the real bottleneck (CPU, disk, locks, memory, network) stands out. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area….
What is running right now and who is blocking whom. If sp_WhoIsActive is installed it uses that for more detail. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025….
Errors and warnings from the SQL Server error log in the last 7 days (current and previous log): failed logins, I/O warnings, deadlocks, memory paged out and so on. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only,…
When each database last passed DBCC CHECKDB, and any suspect (possibly corrupt) pages recorded in msdb. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 09_Integrity.sql – when…
CPU use for the last few hours (SQL Server and everything else), memory use, and which memory clerks are using it. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and…
tempdb files and how full they are, the version store, and which sessions are using tempdb right now. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 11_TempDB.sql…
Health of availability groups, database mirroring and log shipping, whichever this server uses. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 12_High_Availability.sql – availability groups, mirroring and…
The queries using the most CPU, reads and time since their plans were cached. Change the ORDER BY to sort by reads or duration. For history across restarts use Query Store (Performance 02). Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 01_Top_Queries.sql – the queries using the most CPU, reads and time…
Run it in the database: queries that got slower in the last 24 hours than in the 7 days before, usually because the plan changed. Needs Query Store turned on. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 02_Query_Store_Regressions.sql – run IN the database: queries that got slower in the last 24…
Indexes SQL Server thinks would help, across all databases, since start-up, with a CREATE INDEX statement for each. Treat them as hints: check for overlap with existing indexes before creating any. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 03_Missing_Indexes.sql – indexes SQL Server thinks would help, across all databases, since start-up….
Run it in the database: indexes that are written to but never read since start-up. Only meaningful once the server has been up through a full business cycle (month end and so on). Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 04_Unused_Indexes.sql – run IN the database: indexes that are written to…
Run it in the database: statistics that are more than 7 days old or have had more than 20% of rows changed since they were last updated. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 05_Statistics_Age.sql – run IN the database: statistics that are old or have had many changes since they…
Run it in the database: fragmentation of every index over 1000 pages, using the light LIMITED scan. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 06_Index_Fragmentation.sql – run IN the database: fragmentation of indexes over 1000 pages (LIMITED scan, fairly light). Read-only. SET NOCOUNT ON; SELECT OBJECT_SCHEMA_NAME(ps.object_id) + N’.’ + OBJECT_NAME(ps.object_id) AS…
Finds where the optimiser’s row estimates are badly wrong, the root of most bad plans. Part 1 works on every version; part 2 (SQL Server 2019 and later) goes operator by operator through the last actual plan and shows the table, index and columns where a filtered statistic would help. Read-only, changes nothing. Tested on…
Run it in the database: columns whose data is lopsided enough that the normal statistics histogram misleads the optimiser, with the CREATE STATISTICS … WHERE statement that would fix it. It only suggests; it creates nothing. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 08_Filtered_Statistics_Candidates.sql – run IN the database: columns whose…
Run it in the database: indexes that duplicate or are covered by another index, and what they cost in writes. It suggests DROP statements but runs none. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 09_Duplicate_and_Overlapping_Indexes.sql – run IN the database: indexes that duplicate or are covered by another index, and what…
Run it in the database: the auto-statistics settings, how fresh and how well sampled every statistic is, and filtered statistics that have fallen behind. A fuller version of Performance 05. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 10_Statistics_Health.sql – run IN the database: how fresh and how good every statistic is,…
Which cardinality estimator each database actually uses and why: compatibility level, LEGACY_CARDINALITY_ESTIMATION, trace flags and query hints, with advice on what to change. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 11_CE_Configuration_Audit.sql – which cardinality estimator (CE) each database uses, and why. Clive Roberts DBA, 10 October 2026. Read-only. Server-wide. SQL Server…
Queries in the plan cache where SQL Server has to convert a column’s data type (usually varchar against nvarchar), which can turn a seek into a scan and ruin row estimates. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 12_Implicit_Conversions.sql – queries in the plan cache where SQL Server has to convert…
There’s a new SQL Server Management Studio update out today. I’d hold off on it for now. I had problems with the upgrade, and when I mentioned it on LinkedIn, a couple other folks chimed in that they’re having problems… Read the full article at Brent Ozar
Microsoft has started a series of blog posts helping Postgres developers who are “upgrading” to Microsoft SQL. (Microsoft’s been using that generic “SQL” term to refer to its collection of database products: Azure SQL DB, Hyperscale, Managed Instances, and SQL… Read the full article at Brent Ozar
You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In my new totally free How to Think Like… Read the full article at Brent Ozar
Cumulative update for SQL Server 2017, build 14.0.3560.2, released October 2026. Download and release notes (Microsoft KB) All current updates: SQL Server Patches.
Security update (GDR) for SQL Server 2017, build 14.0.2140.2, released October 2026. Download and release notes (Microsoft KB) All current updates: SQL Server Patches.
Cumulative update for SQL Server 2019, build 15.0.4500.2, released October 2026. Download and release notes (Microsoft KB) All current updates: SQL Server Patches.
Security update (GDR) for SQL Server 2019, build 15.0.2200.2, released October 2026. Download and release notes (Microsoft KB) All current updates: SQL Server Patches.
Cumulative update for SQL Server 2022, build 16.0.4305.5, released October 2026. Download and release notes (Microsoft KB) All current updates: SQL Server Patches.
Security update (GDR) for SQL Server 2022, build 16.0.1210.3, released October 2026. Download and release notes (Microsoft KB) All current updates: SQL Server Patches.
Cumulative update for SQL Server 2025, build 17.0.5015.4, released October 2026. Download and release notes (Microsoft KB) All current updates: SQL Server Patches.
Security update (GDR) for SQL Server 2025, build 17.0.1145.4, released October 2026. Download and release notes (Microsoft KB) All current updates: SQL Server Patches.
It happens more often than you’d think. A server is joined to a domain, or taken off one, or the DBA who set it up leaves, and suddenly nobody can get into SQL Server as sysadmin. You don’t need a password to fix it. Anyone in the server’s local Administrators group can get back in…
Kills every user session in one database, for example before a restore. Set the database name and run it first with @Apply = 0 to see the KILL commands it would run; set @Apply = 1 to run them. Your own session is skipped. Works on SQL Server 2016 to 2022 and later. — Kill…
sp_who3 works like sp_who2 but adds the running SQL text, wait type, reads, writes and elapsed time, using the sys.dm_exec_sessions and sys.dm_exec_requests DMVs. It only lists sessions that are running a request. For much more detail use Adam Machanic’s sp_WhoIsActive. Tested on SQL Server 2017 and 2025 and safe to run more than once. USE…
Which trace flags to use on SQL Server 2016 to 2022. Most of the old “always on” flags (1117, 1118, 2371, 4199, 9481) are now default behaviour or a per-database setting, so this script turns them off and keeps only the ones still worth having. Tested on SQL Server 2017 and 2025. — Trace flags…
Creates two SQL Agent jobs, DatabaseIntegrityCheck – SYSTEM_DATABASES and DatabaseIntegrityCheck – USER_DATABASES, which run Ola Hallengren’s DatabaseIntegrityCheck (DBCC CHECKDB) from the SQLDBA database every Sunday at 6 am and email DBA_Alerts if they fail. Install the Maintenance Solution in SQLDBA first (see the Database_Integrity_Check post). Run it in SSMS normal mode, not SQLCMD mode. Tested…
This post used to hold a 2016 copy of Ola Hallengren’s DatabaseIntegrityCheck procedure. Ola keeps his Maintenance Solution up to date (version 2026-09-12 at the time of writing), so always install the current version from his site rather than an old copy: SQL Server Integrity Check – documentation and examples Downloads – MaintenanceSolution.sql installs DatabaseIntegrityCheck…
Creates the SQL Agent job IndexOptimize – USER_DATABASES, which runs Ola Hallengren’s IndexOptimize from the SQLDBA database every Friday at 7 pm and emails DBA_Alerts if it fails. Install the Maintenance Solution in SQLDBA first (see the Index_Optimize post) and create the operator (see Create_Operator_and_Standard_Alerts). Run it in SSMS normal mode, not SQLCMD mode, because…
Backup procedures, tables and jobs (full, differential, log and clean-up). Change \\backupserver\ to your backup share. Updated October 2026, tested on SQL Server 2022. /* Back up the master, msdb, and model databases to ensure that you can roll back to a previous configuration if problems occur. master: This database hosts system-level information. Back up…
This post used to hold a 2016 copy of Ola Hallengren’s IndexOptimize procedure. Ola keeps his Maintenance Solution up to date (version 2026-09-12 at the time of writing), so always install the current version from his site rather than an old copy: SQL Server Index and Statistics Maintenance – documentation and examples Downloads – MaintenanceSolution.sql…
Creates the DBA_Alerts operator and 13 standard alerts: corruption errors 823, 824 and 825, severity 17 to 25, and database option changes (5084), all emailing the operator. Change [email protected] at the top to your own address before running it. Needs Database Mail and the Agent mail profile (see Setup_SQL_Server_Agent_Alerting_System). Tested on SQL Server 2017 and…
Tells SQL Server Agent to use Database Mail (profile SQL_Alerting) so it can send alert and job-failure emails. Set up Database Mail first, then run this and restart SQL Server Agent. Windows only; on Linux use mssql-conf. Tested on SQL Server 2017 and 2025 and safe to run more than once. — 06.Setup_SQL_Server_Agent_Alerting_System.sql — Author:…
Sets up tempdb: one data file per logical CPU (up to 8), all the same size and growth, plus the log. Missing files are added next to the first data file; existing files are never shrunk. Trace flags 1117 and 1118 are not needed for this on SQL Server 2016 and later. Tested on SQL…
Post-install configuration for a new instance: max server memory and MAXDOP worked out from the server’s memory and CPUs, cost threshold for parallelism, optimize for ad hoc workloads, backup compression and checksum, the DAC, error log count, model growth and Query Store. Run it as it is first: it only shows what it would change….
Creates a SQL Agent job, (00:00) Maintenance – CycleErrorLogs, that starts a new error log every night so each log stays small and easy to read. If the job already exists it is left alone. Tested on SQL Server 2017 and 2025 and safe to run more than once. — 02.Cycle_Error_Logs.sql — Author: Clive Roberts…
Gives msdb a sensible starting size and growth (50 MB, growing 200 MB) so job and backup history do not grow it in tiny steps. It only ever grows a file, never shrinks one. Tested on SQL Server 2017 and 2025 and safe to run more than once. — 01.Configure_MSDB.sql — Author: Clive Roberts DBA….
SELECT st.dbid AS QueryExecutionContextDBID, DB_NAME(st.dbid) AS QueryExecContextDBNAME, st.objectid AS ModuleObjectId, SUBSTRING(st.TEXT, dmv_er.statement_start_offset/2 + 1, (CASE WHEN dmv_er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2 ELSE dmv_er.statement_end_offset END – dmv_er.statement_start_offset)/2) AS Query_Text, dmv_tsu.session_id , dmv_tsu.request_id, dmv_tsu.exec_context_id, (dmv_tsu.user_objects_alloc_page_count – dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts, (dmv_tsu.internal_objects_alloc_page_count – dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts, dmv_er.start_time, dmv_er.command, dmv_er.open_transaction_count, dmv_er.percent_complete, dmv_er.estimated_completion_time, dmv_er.cpu_time, dmv_er.total_elapsed_time, dmv_er.reads,dmv_er.writes, dmv_er.logical_reads, dmv_er.granted_query_memory, dmv_es.HOST_NAME,…
Who is connected, to which database, from where. Uses sys.dm_exec_sessions, which replaces the old sys.sysprocesses view. Works on SQL Server 2016 to 2022 and later. — Connections per database and login (SQL Server 2016-2022) SELECT DB_NAME(s.database_id) AS [Database Name], s.login_name AS [Login Name], s.host_name AS [Host Name], s.program_name AS [Program Name], COUNT(*) AS [No Of…
VLF (virtual log file) count per database using sys.dm_db_log_info, which replaces DBCC LOGINFO from SQL Server 2016 SP2. No temp tables or sp_MSforeachdb needed. — High VLF counts can affect write performance — and they can make full database restores and crash recovery take much longer. — Try to keep VLF counts under 200 in…
declare @cmd nvarchar(200) set @cmd = ‘move \\server\UserDB\*.bak \\server\UserDB\Archive’ exec xp_cmdshell @cmd
VLF detail per database: count, active VLFs, log size, average VLF size and what is stopping log reuse. Uses sys.dm_db_log_info (SQL Server 2016 SP2 and later). The fix for a high count is at the bottom. — VLF detail per database (SQL Server 2016 SP2 and later) — Shows how many VLFs there are, how…
Error message when you open SQL Server Configuration Manager in SQL Server: “Cannot connect to WMI provider. You do not have permission or the server is unreachable” https://support.microsoft.com/en-us/kb/956013
Static Tables – Set Fill Factor at 100 (or default server fill factor), As these tables are never changing, keeping the Fill Factor at 100 is the best option. They conserve the space, and also there is no fragmentation. Tables Updated Less Often – Set Fill Factor at 95. These tables are more or less…
— 25 missing indexes that will improve performance the most SELECT TOP 25 dm_mid.database_id AS DatabaseID, DB_NAME(dm_mid.database_id) AS [Database], dm_migs.avg_user_impact * ( dm_migs.user_seeks + dm_migs.user_scans ) AS [Avg Estimated Impact], dm_migs.last_user_seek AS [Last Usage Time], OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [Table], ‘CREATE INDEX [IDX_’ + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + ‘_’ + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ”), ‘, ‘, ‘_’), ‘[‘, ”),…
SELECT( cpu_count / hyperthread_ratio ) AS NumberOfPhysicalCPUs, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE(( cpu_count – hyperthread_ratio ) / ( cpu_count / hyperthread_ratio )) END AS NumberOfCoresInEachCPU, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE( cpu_count / hyperthread_ratio ) * (( cpu_count – hyperthread_ratio ) / ( cpu_count / hyperthread_ratio )) END AS TotalNumberOfCores,…
Classic way to check the database is alright. DBCC CHECKDB(‘DatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS
In the SQL Server Management Studio, to find out details of the active transaction, execute following command DBCC opentran() You will get the detail of the active transaction, then from the SPID of the active transaction. For example, if SPID is 69 then execute the command as sp_who2 69 sp_lock 69 Now , you can…
SELECT SJ.name as job_name , SJ.enabled as is_job_enabled , SS.enabled as is_schedule_enabled /*, ISNULL(SJ.description, ”) as job_desc*/ , SS.name as schedule_name , CASE freq_type WHEN 1 THEN ‘Occurs on ‘ + STUFF(RIGHT(active_start_date, 4), 3,0, ‘/’) + ‘/’ + LEFT(active_start_date, 4) + ‘ at ‘ + REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3,…
— Unused Index Script SELECT TOP 25 o.name AS ObjectName , i.name AS IndexName , i.index_id AS IndexID , dm_ius.user_seeks AS UserSeek , dm_ius.user_scans AS UserScans , dm_ius.user_lookups AS UserLookups , dm_ius.user_updates AS UserUpdates , p.TableRows , ‘DROP INDEX ‘ + QUOTENAME(i.name) + ‘ ON ‘ + QUOTENAME(s.name) + ‘.’ + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS ‘drop statement’…
SELECT DISTINCT wt.wait_type FROM sys.dm_os_waiting_tasks AS wt JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id WHERE s.is_user_process = 0
DECLARE @CounterPrefix NVARCHAR(30) SET @CounterPrefix = CASE WHEN @@SERVICENAME = ‘MSSQLSERVER’ THEN ‘SQLServer:’ ELSE ‘MSSQL$’ + @@SERVICENAME + ‘:’ END ; — Capture the first counter set SELECT CAST(1 AS INT) AS collection_instance , [OBJECT_NAME] , counter_name , instance_name , cntr_value , cntr_type , CURRENT_TIMESTAMP AS collection_time INTO #perf_counters_init FROM sys.dm_os_performance_counters WHERE ( OBJECT_NAME =…
SELECT TOP 10 execution_count , statement_start_offset AS stmt_start_offset , sql_handle , plan_handle , total_logical_reads / execution_count AS avg_logical_reads , total_logical_writes / execution_count AS avg_logical_writes , total_physical_reads / execution_count AS avg_physical_reads , t.text FROM sys.dm_exec_query_stats AS s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t ORDER BY avg_physical_reads DESC
SELECT TOP 10 wait_type , max_wait_time_ms wait_time_ms , signal_wait_time_ms , wait_time_ms – signal_wait_time_ms AS resource_wait_time_ms , 100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits , 100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits , 100.0 * ( wait_time_ms – signal_wait_time_ms ) / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits FROM sys.dm_os_wait_stats WHERE…
A tail-log backup captures the log records that have not been backed up yet, so a restore loses nothing. Take one before restoring over a database, or after a failure if the log file can still be read. The database must be in FULL or BULK_LOGGED recovery. Tested on SQL Server 2017 and 2025. In…
Types of Replication Microsoft SQL Server provides the following types of replication for use in distributed applications: Transactional replication. For more information, see Transactional Replication. Merge replication. For more information, see Merge Replication. Snapshot replication. For more information, see Snapshot Replication.
Index type Description Clustered A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values. Non clustered A non clustered…
Database Engine Stored Procedures (Transact-SQL) SQL Server 2014 QL Server supports the following system stored procedures that are used for general maintenance of an instance of SQL Server. sp_add_data_file_recover_suspect_db sp_executesql sp_add_log_file_recover_suspect_db sp_getapplock sp_addextendedproc sp_getbindtoken sp_addextendedproperty sp_help sp_addmessage sp_helpconstraint sp_addtype sp_helpdb sp_addumpdevice sp_helpdevice sp_altermessage sp_helpextendedproc sp_attach_db sp_helpfile sp_attach_single_file_db sp_helpfilegroup sp_autostats sp_helpindex sp_bindefault sp_helplanguage sp_bindrule sp_helpserver sp_bindsession…
READ UNCOMMITTED Specifies that statements can read rows that have been modified by other transactions but not yet committed. Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would…
CONCAT Function Prior to SQL Server 2012, we used to use the “+” operator to combine or concatenate two or more string values but starting with SQL Server 2012, we can use CONCAT function to perform this type of operation more neatly. For example, the below script and screenshot shows usage of the “+” operator…
STUFF Function We looked at using the CONCAT function to concatenate or combine two or more string values. With this function, string values are appended at the end of the prior string but there are times when you want to insert a string value into another string value or replace a part of one string…
To analyze further, the following query can be used to find out which indexes don’t have clustered indexes defined: WITH CTE_1 AS ( SELECT db_name() as dbname, o.name as tablename, (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = i.index_id AND i.object_id = p.object_id) as number_of_rows FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id =…
These three issues are all related, yet very different. When we deal with reports of problems, it’s essential to clearly identify what is actually happening. Sometimes, we need clarification by asking about the symptoms, instead of accepting what the user says without delving any deeper. The root cause of a problem may not even be…
SET XACT_ABORT is helpful in many ways. example: suppose you are working on a stored procedure which is doing a complex data manipulation. You though this query will run with in 1 minute when you run it from .NET or other code , but some how it took quite long time and with in one…
Locking hint Description HOLDLOCK Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE. NOLOCK Do not issue shared locks and do not honor exclusive locks. When this option is in effect,…
Errors 823,824, and 825 To do this, run the following command: USE msdb GO EXEC msdb.dbo.sp_add_alert @name = N’823 – Read/Write Failure’, @message_id = 823, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 1 GO EXEC msdb.dbo.sp_add_alert @name = N’824 – Page Error’, @message_id = 824, @severity = 0, @enabled = 1,…
The DBCC commands a DBA still uses on SQL Server 2016 to 2022, grouped by job, with the old ones that have gone or been replaced at the end. Examples tested on SQL Server 2017 and 2025. Checking for corruption DBCC CHECKDB Checks the allocation, structure and logical integrity of every object in a database….
As we use PERFMON – Performance Monitor to identify real time happening on SQL Server, Here using a DMV “sys.dm_os_performance_counters” may be used to calculate number of transactions on SQL Server as DECLARE @Counter bigint SELECT @Counter = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘Transactions/sec’ AND object_name = ‘SQLServer:Databases’ AND instance_name = ‘Test’ — Your…
Data, log and total size of each user database in MB, with recovery model, compatibility level and state. Uses the file type rather than the .mdf/.ldf name, so extra .ndf files are counted. Works on SQL Server 2016 to 2022 and later. — Data, log and total size per user database in MB (SQL Server…
SELECT P.[publication] AS [Publication Name] ,A.[publisher_db] AS [Database Name] ,A.[article] AS [Article Name] ,A.[source_owner] AS [Schema] ,A.[source_object] AS [Table] FROM [distribution].[dbo].[MSarticles] AS A INNER JOIN [distribution].[dbo].[MSpublications] AS P ON (A.[publication_id] = P.[publication_id]) ORDER BY P.[publication], A.[article];
SELECT @@SERVERNAME AS server_name, sjs.last_run_outcome,sj.enabled, sj.name, sjs.step_id, sjs.step_name, sjs.database_name, CAST(CAST(sjs.last_run_date AS varchar(10)) AS datetime) AS failed_date FROM msdb.dbo.sysjobsteps AS sjs INNER JOIN msdb.dbo.sysjobs AS sj ON sjs.job_id = sj.job_id WHERE (sjs.last_run_date <> 0) AND (CAST(CAST(sjs.last_run_date AS varchar(10)) AS datetime) > GETDATE() – 2) ORDER BY sj.name
SELECT r.session_id, r.command, CONVERT(NUMERIC(6,2),r.percent_complete)AS [Percent Complete], CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) AS [SQL Command] FROM sys.dm_exec_requests AS r WHERE r.command IN (‘RESTORE DATABASE’,’BACKUP DATABASE’)
select pvt.abc,pvt.def,pvt.ghi from ( SELECT ConnectionName, Connection.ConnectionString FROM dbo.Global_Applications_ConnectionString AS Connection ) AS SourceTable pivot ( min(ConnectionString) for ConnectionName in (abc,def,ghi) ) as pvt
select article, * from dbo.MSreplication_objects where article like ‘Table_name’ select sp.name, ss.srvname, sa.name from sysarticles sa join syssubscriptions ss on ss.artid = sa.artid join syspublications sp on sa.pubid = sp.pubid where sa.name like ‘table_name’
SELECT DB_NAME() AS [Database], T.TABLE_NAME AS [Tables without Primary Keys] FROM INFORMATION_SCHEMA.TABLES AS T WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND T.TABLE_NAME = TC.TABLE_NAME) AND T.TABLE_TYPE = ‘BASE TABLE’
USE master; GO — Create the Event Session IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’DBDeletedCreated’) DROP EVENT SESSION DBDeletedCreated ON SERVER; GO CREATE EVENT SESSION DBDeletedCreated ON SERVER ADD EVENT sqlserver.object_created ( SET collect_database_name = (1) ACTION(sqlserver.nt_username,sqlserver.session_id,sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.sql_text) WHERE object_type = ‘DATABASE’ ), ADD EVENT sqlserver.object_deleted( SET collect_database_name = (1) ACTION(sqlserver.nt_username,sqlserver.session_id,sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.sql_text) WHERE object_type = ‘DATABASE’ )…
SELECT CustomerID , SalesOrderID , CAST(OrderDate AS DATE) AS OrderDate , TotalDue , LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS TD FROM Sales.SalesOrderHeader ORDER BY CustomerID
CREATE PROCEDURE dbo.ParseXMLtoTable @strXML AS XML ,@rootnode NVARCHAR(255) AS BEGIN SET NOCOUNT ON DECLARE @strText AS NVARCHAR(MAX) ,@idoc INT ,@id INT ,@parentid INT IF OBJECT_ID(‘tempdb..#ChildList’) IS NOT NULL DROP TABLE #ChildList CREATE TABLE #ChildList ( [RowNum] INT IDENTITY(1,1) NOT NULL, [parentid] INT NULL, [id] INT NULL, PRIMARY KEY (RowNum)) IF OBJECT_ID(‘tempdb..#NodeList’) IS NOT NULL DROP…
IF EXISTS (SELECT * FROM sys.objects WHERE type = ‘P’ AND name = ‘Usp_findstring’) DROP PROCEDURE Usp_findstring GO CREATE PROC Usp_findstring (@INPUTSTRING VARCHAR(max), @SEARCHSTRING VARCHAR(500)) AS BEGIN DECLARE @OCCURENCES AS INT, @POSITION AS INT, @RETURNVALUE INT SET @OCCURENCES = 0 SET @POSITION = 0 WHILE @POSITION < Len(@INPUTSTRING) BEGIN IF Charindex(@SEARCHSTRING, @INPUTSTRING, @POSITION) > 0…
— “DT” is an abbreviation for “DATETIME”, not “DATE” DECLARE @StartDT DATETIME ,@EndDT DATETIME ; SELECT @StartDT = ‘2000-01-01 10:30:50.780’ ,@EndDT = ‘2000-01-02 12:34:56.789’ ; –===== Display the dates and the duration SELECT StartDT = @StartDT ,EndDT = @EndDT ,Duration = @EndDT-@StartDT ;
How to Fix SQL Server Database Error 3707? — 1. ALTER Database leads SET Emergency; GO — 2. ALTER Database leads Set Single_User GO — 3. DBCC CHECKDB (leads, REPAIR_ALLOW_DATA_LOSS) With ALL_ERRORMSGS; GO — 4. ALTER Database leads set multi_user GO
DBCC SHRINKDATABASE (N’Leads’, 0,TRUNCATEONLY)
backup.sql BACKUP DATABASE [AdaptiveCR] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdaptiveCR.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdaptiveCR-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO MF20bak.cmd sqlcmd -S .\SQLEXPRESS -i C:\backup.sql Scheduled Tasks to call MF20bak.cmd
EXEC sys.sp_configure N’xp_cmdshell’, 1;
How to output a file from a stored procedures in MS SQL, this is how I did it a few years ago exec master..xp_cmdShell ‘bcp “SELECT url.loc, url.publication_date, url.keywords FROM ICONews..vw_googlenews url For XML Auto,Elements” queryout C:\Inetpub\wwwroot\website\SQL\google.xml -c -t, -T -S IP address’ Link: http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell
MS SQL To find the start of the month This code: DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) was excuted on 2/3/2010 at 16:11 the result: 01/03/2010 00:00:00 DBA-sentinel
Precious Lite 2025 | All Rights Reserved. Precious Lite theme by Flythemes