sql solace

NSSM : Starting applications on startup

2020-03-01T18:04:00.000+00:00

Great tool to start applications on startup. NSSM : The Non sucking service manager.

Cursor : Loop Tables

2019-10-23T21:05:00.001+01:00

Cursor to loop tables.

I use this pattern a lot in DBA scripts...

DECLARE @SQL NVARCHAR(1000)
DECLARE @input_schema VARCHAR(100)
DECLARE @input_table VARCHAR(100)
DECLARE @current_schema VARCHAR(100)
DECLARE @current_table VARCHAR(100)

SET @input_schema = null
SET @input_table = null

DECLARE tablecursor CURSOR FOR
  SELECT table_schema, table_name
  FROM   information_schema.TABLES
  WHERE  TABLE_TYPE = 'BASE TABLE'
    AND  (TABLE_SCHEMA = @input_schema OR (@input_schema = '' OR @input_schema is null))
    AND  (TABLE_NAME = @input_table OR (@input_table = '' OR @input_table is null))

OPEN tablecursor

FETCH NEXT FROM tablecursor INTO @current_schema, @current_table

WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @SQL = 'EXEC add_to_table_queue '+ @current_schema + ' , ' + @current_table

      PRINT @SQL
      --EXEC Sp_executesql @SQL

      FETCH NEXT FROM tablecursor INTO @current_schema, @current_table
  END

CLOSE tablecursor
DEALLOCATE tablecursor

SQL 2000 : Online & Offline databases

2019-03-19T14:38:00.001+00:00

I can't believe I still need these in 2019...

-- SQL 2000 : Offline databases
select * from master..sysdatabases
where status & 512 = 512
and dbid > 4

-- SQL 2000 : Online databases
select * from master..sysdatabases
where status & 512 <> 512
and dbid > 4

TSQL : Loop a date range

2019-01-10T23:27:00.001+00:00

Useful for Incremental processing...

DECLARE @minDate DATE
SET @minDate = '1997-02-13'
DECLARE @maxDate DATE
SET @maxDate = GETDATE()
 
DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)
DECLARE @counterDate DATE
DECLARE @counterDateText VARCHAR(30)
DECLARE @MinCount INT
DECLARE @Stepsize INT
 
DECLARE @stepstart DATETIME
DECLARE @stepend DATETIME
DECLARE @duration bigint
DECLARE @message varchar(200)

SELECT @Counter = DATEDIFF(dd,@minDate,@maxDate)
SELECT @MinCount = 0
SELECT @Stepsize = 1
 
WHILE @Counter >= @MinCount 
 
BEGIN
SET @CounterText = CONVERT(VARCHAR(30),@Counter,23)
--RAISERROR (@CounterText, 10, 1) WITH NOWAIT  
 
SET @counterDate = DATEADD(dd,-@counter,@maxDate)
SET @counterDateText = CONVERT(VARCHAR(30),@counterDate,120)

RAISERROR (@counterDateText, 10, 1) WITH NOWAIT  

SET @stepstart = GETDATE()


-- do useful stuff here
-- WAITFOR DELAY '00:00:01'

SET @stepend = GETDATE()

SET @duration = DATEDIFF(ss,@stepstart,@stepend)
SET @message = 'Took ' + CONVERT(varchar(20),@duration) + ' seconds'
RAISERROR (@message, 10, 1) WITH NOWAIT  

 
SELECT @Counter = @Counter - @Stepsize 
END

TED Talk : Forget the pecking order at work

2018-12-17T22:48:00.000+00:00

This TED talk feels rather relevant right now

Link : Forget the pecking order at work

Manually Checking Table Statistics

2018-08-03T23:48:00.001+01:00

How to examine table statistics and update them if necessary

UPDATE STATISTICS  dbo.Study WITH FULLSCAN

DBCC SHOW_STATISTICS ('dbo.study','pk_study')

SELECT COUNT(*) AS [RowsInTable] FROM dbo.study

findInName - Stored Procedure to search all object names

2018-07-31T00:40:00.002+01:00

-search all databases
CREATE OR ALTER PROC tools.findInName
(@searchstring VARCHAR(200))
AS
BEGIN
SET NOCOUNT ON
DECLARE @results TABLE (
    [search_term] VARCHAR(255),
    [database_name] VARCHAR(255),
    [schema_name] VARCHAR(255),
    [object_Name] VARCHAR(255),
 [type_desc] VARCHAR(255) )

