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.

Small script to identify head blocker

If there are blocking sessions, this query will help you identify the session that causes the first blocking condition.  


select Blocking.spid as HeadOfBlockingChain
from master.dbo.sysprocesses Blocking inner join master.dbo.sysprocesses Blocked
on Blocking.spid = Blocked.Blocked
where Blocking.Blocked = 0

Then you can use dynamic views or use dbcc inputbuffer to get the query text.

dbcc inputbuffer(53)

In another post I will show you how to get both the blocker and blocked spids and their query text using dynamic views.

Amount of space (total, used and free) in tempdb

In this post we will see how to determine the amount of total and free space tempdb has.

Tempdb is one of the system databases and there is only one by SQL Instance. Tempdb is used by all of the other databases when queries require to work with temporary data. 

It's important to mention that if tempdb has issues it will have an impact in all other databases on the Instance, so you see now why it is important keeping it problem free.

The following script uses sys.dm_db_file_space_usage to check the total size of tempdb by summing the values of the following columns: 

• user_object_reserved_page_count
• internal_object_reserved_page_count
• version_store_reserved_page_count
• mixed_extent_page_count
• unallocated_extent_page_count

Used space is calculated the same way just skipping "unallocated_extent_page_count" and finally the Free Space is calculated using this "unallocated_extent_page_count" column.

As all column values are in database pages, we multiply this value by 8 (one page is 8KB) and divided by 1024 to calculate the value Megabytes.


--Amount of space (total, used, and free) in tempdb
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count
+ unallocated_extent_page_count) * (8.0/1024.0)
AS [TotalSizeOfTempDB(MB)]
, SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count) * (8.0/1024.0)
AS [UsedSpace (MB)]
, SUM(unallocated_extent_page_count * (8.0/1024.0))
AS [FreeSpace (MB)]
FROM sys.dm_db_file_space_usage

The output will look as follows.







Monitoring this output will help you determine patterns of tempdb usage during a period of time, this way you can whether schedule your jobs to run when there is less tempdb usage or increase the allocated space according to the demand.