Re: LOG Device Corrupted

From: Henry (remove_henrychoi_at_mail.hongkong.com)
Date: 04/24/04 

Date: Sat, 24 Apr 2004 10:17:15 +0800

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
>
>

Relevant Pages

  • Re: sp_attach_single_file_db
    ... Being that Rob has access to the database and can still ... Stop sql server and rename the existing MDF to a new one and copy the ... Now execute the undocumented DBCC to create a log file ...
    (microsoft.public.sqlserver.server)
  • Re: HELP on How to move database files
    ... http://www.support.microsoft.com/?id=314546 Moving DB's between Servers ... Issues When a Database Is Moved Between SQL Servers ... for SQL Server ... > What I want to do is to move the transaction log file to a different> location or even better get rid of it and create a new log file in its new ...
    (microsoft.public.sqlserver.server)
  • Re: SPS - SQL Server - LOG FILES.
    ... Detach the database _site. ... move the log file to another drive. ... >> We Deleted these .LDF files after stopping the SQL Server ON TEST ...
    (microsoft.public.sharepoint.portalserver)
  • Re: sp_attach_single_file_db
    ... Anyone worked a bit with SQL Server know that SQL Server does recovery at startup. ... SQL Server go through the transaction log and "synchronizes" the database with what happened since ... Stop sql server and rename the existing MDF to a new one and copy the ... Now execute the undocumented DBCC to create a log file ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction Log suddenly became huge and database stopped working
    ... In addition to the other posts: to understand why it was difficult for you to shrink the log, you need to understand the concept of a virtual log file and also investigate the virtual log file structure in the ... We have an SQL Server 2005 on a windows 2003 webserver ... The database is set to autoshrink and also is backed up nightly. ... Microsoft Analysis Services Client Tools 2005.090.3042.00 ...
    (microsoft.public.sqlserver.server)