| Author |
Topic |
|
alisaghatchi
Starting Member
1 Post |
 Posted - 2009-05-23 : 10:26:03
|
| To Whom It May ConcernHello Lady/sirWe have a Microsoft SQL_2005 in our Company.our supervisor who was an outsource company had a wrong and set LogFile Limitation to 'UnLimited'.So our Logfile Size extended to 800GB.Yes 800GB. After this we Contacted to our Supervisor and said him our Problem.He said Detach SQL-Server and Delete LogFile then Attach SQL-Server.we did this and in this time we had a tragedy.our SQL-Server was not work Properly.We just have some backups .our 2 days ago Backup do not retrive properly. can you Help us?Befor your answer I thanks you.thanks a lot : Ali Saghatchi Langroudi-Iran-Tehran |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-05-23 : 16:39:49
|
| Ok. There are a number of problems here.The full log file was probably because you weren't backing it up. Please read through this. [url]http://www.sqlservercentral.com/articles/64582/[/url]Never, never, never delete the log file of a SQL database. It's not optional, it won't always recreate and sometimes doing so results in a suspect database. What exactly was the error that you got trying to reattach the DB?There's a hack that may get the DB back, but it may result in data loss. No way around that. You deleted the log file, which is the portion of the database that ensures transactional integrity.Basically it involves creating a new DB, shutting down SQL, swapping the bad DB's data file in and then starting SQL. Has to be followed by a database repair.The procedure is detailed on these two blog posts.http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspxhttp://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspxIf you can't get things back, I'll ping Paul to see if he can help.What, precisely, do you mean by "Backup do not retrive properly"?If there's any chance of restoring a backup, it's a far better option that trying to hack the DB back.--Gail ShawSQL Server MVP |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-24 : 01:46:00
|
Sometimes the sp_attach_single_file_db can help you, by recreating an empty log file in the same directory as the datafile.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-05-24 : 01:57:39
|
| I did request that microsoft change the default recovery model to simple so that logs didn't grow uncontrollably.The idea being that it would be changed to full/bulk logged when log backups were applied so there wouldn't be a problem.This got a lot of support but microsofts response was that it could cause loss of data. Your (and others) experience shows how they didn't understand the issue - and didn't want to discuss it.I always set the recovery model of the model database to simple on installtion so that any new databases will default to simple.I've an article here on transaction logs any why they growhttp://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.htmlAnd possibilities of recovering from failyres herehttp://www.nigelrivett.net/SQLAdmin/RecoverCorruptDatabase.htmlI would sack the "Supervisor".One of the most important things to do is to test backups and they should have that in place.Given the issues you have it doesn't sound like they are doing their job properly and you should get someone to review your system and practises.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-05-24 : 03:57:07
|
quote:
Originally posted by nr
I did request that microsoft change the default recovery model to simple so that logs didn't grow uncontrollably.The idea being that it would be changed to full/bulk logged when log backups were applied so there wouldn't be a problem.
Do you have a Connect suggestion for that?I can see both sides, it's definitely a tricky problem. Worth more debate.--Gail ShawSQL Server MVP |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-05-24 : 04:07:03
|
| It was a long time ago when suggestions got voted on and the top 3 got reviewed.This came top but was rejected.>> I can see both sides, it's definitely a tricky problem. Worth more debate.Not really - what's the downside?If you don't implement log backups then the only thing you get from full recovery is that you can save the log file and replay it in the event of a crash - but if you're capable of doing that you probably wouldn't be in this situation.Look at all the people that have problems with log files filling the disk - and these are the people who don't really know what they are doing and need protection.When they dumbed down the installation and admin of sql server (a bad thing imho) they should have also made it so that the default installation would work without further intervention.Note that when you do add lotg backups you will get an error if the recovery model is wrong.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-05-24 : 04:20:28
|
quote:
Originally posted by nr
Note that when you do add lotg backups you will get an error if the recovery model is wrong.
Which is great for people who bother to read errors or use the default maint plans to set log backups up. I've seen a case where the DB was in full, log backups were running. Someone (no one knew who) switched the DB to simple, no one checked errors. 2 days later, DB failed. When they found that they couldn't restore to point of failure, boss sent serious accusations to MS that their software had caused data loss."Your software's default settings filled my drive" is a safer accusation than "Your software's default settings resulted in data loss costing x million dollars"If DBAs knew what they were doing, either default setting would work. It's the people who don't that's the problem and they could get into trouble either way.Hell, I've seen someone bitterly complain to MS that the DB can't be retrieved from the log only if the DB's in full recovery and there's never been a full backup.Maybe we should debate this elsewhere? Don't want to clog thread here in case OP comes back and needs more help with his DB.--Gail ShawSQL Server MVP |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-05-26 : 08:31:17
|
| My take - production databases should always be in full recovery mode with an appropriate backup scenario. If you are going to take this to another thread, I'll chime in with my thoughts as well. I like a good debate!Terry-- Procrastinate now! |
|
|
|
|
I have a Problem,Please Help Me