Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-04-15 : 07:41:58
|
| Jay Patel writes "During an exercise to try and "reinitialise" a primary ( only ) log file for a database ( by detaching the db, delete the log and then re-attach ) I would normally expect SQL server to re-created the log file for me.However, during a recent exercise, when I attempted to attach I recieved the following error :-Error 1813:Could not open new database 'RealsecureDB'. CREATE DATABASE is aborted.Device activation error. The physical file name 'E:\Program Files\ISS\RealSecure SiteProtector\Site Database\Data\RealSecureDBLog.ldf' may be incorrect.Now I believed that the attach process would look at the location of the log file as read from the primary datafile, if not found it would re-reate this file - not at that location but from my experience the location of the primary data file. In this case, I can't see why SQL server is expecting to have the original log - is it because of what I fear - that an some transaction state requires resolution can only be done so from the log at time of crash?.would be grateful for any insight - the MSN Knowledge Base has no information on this regds Jay " |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-04-15 : 09:52:25
|
| Try Using sp_attach_single_file_db. It belive it will solve your problem.Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
 |
|
|
jaypatel
Starting Member
10 Posts |
Posted - 2003-04-17 : 04:14:46
|
| Unfortunately the same error is recieved with the single file function.It smells of another MS unkown. It looks like if there is some entry that is unresolved and MS needs to have the transaction log in place to resolve it - it can be the only logical explanation to me.If it is - its a spanner in the works - because this transaction log according to the DBA was so large ( 16GB ) that they had to delete it after the detachment because they had no physical space elsewhere to put it. They read the documentation and simply it states that once detached, if the log file is physically removed, re-attaching will create the neccessary log file if not found. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-17 : 04:53:56
|
| It should work but as always it can fail.This is why you always take a backup before this sort of operation.I would suggest restoring the backup and trying again.Might be worth running a manual checkpoint before the dettach.Not sure where it tries to create the log as haven't tested it - but it will pick up and try to process any log of that name that already exists.==========================================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. |
|
|
|
jaypatel
Starting Member
10 Posts |
Posted - 2003-04-17 : 09:48:53
|
| A backup was taken but this methods was used to try and resolve the issue of the large transaction log. It still doesn't answer the question that when a db_detach_db is issued - what state the db file and logs are in - this tells me that there is some discrepancy somewhere. |
|
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-04-18 : 03:40:03
|
| Ok. Try This..CREATE DATABASE dbname ON PRIMARY(FILE='E:\Program Files\SQL\DATA\FILE.MDF')FOR ATTACHNOTE: The above will not create a new log file if you had more than one ldf files before detaching.Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
|
|
|
jaypatel
Starting Member
10 Posts |
Posted - 2003-04-22 : 05:28:16
|
| thanks - but even this method still produces the same error. I will raise this MS through a call to see what light they can shed - but I think I may get fobbed of with some other info. |
|
|
|
|
|
|
Truncation of the Transaction Log and SP_ATTACH_DB