DECLARE @dbname sysname;
DECLARE @actualsearchstring VARCHAR(200);

SET @actualsearchstring = REPLACE(@searchstring,'_','/_')

DECLARE @dsql nvarchar(max) = '
SELECT  ''' +@searchstring + '''  AS [search_term]
     ,DB_NAME() AS [database_name]
  ,TABLE_SCHEMA
  ,TABLE_NAME
  ,TABLE_TYPE 
 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE ''%'+@actualsearchstring+'%'' ESCAPE ''/''

UNION ALL

SELECT   ''' +@searchstring + '''  AS [search_term]
     ,DB_NAME() AS [database_name]
     ,ROUTINE_SCHEMA
  ,ROUTINE_NAME
  ,ROUTINE_TYPE 
 FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_NAME LIKE ''%'+@actualsearchstring+'%'' ESCAPE ''/''

';
DECLARE @execCommand nvarchar(max);

DECLARE dbs CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY 
FOR
SELECT name FROM sys.databases (NOLOCK)
OPEN dbs;
FETCH NEXT FROM dbs INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @execCommand = 'EXEC ' + QUOTENAME(@dbname) + '.sys.sp_executesql @dsql';

    INSERT @results
    EXEC sys.sp_executesql @execCommand, N'@dsql nvarchar(max)', @dsql

    FETCH NEXT FROM dbs INTO @dbname; 
END

CLOSE dbs;
DEALLOCATE dbs;
SELECT * FROM @results
END
GO

EXEC tools.findInName '_'

TSQL - Searching for Escaped Characters

2018-07-31T00:38:00.001+01:00

Use the ESCAPE keyword to define a character to use to escape a literal. The _ underscore cannot usually be searched, to do so escape it like this.

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%/_%' ESCAPE '/'

findInCode - Stored Procedure to search code in all databases for a string

2018-07-31T00:34:00.001+01:00

--search all databases
CREATE OR ALTER PROC tools.findInCode
(@searchstring VARCHAR(200))
AS
BEGIN
SET NOCOUNT ON
DECLARE @results TABLE (
     [searchTerm] VARCHAR(255)
    ,[databaseName] VARCHAR(255)
    ,[schemaName] VARCHAR(255)
    ,[objectName] VARCHAR(255)
 ,[typeDesc] VARCHAR(255) )
DECLARE @dbname sysname;
DECLARE @dsql nvarchar(max) = '
 SELECT ''' +@searchstring + '''  AS [search_term]
   ,DB_NAME() AS [databaseName]
   ,SCHEMA_NAME(schema_id) as schemaName
   ,o.name AS ObjectName
   ,o.type_desc AS typeDesc
 FROM sys.sql_modules m (NOLOCK)
 INNER JOIN sys.objects o (NOLOCK) ON m.object_id = o.object_id
 WHERE m.definition LIKE ''%'+@searchstring+'%''
';
DECLARE @execCommand nvarchar(max);

DECLARE dbs CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY 
FOR
SELECT name FROM sys.databases (NOLOCK)
OPEN dbs;
FETCH NEXT FROM dbs INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @execCommand = 'EXEC ' + QUOTENAME(@dbname) + '.sys.sp_executesql @dsql';

    INSERT @results
    EXEC sys.sp_executesql @execCommand, N'@dsql nvarchar(max)', @dsql

    FETCH NEXT FROM dbs INTO @dbname; 
END

CLOSE dbs;
DEALLOCATE dbs;
SELECT * FROM @results
END
GO

EXEC tools.findInCode 'findme'

Database Mirroring : TSQL Health Scripts

2018-07-27T00:17:00.000+01:00

-- Dynamic Managment View of configuration information

SELECT db_name(sd.[database_id])           AS [Database Name]
      ,sd.mirroring_state                  AS [Mirror State]
      ,sd.mirroring_state_desc             AS [Mirror State]
      ,sd.mirroring_partner_name           AS [Partner Name]
      ,sd.mirroring_role_desc              AS [Mirror Role] 
      ,sd.mirroring_safety_level_desc      AS [Safety Level]
      ,sd.mirroring_witness_name           AS [Witness]
      ,sd.mirroring_connection_timeout AS [Timeout(sec)]
FROM sys.database_mirroring AS sd
WHERE mirroring_guid IS NOT null
ORDER BY [Database Name];

-- history of all mirored transactions in last 2 hours

USE msdb;

EXEC sp_dbmmonitorresults DatabaseName, 2, 0;

--os performance counters about mirroring

SELECT *,[counter_name] as CounterName,[cntr_value] as CounterValue
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ('%Database Mirroring%')
AND [instance_name]='mirroredDatabase'

Linked Server Setting - Promotion of Distributed Transactions

2018-07-22T01:17:00.003+01:00

This error occured when I tried to access a stored proedure via a linked server.
Getting the results worked fine, simply executing the stored procedure.

EXEC [SERVERNAME].[DatabaseName].[SchemaName].[myStoredProcedure]

But putting the results into a temporary table meant it became a 'distributed transaction'

CREATE TABLE ##tempTable (column1, column2...)
INSERT INTO ##tempTable
EXEC [SERVERNAME].[DatabaseName].[SchemaName].[myStoredProcedure]

OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 12
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" was unable to begin a distributed transaction.

Both servers were already running the Distributed Transaction Coordinator (DTC) service.
Multiple posts suggest tuning the security of DTC, but I eventually found the solution was much simpler. Like the setting for Remote Procedure calls RPC, it turned out to be a property of the linked server itself, 'Enable Promotion of Distributed Transactions'.

The default for 'Enable Promotion of Distributed Transactions' is TRUE, setting it to FALSE enabled my code to run.

Renaming a Linked Server

2018-07-19T01:23:00.000+01:00

Simple but very helpful as scripting out a linked server won't include the password information.

EXEC master.dbo.sp_serveroption @server=N'OLDNAME', @optname=N'name', @optvalue=N'NEWNAME'

Trace Flag 7470

2018-07-08T14:01:00.005+01:00

Trace flag 7470 works in SQL 2017! I'm always a little reluctant to use trace flags, especially after reading that Trace flags were becoming redundant due to more database level settings.

Anyway, I've eliminated the Sort operator spilling to tempdb by applying 7470.

Note, the documentation says it doesn't apply to SQL 2017, but I had it on good authority that it would,

Trace Flag 7470 : Fix operator spills to tempdb

TSQL : HEAP table usage script

2018-06-03T23:21:00.000+01:00

This script reports usage of HEAP tables over a 15 minute period.
It reports reads, writes and row counts for the tables.
I wrote it as I found a system exhibiting high values for Performance Counter 'forwarded records/sec'.

See Also : TSQL : HEAPS and forwarded records

IF OBJECT_ID('tempdb..#Snap1') IS NOT NULL DROP TABLE #Snap1
IF OBJECT_ID('tempdb..#Snap2') IS NOT NULL DROP TABLE #Snap2
IF OBJECT_ID('tempdb..#rowcounts') IS NOT NULL DROP TABLE #rowcounts

SELECT   
      s.object_id
    , OBJECT_SCHEMA_NAME(s.object_id) AS schemaname
    , OBJECT_NAME(s.object_id) AS tablename
    , i.name AS indexname
       , SUM(user_seeks) user_seeks, SUM(user_scans) user_scans, SUM(user_lookups) user_lookups, reads = SUM(user_seeks + user_scans + user_lookups), writes =  SUM(user_updates)
INTO #snap1
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
  AND OBJECTPROPERTY(s.object_id,'TableHasClustIndex') = 0 -- Reporting only on HEAPS
GROUP BY s.object_id,i.name

WAITFOR DELAY '00:15:00'

SELECT   
      s.object_id
    , OBJECT_SCHEMA_NAME(s.object_id) AS schemaname
    , OBJECT_NAME(s.object_id) AS tablename
    , i.name AS indexname
       , SUM(user_seeks) user_seeks, SUM(user_scans) user_scans, SUM(user_lookups) user_lookups, reads = SUM(user_seeks + user_scans + user_lookups), writes =  SUM(user_updates)
INTO #snap2
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
  AND OBJECTPROPERTY(s.object_id,'TableHasClustIndex') = 0 -- Reporting only on HEAPS
GROUP BY s.object_id,i.name

SELECT   sys_schemas.name AS schemaName
  ,sys_objects.name AS tableName
  ,sys_indexes.name AS indexName
  ,SUM(partition_stats.used_page_count) * 8 AS indexsizekb
  ,MAX(partitions.rows) AS tablerows
INTO #rowcounts
FROM sys.dm_db_partition_stats partition_stats
INNER JOIN sys.indexes sys_indexes
  ON partition_stats.[object_id] = sys_indexes.[object_id] 
    AND partition_stats.index_id = sys_indexes.index_id
INNER JOIN sys.objects sys_objects
  ON sys_objects.[object_id] = partition_stats.[object_id] 
INNER JOIN sys.schemas sys_schemas  
  ON sys_objects.[schema_id] = sys_schemas.[schema_id] 
  AND sys_schemas.name <> 'SYS'
INNER JOIN sys.partitions partitions
  ON partitions.object_id = partition_stats.object_id 
WHERE OBJECTPROPERTY(sys_objects.object_id,'TableHasClustIndex') = 0
GROUP BY sys_schemas.name
  ,sys_objects.name
  ,sys_indexes.name
ORDER BY sys_schemas.name
  ,sys_objects.name
  ,sys_indexes.name

SELECT   i.schemaname
  ,i.tablename
  ,i.indexname
  ,(j.user_seeks - i.user_seeks)  AS seeks_period
  ,(i.user_scans - i.user_scans) AS scans_period
  ,(j.user_lookups - i.user_lookups) AS lookups_period
  ,(j.reads - i.reads) AS reads_period
  ,(j.writes - i.writes) AS writes_period
  ,r.tablerows
  ,r.indexsizekb
FROM #Snap1 i
INNER JOIN #Snap2 j ON i.tablename = j.tablename
INNER JOIN #rowcounts r ON r.tableName = i.tablename AND r.indexName = i.indexname
AND COALESCE(i.indexname,'!') = COALESCE(j.indexname,'!')

TSQL : HEAPS and Forwarded Records

2018-06-03T15:27:00.001+01:00

A little bemused that I'm still finding this in application code in the 21st Century, but there you go. A live app experiencing performance problems due to a significant number of heaps, each frequently used.

SELECT OBJECT_NAME(stats.object_id) as [Table],
idx.name as [Index],
stats.index_type_desc,
stats.page_count,
stats.avg_fragmentation_in_percent,
stats.forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS stats
INNER JOIN sys.indexes AS idx (NOLOCK)
ON stats.OBJECT_ID = idx.OBJECT_ID
AND stats.index_id = idx.index_id
WHERE forwarded_record_count > 0
ORDER BY forwarded_record_count desc

TSQL : Clustered Indexes, Identities & Primary Keys

2018-06-01T19:46:00.000+01:00

I'm Currently looking at a large database where there are

