GuardPoint10 – 

Stuck Controller Commands, Cannot Download, ACS Server Down / SQL Database Full

IMPORTANT SQL DISCLAIMER

Do not perform any SQL database modification, deletion, maintenance or recovery operation against a GuardPoint10 database without first confirming the proposed action with Sensor Access Technical Support.
A current database backup should always be taken before making any direct SQL changes.
Any direct SQL work carried out without Sensor Access Technical Support present is performed at your own responsibility and risk. Incorrect SQL operations can remove required GuardPoint10 data, leave application relationships in an inconsistent state, or potentially corrupt the database.
Read-only diagnostic queries included in this article may be used to gather information, but any query which changes or deletes data should only be performed under the guidance of Sensor Access Technical Support.

SQL query colour key:

Symptoms this article covers

Use this article when GuardPoint10 displays one or more of the following symptoms:

• Controller commands remain processing for hours.
• Cardholders do not download to controllers.
• Only a small number of the expected cardholders download.
• Remaining Commands does not decrease.
• Access Group downloads do not process.
• Cannot Download Try Again Later.
• Clear Cardholders does not complete.
• Controller initialisation does not complete.
• Cardholders cannot be deleted.
• GuardPoint10 database backup fails.
• GP10 becomes extremely slow or unresponsive.
• AcsServer will not start.
• AcsNMService / NMService appears stuck.
• GUI reports that ACSServer is not detected.
• Multiple unrelated GP10 functions stop working at the same time.
• SQL logs contain PRIMARY filegroup is full.
• SQL logs mention dbo.PersistedJobs.
• SQL operations report Execution Timeout Expired.

Search terms

GuardPoint10, GP10, stuck controller commands, remaining commands, pending commands, cardholders not downloading, Cannot Download Try Again Later, controller stuck processing, controller initialisation stuck, ACS Server down, AcsServer not starting, ACSServer not detected, NMService, AcsNMService, SQL Express full, 10GB database, AC8_Default full, PRIMARY filegroup full, PersistedJobs, database full, cardholder download timeout, SQL timeout.

Overview

GuardPoint10 uses SQL Server to store its configuration and internal processing information.

One important table is dbo.PersistedJobs. PersistedJobs can be thought of as GP10's internal work queue.

Operations such as adding or changing cardholders, changing Access Groups, sending cardholders to controllers, controller initialisation, reader downloads, input/output downloads, card-format downloads, weekly/daily program downloads and automatic resend operations can create one or more jobs.

A single action in the GUI may therefore create many underlying jobs.

On a large installation, particularly following a substantial cardholder import, Access Group restructuring or controller reinitialisation, a very large number of jobs can be generated.

If these jobs accumulate rather than being successfully processed and cleared, PersistedJobs can grow significantly.

This is particularly serious when GP10 is using SQL Server Express because SQL Express has a 10 GB maximum database size.

Once AC8_Default reaches this limit, GP10 can no longer reliably write to the database. This can make what initially appears to be a controller communication problem become a system-wide failure.

Important: determine whether this is a controller problem or a database problem

Before concentrating on controller communications, establish the scale of the fault.

If:

• one controller is affected;
• all other controllers download normally;
• GP10 remains responsive;
• cardholders can still be edited;
• backups still work;

Then troubleshoot that individual controller/network first.

If several unrelated GP10 functions fail simultaneously, for example:

• cardholder downloads fail;
• cardholders cannot be deleted;
• controller initialisation fails;
• backup fails;
• multiple controllers stop processing;
• AcsServer becomes unstable;

Check SQL/database health immediately.

A database problem can prevent GP10 from creating or updating the controller jobs required for communications.

Step 1 – Send the complete GP10 logs to Sensor Access

As soon as the issue occurs, locate:

LOCATION
C:\ProgramData\ACS\Logs

Zip the entire Logs folder and email it to:  support@sensoraccess.co.uk

Please include:

• the approximate date the problem occurred;
• the approximate time the problem occurred;
• a short description of what was being attempted at the time.

Sending the complete folder is preferable to selecting individual log files because different parts of the same failure may be recorded by different GP10 services.

Sensor Access Technical Support can then correlate the logs and carry out a full review.

If you wish to make an initial check yourself, useful search terms include:

• PersistedJobs
• PRIMARY filegroup is full
• Could not allocate space
• Execution Timeout Expired
• UpdateJob
• InsertNewJobInDatabase
• Cannot Download
• ACSServer IS NOT DETECTED

Step 2 – Look for the critical SQL error

A particularly important error is:

LOG ERROR TO LOOK FOR
Could not allocate space for object 'dbo.PersistedJobs' ... in database 'AC8_Default' because the 'PRIMARY' filegroup is full.

You may also see the same error against another GP10 table once absolutely no database space remains.

If this error is present, repeatedly restarting GP10 or repeatedly sending controller downloads is unlikely to resolve the underlying problem.

SQL needs usable database space before GP10 can reliably process jobs again.

Step 3 – Check the AC8_Default database size and internal free space

READ-ONLY QUERY:

USE AC8_Default;
 
 SELECT
     name,
     size * 8.0 / 1024 AS SizeMB,
     (size - FILEPROPERTY(name, 'SpaceUsed')) * 8.0 / 1024 AS FreeSpaceMB
 FROM sys.database_files
 WHERE type = 0;

Pay particular attention to FreeSpaceMB.

For SQL Express, a data file near the 10 GB limit with almost no internal free space is a major warning sign.

IMPORTANT
Windows may still show plenty of free disk space. That does not mean SQL Express can continue growing AC8_Default. The SQL Express database-size limitation is separate from free disk capacity.

Step 4 – Identify what is using the database

READ-ONLY QUERY:

USE AC8_Default;
 
 SELECT
     s.name + '.' + t.name AS TableName,
     SUM(p.rows) AS RowCount,
     CAST(SUM(a.total_pages) * 8.0 / 1024 AS decimal(18,2)) AS ReservedMB,
     CAST(SUM(a.used_pages) * 8.0 / 1024 AS decimal(18,2)) AS UsedMB
 FROM sys.tables t
 JOIN sys.schemas s
     ON t.schema_id = s.schema_id
 JOIN sys.indexes i
     ON t.object_id = i.object_id
 JOIN sys.partitions p
     ON i.object_id = p.object_id
    AND i.index_id = p.index_id
 JOIN sys.allocation_units a
     ON p.partition_id = a.container_id
 WHERE i.index_id IN (0,1)
 GROUP BY
     s.name,
     t.name
 ORDER BY ReservedMB DESC;

If dbo.PersistedJobs is several gigabytes and significantly larger than all other tables, investigate it further.

Step 5 – Check PersistedJobs

Total jobs and payload

READ-ONLY QUERY:

USE AC8_Default;
 
 SELECT
     COUNT(*) AS TotalJobs,
     CAST(
         SUM(ISNULL(DATALENGTH(Data),0))
         / 1024.0 / 1024.0
         AS decimal(18,2)
     ) AS DataMB
 FROM dbo.PersistedJobs;

The Data column is varbinary(max) and can account for a very large proportion of the table size.

Jobs by status

READ-ONLY QUERY:

SELECT
     Status,
     COUNT(*) AS JobCount
 FROM dbo.PersistedJobs
 GROUP BY Status
 ORDER BY Status;

Jobs by type and status

READ-ONLY QUERY

SELECT
     JobType,
     Status,
     COUNT(*) AS JobCount,
     CAST(
         SUM(ISNULL(DATALENGTH(Data),0))
         / 1024.0 / 1024.0
         AS decimal(18,2)
     ) AS DataMB,
     MIN(FirstPersistTime) AS Oldest,
     MAX(LastPersistTime) AS Newest
 FROM dbo.PersistedJobs
 GROUP BY
     JobType,
     Status
 ORDER BY DataMB DESC;

