Database on Suspect Mode

NOTE: The below was a very particular scenario,  the best way to recover a SUSPECT Database is to restore from a BACKUP.

Scenario

After a network issue with the SAN one of our databases went to suspect mode. Checking the logs both Windows App Logs and SQL Server Error Logs we got this:

LogWriter: Operating system error 1117(The request could not be performed because of an I/O device error.) encountered.

The log for database 'X' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

During undoing of a logged operation in database 'X', an error occurred at log record ID (326627:109731:1). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

First we verified the Log Drive was present and the Database Log file was there (it was) so this was good.

We try to bring the database online since the log file was there and there was no need to rebuild it.

ALTER DATABASE [x] SET ONLINE

But we hit this error.

Msg 5061, Level 16, State 1, Line 1

ALTER DATABASE failed because a lock could not be placed on database 'TQ_ThreatLinQ'. Try again later.

Verified there were no process running against this database but just in case I try to put it in RESTRICTED_MODE

ALTER DATABASE [x] SET RESTRICTED_USER NO_WAIT

Same error, the DB STARTUP background process was blocking any attempt against this database, however it was not doing a thing.

Since this Database was part of a dedicated cluster we fail over the DB Instance and that fix the issue.

Recovery went successfully this time for the database.

Recovery is complete. This is an informational message only. No user action is required.

Databases with most missing Indexes

It’s often the case that you have several different databases running on the same server. A consequence of this is that no matter how optimal your individual database may be, another database on the server, running suboptimally, may affect the server’s resources, and this may impact the performance of your database. Remember, CPU, memory, and tempdb are shared across all the databases on the server. Now that you know about the importance of indexes on query performance, it makes sense to report on those databases with the most missing indexes, because they may be indirectly affecting the performance of your database

High I/O is one of the main reasons for poor performance and often this is caused by missing indexes. The following query will list the databases with most missing indexes.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT

DB_NAME(database_id) AS DatabaseName

, COUNT(*) AS [Missing Index Count]

FROM sys.dm_db_missing_index_details

GROUP BY DB_NAME(database_id)

ORDER BY [Missing Index Count] DESC

How to decrypt SQL Objects?

The other day I ran into the situation where I needed to troubleshoot an specific stored procedure from a Legacy Environment which was encrypted. I tried to find if anyone had the stored proc code without luck.

After searching for a while I found a post on Jon's Gurgul blog about how to do this (tested it in SQL 2005/2008). As you need to query sys.sysobjvalues, which can only be accessed when using the Dedicated Admin Connection (DAC) you need to connect this way. 

Here are the steps:

You can enable Dedicated Admin Connection (DAC) or physically connect to the server and use sqlcmd.

EXEC sp_configure 'remote admin connections', 1

GO

RECONFIGURE

GO

Then with this done open SSMS, choose File, New, Database Engine Query and set your servername to be admin:ServerName

The last step is to change the code lines to reference the object you want the underlying text for and then check the messages tab

SET @ObjectName = 'sp_encrypted'

SET @Schema = 'dbo'

Here is the code of the Script.

DECLARE @EncObj VARBINARY(MAX),@DummyEncObj VARBINARY(MAX),@ObjectNameType NVARCHAR(50),@ObjectNameStmTemplate NVARCHAR(4000),@Schema nVARCHAR(MAX),@ObjectName NVARCHAR(50),@TemplateObj NVARCHAR(max)

DECLARE @p INT,@p1 NVARCHAR(MAX),@p2 NVARCHAR(MAX),@QueryForDummyObj NVARCHAR(MAX),@C INT

SET NOCOUNT ON

SET @ObjectName = 'sp_encrypted'

SET @Schema = 'dbo'

--please set @Schema = null for database/server triggers if needed

IF EXISTS

(

SELECT 1 FROM syscomments WHERE [encrypted] = 1 AND [id]= OBJECT_ID(@Schema+'.'+@ObjectName) OR @Schema IS NULL

)

BEGIN

IF EXISTS

(

SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e

on (ec.[endpoint_id]=e.[endpoint_id])

WHERE e.[name]='Dedicated Admin Connection'

AND ec.[session_id] = @@SPID

)

BEGIN

SELECT TOP 1 @ObjectName=ObjectName,@ObjectNameType=ObjectType,@ObjectNameStmTemplate=ObjectStmTemplate,@EncObj = [imageval]

FROM

