• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 636
  • Last Modified:

Attach & Detach database

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
0
pvcdm
Asked:
pvcdm
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
David ToddSenior DBACommented:
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

0
 
AllamzCommented:
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
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pvcdmAuthor 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
0
 
pvcdmAuthor 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
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
My post above indicates how to convert those three log files into a single file and then do as what you have did earlier..
0
 
David ToddSenior DBACommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now