cancel
Showing results for 
Search instead for 
Did you mean: 

SQL 2008 compressed backup waste 50% of tape size

ahmed_gad1
Level 3

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 ACCEPTED SOLUTIONS

Accepted Solutions

mph999
Level 6
Employee Accredited

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

Nicolai
Moderator
Moderator
Partner    VIP   

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 3

mph999
Level 6
Employee Accredited

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.

 

ahmed_gad1
Level 3

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.

Nicolai
Moderator
Moderator
Partner    VIP   

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