• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • 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 & ArchitectCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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 & ArchitectCommented:
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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