Symptoms

You have noticed that the system database has become unusually large and maintenance fails with the following error message:

Stored procedure: [dbo].[SpDeleteOlderRecords] Failed; Inner Message: The transaction log for 
database 'SystemDB' is full due to 'ACTIVE_TRANSACTION'

Environment

BarTender System Database

Diagnosis

In Administration Console under "Administrative Tasks > View Database Size..."  make sure that the transaction log file size is small.

Normally, the SQL Server transactional log file (.LDF) for the BarTender System Database grows during database maintenance, but should shrink to its original size after maintenance has finished. However, this does not always occur and the LDF file starts growing too much.

Solution

The customer's Transaction Log is not big enough to handle all of the transaction information for the maintenance operations. The maintenance operations consists of three steps:   

1. Backup
2. Delete older records
3. Reorganize or rebuild the indexes (may be requiring a lot of Transaction Log space)

It is recommended to allocate more space for the Transaction Log.  Additionally, the Recovery Model should be "Simple", as it uses much less Transaction Log space.

Steps:

1. In SQL Server Management studio, verify what the Maxsize for your Transaction log file is by right clicking your System Database>Properties>Files.
2. In the system database properties verify that the recovery model for your System Database is set to Simpleby right clicking your System Database>Properties>Options>Recovery Model
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.
   
   
   Note: if the system database needs to be cleaned out on a weekly basis due to SQL Server Express size limits, the 10GB size limit may not be sufficient. The database system may require a full copy of SQL Server and a dedicated disk (possibly another VMWare image/server blade). Be aware that making these changes wont obviate the need for periodic backup and archive operations on the database.

"Manual unlock" procedure

1. Stop the "BarTender System Service" Windows Service.
2. 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.
3. Run the below procedure (if 0 rows are affected, run the call once more): 
   

   exec SpDsUnlockMaintenance 0

4. Browse to the "SpDeleteRecords" procedure.
5. Right-click on "SpDeleteRecords" and choose "Script stored procedure as" > "ALTER to" > "New query editor window"
6. 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))

7. Now run this query (typically with F5) to alter the Stored Procedure.
8. Restart the "BarTender System Service" Windows Service.

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.(In the below script, you will need to replace [Database name] with the name of your database)

-- Set the system database recovery model to SIMPLE, and then runs the DBCC SHRINKFILE operation over the Transaction Log.
ALTER DATABASE "[Database name]" SET RECOVERY SIMPLE
DBCC SHRINKFILE ([Database name]_Log, 1)​;
GO 

Additional resources

• Manage the size of the transaction log file