Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2004
    Posts
    11

    Unanswered: SQL2000 log file issue

    On one of my servers, the log file mysteriously grew to 38Gb. It completely filled the HDD, so I created another log file, took the database offline, deleted the very large log file, and then renamed the new log file to be the same name as the one I deleted. When I try to bring the database back online, I get a message saying that the log file is not a primary log file, and the database stays offline. What can I do to get this database back online?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Tell me about your SQL Server recovery mode and your disaster recovery plan.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2004
    Posts
    11
    I can tell you that I am not very awesome with SQL server. The log file is gone permanently. This is on a test server (which is not backed up), but it is important that I am able to get all the data, table defs, and stored procedures back. I did do a backup of the database when it was initially setup in November, but it has changed quite a bit since then.

    Is there any way at all to make the database think that the log file is the primary log file so that I can bring it back online?

  4. #4
    Join Date
    Jun 2003
    Posts
    269
    1). first u detach ur db,
    Code:
    exec  sp_detach_db 'urdbname'
    --eg:EXEC sp_detach_db @dbname = 'pubs'
    2) now u delete the log file u created.(.ldf file)

    3) now attach ur .mdf file.it will create .ldf file automatically.
    Code:
     
    EXEC sp_attach_single_file_db @dbname = 'urdbname', 
     @physname = 'urMdfPathWithFilename'
    --eg: 
    --EXEC sp_attach_single_file_db @dbname = 'pubs', 
      -- @physname = 'c:\Program Files\MicrosoftSQLServer\MSSQL\Data\pubs.mdf'
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Apr 2004
    Posts
    11
    Quote Originally Posted by mallier
    1). first u detach ur db,
    Code:
    exec  sp_detach_db 'urdbname'
    --eg:EXEC sp_detach_db @dbname = 'pubs'
    2) now u delete the log file u created.(.ldf file)

    3) now attach ur .mdf file.it will create .ldf file automatically.
    Code:
     
    EXEC sp_attach_single_file_db @dbname = 'urdbname', 
     @physname = 'urMdfPathWithFilename'
    --eg: 
    --EXEC sp_attach_single_file_db @dbname = 'pubs', 
      -- @physname = 'c:\Program Files\MicrosoftSQLServer\MSSQL\Data\pubs.mdf'

    I just tried that but it is still looking for the old log files. I get an error saying that the create database command was aborted because the log files are missing...

  6. #6
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    use the below code and change to ur db name,path of log file,log file name before executing the code.
    Code:
    use master
    go
    EXEC sp_configure 'allow updates', 1
    RECONFIGURE WITH OVERRIDE
    GO
    BEGIN TRAN
    UPDATE master..sysdatabases
    SET status = status | 32768
    WHERE name = 'urDbName'
    IF @@ROWCOUNT = 1
    BEGIN
       COMMIT TRAN
       RAISERROR('emergency mode set', 0, 1)
    END
    ELSE
    BEGIN
       ROLLBACK
       RAISERROR('unable to set emergency mode', 16, 1)
    END
    GO
    EXEC sp_configure 'allow updates', 0
    RECONFIGURE WITH OVERRIDE
    GO
     
     
     
    -- Restart SQL Server at this point.
     
     
     
     
     
     
    DBCC REBUILD_LOG('urDbName','C:\Program Files\Microsoft SQL Server\MSSQL\data\urDbName_Log.LDF')
     
    /*Perform physical and logical integrity checks at this point.
    Bcp data out if your integrity checks demonstrate that problems exist.
    */
    ALTER DATABASE urDbName SET MULTI_USER
    GO
    -- Set database options and recovery model as desired.
    GO
    use urDbName
    Last edited by mallier; 01-30-06 at 10:54.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  7. #7
    Join Date
    Apr 2004
    Posts
    11
    I do not believe that this option will work because we detached the database in the last step, so when I try to rebuild the logfile, i get a message saying that it can't find my database in sysdatabases

  8. #8
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Quote Originally Posted by chosen_silver
    I do not believe that this option will work because we detached the database in the last step, so when I try to rebuild the logfile, i get a message saying that it can't find my database in sysdatabases
    shutdown sql server

    move the current database file or rename it

    restart sql server

    create a new database of the same name and log file and location as the old database and log file
    get rid of the old database.


    create a new database of the right size and shape with correct log and data file locations

    stop sql server

    rename the new databases.mdf or delete it if you don't have enough space - do not touch the .ldf

    move back in the old database .mdf file or rename it back again

    restart sql server

    now the database will show suspect.

    now u follow the steps i posted in previous post
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  9. #9
    Join Date
    Jun 2003
    Posts
    269

    Cool

    I tested again here with pubs.It worked without any flaw!
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  10. #10
    Join Date
    Apr 2004
    Posts
    11
    Perfect! That seems to have worked, but how do I bring the database out of emergency mode?

  11. #11
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Quote Originally Posted by chosen_silver
    Perfect! That seems to have worked, but how do I bring the database out of emergency mode?
    it wont be in emergency mode after executing the DBCC REBUILD_LOG.I think u have to refresh the window in SQl manager.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  12. #12
    Join Date
    Apr 2004
    Posts
    11
    You are my freakin' hero! It works.

  13. #13
    Join Date
    Jun 2003
    Posts
    269

    Cool

    Quote Originally Posted by chosen_silver
    You are my freakin' hero! It works.
    I have done on more than 10 db in a day when log files driver got failed
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •