SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Data Collections restart and clear cache files

By Matt Frend,

Occasionally the SQL Data Collections will stop collecting even though the collection sets are still running (see "System Hangs" section in this post: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/bb677178(v=sql.105)). If none of the conditions under that section apply, and restarting data collections does not work, then the cache files may also need to be deleted, in addition to a restart.

To simplify this process, you can create a SQL Agent job to do all of this automatically. The SQL Agent job should have 3 steps:

1. Stop the Data Collections (example is for the 3 default collection sets)

2. run the stored procedure "uspDeleteCacheFiles" - this detects the cache file location and then deletes all files with a ".cache" extension. It also confirms whether the data collection sets have been stopped, as this must be the case before cache files can be deleted. This procedure also requires XPCmdShell for file system access to be enabled:

/* enable XPCmdShell for file system access
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
*/

3. Start the data collections

This process has been tested on all SQL Server versions after SQL 2008. We have found that restarts have periodically been required on all SQL Server versions, as explained under the "System Hangs" section in the Troubleshooting post above.

Total article views: 133 | Views in the last 30 days: 6
 
Related Articles
FORUM

Removing data collection jobs

The delete statement conflicted with the reference constraints fk_syscollector_collection_sets_colle...

ARTICLE

Using SQL Server to collect information from your Oracle server

Using linked connections you can collect system information on your Oracle server from SQL server.

FORUM

Delete system database used in Forefront

I want to delete a system database

FORUM

Unrecognized configuration section system.serviceModel.

Unrecognized configuration section system.serviceModel.

FORUM

Dead lock on Collection Set

Dead lock on Collection Set

 
Contribute