SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%/_%' ESCAPE '/'
Tuesday, 31 July 2018
TSQL - Searching for Escaped Characters
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.
findInCode - Stored Procedure to search code in all databases for a string
--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'
Friday, 27 July 2018
Database Mirroring : TSQL Health Scripts
-- 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'
Sunday, 22 July 2018
Linked Server Setting - Promotion of Distributed Transactions
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.
But putting the results into a temporary table meant it became a 'distributed transaction'
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.
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.
Thursday, 19 July 2018
Renaming a Linked Server
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'
Sunday, 8 July 2018
Trace Flag 7470
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
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
Sunday, 3 June 2018
TSQL : HEAP table usage script
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
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,'!')
Subscribe to:
Posts (Atom)
