Manually erase failed tape backups from SQL database

[madeyem]

Case #04319207

Hi everyone,

I did a file-to-tape backup of a Windows file server with almost 20 million files, which failed because of the SQL database limitation of 10 GB. I also tried to split the job into 4 different jobs, but that failed, too.

I now would like to erase all the failed tape backups from the SQL database, but short-erasing the tapes or marking them as free also fails with the same error:

"05.08.2020 11:15:35 Error Failed to mark tape as free Error: Could not allocate a new page for database 'VeeamBackup' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."

In total I have three tapes, that I cannot mark as free or erase.

Can you provide me with a way of erasing all those failed tape backups from the SQL database using SQL Management Studio? Or is there another solution for this? I couldn't find any inside the GUI.

My Disk-to-Disk backups are working well and should not be affected by this.

The database recovery model is set to simple, autogrowth is also enabled, session history retention set to 6 weeks. But the SQL file is almost 10 GB, so I somehow have to erase the failed tape backups from the database.

I already searched the forums, Knowledge Base and Internet for this, but could't find any solution.

Thank you very much in advance.

[Dima P.]

Hello madeyem,

Unfortunately can provide you with SQL query, please keep working with our support team as they should have a workaround. Mind me asking what edition of the SQL server are you running? Is it SQL server standard or SQL Server Express that is bundled with Veeam B&R installation? Thanks!

[madeyem]

Hi Dima,

thanks for your reply. I have the default SQL Server 2016 Express, that comes with the Veeam setup. Yes, I will wait for the support team's response. I just wanted to ask in the forums as well, because maybe someone had the same issue before and can help.

[Dima P.]

madeyem,

Thank you for the update! If you plan to backup so many objects via file to tape job in future - I'd recommend using the Standard edition of the SQL server since it has not limitation in database instance memory usage, thus all the queries work faster and, as a result, all file to tape operations get a boost in overall performance (next step would be to use SSD storage for the Veeam B&R database). Cheers!

[madeyem]

Hi Dima,

yes I'm aware of that now as well.
Unfortunately my support case was not processed, because I'm using the Community Edition and therefore am not entitled to support.

So if someone has dealt with this before and can help me out, I would appreciate that.
Thanks.

[Dima P.]

madeyem,

Thanks! I'll ask support team to re-open the case, stay tuned.

[madeyem]

Ok, thank you Dima!

[madeyem]

I would like to thank Dima, Dmitry and Adrian from the support team for helping me out with this. They provided me with a SQL script to delete all file-to-tape jobs from the database. Considering I'm on the free Community Edition, this is much appreciated and not taken for granted! Thank you!

[Dima P.]

madeyem,

Thank you for the kind words and glad we got this issue resolved. Cheers!

[Gmaz]

Case # 04753231

Hi, I am just experiencing the same problem as madeyem, because the DB has reached 10GB limit and any operation I will try the result is always "... the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.". I worked with Svetlana of Veeam support (case ID as mentioned) and she provided me the script to manually clean SQL database from failed tape backups, but when i exexute it the result is:
"Could not allocate space for object 'dbo.Tape.files'.'IX_Tape.files_directory_id_name' in database 'VeeamBackup' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. "
Can you help me? It seems no operation can be done in this DB, not even delete...

Thanks in advance

[Dima P.]

Hi Gmaz,

I've asked support team to review your case details and provide the needed instructions. Stay tuned.

[Jurica.Radovan]

Hello I have just the same problem. My case numb: # 04860694 Is there any way to get the SQL scripts?

Thanks in advance

Radovan

[wishr]

Hi Jurica,

As far as I see you've already got the information from our support engineers.

Thanks

[Yenming]

Hello I have just the same problem. Case #04946969 Is there any way to get the SQL scripts?

Very thankful

Yenming

[Dima P.]

Hello Yenming,

Please keep working with support team. Thanks!

[Jurica.Radovan]

Hello Wishr,
my problem was solved. Many thanks to Veeam Support Team. After sending copy of my Veeam Backup SQL database to Support, they prepared SQL script.

Once again, many thanks.

Radovan

[Yenming]

Please keep working with support team. Thanks!

Unfortunately, the answer that support gave me is SQL script has been deleted
Is there any other online support that can help?

[wishr]

Hi Yenming,

No, we have a single point of contact for technical issues with no alternative options. Scripts, fixes, and other tools can be only obtained directly from the technical support engineers. I would recommend escalating the case by pressing the "Talk to manager" button if you are unhappy with the service.

Update as of June 2, 2022:
The script has been pulled off from use as a few too many edge cases appeared where unanticipated issues appeared after the script. As this time there is no way of manually cleaning the tables.

Currently available solutions are:

• Use the stored procedure from the KB1995 to drop the session history to recover some space and try to remove the tape from catalog.

• Temporarily migrate the database to a SQL Standard (even trial edition), remove tapes from catalog, and migrate back to SQL Express.

The update has come from Veeam Customer Support.

Thanks