SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 I have a Problem,Please Help Me
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alisaghatchi
Starting Member

Iran
1 Posts

Posted - 05/23/2009 :  10:26:03  Show Profile  Visit alisaghatchi's Homepage  Reply with Quote
To Whom It May Concern

Hello Lady/sir

We 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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/23/2009 :  16:39:49  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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. http://www.sqlservercentral.com/articles/64582/

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.aspx
http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx

If 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 Shaw
SQL Server MVP
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 05/24/2009 :  01:46:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 05/24/2009 01:52:03
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/24/2009 :  01:57:39  Show Profile  Visit nr's Homepage  Reply with Quote
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 grow
http://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.html

And possibilities of recovering from failyres here
http://www.nigelrivett.net/SQLAdmin/RecoverCorruptDatabase.html

I 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.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/24/2009 :  03:57:07  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 Shaw
SQL Server MVP
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/24/2009 :  04:07:03  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/24/2009 :  04:20:28  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 Shaw
SQL Server MVP
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 05/26/2009 :  08:31:17  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000