ErrorFile very large and Scheduled Maintenance failing

Dar Sisk

• 17 de abril de 2023 11:39
• Editado

Hi, 

Our C Drive keeps filling up, primarily made up of the ERRORFILE (>50GB right now) found in the Logs folder of the Bartender install.

The main reason for this file being large is because the daily scheduled maintenance failing task in Bartender Admin Console keeps failing.

When we manually run the task, a popup comes up saying "A maintenance task is currently running. Please try again later."

See attached screenshots

 

0

• 

  Xabier Clemente

  Moderador

  ★ BarTender Hero ★

  • 18 de abril de 2023 07:49

  Hi Dar,
  
  Thank you for contacting Seagull Scientific!
  
  It sounds like your maintenance is not running due to the hard drive space being filled.
  
  We could try the following action plan in order to fix this issue:
  
  We would recommend allocating more space for the Transaction Log, as well as making the Recovery Model "Simple" since it uses much less Transaction Log space:
  
  

  1. In SQL Server Management studio, right click on the BarTender system database (SystemDB1) -> click properties -> go to files. For the transaction log under the autogrowth/maxsize column, click the ... button. Set the max file size to unlimited.
  2. Then still in the system database properties->go to options->set the recovery model to Simple.
  3. Run the "manual unlock" procedure to unlock maintenance, if necessary (see below).
  4. Start the Administration Console to run maintenance.
  5. Once maintenance is complete, take a look at the Transaction Log file to see how much it's grown. 
  6. Go back into database properties and set the size limit of the file to be somewhere in the magnitude of the size of the file on disk, instead of unlimited.
  7. Previous versions of this help file mentioned a truncate command to truncate the entire database. Do not use a truncate command as this ruins the backup chain. This feature has been deprecated since before 2010. Instead, try one of the options in the next section to help reduce the database size.
     
     ** As a general observation, if the customer is needing to clean things out on a weekly basis due to SQL Server Express size limits, they really don't have enough resources. The customer should consider getting a full copy of SQL Server and probably dedicating another disk, possibly another VMWare image/server blade to the database portion of your operations.  However, it will still need to be backed up and archived on a periodic basis.

  
  Here is the following "Manual unlock" procedure (from inside SQL Server Management Studio).

  Truncating Integration messages

  You could remove the data logged in the Integration Data Table directly via SQL Management Studio, by running the below SQL command:
  
  truncate table dbo.IntegrationMessages
  
  This will clear out all the existing Integration messages from the IntegrationMessages table in the system database.

  Other suggestions

  Apart from this, more suggestions you can try are:
  
  Stop the "BarTender System Service" Windows Service.
  
  

  1. Using SQL Server Management Studio, unlock maintenance by calling the following Procedure on your database (if 0 rows are affected, run the call once more).
     

     exec SpDsUnlockMaintenance 0

  2. Alter the SpDeleteRecords procedure so that it is not declared as "WITH EXECUTE AS 'dbo'" (more instructions below).
  3. Restart the "BarTender System Service" Windows Service.

  
  ** To Alter SpDeleteRecords:
  
  

  1. With SQL Server Management Studio's Object Explorer (typically left-hand side pane), expand your database, then visit the "Programmability" folder, choose the "Stored Procedures" folder, and browse to the "SpDeleteRecords" procedure.
  2. Right-click on "SpDeleteRecords" and choose "Script stored procedure as" > "ALTER to" > "New query editor window"
  3. At the new Editor, remove the "WITH EXECUTE AS 'dbo'" from the "ALTER PROC" line, so that it appears as:
     

     ALTER PROC [dbo].[SpDeleteRecords](@pastUtcTicks bigint, @categories nvarchar(1024))

  4. Now run this query (typically with F5) to alter the Stored Procedure.

  Shrinking the log size 

  You should execute the following query to SHRINK the current database's log file size after an operation that creates a large amount of unused space, such as a truncate table or a drop table operation.

  --Select the BarTender System Database
  USE mydatabase;
  GO 
  
  -- Truncate all the existing Integration messages from the IntegrationMessages table 
  TRUNCATE table dbo.IntegrationMessages;
  GO
  
  -- Set the system database recovery model to SIMPLE, and then runs the DBCC SHRINKFILE operation over the Transaction Log.
  ALTER DATABASE "mydatabase" SET RECOVERY SIMPLE
  DBCC SHRINKFILE (mydatabase_Log, 1)​;
  GO 

  Let us know if you have any additional questions or issues, we are always happy to help!

  0
• 

  Roman Wolf

  ★ BarTender Hero ★

  • 6 de octubre de 2023 03:50

  Nice article. Unfortunately I tried this, and got a 60GB log file, which obviously destroyed the poor server (and then the DB itself)

  Surely there's a nicer way to shrink this? How much disk space does it require, exactly? 60GB is 6x the actual database size!! And it still wasnt done

  0

Iniciar sesión para dejar un comentario.