Spdeleteolderrecords Doesn't Complete.
What can be done to get the cleanup done and get the maintenance plan running in a timely manner?
Thanking all in advance,
paul
-
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 -
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 -
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...
0
Vous devez vous connecter pour laisser un commentaire.
Commentaires
3 commentaires