Lock exception while trying to purge data

From DocWiki

Revision as of 14:09, 12 April 2010 by Kallaksh (Talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Lock exception while trying to purge data

Problem Summary After doing 'purge now' from Unified CCX Application Administration web interface, data was not deleted from the database
Error Message The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
Possible Cause This error occurs when there are not enough system locks to complete the current command. SQL Server then attempts to obtain a LOCK block to represent and control the desired lock. When dynamically configured, the lock limit is determined by the available memory. When statically configured, the lock limit is determined by the sp_configure setting.
Recommended Action Either execute the command again when activity on the server is low, or have increase the number of locks by executing sp_configure from the master database.

To view the current configuration:

sp_configure locks GO This reports the minimum, maximum, current run, and configuration values. A config value of 0 indicates that SQL Server manages locks dynamically. If the value is non-zero, we can increase the number of locks by running sp_configure again, specifying the number of locks to be configured. For example, to configure 10,000 locks:

sp_configure locks, 10000 GO RECONFIGURE WITH OVERRIDE GO

Stop and restart SQL Server so the changes can take effect. Locks are allocated at system startup.

If the number of locks cannot be increased at the current time, and the single action requires more locks than the server is currently configured for, you may be able to reduce the number of locks required for the operation. For example, instead of purging data older than 30 months, you can break it into several steps such as, -purge data older than 60 months -purge data older than 50 months -purge data older than 40 months -purge data older than 30 months

Release All Windows releases of Unified CCX
Associated CDETS # None

Rating: 0.0/5 (0 votes cast)

Personal tools