Sunday, 17 December 2017
Friday, 8 December 2017
Error: 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.
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.
Tuesday, 28 November 2017
Database growth from available backup history
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
Tuesday, 8 August 2017
TSQL : Who is connected?
Who is connected to SQL ?
SELECT ec.client_net_address, es.[program_name], es.[host_name], es.login_name, COUNT(ec.session_id) AS [connection count] FROM sys.dm_exec_sessions AS es WITH (NOLOCK) INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK) ON es.session_id = ec.session_id GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name ORDER BY ec.client_net_address, es.[program_name]
Thursday, 15 June 2017
Server is in script upgrade mode
A gotcha from an old SQL 2008 instance today, 'Server is in script upgrade mode'.
Windows Updates had been allowed to include 2008 SP3 (yes I am writing this in 2017!) and the upgrade failed. On starting up, SQL tried (and failed) to bring the tables in the master database up to date. On restart, it would try again.
With no rollback position I attempted restoring the master databases via single user mode, but this proved impossible. In order to get the server live once more I added trace flag 902 to the startup parameters. This prevents the script update from occurring.
The result is a server whose binaries are at SP3, but the master database is at SP2.
It works, it's far from desirable and certainly cannot be upgraded again, but it lives,
The users know no different and it gives us time to plan a migration.
Wednesday, 10 May 2017
Searching SQL Code via sys.sql_modules
I've been used to searching stored procedures via tsql like this.
The problem this encounteres however is that ROUTINE_DEFINTION is NVARCHAR(4000) and hence only returns the first 4000 characters of a stored procedure. The alternative below searches the entire codebase.
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%mytablename%'
The problem this encounteres however is that ROUTINE_DEFINTION is NVARCHAR(4000) and hence only returns the first 4000 characters of a stored procedure. The alternative below searches the entire codebase.
SELECT DISTINCT SCHEMA_NAME(schema_id) as schema_name ,o.name AS Object_Name ,o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.definition LIKE '%mytablename%' ORDER BY 1,2
Thursday, 4 May 2017
Converting a datestring to datetime (yyyymmddhhmmss)
This uses STUFF to format a datestring correctly.
2017-05-04 10:32:53
2017-05-04 10:32:53.000
declare @datestring varchar(20) = '20170504103253' -- Add colons and space to format datetime SET @datestring = STUFF(STUFF(STUFF(@datestring,13,0,':'),11,0,':'),9,0,' ') select @datestring -- Convert .... select convert(datetime,@datestring)
2017-05-04 10:32:53
2017-05-04 10:32:53.000
Wednesday, 19 April 2017
TSQL : ORDER BY in an UPDATE (Queue table example)
Implementing a queue table, I wanted to fetch just one 1 row from a queue table (the next job)
The method below uses one statement, one transaction :)
Mark that row as being dealt with - the UPDATE statement
Fetch all the data about the row - using the OUTPUT clause, rather than a further SELECT
Updates the next desired row in the queue - Using UPDATE against a CTE, which allows you to use ORDER BY
The method below uses one statement, one transaction :)
Mark that row as being dealt with - the UPDATE statement
Fetch all the data about the row - using the OUTPUT clause, rather than a further SELECT
Updates the next desired row in the queue - Using UPDATE against a CTE, which allows you to use ORDER BY
DECLARE @Output TABLE ( id int ,procedurename varchar(200) ,tablename varchar(200) ,databasename varchar(200) ) ;WITH updateCTE AS ( -- doing an update in a CTE lets you use an ORDER BY clause SELECT TOP(1) id, procedurename, tablename, databasename, load_started, spid, load_start FROM [ETL].[myLoadQueue] WHERE batch_start = @batchstart AND load_started = 0 ORDER BY id ) UPDATE updateCTE SET load_started = 1 , spid = @@SPID, load_start = GETDATE() OUTPUT deleted.id, deleted.procedurename, deleted.tablename, deleted.databasename INTO @Output
Saturday, 11 March 2017
COUNT without GROUP BY
I found a novel way of doing a record count today, without using a GROUP BY.
Normally I would have joined another results set containing a GROUP BY, but it appears you can use the OVER window function without a PARTITION BY / ORDER BY statement.
The syntax is COUNT(*) OVER () AS [RecordCount]
I use it below to return the count of addresses in AdventureWorks.
Normally I would have joined another results set containing a GROUP BY, but it appears you can use the OVER window function without a PARTITION BY / ORDER BY statement.
The syntax is COUNT(*) OVER () AS [RecordCount]
I use it below to return the count of addresses in AdventureWorks.
SELECT DISTINCT a.[City] ,sp.Name As StateProvince ,cr.Name AS CountryRegion ,COUNT(*) OVER (PARTITION BY sp.[StateProvinceID]) AS AddressesInThisProvince ,COUNT(*) OVER (PARTITION BY cr.CountryRegionCode) AS AddressesInThisCountry ,COUNT(*) OVER () AS TotalAddresses FROM [AdventureWorks2014].[Person].[Address] a INNER JOIN [AdventureWorks2014].[Person].[StateProvince] sp ON a.[StateProvinceID] = sp.[StateProvinceID] INNER JOIN [AdventureWorks2014].[Person].[CountryRegion] cr ON sp.CountryRegionCode = cr.CountryRegionCode WHERE cr.Name = 'Germany' ORDER BY 1,2,3
Subscribe to:
Posts (Atom)