Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup and restore solution for 800TB of data


Backup and restore solution for 800TB of data

Author
Message
Chandu-212374
Chandu-212374
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 323
Our production database on SQL 2005 has grown to a size of around 800 Terabytes of data and transaction log is around 100 TB. We have set the frequency interval of the log backup job to 10 seconds, but one execution of the log backup is taking 50 seconds to complete. We are not sure when the next backup job is happening after the start of the previous backup job. It's all messed up and systems have become too slow to the extent of hanging frequently. Can someone suggest a better backup and restore solution.
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19446 Visits: 32324
You're doing a 100TB log backup in 50 seconds? That's simply amazing right there.

But the question I have is, with an 800TB database (which is about 800 times the size of the largest database I've dealt with), how is it possible that you have a 100tb log? That means you're moving 1/8 of the data through the log at any one time. On any scale system, that's excessive.

I'm excited to see what people have to say about this one.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (906 reputation)SSC Eights! (906 reputation)SSC Eights! (906 reputation)SSC Eights! (906 reputation)SSC Eights! (906 reputation)SSC Eights! (906 reputation)SSC Eights! (906 reputation)SSC Eights! (906 reputation)

Group: General Forum Members
Points: 906 Visits: 8317
That would mean less than 10 minutes to move a PetaByte of information.
I was amazed to recently see a TeraByte moved in 30 minutes.
The older I get, the faster technoolgy seems to move.
Could you share an outline of the hardware to do this?

Something does seem a bit odd - can you verify the size of the db?
And can you tell us the size 24 hours ago? a week ago?

Greg E
Chandu-212374
Chandu-212374
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 323
Actually over the couple of years, the db size has grown from few tens of GBs to such a huge size. At the time of design, we have kept the settings as 50 seconds for worst case scenario. We can't afford to increase this time now. And there are a lot of applications depending on the database doing high volume transactions that tlog size is also getting unmanageable..Please suggest a different backup solutions available.

By the way, you mean TLog can't be 100TB for this database ?
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19446 Visits: 32324
I didn't say it "can't" be 100tb, I said it probably shouldn't be. You can have a tlog hundreds of times the size of your database, but that doesn't make it right.

What I'm saying is, it's very abnormal for a transaction log to represent such a large percentage of the data in the database itself. That means that you have up to 100 terabytes of transactions where the data is only 800 terabytes. You can replace the tb with gb or mb or b, and it still sounds problematic. It means 1/8 of your total data is being transacted upon, based on your issue, within 50 seconds. Again, highly unusual. But then you're into the range of unusual.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40288 Visits: 18846
It can be 800TB, but that is huge. Are you sure you're not mistaking the size by a factor of 10?

Also, I'm curious how you are backing up the log every 10 sec. Are you using some external program. AFAIK, jobs to schedule can be done every minute, not more granular than that.

Maybe you can post the output from

select * from sys.database_files



Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
GregoryF
GregoryF
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1058 Visits: 885
Are you really sure it's 800TB and not 800GB?

SQL 2008 only support 512TB Databases
http://msdn.microsoft.com/en-us/library/ms143432.aspx

So does SQL 2005
http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53990 Visits: 44623
Get a professional in. There are going to be very few people with experience in databases over a few TB and an 800TB has to be one of the biggest SQL Server databases around.

I'd suggest approaching Microsoft and seeing if you can get the CAT (customer advisory team) people involved. Or contact Paul Randal and see if he'd be available for some consulting

With a database this big, this active and as important as you indicate, you should be looking at getting the absolute top people in to do a professional and comprehensive job. Relying on free help for something like this is recklessness at best.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26402 Visits: 38120
Must be using multiple t-log files since the max size of the t-log is 2 TB.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Dugi
Dugi
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1438 Visits: 3511
According to max specifications of SQL Server how it can be log file 100 TB ( max spec. 2TB)! ? !

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search