September 21, 2010 at 5:49 am
Hi all
I was hoping for some advice if possible, basically we have a fairly large database that originally consisted of one data file and one log file, during this time all was well but at some point in time another 2 log files were added - one of which was deleted from the filesystem (and not in SQL Server - no it wasen't by me), now due to this the systables has marked the log file as DEFUNCT in the configuration, but the entry still exists in the database properties.
This is causing massive problems because:
1. We can now no longer back up the database because the error: "An error occurred while processing 'BackupMetadata' metadata for database id xx file xx (which is this specific transaction log file)
2. We can't copy it
3. You cannot remove the offending log file from the database properties // Files because it claims it does not exist
4. We are worried about re-starting SQL Server in case this important database will not come back online (without a valid backup for some time)
Can anyone offer the best solution to this? is there any way to copy the database files (which are in-use live) to another server so that we may attempt to fix the missing log file issue?
Just to point out that at this time without a SQL restart the database "appears" to be functioning normally
Thanks all - any help would be much appreciated 🙂
September 21, 2010 at 6:15 am
First things first.
Do not restart SQL!
Do not detach the database!
Do not reboot the server!
Do any of those and it's likely that the DB will come back recovery pending at best.
Is it online and usable?
When will it be possible for you to arrange a downtime window to try and fix 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
September 21, 2010 at 6:22 am
Hi Gail
Thanks for your reply
Yes at present all is well and the database is working normally
We would probably have to do this out of hours but this is now urgent due to the lack of backups, so anytiime in the evenings really
What would be best is we could at least get a copy of it somehow before starting to attempt to fix the issue, just worried about data loss
Thanks again
September 21, 2010 at 6:35 am
Here's the bad news. There's no way to get a copy (short of bcp OUT) without taking the DB offline. Take it offline and you may not get it back online.
Can you run a checkDB in the meantime. Want to be sure there's nothing more than the missing log wrong.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
If you're worried about the data, see if you can export to flat files. Not pretty, but it's about the only way at the moment.
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
September 21, 2010 at 7:45 am
Boo I thought as much (copy), I was wondering if there was a way to perform a kind of shadow copy (i.e. whereby it ignores the fact that the files are in use // locked, maybe a backupexec open file backup? but there is no way to assume that that is going to produce a non-corrupted database anyway I guess)
Ok I ran the checkdb command and it didn't come back with any errors
September 21, 2010 at 7:51 am
Martonius333 (9/21/2010)
was wondering if there was a way to perform a kind of shadow copy (i.e. whereby it ignores the fact that the files are in use // locked, maybe a backupexec open file backup?
You can, but I'll near guarantee you that files copied in that manner will be useless.
Please run this and post any errors:
checkpoint
Any further errors in the SQL: error log, from the backup or from when SQL started? Anything relating to this DB?
What's the recovery model of this DB? (Assuming Simple)
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
September 21, 2010 at 8:01 am
No problems - I guess only way then would be via "Generate a very big script" and export all the objects // data etc?
Ok ran "checkpoint" and this did not generate any errors
Recovery model is currently set to simple and the error when I try and run a full backup is:
System.Data.SqlClient.SqlError: An error occurred while processing 'BackupMetadata' metadata for database id 19 file id 4
If I select this out of sys.database_files, it gives me the offending LDF file:
state: 7
state_desc: DEFUNCT
size: 1
Thanks for your help 🙂
September 21, 2010 at 8:11 am
Martonius333 (9/21/2010)
No problems - I guess only way then would be via "Generate a very big script" and export all the objects // data etc?
For data I recommend bcp out.
Can you check the error log as I asked please.
I have an idea, but I want to test locally before I tell you what to do, and I don't know when I'll get a chance to do that properly (got a usergroup meeting in an hour)
I suspect the export will take a while.
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
September 22, 2010 at 10:17 am
Hi Gail
Apologies for the delay in replying, I meant to reply yesterday straight away but had to go out of office (crazy when this is critical!!)
Here are the errors from application log and SQL error logs:
---
AppDomain 516 (MyDatabase.dbo[runtime].524) is marked for unload due to memory pressure.
An error occurred while executing the following query: "BACKUP DATABASE [MyDatabase] TO virtual_device = 'MyDatabase_00__4d89669d_4aa3_474d_a160_bcaae6d9eff8_' WITH name = 'Backup Exec SQL Server Agent', DIFFERENTIAL".
On server: "MyServerName".
SQL error number: "0E34".
SQL error message: "An error occurred while processing 'BackupMetadata' metadata for database id 19 file id 4.
".
For more information, click the following link:
http://eventlookup.veritas.com/eventlookup/EventLookup.jhtml
SQLVDI: Loc=CVDS::Close. Desc=Abnormal termination state. ErrorCode=(0). Process=10148. Thread=7780. Client. Instance=. VD=Global\MyDatabase_00__4d89669d_4aa3_474d_a160_bcaae6d9eff8__SQLVDIMemoryName_0.
SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=10148. Thread=7780. Client. Instance=. VD=Global\MyDatabase_00__4d89669d_4aa3_474d_a160_bcaae6d9eff8__SQLVDIMemoryName_0.
---
I have created a second database using Generate Script from the broken database with all tables, stored procs, views etc
I now need to copy all the data from the broken database from each of the 17 tables, is there a way to script BCP to trawl through each table and output the data into the second database AND include original identities etc? or do I need to do this using a SELECT ... INSERT?
Thanks
September 22, 2010 at 5:41 pm
To copy the data, you might want to try SQL Data Compare. You can download a fully functional trial edition from Redgate.
When you perform the comparison, it will generate the synchronization scripts for you.
However, if you have tables that do not have primary keys, or a set of rows that define a unique row - you will have to manually define those to perform the comparison.
You can also download SQL Compare - and use that to make sure the new database has all objects, permissions, logins, users, etc... This tool will compare the schemas between the two databases and identify where they are not in synch.
If those don't work - then you can fall back on using BCP out. I believe you can set an option on BULK INSERT to include identity columns. Not sure though - so look it up in books online. I do know this option is available in SSIS when you use the fast table load option.
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
September 27, 2010 at 4:58 am
Thanks Jeffrey that is very helpful, I am going to try that product and see if it helps
In the mean time will check out the bcp syntax as I have not used that before (I am not an out and out SQL DBA, I know SQL but this has kind of been dropped on me)
Thanks guys
September 27, 2010 at 5:43 am
Apologies, I haven't had chance to try and repo this situation to see how (if) it can be solved. Are you OK with recreating the DB?
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
September 27, 2010 at 9:34 am
Hi Gail
No problems - I was really busy too last week, life in the I.T fast lane hey! 🙂
I have recreated the database (empty) just trying to figure out the best method to copy the data over keeping the identities intact, a straight db cpoy fails due to metadata (missing log file) is there any chance you wouldn't mind giving me an example of using bcp to do this?
There are 17 tables, so quite a lot of data
Again guys, I really appreciate your help
September 27, 2010 at 10:16 am
Ok just let you know I've managed to shadow copy the MDF file, now going to attach it on a DEV server to make sure it creates the transaction logs etc automatically.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply