SQL Express DB full (over 10GB)

[unsichtbarre]

Support case ID: #913610

I have a Veeam server that runs only a single files to tape job as part of a compliance initiative/requirement. It's been working great, copying a couple hundred files to tape every day for almost a year. The total data hasn't even filled up one LTO tape, in fact it's less than 5GB total!

Imagine how surprised I was to find the job failing because the SQL Express database had grown to over 10GB! I work with Veeam servers that protect several hundred VMs on an hourly basis with Replication, Tape, and Backup, and the database rarely grows over 10GB, even in those extremely active situations! Here the SQL Express DB has grown larger than the protected data!

The solution (aside from going to SQL Enterprise - for which there is no budget in this case) is to purge the database, but I can't seem to find the correct SQL Query and the support tech sent me the wrong query. I DO have a DB Backup!

THX in ADV!

[HannesK]

Hello,
the case number points to a 4 years old case - can you check that?

As long as you don't backup hundreds of thousands or millions of files with file-to tape, the 10 GB express should be good enough for about 500 VMs.

In general: many files with "file to tape" jobs can fill up the database quickly.

If you see issues, please continue working together with support that they can fix it.

Best reagrds,
Hannes

PS: there is also Standard Edition of SQL...

[DonZoomik]

You could try enabling page compression on database. You do need 2016 SP1 or newer for that. I've done it on a few Veeam One (community editions) databases and noticed no issues (no noticeable performance impact), it should be transparent to the application.

[unsichtbarre]

Case #: 03799616 is the correct case number - don't know how it got scrambled in copy&paste.

I am concerned with the growth to the DB, as my experience is the same as yours with DB size.

Question is: How do I effectively shrink the DB and continue operations.

THX

[HannesK]

Hello,
I just read in the case

To quickly recap, the Veeam Database was at the 10 GB SQL Express limit. This is due to the tape job catalog consuming the data from consistent file to tape backups. To workaround, we performed a configuration restore without the catalog information

So if you really have that much data because of file-to-tape, then I would think about SQL standard or the suggestion from DonZoomik.

For all database operations, support is the way to go.

Best regards,
Hannes

[DonZoomik]

I've used these examples, grabbed from Google long time ago with some modification to escape some VeeamOne schema/table name chars.
These just generate expressions (my SQL-fu is weak, I don't know how to write dynamic SQL), you must copy results (alter table statements) and execute them separately. They work from smallest to largest table/index so you should be fine (stay under 10GB). After completion, you may shrink the database if you want but that's optional as there will be free space within the database. If you want to revert, set DATA_COMPRESSION=NONE but this will probably blow your 10G limit.

This generates SQL statements to compress tables

Code: Select all

SET NOCOUNT ON

SELECT 
   'ALTER TABLE [' 
   + s.[name] 
   + '].[' 
   + o.[name] 
   + '] REBUILD WITH (DATA_COMPRESSION=PAGE);'
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
   ON o.[object_id] = i.[object_id]
INNER JOIN sys.schemas AS s WITH (NOLOCK)
   ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)
   ON i.[object_id] = ps.[object_id]
AND ps.[index_id] = i.[index_id]
WHERE o.[type] = 'U'
ORDER BY ps.[reserved_page_count]

SET NOCOUNT OFF

This generates SQL statements to compress indexes

Code: Select all

SET NOCOUNT ON

SELECT 
   'ALTER INDEX "' 
   + i.[name] 
   + '" ON "' 
   + s.[name] 
   + '"."' 
   + o.[name] 
   + '" REBUILD WITH (DATA_COMPRESSION=PAGE);'
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
   ON o.[object_id] = i.[object_id]
INNER JOIN sys.schemas s WITH (NOLOCK)
   ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)
   ON i.[object_id] = ps.[object_id]
AND ps.[index_id] = i.[index_id]
WHERE o.type = 'U' 
AND i.[index_id] >0
ORDER BY ps.[reserved_page_count]

SET NOCOUNT OFF

[unsichtbarre]

I wanted to follow up with a resolution (Sort of).

Turns out that a File to Tape job in Veeam causes the database to grow. All of the Veeam Support engineers I spoke with agreed on this point. In the end, the recommendation was to create a new Veeam database and restore from configuration backup without sessions or history. This worked for Veeam, but trashed my job settings and tape library rendering "instance not set to an instance of an object" error every time I tried to access/edit/reconfigure the job.

Solution: Started with new Veeam database and re-configure from the ground-up. Also, now that I am aware that Files to Tape jobs grow the database hugely, I included a TGZ archive/compression step in my pre-tape script. Now, instead of archiving several hundred log files per day, I archive one TGZ.

[Dima P.]

Hello John,

Glad to hear that proposed workaround fixed the issues. Meanwhile we've asked RnD folks to review the case details in order to understand the root cause of the issue. Thank you for your post and sorry for any inconvenience caused.