This can show whether a particular type of controller operation is responsible for most of the growth.

Examples seen during troubleshooting include:

• InputsJob
• ReadersJob
• OutputJob
• CardFormatJob
• InitializeControllerJob
• WeeklyProgramsJob
• DailyProgramsJob
• BuildCardholderJob
• Cards.CardholderJob
• Cards.DownloadJob

Important warning about PersistedJobs statuses

Do not assume that an old job is safe to delete.

Persisted jobs can have:

• parent jobs;
• child jobs;
• dependencies;
• different processing states.

The numeric Status values are internal application states and should not be treated as a documented public API.

A job which is several days old may still be something GP10 expects to complete.

Deleting the wrong jobs can:

• remove legitimate pending controller operations;
• break parent/child job relationships;
• leave GP10 in an inconsistent state;
• potentially damage or corrupt application data.

Never create a routine Global Reflex SQL action which simply deletes PersistedJobs based on age.

Any direct SQL operation against a live GP10 database must be carefully assessed first.

Emergency recovery – Sensor Access Technical Support only

DO NOT RUN DELETE OR TRUNCATE QUERIES WITHOUT SENSOR ACCESS TECHNICAL SUPPORT

Do not run any DELETE, TRUNCATE or other data-changing SQL query against PersistedJobs without Sensor Access Technical Support present.
This includes attempts to adapt an example from this article or from a previous support incident.
Job statuses and dependencies must be assessed for the specific installation before anything is removed.
A current database backup must be taken wherever possible before any recovery operation begins.

This section describes the type of recovery that may be required when an installation is already non-operational because the SQL database has reached its limit.

It is not a routine database-maintenance procedure.

Before deleting anything, Sensor Access Technical Support will normally establish:

1.  The actual cause from the GP10 logs.

2.  Whether PersistedJobs is responsible for the database growth.

3.  Whether a current database backup is available.

4.  The database recovery model.

5.  The PersistedJobs schema.

6.  Whether relevant triggers or dependencies are present.

7.  Which job population represents genuinely completed historical jobs for that installation/version.

8.  Whether GP10 services should be stopped during the recovery.

Never assume a status value is safe to delete simply because that value was used successfully during a previous incident.

During the incident that led to this article, completed historical jobs were positively identified from the application logs before anything was removed.

The deletion was then performed in small controlled batches.

TECHNICAL SUPPORT EXAMPLE ONLY – DO NOT RUN WITHOUT SENSOR ACCESS TECHNICAL SUPPORT

DELETE TOP (5000)
 FROM dbo.PersistedJobs
 WHERE <CONFIRMED COMPLETED-JOB CRITERIA>;
 
 CHECKPOINT;

The actual selection criteria must be established for the specific system.

Never do this:

DO NOT RUN
TRUNCATE TABLE dbo.PersistedJobs;

DO NOT:

• delete all jobs;
• delete jobs purely because they are old;
• blindly copy a status number from another support incident;
• create an unattended Global Reflex to clear PersistedJobs.

Why small DELETE batches are used during a controlled recovery

A very large single DELETE can:

• create a long-running transaction;
• heavily load SQL;
• cause blocking;
• grow or stress the transaction log;
• make rollback extremely slow;
• make it difficult to monitor progress.

Small controlled batches make it easier for Technical Support to monitor the effect of the operation. 

TECHNICAL SUPPORT / RECOVERY OPERATION
CHECKPOINT;

This may be used between controlled recovery batches where appropriate.

Monitor free database space during recovery

READ-ONLY QUERY

SELECT
     name,
     size * 8.0 / 1024 AS SizeMB,
     (size - FILEPROPERTY(name, 'SpaceUsed')) * 8.0 / 1024 AS FreeSpaceMB
 FROM sys.database_files
 WHERE type = 0;

A successful deletion may reclaim internal SQL space even though the physical .mdf file does not become smaller in Windows.

That is normal. SQL can reuse the free pages inside the existing MDF.

Do not immediately shrink the MDF

A database shrink is normally unnecessary for this recovery.

If the data file remains approximately 10 GB but now contains several gigabytes of internal free space, SQL can reuse that free space.

Shrinking can introduce substantial index fragmentation and additional unnecessary disk activity.

The immediate objective is to recover usable internal SQL space, not make the Windows MDF file look smaller.

Step 6 – Restart GP10 carefully

Once sufficient database space has been recovered:

1.  Start the GP10/ACS services.

2.  Confirm AcsServer remains running.

3.  Confirm NMService remains running.

4.  Log into GP10.

5.  Do not immediately initialise or resend every controller.

6.  Allow the system to sit idle for approximately 10–15 minutes.

7.  Recheck PersistedJobs.

8.  Recheck database free space.

If the job counts remain broadly stable and free space does not collapse again, proceed with a controlled controller test.

Step 7 – Test one controller only
Select one controller.

Avoid:

• whole-site initialisation;
• multiple simultaneous full cardholder downloads;
• repeated resend attempts.

Send the required operation to that one controller and monitor:

READ-ONLY QUERY:

SELECT
     Status,
     COUNT(*) AS JobCount
 FROM dbo.PersistedJobs
 GROUP BY Status
 ORDER BY Status;

and

READ-ONLY QUERY:

SELECT
     name,
     size * 8.0 / 1024 AS SizeMB,
     (size - FILEPROPERTY(name, 'SpaceUsed')) * 8.0 / 1024 AS FreeSpaceMB
 FROM sys.database_files
 WHERE type = 0;

Normal-looking behaviour

• Some new jobs appear while processing.
• Completed jobs rise gradually.
• Database free space reduces only modestly.
• GP10 services remain running.
• The controller eventually completes its operation.

Warning signs

• Thousands or tens of thousands of jobs appear rapidly from one action.
• Database free space drops by hundreds of MB very quickly.
• Controller remains processing indefinitely.
• SQL errors return.
• AcsServer or NMService becomes unstable.

Step 8 – Database space recovered, but controller still stuck
Recovering database space may expose a second problem.

During the incident that led to this article, GP10 became operational again and the database had several gigabytes free, but a full cardholder send still appeared to make no progress.

The later logs showed Execution Timeout Expired affecting several different operations, including:

• database reads;
• planned card count retrieval;
• creation of new jobs;
• updating jobs;
• cardholder resend jobs.

This indicates SQL performance/blocking rather than simply a slow controller connection.

Check active SQL requests and blocking

Run this while the fault is occurring:

READ-ONLY QUERY:

USE AC8_Default;
 
 SELECT
     r.session_id,
     s.login_name,
     s.host_name,
     s.program_name,
     r.status,
     r.command,
     r.blocking_session_id,
     r.wait_type,
     r.wait_time,
     r.last_wait_type,
     r.cpu_time,
     r.total_elapsed_time,
     r.reads,
     r.writes,
     r.logical_reads,
     DB_NAME(r.database_id) AS DatabaseName,
     t.text AS SqlText
 FROM sys.dm_exec_requests r
 JOIN sys.dm_exec_sessions s
     ON r.session_id = s.session_id
 OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
 WHERE r.session_id <> @@SPID
   AND (
         r.database_id = DB_ID('AC8_Default')
         OR r.blocking_session_id <> 0
       )
 ORDER BY
     r.blocking_session_id DESC,
     r.total_elapsed_time DESC;

also run: 

READ-ONLY DIAGNOSTIC COMMAND

DBCC OPENTRAN('AC8_Default');

These results can then be supplied to Sensor Access Technical Support.

Interpreting common SQL waits

LCK_M_... – Likely SQL blocking/locking. The blocking session should be identified before deciding on any corrective action.

PAGEIOLATCH_... – SQL is waiting for database pages to be read from storage. Investigate disk/storage performance.