HEAPS (tables without a clustered index)
IDENTITY columns that could serve as Primary Keys (but aren't)
PRIMARY KEYS that have been defined as non-clustered (someone has scripted them out and ignored the default clustered status of a PK).

Anyway, this is coming in useful ...

SELECT   SCHEMA_NAME(schema_id) AS SchemaName
  ,name AS TableName
  ,OBJECTPROPERTY(object_id,'TableHasClustIndex') HasClusteredIndex
  ,OBJECTPROPERTY(object_id,'TableHasIdentity') HasIdentity
  ,OBJECTPROPERTY(object_id,'TableHasprimarykey') HasPrimaryKey
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
ORDER BY 1, 2

SQLTips : CONCAT

2018-05-18T19:13:00.000+01:00

The CONCAT function is available from SQL 2016+. It can save you a lot of messing around with adding strings together and coping with NULLS and empty strings.

SELECT CONCAT('The cat sat',' ', 'on', ' ', 'the mat')
GO
 

SELECT CONCAT(NULL, ' and void')
GO

 
DECLARE @int int = 99
DECLARE @varchar varchar(13) = ' red balloons'
SELECT CONCAT(@int,@varchar)
GO

 
DECLARE @varchar varchar(20) = 'The date today is '
DECLARE @dt datetime = GETDATE()
SELECT CONCAT(@varchar,@dt)
GO

sp_ms_marksystemobject

2018-05-15T00:43:00.000+01:00

On searching for a table I found it to be hidden. Turns out it was marked as 'ms shipped' i.e was a system object.

select * from sys.tables WHERE name = 'sysssislog'

To achieve this yourself you can use sp_ms_marksystemobject

EXEC sp_ms_marksystemobject 'dbo.sysssislog'

Tables with Computed Columns

2018-04-09T22:57:00.001+01:00

Tables with Computed Columns

SELECT
  SCHEMA_NAME(schema_id) AS schemaname
, t.name as tablename
, cc.name
, cc.definition
FROM sys.tables t
INNER JOIN sys.Computed_columns cc
ON t.object_id = cc.object_id

and without

select
  SCHEMA_NAME(schema_id) AS schemaname
, t.name as tablename
from  sys.tables t
WHERE object_id not in (SELECT object_id FROM sys.Computed_columns)

Suspect Database - Steps to Recovery

2018-03-20T02:16:00.000+00:00

For reference, the steps I took to (successfully) recover a 2008 database that was marked as Suspect in Management Studio.

Run each separately, and review the results...

EXEC sp_resetstatus 'mySuspectDB'

ALTER DATABASE mySuspectDB SET EMERGENCY

DBCC CHECKDB('mySuspectDB')

ALTER DATABASE mySuspectDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB ('mySuspectDB', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE mySuspectDB SET MULTI_USER

HASHBYTES and FOR XML to create a binary checksum

2018-02-16T20:37:00.000+00:00

SELECT TOP (1000) [Id]
      ,[AboutMe]
      ,[Age]
      ,[CreationDate]
      ,[DisplayName]
      ,[DownVotes]
      ,[EmailHash]
      ,[LastAccessDate]
      ,CHKSUM = CONVERT(VARBINARY(20),HASHBYTES('MD5', (SELECT s.* FROM (VALUES(NULL))Foo(Bar) FOR XML AUTO, BINARY BASE64)))
  FROM [StackOverFlow2010].[dbo].[Users] s

Mail process taking forever Suspended Process - msdb.dbo.sp_readrequest;1

2018-01-04T21:10:00.002+00:00

Finding a process taking forever in the suspended state, I wondered what it could be.
Fortunately Irina Tudose had already done the hard work for me. It is a process used by the mail procedures in msdb. She recommends changing the default value for DatabaseMailExeMinimumLifeTime to resolve this.

See also

SQL Server: Why a Session With sp_readrequest Takes so Long to Execute

UPDATE msdb.dbo.sysmail_configuration

SET paramvalue = 60 --60 Seconds

WHERE paramname = 'DatabaseMailExeMinimumLifeTime'

Notepad : Commonly used commands

2017-12-17T22:22:00.001+00:00

Force Windows Update wuauclt.exe /updatenow

Error: 8623, Severity: 16, State: 1. The query processor ran out of internal resources

2017-12-08T22:05:00.000+00:00

Caught out when generating some dynamic sql today. The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. Dynamically generating a the IN clause and I accidentally passed a list of over 22,000 values. It turns out IN can't take more than 10,000 values. 8623 The query processor ran out of internal resources and could not produce a query plan. I've obviously rewritten it, but thought the error message interesting.

Database growth from available backup history

2017-11-28T14:47:00.002+00:00

SELECT  
 BackupRange.Database_name 
 ,First_Backup 
 ,Last_Backup 
 ,DATEDIFF(dd,First_Backup,Last_Backup) AS days_history 
 ,firstbackup.backup_size/1024/1024 AS FirstBackupSizeMB 
 ,lastbackup.backup_size/1024/1024 AS LastBackupSizeMB 
 ,(lastbackup.backup_size/1024/1024) - (firstbackup.backup_size/1024/1024) as GrowthMB 
 ,CASE WHEN DATEDIFF(dd,First_Backup,Last_Backup) > 0
  THEN ((lastbackup.backup_size/1024/1024) - (firstbackup.backup_size/1024/1024)) / DATEDIFF(dd,First_Backup,Last_Backup)
  ELSE 0
  END AS GrowthRate_MBday 
FROM  
(SELECT  
 [database_name] 
 ,MIN(backup_start_date) AS 'First_Backup' 
 ,MAX(backup_start_date) AS 'Last_Backup' 
FROM msdb.dbo.backupset  
WHERE [type] = 'D'
--AND [database_name] = N'mydatabase'  
GROUP BY [database_name]) BackupRange  
LEFT JOIN msdb.dbo.backupset firstbackup  
  ON  firstbackup.database_name = BackupRange.database_name
  AND firstbackup.backup_start_date = BackupRange.First_Backup
LEFT JOIN msdb.dbo.backupset lastbackup  
  ON  lastbackup.database_name = BackupRange.database_name
  AND lastbackup.backup_start_date = BackupRange.last_Backup