(

SELECT name ObjectName,

CASE WHEN [type] = 'P' THEN N'PROCEDURE'

WHEN [type] = 'V' THEN 'VIEW'

WHEN [type] IN ('FN','TF','IF') THEN N'FUNCTION'

WHEN [type] IN ('TR') THEN N'TRIGGER'

ELSE [type]

END ObjectType,

CASE WHEN [type] = 'P' THEN N'WITH ENCRYPTION AS'

WHEN [type] = 'V' THEN N'WITH ENCRYPTION AS SELECT 123 ABC'

WHEN [type] IN ('FN') THEN N'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 END'

WHEN [type] IN ('TF') THEN N'() RETURNS @t TABLE(i INT) WITH ENCRYPTION AS BEGIN RETURN END'

WHEN [type] IN ('IF') THEN N'() RETURNS TABLE WITH ENCRYPTION AS RETURN SELECT 1 N'

WHEN [type] IN ('TR') THEN N' ON ' + OBJECT_NAME(ao.[parent_object_id]) + ' WITH ENCRYPTION FOR DELETE AS SELECT 1 N'

ELSE [type]

END ObjectStmTemplate,

sov.[imageval]

FROM sys.all_objects ao

INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND ao.[Object_id] = sov.[objid]

WHERE [type] NOT IN ('S','U','PK','F','D','SQ','IT','X','PC','FS','AF')

AND ao.[name] = @ObjectName

AND ao.[schema_id] = SCHEMA_ID(@Schema)

--Server Triggers

UNION ALL SELECT name [ObjectName],'TRIGGER' [type],N'ON ALL SERVER WITH ENCRYPTION FOR DDL_LOGIN_EVENTS AS SELECT 1' [ObjectStmTemplate],sov.[imageval] FROM sys.server_triggers st

INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND st.[Object_id] = sov.[objid]

WHERE name = @ObjectName

--Database Triggers

UNION ALL SELECT name [ObjectName],'TRIGGER' [type],N'ON DATABASE WITH ENCRYPTION FOR CREATE_TABLE AS SELECT 1' [ObjectStmTemplate],sov.[imageval] FROM sys.triggers dt

INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND dt.[Object_id] = sov.[objid] AND dt.[parent_class_desc] = 'DATABASE'

WHERE name = @ObjectName

) x

--SELECT @ObjectName,@ObjectNameType,@ObjectNameStmTemplate,@EncObj

--Chunks

SET @C = CEILING(DATALENGTH(@EncObj) / 8000.0)

--Alter the existing object, then revert so that we have the dummy object encrypted value

BEGIN TRANSACTION

SET @p = 1

SET @p1= N'ALTER'+SPACE(1)+@ObjectNameType+SPACE(1)+ISNULL((@Schema+'.'),'')+@ObjectName +SPACE(1)+@ObjectNameStmTemplate;

SET @p1=@p1+REPLICATE('-',4000-LEN(@p1))

SET @p2 = REPLICATE('-',4000)

SET @QueryForDummyObj = N'EXEC(@p1'

WHILE @p<=@C

BEGIN

SET @QueryForDummyObj=@QueryForDummyObj+N'+@f'

SET @p =@p +1

END

SET @QueryForDummyObj=@QueryForDummyObj+')'

EXEC sp_executesql @QueryForDummyObj,N'@p1 NVARCHAR(4000),@f VARCHAR(8000)',@p1=@p1,@f=@p2

SELECT @DummyEncObj = sov.[imageval]

FROM sys.all_objects ao

INNER JOIN sys.sysobjvalues sov ON sov.[valclass]=1 AND ao.[Object_id]=sov.[objid]

WHERE ao.[name]=@ObjectName AND (ao.[schema_id]=SCHEMA_ID(@Schema) OR @Schema IS NULL)

ROLLBACK TRANSACTION

--Replacement Text

SET @TemplateObj = N'CREATE'+SPACE(1)+@ObjectNameType+SPACE(1)+ISNULL((@Schema+'.'),'')+@ObjectName +SPACE(1)+@ObjectNameStmTemplate+REPLICATE('-',4000)

DECLARE @i INT

SET @i = 1

WHILE @i<@C

BEGIN

SET @TemplateObj=@TemplateObj+REPLICATE(N'-',4000)

SET @i =@i+1

END

----Simple Char Decrypt

--DECLARE @Pos INT

--SET @Pos=1

--WHILE @Pos<=DATALENGTH(@EncObj)/2

--BEGIN

--PRINT NCHAR(UNICODE(SUBSTRING(CAST(@EncObj AS NVARCHAR(MAX)),@Pos,1))^(UNICODE(SUBSTRING(@TemplateObj,@Pos,1))^UNICODE(SUBSTRING(CAST(@DummyEncObj AS NVARCHAR(MAX)),@Pos,1))))

--SET @Pos=@Pos+1

--END

----8000 Char Decrypt Strings

DECLARE @CNumber INT,@CEncObj NVARCHAR(MAX),@CDummyEnc NVARCHAR(MAX),@CPiece NVARCHAR(MAX),@CPosition INT,@CTemplateObj NVARCHAR(MAX)

SET @CNumber=1

WHILE @CNumber<=@C

BEGIN

SELECT @CEncObj=SUBSTRING(@EncObj,(@CNumber-1)*8000+1,8000)

SELECT @CDummyEnc=SUBSTRING(@DummyEncObj,(@CNumber - 1) * 8000+1,8000)

SELECT @CTemplateObj=SUBSTRING(@TemplateObj,0+((@CNumber-1)*4000),4000)