WRITELOG – SQL is waiting on transaction-log writes. Investigate log/disk performance and long transactions.

No obvious blocking, but extremely high logical reads – Potential areas to investigate include poor query plans, stale SQL statistics, heavily fragmented indexes, or database/table condition after very large data changes.

Do not immediately rebuild indexes or run broad SQL maintenance against the live system. Establish the actual bottleneck first.

Why large imports and Access Group changes can generate so much work

An Excel import itself is only the first stage.

GP10 must first determine whether each spreadsheet row is:

• a new cardholder;
• an update to an existing cardholder.

If access information changes, GP10 then needs to calculate which controllers are affected.

For example, if a student changes from one Access Group to another:

1.  GP10 updates the cardholder.

2.  It determines which readers/controllers were provided by the previous Access Group.

3.  It determines which readers/controllers are provided by the new Access Group.

4.  Removal commands may be required from some controllers.

5.  New cardholder commands may be required on other controllers.

6.  The necessary jobs are created and processed.

Multiply this by several thousand cardholders and the number of controller operations can become substantial.

Large imports should therefore be treated as significant system changes rather than simply spreadsheet updates.

Controller memory also matters

A separate issue can occur if an Access Group causes more cardholders to be assigned to a controller than the controller can store.

Always check the controller's cardholder capacity.

For example, a standard controller with approximately 20,000-cardholder memory must not be expected to hold more credentials than its supported capacity.

This is separate from the SQL Express 10 GB database limitation.

Both limits need to be considered on large installations.

Preventative recommendations

Keep GP10 current

Older GP10 versions may not include later fixes and improvements relating to:

• cardholder imports;
• Access Group processing;
• controller command handling;
• SQL/database operations;
• controller communication reliability.

Review the installed version during any incident involving large imports or unusual controller-job behaviour.

Review SQL Express on large installations

SQL Express can be appropriate for many installations, but its 10 GB database limit provides limited headroom for a very large or highly active GP10 system.

For installations with:

• tens of thousands of cardholders;
• large batch imports;
• many controllers;
• substantial integration activity;

consider whether a supported full SQL Server edition is more appropriate.

Maintain regular database backups

Regular database backups are strongly recommended, particularly before:

• major cardholder imports;
• significant Access Group restructuring;
• GP10 upgrades;
• major controller work;
• any direct SQL support work.

For the recommended GuardPoint10 backup, restore and disaster-recovery procedures, refer to:

GuardPoint 10 Database Backup, Restore and Disaster Recovery Guidance

Backups should also form part of the customer's normal IT backup/disaster-recovery process and should not exist solely on the GP10 server.

Monitor before large termly/batch imports

Before a major import:

• confirm GP10 database free space;
• check current PersistedJobs volume;
• check controller Remaining Commands;
• confirm controller memory capacities;
• confirm a recent backup exists.

After the import:

• monitor PersistedJobs growth;
• monitor database free space;
• monitor controller Remaining Commands;
• avoid immediately launching multiple full controller initialisations.

Quick diagnostic checklist

When a user reports “controller commands stuck”, check in this order:

1.  Is this one controller or many?

2.  Can cardholders still be edited/deleted?

3.  Does GP10 backup work?

4.  Can another controller download?

5.  Are AcsServer and NMService running?

6.  Zip and send the complete C:\ProgramData\ACS\Logs folder to support@sensoraccess.co.uk, including the approximate date and time of the fault.

7.  Search logs for PersistedJobs and PRIMARY filegroup is full.

8.  Check AC8_Default size and free space.

9.  Check the largest database tables.

10.  Check PersistedJobs row count and payload.

11.  If the database is full, stop creating more controller jobs.

12.  Do not delete PersistedJobs without Sensor Access Technical Support.

13.  Once recovered, restart services and allow GP10 to idle.

14.  Test one controller only.

15.  If SQL starts timing out, check blocking/waits and send the results to Sensor Access Technical Support.