corrupt mdf and ldf file
-
In a weird situation
I have a SQL 2000 database that had SUSPECT beside it. In reading on the net, found article that said to detach and re-attach the database. It detached okay, but now when I try to attach it, it keeps telling me the log file is corrupt. I have tried everything I know to attach the mdf file without the ldf file and it keeps asking for the ldf file that is corrupt. I want to attach the mdf file without the ldf file so that it will create a new ldf file for me. Unfortunately we do not have a backup of this database. Is there a way I can force it to attach the old mdf and have it create a new ldf file? I have tried creating a new database, dropping it and then using the new ldf file attach the old mdf to it. It tells me that I can't use the ldf file from another database. It appears to me that the mdf file is corrupt also, but at this point I am over my head and not quite sure on what to do next. We do not have a backup and if I can recover it, it would save us some time having to recreate it.
Any help would be greatly appreciated
Thanks
-
Terry,
Assuming you are using SQL Server 2000 you can use the system stored procedure sp_attach_single_file_db, check BOL for documentation, but here is an example:
EXEC sp_detach_db @dbname = 'pubs'
EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
This stored procedure will attach the data file you have and create a new transaction log file. Be aware that doing this you will lose any transactions that were not commited from the corrupt log before corruption/detaching. In the future endeavour to backup the tale of the transaction log before performing any admin tasks such as this as you would hopefully be able to recover any lost transactions from this backup after successfully re-attaching the data file.
Hope this helps. One other thing is the database running under Simple, Full or Bulk logged recovery model?
-
Hi!!
Just leave all the things..go to EM there u need to go to all task then attach db there u will ask for mdf file location...with out ldf file u get message for creating new ldf file if u say ok then it will attach ur server with that DB...atleast u get ur data..you just lose the transaction log file...
Regards,
Papillon
-
Tried both replies with same error number as result
Both keep wanting the log file and I never get the prompt to create the new log file from EM. At this point losing the log file will be much less painful than losing the database.
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'SUSDB'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'E:\WSUS\SUSDB_log.LDF' may be incorrect.
Thanks for any help you can provide!
-
dunno if this is it, but i've seen a simialr situation:
Device activation error. The physical file name 'E:\WSUS\SUSDB_log.LDF' may be incorrect.
you would get this error if:
the folder path to create the file is incorrect (no E: drive? no \SUSDB folder already exists on E
ora file actually already exists with that same name in that folder: SUSDB_log.LDF or
that file was read only
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
lowell has a very good point, move/archive (on another physcial drive or folder) any other log or data files you have related to the original database (including the corrupt log file but exluding the data file you are trying to attach), then try running sp_attach_single_file_db, this will create a new log fiel with the same name as the existing (corrupt) database log file.
ll
-
Moved the log file to a different drive. Created a new folder for the mdf file and put it in there
Ran the attach single file SP and got this error
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'e:\wus\susdb.mdf' may be incorrect.
Does this mean the mdb file is also corrupt, also is there a way to fix a corrupted mdb file?
Thanks again for the replies!
-
Moved mdb file to a different sql2000 test server. Ran the attach single file SP on this server and got the same error shown below as before on the other SQL 2000 server where the mdf file was originally.
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'SUSDB'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'E:\WSUS\SUSDB_log.LDF' may be incorrect.
The mdb file is now located on the new server at D:\wsus\susdb.mdf
It seems that the mdb file just will not create a new log file or forget about the old ldf file.
-
terry,
have you changed the physical filenename of the data or log files? can you post the query you are running to try and attach the single file.
thanks,
ll
-
Have not changed the mdf file name, only the drive and or folder they were located in. In the last try on the test SQL server moved it to the drive on the SQL test server, but left the name the same.
On the test server here is the query I am running that still gives me the Error 1813
EXEC sp_attach_single_file_db @dbname = 'SUSDB', @physname = 'd:\wsus\susdb.mdf'
On the original server here is the query that gave me the 5105 message
EXEC sp_attach_single_file_db @dbname = 'SUSDB', @physname = 'e:\wus\susdb.mdf'
Original location and names were as follows
mdf was e:\wsus\susdb.mdf
ldf was e:\wsus\susdb_log.ldf
-
right terry,
first of all, did this database originally have multiple log files? see here: http://support.microsoft.com/kb/q271223/ if it did, there's not a lot we can do.
what process did you originally use to detach the database when it was marked suspect?
i notice that this is a MS WSUS database you are trying to attach, i have checked our install of WSUS and confirmed that data and log filenames and database name are correct.
in my experience 'sp_attach_single_file_db' has never failed with error 5105 unless there is a typo in the filename when attaching, i've just spotted that you have a typo in your command for trying to attach to the original server: @physname = 'e:\wus\susdb.mdf', should this not be @physname = 'e:\wsus\susdb.mdf'?
ll
-
I had this similar problem, I crash a DB trying to restore a backup to it but got stuck in the middle of the process, I try the same detach attach procedure and had the same problem, tried the 'sp_attach_single_file_db' (having single mdf and ldf also) so SQL regenerates the new transaction log and it didn't work, to me apparently the state of corruption of a db is handled different than when the log file is missing and can be recreated, looks like inside SQL something is missing because it looks like it grabs the information in sysfiles of the db being recreated and tries to find it instead of giving the instruction of being regenerated(because I also tried recreating it in another server and had same error).
I can only suggest to try to restore this in SQL 2005 to se if this has been fixed.
-
Make sure you have enough space on your drive to create a new .ldf file then proceed. When I use 'sp_attach_single_file_db', I detach the database (EXEC sp_detach_db @dbname = 'pubs') using SQL analyzer (you have to use SQL Analyzer, or this will not work correctly. Reference books online), then I leave my .mdf file in the same directory I had it in when I started. Just renamed the .ldf file to something like Log_old.ldf, then run the 'sp_attach_single_file_db' (EXEC sp_attach_single_file_db @dbname = 'pubs', @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'). This should work correctly unless you have something misspelled like others have said!!
Good Luck!!
PS: don't forget to delete the old .ldf file once your database is working properly.
-
No typos, there is enough drive space and I get same error on 2 different sql servers
SP attach single file simply fails everytime with the 1813 msg
So going to a different tack,
Someone suggested a restore, can I restore a database from the original mdf file?
Also I put the corrupt log file back and ran a normal attach in query analyzer with this command
Exec sp_attach_db 'SUSDB', 'e:\wsus\susdb.mdf', 'e:\wsus\susdb_log.ldf'
with this result
Server: Msg 3624, Level 20, State 1, Line 1
261 transactions rolled forward in database 'SUSDB' (8).
Location: page.cpp:3453
Expression: m_freeCnt + emptySlots * sizeof (Slot) >= spaceNeed
SPID: 84
Process ID: 1916
Connection Broken
On the original server I have over 9 GB available. The database file is around 2 GB and the corrupted log file is around 2 GB.
Is there a way around this problem?
I appreciate all the replies!
-
terry,
there is no way to do a restore from the original MDF files.
i assumed you had already tried the standard attach in your first post 'Exec sp_attach_db 'SUSDB', 'e:\wsus\susdb.mdf', 'e:\wsus\susdb_log.ldf''
it could be that now transactions have been rolled forward a single file database attach may work, worth a try?
i have never seen the error 3624, so suggest a good search on google and sqlservercentral.
worth checking out this previous post as well: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=240991&p=2
let us know how you get on,
ll
p.s. can you confirm the questions i asked previously including the ones about how you dettached?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply