Attach & Detach database

Posted on 2009-02-18
Last Modified: 2012-05-06

I had done some time before to reduce log file size in sql server; detached the database and rename the big log file and attached database using Enterprise Manager. It was created new log file without any issue.
In my current company we have three log files in one database (I could not understand why they created 3 log files) Now one of the transaction log file is big.  I did the same steps what I done before (rename all 3 log files), but I received the following error message.
Error 1813: Could not open new database. CREATE DATABASE is aborted.
Please find attached snap shot Transaction log setup and error message.
Is it possible can I make one log file instead of three log file?
Question by:pvcdm
    LVL 35

    Accepted Solution


    First off, in screenshot 2, there is a delete button for the extra files. I'd try deleting the small log files. This could take some time, so be patient.

    Then try the detach/attach

    But first make sure you have a known good current backup. On second thoughts, take one before you start.

    To reattch now, specify all data and log files. Given your current screen shots from the GUI, are you using the GUI for this, or a script?


    LVL 6

    Expert Comment

    Hello Pvcdm,

    Would you please try to create a new database with the same name and try to restore the existing database over the newly created one.


    Mohamed Allam
    Senior Solution Developer
    LVL 57

    Assisted Solution

    by:Raja Jegan R
    If you are going to maintain this new database, then you can convert those 3 log files into a single one and later on proceed with your task.

    To achieve that try the steps below:

    1. Take a full Backup of MAXIMO datase. (make sure it is without any errors by checking RESTORE VERIFYONLY command)
    2. Drop the existing database.
    3. Remove all the mdf and ldf files in the paths mentioned.
    4. Create a new database named MAXIMO again with single ldf file.
    5. Restore that backup to this new database.

    And after that the steps which you follow earlier will suffice.
    Hope this helps.

    Author Comment

    I got some infomration that if i take tranaction log backup, the log file size will be reduced.

    If i take it will be single log file. but i have three log file. please let me know i have to delete and restore log file to single .

    I am working in GUI only. not script.

    please give me some idea..(because database has lot of permission, so i want to do only in log file)


    Author Comment

    Please omit the above comments.

    I am working in GUI only, not in script.
    I got some information that if I take transaction log backup, the log file size will be reduced.
    If yes, it will be a single log file, but I have three log files. How can I restore?
    Please give me some idea.(Because database has lot of permission, I dont want to restore database; only I need to reduce log file size)

    LVL 57

    Expert Comment

    by:Raja Jegan R
    My post above indicates how to convert those three log files into a single file and then do as what you have did earlier..
    LVL 35

    Expert Comment

    by:David Todd

    A transaction log backup will not shrink the log file on its own!

    It make empty the log file; it may then be able to be shrunk; I hope auto-shrink itsn't on!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now