HOWTO: Fix vRanger "The database transaction was rolled back" Error

Update: Andrew G in the comments posted a link to a newer version of this script for newer versions of vRanger.  You can find that link here:

http://en.community.dell.com/techcenter/data-protection/f/4784/t/19555569

This HOWTO describes how to resolve an issue with vRanger 6.0.1 whereby ALL jobs be it existing or new, incremental or full always fail with the same error: “The database transaction was rolled back” as demonstrated by this sample email log:

There are numerous possible causes for this error.  However, if you are having is for all backups, the most likely cause is your database is full. By default, vRanger 6.0.1 uses SQL 2005 Express MSDE.  Besides being free, it has the baked in limitation that no single database can exceed 4GB in size.  “The database transaction was rolled back” indicates you have maxed out your allocation.  How can you confirm this is your issue?

• As vRanger does not ship with SQL Management Studio by itself, you’ll need to download SQL Management Studio express (http://www.microsoft.com/en-us/download/details.aspx?id=7593).  Once you have it, launch it and connect to SERVERNAME\INSTANCENAME (default is VRANGERPRO)

• Look under Management / SQL Server Logs / Current.  You should find the following error:

“Could not allocate space for object ‘dbo.SavePointXML’.’PK_Manifest’ in database ‘vRangerPro’ because the ‘PRIMARY’ filegroup is full.”

• What’s happened here is that vRanger does not automatically delete any activity logs it generates, ever.  Therefore it’s only a matter of time before the 4GB wall is hit. This is documented in this vRanger KB article: https://support.software.dell.com/vranger/kb/82646

• The fix is to run a custom SQL script that will purge all of the orphaned data.  But before we do that, let’s verify exactly what the issue is
• You can review the orphaned data by selecting New Query from SQL Management Studio while connected to SERVERNAME\VRANGERPRO and pasting in the query Get Size of All Tables in a Database at the end of this HOWTO.
• Copy and paste the results into Excel (or customize the SQL query depending on your preference / skillset) Sort the Database size column from largest to smallest
• You’ll likely find that the table SavePointXML is at 4GB or full

• The good news is, you don’t have to clean this up and all of the dependencies on your own.  VisionCore/Quest/Dell has provided a SQL script in the KB article above (also included at the end of this email for reference)
• Copy and paste Purge Obsolete vRanger Data script at the end of this HOWTO into a new query and run it
• Note that in my case with the database being full, it took over 45 minutes for the execution of the script to complete and this is without any other backups running so be patient
• Note: If you get Query completed with errors, check the Messages tab.  If your error message is similar to the one below, it can likely be ignored

• With the database purged, try running your job again and this time it should be successful

Get Size of All Tables in a Database (found from Google, original author unknown)

SELECT

    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255 
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name

Purge Obsolete vRanger Data

-- this script will purge all logically deleted SPs (ones that don't participate in retention any more)
-- and corresponding statistics (job/task/backuptask/restoretask)

USE vRangerPro

PRINT 'cleaning Catalog'
DELETE Catalog WHERE SavePointId IN
(SELECT SavePointId FROM SavePoint WHERE IsDeleted=1)

PRINT 'cleaning RestoreTask'
DELETE RestoreTask WHERE SavePointId IN
(SELECT SavePointId FROM SavePoint WHERE IsDeleted=1)
DELETE Task WHERE TaskTypeId=1 AND TaskId NOT IN
(SELECT TaskId FROM RestoreTask)

PRINT 'cleaning BackupTask'
DELETE BackupTask WHERE SavePointId IS NULL OR SavePointId IN
(SELECT SavePointId FROM SavePoint WHERE IsDeleted=1)
DELETE Task WHERE TaskTypeId=0 AND TaskId NOT IN
(SELECT TaskId FROM BackupTask)

PRINT 'cleaning RestoreTemplateTask'
DELETE VmDeviceMapping WHERE RestoreTemplateTaskId IN
(SELECT RestoreTemplateTaskId FROM RestoreTemplateTask WHERE SavePointId IN
(SELECT SavePointId FROM SavePoint WHERE IsDeleted=1))
DELETE RestoreTemplateTask WHERE SavePointId IN
(SELECT SavePointId FROM SavePoint WHERE IsDeleted=1)

PRINT 'cleaning SavepointXml'
DELETE SavePointXml WHERE SavePointId IN
(SELECT SavePointId FROM SavePoint WHERE IsDeleted=1)
PRINT 'cleaning Savepoint (incremental and differential savepoints)'
DELETE SavePoint WHERE IsDeleted=1 AND RootSavePointId IS NOT NULL

PRINT 'cleaning Savepoint (full savepoints)'
DELETE SavePoint WHERE IsDeleted=1
PRINT 'delete failed (1), aborted (2) and canceled (3) only from backup and restore'
DELETE BackupTask WHERE TaskId IN
(SELECT TaskId FROM Task WHERE TaskStatusId IN (1,2,3) AND TaskTypeId = 0)

DELETE RestoreTask WHERE TaskId IN
(SELECT TaskId FROM Task WHERE TaskStatusId IN (1,2,3) AND TaskTypeId = 1)
DELETE Task WHERE TaskStatusId IN (1,2,3) AND TaskTypeId IN (0,1)

PRINT 'delete all statistics for deleted templates'
DELETE BackupTask WHERE TaskId IN
(SELECT TaskId FROM Task WHERE JobId IN
(SELECT JobId FROM Job WHERE TemplateVersionId IN
(SELECT TemplateVersionId FROM TemplateVersion WHERE TemplateId IN
(SELECT TemplateId FROM Template WHERE IsDeleted='1')) AND JobTypeId = 0))

DELETE FROM RestoreTask WHERE TaskId IN
(SELECT TaskId FROM Task WHERE JobId IN
(SELECT JobId FROM Job WHERE TemplateVersionId IN
(SELECT TemplateVersionId FROM TemplateVersion WHERE TemplateId IN
(SELECT TemplateId FROM Template WHERE IsDeleted='1')) AND JobTypeId = 2))

DELETE Task WHERE JobId IN
(SELECT JobId FROM Job WHERE TemplateVersionId IN
(SELECT TemplateVersionId FROM TemplateVersion WHERE TemplateId IN
(SELECT TemplateId FROM Template WHERE IsDeleted='1')) AND JobTypeId IN (0,2))

--PRINT 'delete all restore tasks'
--DELETE RestoreTask
--DELETE Task WHERE TaskTypeId=1

PRINT 'cleaning Job'
DELETE Job WHERE JobId NOT IN
(SELECT DISTINCT JobId FROM Task)
PRINT 'shrinking DB'
DBCC SHRINKDATABASE (vRangerPro)