A modified and hardened process that updates statistics

SET NOCOUNT ON DECLARE @name VARCHAR(128) = '' -- database name , @update_stats varchar(max) = 'EXECUTE {DBNAME}..sp_updatestats' , @sql_script varchar(max) = '' , @errorMessage varchar(max) = '' , @Recips VARCHAR (1024) = 'foo@foo.org; ' -- , @Subject VARCHAR (1024) = 'TESTING: Failure of maintenance plan for updating statistics ' , @Subject VARCHAR (1024) = 'Failure of maintenance plan for updating statistics ' -- DEV -- , @mailProfile VARCHAR (64) = 'Mail' -- QA , @mailProfile VARCHAR (64) = 'Mail' -- Prod -- , @mailProfile VARCHAR (64) = 'Mail' /* SELECT @Recips += ISNULL (ap.pValue, 'Elmer; ') FROM dbo.params ap WHERE ap.pName = 'Elmer' */ DECLARE db_cursor CURSOR FOR -- Force an error for testing ... -- SELECT name SELECT QUOTENAME (name) FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb','AspNetSqlSessionState') OPEN db_cursor BEGIN TRY FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SELECT @name SELECT GETDATE () [Start] SET @sql_script = REPLACE(@update_stats, '{DBNAME}', @name) EXEC (@sql_script) FETCH NEXT FROM db_cursor INTO @name SELECT GETDATE () [End] END END TRY BEGIN CATCH SELECT @errorMessage += @name + ': ' + someDB.dbo.fnGetErrorTryCatch () END CATCH CLOSE db_cursor DEALLOCATE db_cursor SELECT @errorMessage IF ISNULL (@errorMessage, '') != '' BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = @mailProfile , @recipients = @Recips , @body_format = 'HTML' , @body = @errorMessage , @subject = @Subject END

How to tell if statistics are actually being updated

Even if everything 'looks' good: SELECT 'Index Name' = i.name, 'Statistics Date' = STATS_DATE(i.object_id, i.index_id) FROM sys.objects o JOIN sys.indexes i ON o.name = 'ORDERs' AND o.object_id = i.object_id; GO

Deceptively simple ... and potentially evil

drop table tNUM CREATE TABLE dbo.tNUM ( NUM INT PRIMARY KEY ); WITH L0 AS (SELECT 1 AS c UNION ALL SELECT 1), -- 2 L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), -- 4 L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), -- 16 L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), -- 256 L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 L6 AS (SELECT 1 AS c FROM L5 AS A CROSS JOIN L5 AS B), -- 18,446,744,073,709,551,616 ... ---- Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L2) Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L5) --L6) INSERT INTO dbo.tNUM ( NUM ) SELECT -- TOP (1000) n FROM Nums --SELECT TOP (1000) -- n --FROM Nums ORDER BY n; SELECT COUNT (*) FROM tNum

FULL-TEXT (FT), SQL Server, Index Blocking, SQL Server

Context: FULL-TEXT (FT), SQL Server, Index Blocking, SQL Server FT_MON, FT_SEARCH, FT_CRAWL Resolution: a.) Verify that FT is the issue with sp_who2 b.) Put the database in single user mode and drop the index. ALTER DATABASE [foobar] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DROP FULLTEXT INDEX ON foo; GO ALTER DATABASE [foobar] SET MULTI_USER WITH ROLLBACK IMMEDIATE; USE [Shared] GO ALTER -- CREATE PROC dbo. NoBotFindFullTextIndices ( @TableName VARCHAR (256) = '' , @DatabaseName VARCHAR (256) = '' , @found INT = 0 OUTPUT ) AS BEGIN /*************************************************************** Author 2018-02-28 15:42:50.917 1968.95 Source: https://stackoverflow.com/questions/16280918/how-to-find-full-text-indexing-on-database-in-sql-server-2008 Execution: DECLARE @foundIt INT = 0 EXEC [Shared].dbo.sp_NoBotFindFullTextIndices 'OLS_PRODUCT_SEARCH' , 'SMCO-PERSONIFY-DEV' , @found = @foundIt OUTPUT Note - a simpler way but with less utility ... SELECT DISTINCT object_name(fic.[object_id])as table_name , [name] FROM [foobar].sys.fulltext_index_columns fic JOIN [foobar].sys.columns c on c.[object_id] = fic.[object_id] and c.[column_id] = fic.[column_id] ****************************************************************/ SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @execMe NVARCHAR (2048) = '' SET @execMe = ' SELECT COUNT (t.name) FROM [' + @DatabaseName + '].sys.tables t JOIN [' + @DatabaseName + '].sys.fulltext_indexes fi ON t.[object_id] = fi.[object_id] JOIN [' + @DatabaseName + '].sys.fulltext_index_columns ic ON ic.[object_id] = t.[object_id] JOIN [' + @DatabaseName + '].sys.columns cl ON ic.column_id = cl.column_id AND ic.[object_id] = cl.[object_id] JOIN [' + @DatabaseName + '].sys.fulltext_catalogs c ON fi.fulltext_catalog_id = c.fulltext_catalog_id JOIN [' + @DatabaseName + ']. sys.indexes i ON fi.unique_index_id = i.index_id AND fi.[object_id] = i.[object_id] WHERE t.name = ''' + @TableName + ''';' EXEC @found = sp_executeSQL @execMe END