SET @CPiece=REPLICATE(N'-',(DATALENGTH(@CEncObj)/2))

SET @CPosition=1

WHILE @CPosition<=DATALENGTH(@CEncObj)/2

BEGIN

SET @CPiece=STUFF(@CPiece,@CPosition,1,NCHAR(UNICODE(SUBSTRING(@CEncObj,@CPosition,1))^(UNICODE(SUBSTRING(@CTemplateObj,@CPosition,1))^UNICODE(SUBSTRING(@CDummyEnc,@CPosition,1)))))

SET @CPosition=@CPosition+1

END

PRINT @CPiece

SET @CNumber=@CNumber+1

END

END

ELSE

BEGIN

PRINT 'Use a DAC Connection'

END

END

ELSE

BEGIN

PRINT 'Object not encrypted or not found'

END

syspolicy_purge_history - PowerShell subsystem failed to load Error

Error Message: 

Step 3 of job 'syspolicy_purge_history' (0x92D690B2954DA942915A08224A906C93) cannot be run because the PowerShell subsystem failed to load.  The job has been suspended.

Cause:  Invalid location of SQLPS.exe file

Steps to fix:

1.     Search the location of SQLPS.exe file by using the following quey

SELECT * FROM msdb.dbo.syssubsystems WHERE start_entry_point ='PowerShellStart'

   

     2.     Go to the server and check whether the file ‘SQLPS.exe’ is located in the path as per step 1.

     3.      If the paths are different, enable 'allow updates' using the below script to be able to write into system databases. If they are the same go to step 6.

sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE 

    4.      Update the correct path.

UPDATE msdb.dbo.syssubsystems SET agent_exe='Enter Correct Full Path Here\SQLPS.exe' WHERE start_entry_point ='PowerShellStart'

    5.     Disable 'allow updates'

sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE 

   6.    Get the SQL Server name you are working on by running:  SELECT @@SERVERNAME

   7.    Go to the job properties and edit the 'Erase Phantom System Health Records' step.

Insert the value gathered from step 6.

8.    Restart SQL Agent Service.

9.    Re-run syspolicy_purge_history job.

syspolicy_purge_history - What is it?

When you do a Sql Server 2008 installation or upgrade from a previous version, a SQL Agent job will be created during the installation/upgrade process: syspolicy_purge_history.

With SQL Server 2008 a new feature “Policy Management” was introduced. When you define a policy, this can be evaluated against targets to check for compliance. Each evaluation of compliance will be recorded in tables in msdb database:

• syspolicy_policy_execution_history_details_internal
• syspolicy_policy_execution_history_internal
• syspolicy_policy_category_subscriptions_internal

The purpose of syspolicy_purge_history job is to remove records in those tables and keep them under reasonable size. 

Error: 701, Severity: 17, State: 135 - There is insufficient system memory in resource pool 'default' to run this query.

A few days ago one of our customers started to complain their queries were failing because of the following error:

Error: 701, Severity: 17, State: 135 - There is insufficient system memory in resource pool 'default' to run this query.

In the SQL Server Error Log we could see a bunch of entries with the same error message which indicated the SQL Instance was having memory pressure issues. 

To give you a little bit of background, this SQL Instance had allocated only 1GB of Memory with only one User Database; for this particular scenario it was quit obvious to determine which database was consuming all the memory however when we have multiple databases on the same Instance it is important to determine which database is consuming the available memory.

The following script can be used to determine whether a database is hogging the buffer pool and should perhaps be moved to its own server or just increase the Maximum Server Memory Setting for the SQL Instance if available Memory in the server exists. 

*Note: Remember to leave enough memory to the operating system before Increasing Maximum Server Memory.


SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT
ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName
, CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))
AS [Size (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY DatabaseName

The solution for this case was just increasing the Maximum Server Memory Setting, we never saw those error entries again.

Finding the state of your statistics

Up-to-date statistics help ensure that the most appropriate index is chosen to obtain
the underlying data. They also help ensure that the correct index access mechanism is
chosen, for example, seek or lookup. Running the SQL script given in the following
listing will identify the current state of your statistics


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
ss.name AS SchemaName
, st.name AS TableName
, s.name AS IndexName
, STATS_DATE(s.id,s.indid) AS 'Statistics Last Updated'
, s.rowcnt AS 'Row Count'
, s.rowmodctr AS 'Number Of Changes'
, CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS
DECIMAL(28,2)) * 100.0)
AS DECIMAL(28,2)) AS '% Rows Changed'
FROM sys.sysindexes s
INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100
AND s.indid > 0
AND s.rowcnt >= 500
ORDER BY SchemaName, TableName, IndexName

The output will show you when index statistics were last updated and the percentage of rows that have changed since the last update of statistics. This is the criteria you should follow to determine if you need to update your statistics.

Note: An index’s statistics tend to be updated automatically when 20% of its rows have
changed. For large tables, this might take a considerable time, during which queries
may run more slowly because of having stale statistics.