Highlighted

SQL 2008 compressed backup waste 50% of tape size

I have tapes assigned for backup policies of SQL 2008 databases, the backed up databases are compressed with the compression option in SQL 2008 server.

the problem is that the tapes used for these SQL databases backup reaches full status when it's about 50% of the actual size of the tape.( Example : 1.6 TB tapes are full when it contains about 700 GB).

i don't understand what is the reason of this behavior, while this problem didn't appear when i was using databases backup from SQL 2000 server or normal Flat files backup. it takes the whole tape size (almost 1.6 TB).

 

So, Kindly i need your help with this issue that waste almost 50% of my tapes size

2 Solutions

Accepted Solutions
Highlighted
Accepted Solution!

NBU isn't marking the tape

NBU isn't marking the tape full, the drive itself is detecting that and telling NBU the tape is full.

Therefore, nothing can be done in NBU.

Generally, modern drives know the data received can'tt be compressed 'again' (as already compressed) and so hopefully won't try, as if you compress compressed data it generally gets bigger, though not by very much.

You may do better to turn off compression in SQL and let the drive hardware do it, it may well do a better job.

I have seen faulty drives mark tapes full to early, so this is another possibility, bad firmware could also cause it.

 

View solution in original post

Highlighted
Accepted Solution!

I will try to clarify. The

I will try to clarify. The space figures you see for LTO drives assumes a 1:2 compression . The tape hold 800Gb native (or we can call it guaranteed space)  and with a 1:2 compression you get 1.6TB per tape.

HOWEVER - if the data is already compresses like SQL 2008 backup files, you will only get the native/guaranteed space.

Please note that the backup application does not "know" tapedrives are compressing data. Backup application will keep sending data until the tape drive raises the End Of Media signal.

As a experiment - try to zip a SQL dump files. I bet the zip file will remain the same size or even increase in size. 

Hope this clarify - It sound all normal to me what you tell. 

Best Regard

Nicolai

View solution in original post

3 Replies
Highlighted
Accepted Solution!

NBU isn't marking the tape

NBU isn't marking the tape full, the drive itself is detecting that and telling NBU the tape is full.

Therefore, nothing can be done in NBU.

Generally, modern drives know the data received can'tt be compressed 'again' (as already compressed) and so hopefully won't try, as if you compress compressed data it generally gets bigger, though not by very much.

You may do better to turn off compression in SQL and let the drive hardware do it, it may well do a better job.

I have seen faulty drives mark tapes full to early, so this is another possibility, bad firmware could also cause it.

 

View solution in original post

thanks for your reply

thanks for your reply mph999;

but i need to clarify that we don't compress the backed up data again by Netbackup software. it's compressed only from the SQL server and i need to do it that way as it saves about 80% of the space using SQL backup compression.

for the bad firmware: i don't think it the reason, as it happens in SQL 2008 compressed backup only, not in the flat files backups or the SQL 2000 (non compressed) backups.

Highlighted
Accepted Solution!

I will try to clarify. The

I will try to clarify. The space figures you see for LTO drives assumes a 1:2 compression . The tape hold 800Gb native (or we can call it guaranteed space)  and with a 1:2 compression you get 1.6TB per tape.

HOWEVER - if the data is already compresses like SQL 2008 backup files, you will only get the native/guaranteed space.

Please note that the backup application does not "know" tapedrives are compressing data. Backup application will keep sending data until the tape drive raises the End Of Media signal.

As a experiment - try to zip a SQL dump files. I bet the zip file will remain the same size or even increase in size. 

Hope this clarify - It sound all normal to me what you tell. 

Best Regard

Nicolai

View solution in original post