tag:blogger.com,1999:blog-24536935401490323892023-11-16T11:17:16.311+00:00sql solacer5d4http://www.blogger.com/profile/09999231147372917249noreply@blogger.comBlogger1043125tag:blogger.com,1999:blog-2453693540149032389.post-68064216787783023192020-03-01T18:04:00.000+00:002020-03-01T18:04:40.346+00:00NSSM : Starting applications on startupGreat tool to start applications on startup.
<a href="https://nssm.cc/download">NSSM : The Non sucking service manager.</a>r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-37904652782796283772019-10-23T21:05:00.001+01:002019-10-23T21:05:11.093+01:00Cursor : Loop TablesCursor to loop tables.
<br />
<br />
I use this pattern a lot in DBA scripts...<br />
<br />
<pre class="brush: sql">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
</pre>
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-19346662908900225912019-03-19T14:38:00.001+00:002019-03-19T14:38:06.030+00:00SQL 2000 : Online & Offline databasesI can't believe I still need these in 2019...
<br />
<br />
<pre class="brush: sql">-- 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
</pre>
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-83086334598539116402019-01-10T23:27:00.001+00:002019-11-08T16:13:05.442+00:00TSQL : Loop a date rangeUseful for Incremental processing...
<br />
<pre class="brush: sql">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
</pre>
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-89982695468207162072018-12-17T22:48:00.000+00:002019-05-15T22:49:21.019+01:00TED Talk : Forget the pecking order at work<br />
This TED talk feels rather relevant right now <br />
<br />
Link : <a href="https://www.ted.com/talks/margaret_heffernan_why_it_s_time_to_forget_the_pecking_order_at_work/up-next">Forget the pecking order at work</a><br />
<br />
<br />r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-67867925699688981692018-08-03T23:48:00.001+01:002018-08-03T23:48:19.666+01:00Manually Checking Table StatisticsHow to examine table statistics and update them if necessary
<pre class="brush: sql">
UPDATE STATISTICS dbo.Study WITH FULLSCAN
DBCC SHOW_STATISTICS ('dbo.study','pk_study')
SELECT COUNT(*) AS [RowsInTable] FROM dbo.study
</pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_trXVVYeEbLtvKsihI2V4pPfB8riA16fPzVaRnneX2GTXCp0sLzyCx-dqTP-WlF2hp52K3cPMc-0w7ugkbCVtn8BUo6lhXoSzlFodxyYmNolSo0hxJb5tZc25D1I-xgFVGNnpb2tsRlQ/s1600/statistics_post.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="543" data-original-width="594" height="584" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_trXVVYeEbLtvKsihI2V4pPfB8riA16fPzVaRnneX2GTXCp0sLzyCx-dqTP-WlF2hp52K3cPMc-0w7ugkbCVtn8BUo6lhXoSzlFodxyYmNolSo0hxJb5tZc25D1I-xgFVGNnpb2tsRlQ/s640/statistics_post.png" width="640" /></a></div>
<br />r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-31667706878492963942018-07-31T00:40:00.002+01:002018-07-31T00:40:31.683+01:00findInName - Stored Procedure to search all object names<pre class="brush: sql">
-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 '_'
</pre>
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-36542793271433316242018-07-31T00:38:00.001+01:002018-07-31T00:38:35.178+01:00TSQL - Searching for Escaped CharactersUse 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.
<pre class="brush: sql">
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%/_%' ESCAPE '/'
</pre>
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-38854474752070876772018-07-31T00:34:00.001+01:002018-07-31T00:34:21.622+01:00findInCode - Stored Procedure to search code in all databases for a string
<pre class="brush: sql">
--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'
</pre>r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-82528805894441388762018-07-27T00:17:00.000+01:002018-08-02T00:31:31.224+01:00Database Mirroring : TSQL Health Scripts
<pre class="brush: sql">-- 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];
</pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvwD0vueYqgBIx_NdxnrzgfCyC4norwPIG2QoVyFehTGdDiq2-0hHXch6rKiP-qc3NNx_LMkC8NhXNuQZkGgRNxuCJi5wVq6iK12jNoKr1bIaOP7oRXS_EZu-OmE_2g15Jp2QrPQF9U0k/s1600/mirroring_dmv.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="328" data-original-width="836" height="155" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvwD0vueYqgBIx_NdxnrzgfCyC4norwPIG2QoVyFehTGdDiq2-0hHXch6rKiP-qc3NNx_LMkC8NhXNuQZkGgRNxuCJi5wVq6iK12jNoKr1bIaOP7oRXS_EZu-OmE_2g15Jp2QrPQF9U0k/s400/mirroring_dmv.jpg" width="400" /></a></div>
<br />
<pre class="brush: sql">-- history of all mirored transactions in last 2 hours
USE msdb;
EXEC sp_dbmmonitorresults DatabaseName, 2, 0;
</pre>
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMG2wjTV683NGnDAa1Pk8mQ6KYnEZ2UFwXeIkm86zyjo4UVokRAXbohT5FrgWz6v_PMxTT0oYXNzHy1RSWrWw0IWMnn977ZvXi8DIDukKYxX9fcei70DNmia8mPWEHn6eZoxMvr3Air2M/s1600/mirroring_mirrored_transactions.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="519" data-original-width="1229" height="168" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMG2wjTV683NGnDAa1Pk8mQ6KYnEZ2UFwXeIkm86zyjo4UVokRAXbohT5FrgWz6v_PMxTT0oYXNzHy1RSWrWw0IWMnn977ZvXi8DIDukKYxX9fcei70DNmia8mPWEHn6eZoxMvr3Air2M/s400/mirroring_mirrored_transactions.JPG" width="400" /></a></div>
<br />
<br />
<pre class="brush: sql">--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'
</pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcX24C4qugLFD_JAWG4eWjgxPu14ipBxf50_Ql6wcnzzpGRxkOgMCCVTwGlpfZ_51q8JodtjbRERHZqmM_9d-cVTBfciUcZ4RZ7ySlygv7Op_gU1Pl4SQRH5I3BZV6kCWFGvCC4ihmi1g/s1600/mirroring_os_performance_counters.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="583" data-original-width="926" height="250" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcX24C4qugLFD_JAWG4eWjgxPu14ipBxf50_Ql6wcnzzpGRxkOgMCCVTwGlpfZ_51q8JodtjbRERHZqmM_9d-cVTBfciUcZ4RZ7ySlygv7Op_gU1Pl4SQRH5I3BZV6kCWFGvCC4ihmi1g/s400/mirroring_os_performance_counters.JPG" width="400" /></a></div>
<br />r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-85435664810759792492018-07-22T01:17:00.003+01:002018-07-22T08:39:33.353+01:00Linked Server Setting - Promotion of Distributed TransactionsThis error occured when I tried to access a stored proedure via a linked server.<br />
Getting the results worked fine, simply executing the stored procedure.<br />
<br />
<pre class="brush: sql">EXEC [SERVERNAME].[DatabaseName].[SchemaName].[myStoredProcedure]
</pre>
<br />
But putting the results into a temporary table meant it became a 'distributed transaction'<br />
<br />
<pre class="brush: sql">CREATE TABLE ##tempTable (column1, column2...)
INSERT INTO ##tempTable
EXEC [SERVERNAME].[DatabaseName].[SchemaName].[myStoredProcedure]
</pre>
<br />
OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" returned message "The partner transaction manager has disabled its support for remote/network transactions.".<br />
Msg 7391, Level 16, State 2, Line 12<br />
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" was unable to begin a distributed transaction.<br />
<br />
Both servers were already running the Distributed Transaction Coordinator (DTC) service.<br />
Multiple posts suggest tuning the security of DTC, but I eventually found the solution was much simpler. Like the setting for Remote Procedure calls <a href="https://sqlsolace.blogspot.com/2009/09/server-dev-02-is-not-configured-for-rpc.html">RPC</a>, it turned out to be a property of the linked server itself, 'Enable Promotion of Distributed Transactions'.<br />
<br />
The default for 'Enable Promotion of Distributed Transactions' is TRUE, setting it to FALSE enabled my code to run.<br />
<br />r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-53652924566777048582018-07-19T01:23:00.000+01:002018-07-22T01:24:04.807+01:00Renaming a Linked ServerSimple but very helpful as scripting out a linked server won't include the password information.<br />
<br />
<pre class="brush: sql">EXEC master.dbo.sp_serveroption @server=N'OLDNAME', @optname=N'name', @optvalue=N'NEWNAME'
</pre>
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-79605383980102526152018-07-08T14:01:00.005+01:002018-07-08T14:01:56.035+01:00Trace Flag 7470Trace 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.<br />
<br />
Anyway, I've eliminated the Sort operator spilling to tempdb by applying 7470.<br />
<br />
Note, the documentation says it doesn't apply to SQL 2017, but I had it on good authority that it would,<br />
<br />
<a href="https://support.microsoft.com/en-us/help/3088480/fix-sort-operator-spills-to-tempdb-in-sql-server-2012-or-sql-server-20">Trace Flag 7470 : Fix operator spills to tempdb</a><br />
<br />
<br />r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-43899877128123317932018-06-03T23:21:00.000+01:002018-06-04T08:25:35.754+01:00TSQL : HEAP table usage scriptThis script reports usage of HEAP tables over a 15 minute period.<br />
It reports reads, writes and row counts for the tables.<br />
I wrote it as I found a system exhibiting high values for Performance Counter 'forwarded records/sec'.<br />
<br />
See Also : <a href="http://sqlsolace.blogspot.com/2018/06/tsql-heaps-and-forwarded-records.html">TSQL : HEAPS and forwarded records</a><br />
<br />
<pre class="brush: sql">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,'!')
</pre>
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-76443928752323252932018-06-03T15:27:00.001+01:002018-06-04T08:24:34.290+01:00TSQL : HEAPS and Forwarded RecordsA 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.
<br />
<pre class="brush: sql">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
</pre>
See also : <a href="http://sqlsolace.blogspot.com/2018/06/this-script-reports-usage-of-heap.html">HEAP table usage script
</a><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4-u-Fqu_MdsHgMoHmuKF5L1Fmp63AkHR5u1FRg3la1MAVg1J-QL93slrNiufdxrm5XFTB5yedxks-ncke8kNJ-IGvalTXSlAPttVx0nyvKO2f_34QXIzWPRa9pFsc2JB8mlALseIYFW0/s1600/heaps.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="541" data-original-width="787" height="275" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4-u-Fqu_MdsHgMoHmuKF5L1Fmp63AkHR5u1FRg3la1MAVg1J-QL93slrNiufdxrm5XFTB5yedxks-ncke8kNJ-IGvalTXSlAPttVx0nyvKO2f_34QXIzWPRa9pFsc2JB8mlALseIYFW0/s400/heaps.png" width="400" /></a></div>
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-81589753155365671512018-06-01T19:46:00.000+01:002018-06-04T08:13:32.824+01:00TSQL : Clustered Indexes, Identities & Primary KeysI'm Currently looking at a large database where there are
<ul>
<li>HEAPS (tables without a clustered index)</li>
<li>IDENTITY columns that could serve as Primary Keys (but aren't)</li>
<li>PRIMARY KEYS that have been defined as non-clustered (someone has scripted them out and ignored the default clustered status of a PK).</li>
</ul>
<div>
Anyway, this is coming in useful ...</div>
<br />
<br />
<pre class="brush: sql">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
</pre>
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-73845305806363222922018-05-18T19:13:00.000+01:002018-06-03T16:14:08.045+01:00SQLTips : CONCATThe 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.
<br />
<pre class="brush: sql">
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
</pre>
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-21640573236680511522018-05-15T00:43:00.000+01:002018-08-02T00:43:35.526+01:00sp_ms_marksystemobject 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.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSZGN5JjLjlyBFyTfItn6RsE567QzTd7baWRlXw9j4wtQ4Pn6LTgjqlhv1pMIaDf-3lbAuUGlHbcZtKtFdbaV-uMlrCGOzbpDQm1lqhXcjKjgeIaWyZtkoOxnjQ0kLtVwdydvZBRKg1mQ/s1600/ssms.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="134" data-original-width="297" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSZGN5JjLjlyBFyTfItn6RsE567QzTd7baWRlXw9j4wtQ4Pn6LTgjqlhv1pMIaDf-3lbAuUGlHbcZtKtFdbaV-uMlrCGOzbpDQm1lqhXcjKjgeIaWyZtkoOxnjQ0kLtVwdydvZBRKg1mQ/s1600/ssms.jpg" /></a></div>
<br />
<pre class="brush: sql">select * from sys.tables WHERE name = 'sysssislog'
</pre>
<pre class="brush: sql">
</pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjm7j61bRhTSLZ5JJBpX3pL4a0EIud22D0YrP45bArlXoo8mOTL0eVmhU-SzjFc_rStgodzBGv9TfhqAGmvN9Ef4s5e9cuID6EQgVMk-rSRx4vnV8PfjhafjIFabDrr81Ab9IP297K6C7I/s1600/ssislog.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="151" data-original-width="966" height="50" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjm7j61bRhTSLZ5JJBpX3pL4a0EIud22D0YrP45bArlXoo8mOTL0eVmhU-SzjFc_rStgodzBGv9TfhqAGmvN9Ef4s5e9cuID6EQgVMk-rSRx4vnV8PfjhafjIFabDrr81Ab9IP297K6C7I/s320/ssislog.JPG" width="320" /></a></div>
<br />
To achieve this yourself you can use sp_ms_marksystemobject <br />
<br />
<pre class="brush: sql">EXEC sp_ms_marksystemobject 'dbo.sysssislog'
</pre>
<br />r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-27065733711219395032018-04-09T22:57:00.001+01:002018-06-03T14:19:09.229+01:00Tables with Computed ColumnsTables with Computed Columns
<br />
<br />
<pre class="brush: sql">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
</pre>
<br />
and without
<br />
<pre class="brush: sql">
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)
</pre>r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-54919711592582532282018-03-20T02:16:00.000+00:002018-07-22T02:16:16.708+01:00Suspect Database - Steps to RecoveryFor reference, the steps I took to (successfully) recover a 2008 database that was marked as Suspect in Management Studio.<br />
<br />
Run each separately, and review the results...<br />
<br />
<br />
EXEC sp_resetstatus 'mySuspectDB'<br />
<br />
ALTER DATABASE mySuspectDB SET EMERGENCY<br />
<br />
DBCC CHECKDB('mySuspectDB')<br />
<br />
ALTER DATABASE mySuspectDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE<br />
<br />
DBCC CHECKDB ('mySuspectDB', REPAIR_ALLOW_DATA_LOSS)<br />
<br />
ALTER DATABASE mySuspectDB SET MULTI_USER<br />
<div>
<br /></div>
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-85820583676845465072018-02-16T20:37:00.000+00:002018-06-03T16:38:02.219+01:00HASHBYTES and FOR XML to create a binary checksum<pre class="brush: sql">
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
</pre>
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-89092149353216082232018-01-04T21:10:00.002+00:002018-01-04T23:01:51.716+00:00Mail process taking forever Suspended Process - msdb.dbo.sp_readrequest;1<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNH9adCGcWXOFg-ykTU5JvzhDdQrlZzE00iyu5GwFbIrWXguKHR58eEhSqYXvNlDHJKAXpegdpOe-Mp35h3ew5Bmsyx4rAVJtlXfuEGfxNdNvHQ_72OrRk9Bl5TiyBpXDrplUXGR72UkA/s1600/mail_screenshot.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="363" data-original-width="789" height="183" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNH9adCGcWXOFg-ykTU5JvzhDdQrlZzE00iyu5GwFbIrWXguKHR58eEhSqYXvNlDHJKAXpegdpOe-Mp35h3ew5Bmsyx4rAVJtlXfuEGfxNdNvHQ_72OrRk9Bl5TiyBpXDrplUXGR72UkA/s400/mail_screenshot.PNG" width="400" /></a></div>
<br />
<br />
Finding a process taking forever in the suspended state, I wondered what it could be.<br />
Fortunately Irina Tudose had already <a href="http://yrushka.com/index.php/sql-server/performance-tunning/msdb-dbo-sp_readrequest1-long-running-process/">done the hard work for me.</a> It is a process used by the mail procedures in msdb. She recommends changing the default value for DatabaseMailExeMinimumLifeTime to resolve this.<br />
<br />
See also<br />
<br />
<span style="background-color: white; color: #333333; font-family: "verdana" , "arial" , sans-serif; font-size: 16.25px;"><a href="http://www.connectsql.com/2012/09/sql-server-why-session-with.html">SQL Server: Why a Session With sp_readrequest Takes so Long to Execute</a></span><br />
<br />
<div class="MsoNormal" style="background: white; color: #222222; font-family: arial, sans-serif; font-size: 12.8px; line-height: 15.6pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">UPDATE</span><span style="color: #333333; font-family: "Courier New"; font-size: 10pt;"> msdb.dbo.sysmail_<wbr></wbr>configuration</span><span style="color: #333333; font-family: Verdana, sans-serif; font-size: 10pt;"><u></u><u></u></span></div>
<div class="MsoNormal" style="background: white; color: #222222; font-family: arial, sans-serif; font-size: 12.8px; line-height: 15.6pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SET</span><span style="color: #333333; font-family: "Courier New"; font-size: 10pt;"> paramvalue = 60 </span><span style="color: green; font-family: "Courier New"; font-size: 10pt;">--60 Seconds</span><span style="color: #333333; font-family: Verdana, sans-serif; font-size: 10pt;"><u></u><u></u></span></div>
<div class="MsoNormal" style="background: white; color: #222222; font-family: arial, sans-serif; font-size: 12.8px; line-height: 15.6pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE</span><span style="color: #333333; font-family: "Courier New"; font-size: 10pt;"> paramname = </span><span style="color: red; font-family: "Courier New"; font-size: 10pt;">'<wbr></wbr>DatabaseMailExeMinimumLifeTime<wbr></wbr>'</span></div>
<div class="MsoNormal" style="background: white; color: #222222; font-family: arial, sans-serif; font-size: 12.8px; line-height: 15.6pt;">
<span style="color: red; font-family: "Courier New"; font-size: 10pt;"><br /></span></div>
<br />
<br />r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-46429787323424075722017-12-17T22:22:00.001+00:002017-12-17T22:22:19.945+00:00Notepad : Commonly used commandsForce Windows Update
wuauclt.exe /updatenow
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-20288098589362097102017-12-08T22:05:00.000+00:002017-12-10T22:09:33.660+00:00Error: 8623, Severity: 16, State: 1. The query processor ran out of internal resources 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.
<a href="https://blogs.technet.microsoft.com/mdegre/2012/03/13/8623-the-query-processor-ran-out-of-internal-resources-and-could-not-produce-a-query-plan/">8623 The query processor ran out of internal resources and could not produce a query plan.
</a>
I've obviously rewritten it, but thought the error message interesting.r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0tag:blogger.com,1999:blog-2453693540149032389.post-18814936829145232882017-11-28T14:47:00.002+00:002018-06-03T14:19:10.577+01:00Database growth from available backup history<pre class="brush: sql">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
</pre>
r5d4http://www.blogger.com/profile/03732814547965848157noreply@blogger.com0