LOG Device Corrupted

Hi,
I found my database (Log Device) corrupted, and I used the command
sp_attach_single_file_db to attach the DB device to new sql server. But the
server responed error:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database XXXX, CREATE DATABASE is aborted.
Device activation error. THe physical file name XXXX.LDF may be incorrect.
I tested use another DB to detach and delete the log file, than attach it
again. It is work and the new log file will be created.
What is the problem of my DB device? Is it corrupted as well. Any other
solution to attach the DB device to SQL server?
Please help!!!
Thanks,
HenryHi,
It seems your old database have mutiple LDF files. If you have mutilple LDF
files missed out you will not be able to use the procedure
sp_attach_single_file_db to attach the MDF file alone.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILDLOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Henry" <remove_henrychoi@.mail.hongkong.com> wrote in message
news:eEjj3xUKEHA.644@.tk2msftngp13.phx.gbl...
> Hi,
> I found my database (Log Device) corrupted, and I used the command
> sp_attach_single_file_db to attach the DB device to new sql server. But
the
> server responed error:
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database XXXX, CREATE DATABASE is aborted.
> Device activation error. THe physical file name XXXX.LDF may be incorrect.
> I tested use another DB to detach and delete the log file, than attach it
> again. It is work and the new log file will be created.
> What is the problem of my DB device? Is it corrupted as well. Any other
> solution to attach the DB device to SQL server?
> Please help!!!
> Thanks,
> Henry
>|||Hi Hari,
Thanks but in step 7, system prompted error as:
Server: Msg 2526, Level 16, State 2, Line 1
Incorrect DBCC statement. Check the documentation for the correct DBCC
syntax and options.
I tried in SQL7 ans SQL2000, the result is same. Any setting I've to change
to use this command?
Thanks,
Henry
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGLeRvVKEHA.556@.TK2MSFTNGP10.phx.gbl...
Hi,
It seems your old database have mutiple LDF files. If you have mutilple LDF
files missed out you will not be able to use the procedure
sp_attach_single_file_db to attach the MDF file alone.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILDLOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Henry" <remove_henrychoi@.mail.hongkong.com> wrote in message
news:eEjj3xUKEHA.644@.tk2msftngp13.phx.gbl...
> Hi,
> I found my database (Log Device) corrupted, and I used the command
> sp_attach_single_file_db to attach the DB device to new sql server. But
the
> server responed error:
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database XXXX, CREATE DATABASE is aborted.
> Device activation error. THe physical file name XXXX.LDF may be incorrect.
> I tested use another DB to detach and delete the log file, than attach it
> again. It is work and the new log file will be created.
> What is the problem of my DB device? Is it corrupted as well. Any other
> solution to attach the DB device to SQL server?
> Please help!!!
> Thanks,
> Henry
>|||Hi Henry,
I missed an underscore in the syntax, Use the bleow syntax and contine with
step 8:-
DBCC REBUILD_LOG('dbname','c:\dbname.ldf')
Note:
Replace the dbname and log file name based on ur requirement.
Thanks
Hari
MCDBA
"Henry" <remove_henrychoi@.mail.hongkong.com> wrote in message
news:e81qTJaKEHA.2244@.tk2msftngp13.phx.gbl...
> Hi Hari,
> Thanks but in step 7, system prompted error as:
> Server: Msg 2526, Level 16, State 2, Line 1
> Incorrect DBCC statement. Check the documentation for the correct DBCC
> syntax and options.
> I tried in SQL7 ans SQL2000, the result is same. Any setting I've to
change
> to use this command?
> Thanks,
> Henry
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGLeRvVKEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> It seems your old database have mutiple LDF files. If you have mutilple
LDF
> files missed out you will not be able to use the procedure
> sp_attach_single_file_db to attach the MDF file alone.
> A solution for this is:
> 1. Create a new database with the same name and same MDF and LDF files
> 2. Stop sql server and rename the existing MDF to a new one and copy the
> original MDF to this location and delete the LDF files.
> 3. STart SQL Server
> 4. Now your database will be marked suspect
> 5. Update the sysdatabases to update to Emergency mode. This will not use
> LOG files
> update sysdatabases set status=32768 where name ='dbname'
> 6. Restart sql server. now the database will be in emergency mode
> 7. Now execute the undocumented DBCC to create a log file
> DBCC REBUILDLOG(dbname,'c:\dbname.ldf')
> 8. Execute sp_resetstatus <dbname>
> 9. Restart SQL server and see the database is online.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "Henry" <remove_henrychoi@.mail.hongkong.com> wrote in message
> news:eEjj3xUKEHA.644@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I found my database (Log Device) corrupted, and I used the command
> > sp_attach_single_file_db to attach the DB device to new sql server. But
> the
> > server responed error:
> >
> > Server: Msg 1813, Level 16, State 2, Line 1
> > Could not open new database XXXX, CREATE DATABASE is aborted.
> > Device activation error. THe physical file name XXXX.LDF may be
incorrect.
> >
> > I tested use another DB to detach and delete the log file, than attach
it
> > again. It is work and the new log file will be created.
> > What is the problem of my DB device? Is it corrupted as well. Any other
> > solution to attach the DB device to SQL server?
> >
> > Please help!!!
> >
> > Thanks,
> > Henry
> >
> >
>
>