We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Attach & Detach database

Medium Priority
711 Views
Last Modified: 2012-05-06
Hi,
I DONT HAVE MORE WORKING EXPERIENCE IN SQL SERVER

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?
Thanks
Ra
1.jpg
3a.jpg
3.jpg
4.jpg
Comment
Watch Question

Senior Database Administrator
CERTIFIED EXPERT
Commented:
Hi,

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?

HTH
  David

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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.

Regards,

Mohamed Allam
Senior Solution Developer
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
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

Commented:
Hi,
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)

thanks

Author

Commented:
Please omit the above comments.

Hi,
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)

thanks
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
My post above indicates how to convert those three log files into a single file and then do as what you have did earlier..
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

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!

HTH
  David
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.