Post reply

Attached Database Without LDF

  • February 26, 2009 at 6:02 am

    #128093

    Hi ,

    I am Having Sql Server SP4 pak.

    I wanted to Attached database without LDF file.

    B’coz my LDF size is greater than the MDF

    Thanks and Regards,

    Pravin

  • February 26, 2009 at 6:13 am

    #950537

    have a look at this SP

    SP_ATTACH_SINGLE_FILE_DB

    BOL Ref - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/13bd1044-9497-4293-8390-1f12e6b8e952.htm

  • February 26, 2009 at 6:16 am

    #950539

    You can attach the .mdf file without the .ldf file. When you go to attach it, the interface may say log file not found in the lower pane. Just remove the log file and it will automagically create a new one.

    Greg

  • February 26, 2009 at 6:42 am

    #950555

    aks_pra (2/26/2009)

    Hi ,

    I am Having Sql Server SP4 pak.

    I wanted to Attached database without LDF file.

    B’coz my LDF size is greater than the MDF

    Thanks and Regards,

    Pravin

    If your database is in full recovery mode, you should be doing transaction log backups at some regular interval to keep the log size to a minimum. Are you sure the log is full or is there a lot of unused space? If not you may be able to shrink the file using DBCC SHRINKFILE to a more manageable size.

    I run this in SQL2000 and it shows me the space used and available space for all files within the database (it does run in SQL2005 as well).

    SELECT CAST(size/128.0 as DECIMAL(10,2)) AS Size_in_MB

    , CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 as DECIMAL(10,2)) as Space_Used

    , CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS Available_Space

    , getdate() as RunDate

    from SYSFILES

    Edit - If you do see a lot of available space, see the DBCC Shrinkfile in BOL.

    -- You can't be late until you show up.

  • February 26, 2009 at 9:16 am

    #950668

    aks_pra (2/26/2009)

    I wanted to Attached database without LDF file.

    B’coz my LDF size is greater than the MDF

    That is a very, very bad way of handling a large transaction log. Deleting the log could cause the DB to reattach suspect, or not at all.

    Please read through this for info on how to manage transaction logs properly - Managing Transaction Logs[/url]

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 27, 2009 at 9:38 am

    #951262

    I had to do this last week. 100 gig log file and a 4 gig database.

    First I ran checkdb for errors.

    second I made a backup

    third I detached the database

    fourth I moved the log

    Last I ran the following command

    Notice the last line...not GO 🙂

    USE [master]

    GO

    CREATE DATABASE [goldmine] ON

    ( FILENAME = 'D:\Data\MSSQL\Data\goldmine_data.mdf' )

    FOR ATTACH_REBUILD_LOG

    GO

    sat back and waited for the complaints....still waiting.

  • February 27, 2009 at 10:54 am

    #951299

    Did you determine why the log got so big? 25Xdata is not normal. I'm guessing you have the database in full recovery but never backup the transaction log???

    -- You can't be late until you show up.

  • February 27, 2009 at 11:09 am

    #951304

    Not really, my guess is that it had something to do with corruption, had to run checkdb with data loss...Lucky for me, I didn't loose too much data it affected a table that generally wasn't utilized and we are in the middle of a migrating to a new product that won't use that table. whew!! I tried everthing to shink that log. change the recovery model to simple, backup truncate_only, shinkfile, shrinkdatabase...I even threw it into a pool with VERY cold water......no shrinkage.... that thing was the Dirk Diggler of logs. I finally gave up and killed it with extreme prejudice. 😎

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply