| Author |
Topic |
|
Taragor
Starting Member
46 Posts |
 Posted - 2011-09-24 : 15:59:06
|
| OS: Windows Server 2003 R2 SP1SQL: Sql 2000 SP4Hi All,We had a problem yesterday with a production database. During a large delete execution we ran out of HD space and the process terminated. Since then, we've been unable to access the database the deletion was going against.The only way to detach the DB from sql was to shut down sql service, rename the data and log files then restart SQL.I then tried reattaching the DB (after renaming the files to their original name). This did not work, SQL would hang and stop responding.I ended up deleting the 2 files with the intention of restoring the DB from our backup systems. (We use the tivoli sql backup). When I try to restore the DB when it gets to the log file (takes a REALLY long time as the data file is 200GB) I get an error from the tivoli system saying a timeout occured. When this happens, if I look in SQL it shows the DB but has a status of (loading). Nothing I do can change this status. I am able to detach the database when it has that status but can no longer re-attach afterwards. I moved the data and log file to a new directory (not my usual db file directory) and tried re-attching the db. It says something is wrong with the log file and aborts.I tried deleting the log file and re-attaching the db (expecting sql to create a new one). What happens is it aborts saying there is a problem with the log file but it's pointing to where the original emplacement of the log file was. It's as if there's a trace somewhere in SQL and it refuses to do anything.Does anyone have any suggestions? I'm at my wits end.Sincerely,Tar |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-24 : 17:56:16
|
| Congratulations. You've managed to do just about everything to that database that you should never ever do to a database.When the delete failed it had to roll back. That'll take as long (or longer) than the original delete. By shutting SQL down in the middle of that you left the DB in a non-clean state. Not a problem, SQL can and will do crash recovery on the DB when it starts up (that's probably why SQL went unresponsive after the reattach. It wasn't broken, it was busy and left to finish it would have finished and the DB would have come online.Do you have the original data and log files?If not, then you need to drop that half-restored database and re-restore from Tivoli. There's no way to bring a half-restored database online. It shouldn't take too long, 200GB isn't that large, a 1TB restore can be done in 3 hours. How long did it take to restore when you did restore tests?If the restore fails and Tivoli support can't help, then it could be that the backup itself is damaged. If that's the case, this database is likely a complete and total loss.p.s. the log is not an optional file, SQL won't recreate it in most situations.--Gail ShawSQL Server MVP |
 |
|
|
Taragor
Starting Member
46 Posts |
Posted - 2011-09-24 : 18:33:47
|
| Hi Gila,I bow my head in shame. I am fully aware that I took the complete wrong and idiot approach to the situation. Unfortunately, I can't turn back time. :)Ok let me try to explain again.The database no longer exists in sql.No, I do not have the original files that I deleted the day of the crash (Our department is low funded and there was nowhere for me to dump a 200gb file)What I do have are the tivoli backup files from early that morning prior to all the issues and earlier (daily backups) backups if need be.The issue is that when I restore the backup from tivoli eventually I get a timeout error (files are present in the location I am restoring them). The crash happens when it start either restoring/processing/or attaching the LDF file as it's always when the LDF appears from the restore or soon after that tivoli stops. Never during the MDF step of the restore. When I open enterprise manager after tivoli aborts I can see the DB listed but with a status of (loading). Leaving it for a while does nothing. I can detach the DB even under that status but if I try to re-attach it I get an error saying there is a problem with the LDF file. The error though is pointing to a non existent LDF file (it's pointing to the original location of the data and LDF file) and not to the location of where I'm trying to load the restore files (I restored the files to a directory named SQLBACKUP)So my issue is that it seems as though SQL has kept a ghost of where the LDF file "was" and that is creating issues. Does this make sense the way I'm explaining it?Sincerely,TarP.S. This is probably gonna make you have a stroke but IF all else fails. Would uninstalling sql completely from the server (after detaching all the dbs and moving them) then doing a clean install maybe fix this issue? Am I shooting myself even more in the head by this approach? :) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-24 : 18:56:10
|
quote:
[i]The issue is that when I restore the backup from tivoli eventually I get a timeout error (files are present in the location I am restoring them). The crash happens when it start either restoring/processing/or attaching the LDF file as it's always when the LDF appears from the restore or soon after that tivoli stops. Never during the MDF step of the restore.
Try restoring an earlier backup, it could be the backup you're restoring is damaged. Otherwise log a call with Tivoli support and get their assistance. Make sure there's not a ldf lying around somewhere that's messing things up. Check that there are no permission errors with the files in the new location.btw, there isn't an 'mdf and ldf portion" of the restore. First the restore creates the files, then it puts the data back, then it runs crash recovery to get the DB consistent.You have tested your restores before, right? You know that the restore process does work, right????quote:
When I open enterprise manager after tivoli aborts I can see the DB listed but with a status of (loading). Leaving it for a while does nothing.
You could leave it forever and nothing will happen, it's a half-restored database and nothing can be done with that other than dropping it and starting over.quote:
I can detach the DB even under that status but if I try to re-attach it I get an error saying there is a problem with the LDF file.
You can never reattach a database part-way through the restore process. Only an online database can be detached and successfully attached.quote:
P.S. This is probably gonna make you have a stroke but IF all else fails. Would uninstalling sql completely from the server (after detaching all the dbs and moving them) then doing a clean install maybe fix this issue?
No. It'll waste several hours of your time for nothing. You need to get that restore working, otherwise your database is gone, lost beyond recovery.Might be a good time to speak to your manager and beg leniency.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-24 : 19:05:16
|
| I'm not familiar with Tivoli backup. Does it include a SQL backup "agent" - i.e. that can backup SQL Databases direct to Tape?Or is it just a file-to-tape type backup solution?If its the later, and you were backing up the MDF and LDF files direct to tape, whilst SQL was running, I doubt that the files will be recoverable.If its the SQL Agent type then I wish you luck. We get questions here often enough about non-recoverability from such systems that I've never put my faith in them, although there are some well known and respected Brand Name solutions (and perhaps Tivoli is one of them, the fact that I have not heard of it means nothing in this context ...).Backing up the Disk, an then from the Disk file to Tape means you have a backup file on Disk that you can easily / quickly restore from, and if you need something "older" you can get the tapes out.If it is a file backup of MDF + LDF then I suggest you restore the files to a suitable folder first and then try to attach them.You mention the "restored MDF / LDF files". If that's Tivoli creating a database and restoring it from a tape-backup of the database, then if it doesn't finish restoring you won't have a usable database. Detach / Re-attach won't work because the database files are "incomplete".Maybe there is a timeout config. setting somewhere that you can increase?I don't think that a reinstall of SQL will help. However, I would remove all other "load" from the server to give it the best chance of devoting 100% of CPU and Disk to the restore. Make sure all scheduled tasks are temporarily disabled. (Assuming that no other database on that server needs to be accessed concurrently with this restore) |
|
|
|
Taragor
Starting Member
46 Posts |
Posted - 2011-09-24 : 19:10:52
|
| I haven't tested a restore on this particular DB for quite a while. But I have used the restore on a few other DBS running on the same server numerous times recently and I've never had any issues.I'll have to wait till Monday to talk to tivoli support and see if they have any ideas. I take it there's no table in SQL that keeps tabs on where db/log files are located and their might be a trace entry left there pointing to the old non existent LDF?Tar |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-24 : 19:13:30
|
| Google turned up this:"To prevent the restore from timing out, you may need to up the communication timeout parameter on the TSM server. It is usually set to 30 minutes, but that may not be enough. Use the SETOPT COMMT command on the TSM server to increase it."Also worth checking that you have current Service Pack for SQL and latest version / patches for Tivoli.Check your SQL version wtihSELECT @@VERSIONand then look that up in this list:http://www.sqlteam.com/article/sql-server-versions |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-24 : 19:18:57
|
| "I take it there's no table in SQL that keeps tabs on where db/log files are located and their might be a trace entry left there pointing to the old non existent LDF?"There is a history table for Backup's and Restore's which might give you some clues. Restore history is here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300#268882and backup history is a bit further down that thread. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-24 : 19:39:09
|
quote:
Originally posted by Taragor
I take it there's no table in SQL that keeps tabs on where db/log files are located and their might be a trace entry left there pointing to the old non existent LDF?
Only for existing databases. As soon as the DB is detached or deleted, the file location information is discarded. SQL has no need to know where the files of deleted databases used to be.--Gail ShawSQL Server MVP |
|
|
|
Taragor
Starting Member
46 Posts |
Posted - 2011-09-24 : 20:00:16
|
| Kristen:I don't have full admin control for the tivoli system so I will have to wait till Monday to test out the timeout suggestion. Thanks for the lookup, my heads been in a spin all day trying to solve this.Gila:Hmmm, I guess SQL is pointing to the old folder from the restore process then as it's pointing to where the files used to be and not where I'm restoring them to.Thanks for all your help both of you. I'm gonna leave it till Monday as I'm tired and will probably screw things up even more in frustration. Tar |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-25 : 02:57:12
|
| "not where I'm restoring them to"You could try restoring to the original position then? (That will only make a difference if Tivoli has to have some extra information to tell SQL Server that the database is now in a new position.)Irrespective of that a Backup file (but I don't know how Tivoli works) contains the original location of the database, and you have to specify (as part of Restore) to put it in a different location.So just in case that isn't happening you might want to put the files in the original location |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-25 : 05:06:54
|
quote:
[i]Hmmm, I guess SQL is pointing to the old folder from the restore process then as it's pointing to where the files used to be and not where I'm restoring them to.
SQL has no knowledge of where the DB used to be. If the files are appearing there, it' due to the restore.Remember I said that if you detach a DB part way through restore it won't and can't ever attach again. It's fully expected that it won't. What's probably happened when you try to attach that half-restored database. is that the database hasn't been restored to the point that the database header (not SQL Server's overall system tables) gets updated with the new file locations (that would likely be done at the end of the restore). But it's totally irrelevant, the DB can't be reattached if detached half way through a restore anyway.You need to get that restore working correctly (as in restoring completely without error) if you want to get this DB back.--Gail ShawSQL Server MVP |
|
|
|
Taragor
Starting Member
46 Posts |
Posted - 2011-09-27 : 13:46:22
|
| Gila / Kristen:Wanted to give you a heads up into the conclusion of my fiasco. :)After getting another server up with enough HD space to support the size of the DB I tried doing a restore and ended up with the same issue as the prod one. I did more digging based on the timeout issue you mentioned Kristen and had the IT dept responsible for the backups increase the timeout length on the server side and low and behold the restore is going through.Thanks again to both of you for the help and advice. And Gila I promise to have more patience with SQL the next time something crashes and allow it to do a rollback before pulling the plug :)Tar |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-27 : 14:13:39
|
| Good. That makes it the one successful recovery of the month (I usually have to tell at least one person a month that they've lost/damaged their database beyond repair and beyond recovery)One other thing. Going forward you need to test all your backups. If you'd done test restores of this DB you'd have encountered the timeout error ages back, not in the middle of a crisis.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-27 : 14:36:21
|
| I still think backing up Files to tape is preferable to backing up directly to tape using a SQL Agent.The sorts of issues I have come across on this forum, over the years, is:A Service pack introduces a minor inconsistency such that SQL Agent backups are no longer restorableCannot restore database from tape. There is a similar tape drive on another machine, but no SQL Server installed there. (File restore would work there, then copy file across LAN back to original server)Disaster Recovery company can get files back off damaged tapes, but has issues with files that need to be recovered direct to SQL.having upgraded to new version of SQL we cannot restore old-version database backups anymore. (File backups could be restored, and upgraded - if necessary on a different machineNote that file backup gives you two bites of the cherry. Most times a restore will be from latest-backup. If that is still sitting on the local drive then you have that, PLUS the latest tape backup.I just couldn't live with that risk. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-27 : 15:02:08
|
| Tivoli isn't necessarily to tape, and the OP never stated the backups were to tape.--Gail ShawSQL Server MVP |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-09-28 : 02:13:20
|
| I totally agree with Kristen. Although Tivoli is capable of doing sql server backups, I go to great lengths to handle the db-backups myself and leave tivoli (or whichever other backup system is being used) to do backup the db backup files. The main reason for this is that if the sh¤% hits the fan I'll be the one at the other end of all those pointing fingers when we loose two weeks worth of data because the backup guy didn't have time to investigate his logs. Unless company policy forbids me I ALWAYS backup to local disk first, then move the backups off-server or to some backup device. But then again I rarely work with 200GB+ databases... (unfortunately)- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-28 : 03:31:34
|
| "Tivoli isn't necessarily to tape"Hadn't considered that Gail, but my point about using SQL Agents for backups stands. There are good, and well respected, brands, but in general I think its increasing the complexity of the process, and thus the risk of failure.I don't rely on being able to RightClick and Modify my Stored Procedures in SSMS either, I store them on the disk; I have recently found that a nested quote at the start of the Sproc code will prevent SSMS scripting it back to you, its just another example of hidden complexity biting me in the behind ... I try to steer a KISS course for all these types of scenarios rather than the possible convenience of Mr "Right Click" |
|
|
|
Taragor
Starting Member
46 Posts |
Posted - 2011-09-28 : 09:43:47
|
| Hi All,To start off the answer is yes, we do backup to tapes using the sql agent. Unfortunately that is not by my design. I will discuss what has been mentioned here with my manager and the IT dept and see if exceptions can be made. No matter what though, there is no way I could do the backup locally and then move it off (Not enough disk space and adding new hardware to the server at this time is not an option). That leaves me wondering how reliable is SQL at backing up over LAN? We usually have a fairly stable network infrastructure but it hickups at times and I'm concerned about that.And you are correct Gila about testing restores. Unfortunately we do not have a proper disaster recovery plan in place for some of our systems. Something tells me this may change soon. :)Tar |
|
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2011-09-29 : 11:24:29
|
| You can backup over a LAN, but ideally you'll backup the database locally and use something like RoboCopy to move them over the LAN to a network share.That way any latency etc in the LAN won't interfere with the creation of the actual backup files.Junior DBA learning the ropes |
|
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2011-09-29 : 11:27:54
|
| Although backing up a database around 200Gb could cause you some problems with the space needed.Junior DBA learning the ropes |
|
|
|
Next Page
|
Unable to restore / re-attach a db