November 25, 2009 at 7:37 am
In your kind of situation, you should backup the Transaction Logs more frequentyl, may be every 5 minutes.
The growth of log file may be normal due to usage of data (insert/update/delete). Frequent backup of Log file will make the log file more usable and will not grow fast. This will also minimise the data loss in case of any mis-event.
November 25, 2009 at 7:38 am
GilaMonster (11/25/2009)
Atul DBA (11/25/2009)
As your log file is very huge, and it is required to Truncate the log file for proper utilization of disk space, you need to stop the Mirroring activity, truncate the transaction log, change the database type to Simple (only if this is allowed in your business case), take a full backup and then reconfigure the database Mirroring.Huh? Why on earth would you do all that, requiring large amounts of maintenance and a risk of downtime (what happens if the principal fails while the mirror is down?)?
What's wrong with a simple scheduled log backup? (The correct way to allow log space reuse)
Just another vote for not mucking with the system in that fashion. You're talking about doing seriously dangerous stuff to a production system all in an effort to avoid running a log backup script. Why?
We need to approach systems, especially production systems, like a doctor; first, do no harm.
"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
November 25, 2009 at 7:42 am
roelofsleroux (11/25/2009)
Gila the reason we use mirrors is because this is for a stock exchange trading application.If the database fails for some reason, the mirror kick in. If there is as much as n 10minute delay these guys could loose up to $1million due to down time.
Then you definitely need log backups. Every 10 minutes is a good start, every 5 may be better. Ask your manager/business what the maximum permissible data loss is (not downtime, data loss). Mirroring's good high availability, but it is not perfect. It's not going to help in the slightest if someone (dev/admin) 'accidentally' drops a table or does a delete/update without a where clause. For those you need backups. Plus, if the mirror server's in the same server room as the principal (not uncommon) it won't help if there's a fire/earthquake/meteor/hurricane/tornado/volcano.
How much experience do you have managing a mission critical SQL server? Is there a more senior DBA there? If not, consider getting someone in to advice and evaluate your HA/DR strategy. If 10 min down = several million $ lost, it could well be a good investment.
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
November 25, 2009 at 7:50 am
btw, I used to work with a DBA team that managed a mission critical stock market trading system. We had a windows cluster, the SAN mirrored to a secondary site 15km away and we had log backups every 15 minutes that were copied elsewhere. No such thing as too much redundancy for something like this.
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
November 25, 2009 at 7:51 am
Hahaha!
Gila you sound like Doomsday Prophet. This is South Africa. The only natural disaster we know of so far is a flood 21 years ago that destroyed a town. Last earthquake was 22years ago.
Real Server and Mirror Server are on different locations each with their own secure backup process.
No development is done on the Database. We use a Production/Development concept.
But I will consider your opinion of a 5min transaction log backup when the final product is up and running.
Thanks for all the help.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
November 25, 2009 at 7:55 am
My suggestion for reconfiguring the mirror was based on db size of 200 MB and Log size of 2 GB which is not good sign. Even it was not stated that this is for Stock Trading application. Mirroring setup does not require any downtime.
Based in current input, the Transaction Log backup at every 5-10 minutes a good suggestions.
November 25, 2009 at 7:57 am
roelofsleroux (11/25/2009)
Hahaha!Gila you sound like Doomsday Prophet. This is South Africa. The only natural disaster we know of so far is a flood 21 years ago that destroyed a town. Last earthquake was 22years ago.
Real Server and Mirror Server are on different locations each with their own secure backup process.
No development is done on the Database. We use a Production/Development concept.
But I will consider your opinion of a 5min transaction log backup when the final product is up and running.
Thanks for all the help.
Oops. Stepped right on that land-mine. Where do you think Gail works out of?
"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
November 25, 2009 at 8:02 am
roelofsleroux (11/25/2009)
Gila you sound like Doomsday Prophet. This is South Africa.
Jy ook? So baie mense van Suid-Afrika op hierdie forums.
The only natural disaster we know of so far is a flood 21 years ago that destroyed a town. Last earthquake was 22years ago.
Yeah, but JHB is badly under-mined. I used to work in the CBD (aforementioned trading system) and one of the things on our risk list was a seismic event.
No development is done on the Database. We use a Production/Development concept.
Accidents happen. Been there, done that. Got the t-shirt (and the weekend of overtime)
If you want an external party's opinion on this (consulting), drop me a mail and we can discuss.
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
November 25, 2009 at 8:12 am
Atul DBA (11/25/2009)
My suggestion for reconfiguring the mirror was based on db size of 200 MB and Log size of 2 GB which is not good sign. Even it was not stated that this is for Stock Trading application.
Regardless of the application type, repeatedly dropping and recreating a mirror just to handle a large log file is unnecessary and risky. As a once-off operation, maybe (but unnecessary) as something to be done regularly, absolutely not.
Mirroring setup does not require any downtime.
No, but it does take the admin's time (which could be better spent doing other stuff) and it does introduce a window when there's no redundancy. What happens if the principal fails while the mirror's been reconfigured? If the mirror is frequently dropped and recreated, the chance of that happening goes up.
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
November 25, 2009 at 9:25 am
To follow up on Gails post, the dropping and recreating of the mirror may not take too much time on a 200MB database - but what about when that database grows to 500GB and the Mirror partner is 1500 miles away - connected by a T1. The DBA time has just become exponentially more than for the 200MB database.
Now on the flip side, if we backup the tran logs every 5-10 minutes and leave the log at 2GB (let's say for a CYB abnormal log usage period), we can then reuse the free space in the log file and not touch the mirror setup.
For our scenario, we supported a shipping application for a major international shipping company. The DB was 500GB, clustered and mirrored to a site 1500 Miles away (the partner was also clustered). Downtime was not permissable - much like this scenario.
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
November 25, 2009 at 9:28 am
roelofsleroux (11/25/2009)
Hahaha!Gila you sound like Doomsday Prophet. This is South Africa. The only natural disaster we know of so far is a flood 21 years ago that destroyed a town. Last earthquake was 22years ago.
Real Server and Mirror Server are on different locations each with their own secure backup process.
No development is done on the Database. We use a Production/Development concept.
But I will consider your opinion of a 5min transaction log backup when the final product is up and running.
Thanks for all the help.
So, South Africa has never had riots? No building fires? Never had a local power outage due to a thunderstorm or some idiot drunk-driving a truck into a transformer station?
Trust me, if there are no "natural" disasters, humanity can certainly come up with plenty of "artificial" ones.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 25, 2009 at 9:29 am
Got to admit, interesting advice here, mixture of mis-informed advice and loads of good advice from senior members.
I would take Gails advice and think very carefully about your existing setup and work out exactly how you want to proceed, I have been responsible for trading systems responsible for billions of dollars on a daily basis, and you can never be too careful. if the system is that critical, you need to championing the most effective DR/minimal downtime solution you can think of.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 25, 2009 at 9:37 am
GSquared (11/25/2009)
So, South Africa has never had riots?
Frequently. Often associated with strikes and demonstrations.
Never had a local power outage due to a thunderstorm or some idiot drunk-driving a truck into a transformer station?
Well, there were those 2-4 hour rolling blackouts a couple years back, because the power utility forgot to account for future growth when planning power stations.
Thunderstorms, not unless the lightning hits the substation. It happens, not too often.
We did have a week-long power outage in the centre of Johannesburg earlier this year, because some bright spark tried stealing power cables and set the tunnel on fire. Took ages to get the fire out, longer to replace all the damaged equipment
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
November 25, 2009 at 11:40 pm
Sorry about the assumption Gail that you were an American. Those guys have natural disasters like we South Africans have Eskom power cuts, Union Strikes, and Taxi Violence. :w00t::w00t::w00t:
Luckily we have a generator in the basement that can run for 4 days straight without refueling. And enough UPS power to keep the servers up for at least 7hrs.
Just to be clear to everyone on the situation. The database at this stage is in a development/test phase and is only used by the developers and testers. It’s mirrored because we are testing the performance when the main server does go down.
Before we're going to release the application, we will be building a thorough crisis control plan which will include backups, downtime and data safety – which is only happening end of 2010. But so far daily backups are made and stored offsite on two different locations (Offsite Server and MetroFile). We use version control for development as well to ensure that no major destruction is caused by any noob developers
Thanks for all the helpful tips. I myself are not the DBA, neither am I the System Administrator … I am a SQL Developer … It’s not my responsibility to manage the safety of data. But I will mention your ideas to our System Administrator.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
November 26, 2009 at 3:15 am
GilaMonster (11/25/2009)
roelofsleroux (11/25/2009)
Gila you sound like Doomsday Prophet. This is South Africa.Jy ook? So baie mense van Suid-Afrika op hierdie forums.
Hahahahaha I was laughing waiting to see Gail's reply!
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply