Spdeleteolderrecords Doesn't Complete. Follow

0
Avatar
Legacy Poster

I recently updated the Maintenance plan on my MCSQL 2008 R2 server to include the recommended call to SpDeleteOlderRecords call. The plan has been timing out. I copied the code to a new query window to run it stand alone to find that it was still not completing after over an hour. This particular DB has been up and running for over a year without any maintenance so I would expect that there would be a lot to delete but I would not expect the process to take over an hour. The other problem with the procedure is, due to the number of records being locked, the row locks are being escalated to table locks causing blocking in the production system.

What can be done to get the cleanup done and get the maintenance plan running in a timely manner?

Thanking all in advance,

paul

3 comments

0
Avatar
Domingo Rodriguez
Moderator
Comment actions Permalink

Hello, have you already tried to use BarTender's System Database maintenance (this option can be found under the "Administer > Log Setup > Database" Menu item in BarTender and now by pressing the "Setup" button and selecting the "Maintenance" tab)? If so, what was the result? Did the database shrink correctly?

Could you also paste the full code you're using to run the SpDeleteOlderRecords call?
0
Avatar
Domingo Rodriguez
Moderator
Comment actions Permalink

Also, further recommendations should apply to the time out problem you're having. An important thing to have in mind is that database maintenance task should be done in periods of low or no database activity for optimal results. The time required will be much greater on a busy/logging system + there may appear other problems such as getting extremely large transaction files.

If you've really old logged data in your BarTender System database, one of the following procedures should be better applied:

1. Stop all logging activity.
2. Shut down any remote BarTender System Services (ones not on the server where the database manager is located). As any service, the BarTender System Service for the remote BarTender computers can be found under the Windows services dialog and stopped from there.
3. Purge All.
4. Start everything back up.

or

1. Stop all logging activity.
2. Shut down any remote BSS’s (ones not on the server where the database manager is located).
3. Use the Wizard to configure a new System Database, e.g.: ‘BarTender2012’.
4. Start everything back up.
5. “Re-point” any remote BSS’s to the new database with the remote Wizards.
6. Start logging.
7. Move the old System Database to remote/archival storage.

or

As a third alternative, SpDeleteOlderRecords() has a host of options which allows you to choose what gets deleted (see attachment). You could e.g. do the following:

1. During one period of low activity, clean up the Messages table.
2. During another period of low activity, clean up the Print Jobs.
3. Etc.
The “Print Jobs Cleanup” will by far be the most resource intensive.

Make sure to also regularly defragment the hard drives on which the databases are stored.
0
Avatar
Legacy Poster
Comment actions Permalink

I've encountered this problem as well.  Here's my findings.  Our DB was operational for 2 years prior to maintenance.  For us we are using MS SQL Server (2005).  The DB was close to 40Gb in size.  So first off I logged into SQL Studio and reported on the top table sizes and record counts to get an idea what we were looking at.  Found that almost 30Gb was devoted to label images.  Spdeleteolderrecords uses parameter driven input, so I setup an SQL proc to start well back in history and then progressed slowly to bring ourselves closer to a 90 day retention period.

Example:

DECLARE @timeThreshold int

DECLARE @timeUnits int

DECLARE @recordType nvarchar(1024)

--Choose how many units in the past

SET @timeThreshold = 900

--Where 0 = Days, 1 = Weeks, 2 = Months

SET @timeUnits = 0 SET @recordType = N'ALLR' --All Record Types

EXEC dbo.SpDeleteOlderRecords @timeThreshold, @timeUnits, @recordType

 

I ran this interactively to review results.

 

Remember that each purge/delete run will increase the size of your tasklog and you'll have to deal with backing up the database and shrinking the tlog and every now and then shrink the db.  I reduced the threshold by 50 day increments - the job ran for about an hour each occurrence. It took about a dozen runs to get the retention down to where we wanted.  The database went from 40Gb down to 3 1/2Gb.  We'll schedule the routine to run weekly for ongoing maintenance.  I did notice that a daily purge ran ran 4 mins and a weekly purge ran 7 mins - so better just to run once a week and then perform he necessary post purge maintenance.

 

I hope this helps you...  

 

Please sign in to leave a comment.