Monday, 9 April 2018

Tables with Computed Columns

Tables with Computed Columns

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

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

Thursday, 4 January 2018

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

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'

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

 ,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 
 ,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

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.

   SCHEMA_NAME(schema_id) as schema_name
  , AS Object_Name
FROM sys.sql_modules m 
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE '%mytablename%'