April 29, 2010 at 1:59 pm
Dropped some notes here:
April 29, 2010 at 2:13 pm
Steve Jones - Editor (4/29/2010)
CirquedeSQLeil (4/29/2010)
SQL 2008 now allows jobs to 10 sec.Look at that, it does. I hadn't realized that I could get more granular there. However the docs (http://msdn.microsoft.com/en-us/library/ms175138%28v=SQL.100%29.aspx) need to update the docs to call out more second stuff. It allows recurrence on the minute only, and so you'd have to play games with multiple jobs.
You don't need multiple jobs, just multiple schedules to be able to schedule jobs to sub-minute intervals, and you can even do it with SQL 2000.
See the method I explained on my prior post on this thread.
April 29, 2010 at 3:18 pm
CirquedeSQLeil (4/29/2010)
WayneS (4/29/2010)
CirquedeSQLeil (4/29/2010)
800TB is mouth watering. It would be quite the learning opportunity.Indeed it would be. And he did spell out Terabyte.
Maybe the OP has been off watching the db restore? Wonder how long that would take...
I digress a little more, but what if the drives were SSD? Restore would be lightening fast. Of course you might burn through a ton of disks if you have 100TB of tlogs a day.
That would be super cool.
On another note, if his database was really that big that company would of already been in close contact with Microsoft CSS, ALL the Hardware and software vendors that are even remotely involved with their data...etc LONG before it reached the 800TB level. Microsoft might even donate the help for the publicity alone.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
April 29, 2010 at 3:29 pm
At the previous company we had about 12 TB of data over many huge datamart servers. There were many different databases the Largest was around a TB and it was refreshed from a datasource as at a remote site monthly. That was a 3-4 day process where the data passed through different datamarts to be prepared for monthly reporting. This took a team of DBA's and other support people for the SANs and network. Most the the 12TB was refreshed on daily/weekly or on month cycles with hundreds of databases ranging from 10 mb to 750gb (Besides the Monster 1 TB databases).
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
April 29, 2010 at 3:57 pm
Lowell (4/29/2010)
Steve can you put out some numbers for perspective? SSC has a lot of users and traffic, compared to many of us small biz guys we usually deal with; my biggest db i ever play with is a gig in size or so; only two tables with more than 1M records; how big is SSC's database?
I've worked with a 1.2 TB. Mostly a reporting database, with some transactional stuff added in later. On slow SAN, was a nightmare to backup, restore, checkDB and the like. Even with compressed backups, could take 4-6 hours easily for a full backup.
I couldn't even begin to imagine how to manage a 100TB+ database, let alone one closing in on a PB. It's in a different ballpark all together.
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
April 29, 2010 at 4:19 pm
GilaMonster (4/29/2010)
Lowell (4/29/2010)
Steve can you put out some numbers for perspective? SSC has a lot of users and traffic, compared to many of us small biz guys we usually deal with; my biggest db i ever play with is a gig in size or so; only two tables with more than 1M records; how big is SSC's database?I've worked with a 1.2 TB. Mostly a reporting database, with some transactional stuff added in later. On slow SAN, was a nightmare to backup, restore, checkDB and the like. Even with compressed backups, could take 4-6 hours easily for a full backup.
I couldn't even begin to imagine how to manage a 100TB+ database, let alone one closing in on a PB. It's in a different ballpark all together.
....Ooppsss 1.2 TB and it was nightmare to backup & restore... you have mentioned also that, could take 4-6 hrs for a full BCK, so can anyone tell me how the Microsoft has tested the SQL Server and put the MAX spec. that one DB can have Peta Bytes how they do the tests and what they are considering that the SQL Server 2008 can handle DBs with Peta Bytes...!?! - We can see from experience that DBs with over 1-2 TB can have really problem with BCK and Restore (these things that DBAs has needed to work every day with them)!?
April 29, 2010 at 4:33 pm
Dugi (4/29/2010)
GilaMonster (4/29/2010)
Lowell (4/29/2010)
Steve can you put out some numbers for perspective? SSC has a lot of users and traffic, compared to many of us small biz guys we usually deal with; my biggest db i ever play with is a gig in size or so; only two tables with more than 1M records; how big is SSC's database?I've worked with a 1.2 TB. Mostly a reporting database, with some transactional stuff added in later. On slow SAN, was a nightmare to backup, restore, checkDB and the like. Even with compressed backups, could take 4-6 hours easily for a full backup.
I couldn't even begin to imagine how to manage a 100TB+ database, let alone one closing in on a PB. It's in a different ballpark all together.
....Ooppsss 1.2 TB and it was nightmare to backup & restore... you have mentioned also that, could take 4-6 hrs for a full BCK, so can anyone tell me how the Microsoft has tested the SQL Server and put the MAX spec. that one DB can have Peta Bytes how they do the tests and what they are considering that the SQL Server 2008 can handle DBs with Peta Bytes...!?! - We can see from experience that DBs with over 1-2 TB can have really problem with BCK and Restore (these things that DBAs has needed to work every day with them)!?
Personally, I doubt that even MSFT has built a 512 PB database (SQL's max). In order to build the database and test the restore, they would need 1024PB (1 EB?) of storage + 10% just for overhead to be safe. Somehow, I doubt that any company has 512PB of SAN space available.
I suspect that the max is just theoretical at this point. Also, with a max data file size of 16TB, that would be more datafiles than I can wrap my head around.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 29, 2010 at 7:56 pm
I currently have one production database at 800GB - backed up and restored nightly to a report server. Backup takes ~1.5 hours - restore takes about 2 hours.
I also have a copy of this database on three other servers. One is being used to test an upcoming application upgrade, one is a sandbox for the IS team to test and the other is a weekly restore for reporting (to be replaced by the daily restore above).
The daily restore takes that long because I am also restoring all of the transaction logs up to the point in time when I start the process. Not restoring the transaction logs - the restore takes less than 1.5 hours.
I have several other databases in the 200+GB range used for reporting from another system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 30, 2010 at 4:26 am
Thanks for the overwhelming response and i am really thrilled to see so many responses, but sorry a small correction here (and a big relief for those who are still in shock): It's not 100TB log, it's 100GB log. Honestly i am just a developer here and don't have the exact technical know-how and expertise of the systems here. I don't know what the hell is happening around here, but it is really exciting. Here is what i could collate from the experts working here.
12 x IBM TS3310 Tape library
2, 1.8 Tb/s specially designed Ethernet network interfaces
1.8Tb/s specially designed 8 port Fibre-channel host bus adapter on trials.
We have already pulled in some hardcore industry experts who are racking their heads with the systems. Chat you later.
April 30, 2010 at 4:38 am
Thanks for getting back to us Chandu - we thought we had lost you. May I ask, is your database 800TB? What type of data / business area are you dealing with? It sounds fascinating and I think most people on this thread would appreciate the insight.:)
April 30, 2010 at 5:34 am
Ah, a 100gb log is a VERY far cry from a 100tb log, and makes one heck of a lot more sense.
Personally, I was only ever managing 700gb and that was on SQL Server 7.0.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 30, 2010 at 6:21 am
Chandu-212374 (4/30/2010)
Thanks for the overwhelming response and i am really thrilled to see so many responses, but sorry a small correction here (and a big relief for those who are still in shock): It's not 100TB log, it's 100GB log. Honestly i am just a developer here and don't have the exact technical know-how and expertise of the systems here. I don't know what the hell is happening around here, but it is really exciting. Here is what i could collate from the experts working here.12 x IBM TS3310 Tape library
2, 1.8 Tb/s specially designed Ethernet network interfaces
1.8Tb/s specially designed 8 port Fibre-channel host bus adapter on trials.
We have already pulled in some hardcore industry experts who are racking their heads with the systems. Chat you later.
Thanks for correcting the size error. It sounds like you are still involved with a fairly large database.
Sure stirred up some excitement around here.
Greg E
April 30, 2010 at 8:45 am
Thanks for posting the corrected log size. What is the actual DB datafile size, and database size?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 30, 2010 at 9:31 am
CirquedeSQLeil (4/30/2010)
Thanks for posting the corrected log size. What is the actual DB datafile size, and database size?
Ya I'd like to know to!
April 30, 2010 at 11:05 pm
Chandu-212374 (4/29/2010)
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.
Better than what? If you are writing to a networked USB drive then I have some suggestions ......
I suspect most people taking an interest in this thread would like to see exactly what you are doing now. Presumably the size of each log backup is significantly less than 100GB, and you have already done some analysis on what resource the backup job is waiting on.
Viewing 15 posts - 46 through 59 (of 59 total)
You must be logged in to reply to this topic. Login to reply