Suspect Database - Steps to Recovery

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

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

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

Force Windows Update wuauclt.exe /updatenow

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.

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

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]