asked on
Remove extra transaction log files from sql server 2000 database
Hi Experts,
I am working with SQL server 2000 and have a database that has multiple transaction log file.
I need to remove the extra transaction log files and keep only one. Attached is the code that I am planning to execute. I am NOT A DBA but have this responsibility to perform this task.
Attached are also screenshots of maintenance plan and transaction log properties.
Please advice if the steps I am about to take are correct or not and any other steps should I be taking or not. I have seen a similar question posted on EE, but I am just nervous since this is the production server and I can not afford to loose any data.
Please advise ASAP since my transaction log is filling up the drive and I must clean up ASAP.
Thank you in advance.
I am working with SQL server 2000 and have a database that has multiple transaction log file.
I need to remove the extra transaction log files and keep only one. Attached is the code that I am planning to execute. I am NOT A DBA but have this responsibility to perform this task.
Attached are also screenshots of maintenance plan and transaction log properties.
Please advice if the steps I am about to take are correct or not and any other steps should I be taking or not. I have seen a similar question posted on EE, but I am just nervous since this is the production server and I can not afford to loose any data.
Please advise ASAP since my transaction log is filling up the drive and I must clean up ASAP.
Thank you in advance.
DBCC SHRINKFILE(ResQCX32_2_log, 100)
BACKUP log ResQCX32 WITH TRUNCATE_ONLY
DBCC SHRINKFILE(ResQCX32_2_log, 100)
DBCC SHRINKFILE (ResQCX32_2_log, EMPTYFILE )
ALTER DATABASE ResQCX32 REMOVE FILE ResQCX32_2_log
Microsoft SQL Server
Last Comment
Dimitris
my suggestion is this
Backup your database
Change the database recovery model to simple (properties menu)
Delete from the properties menu again on the transaction log tab the second file
change you database recovery model back to full
that's all
Backup your database
Change the database recovery model to simple (properties menu)
Delete from the properties menu again on the transaction log tab the second file
change you database recovery model back to full
that's all
ASKER
So I don't have to shrink the logfile? or execute any of the sql commands I mentioned above?
If you have a full backup of your database then there is no need to try to shrink it.
Also by changing the recovery model to simple will do an auto-truncate to the transaction log.
If you perform the actions I describe above from the GUI then there is no need to execute any SQL statement.
Also by changing the recovery model to simple will do an auto-truncate to the transaction log.
If you perform the actions I describe above from the GUI then there is no need to execute any SQL statement.
Dont ever plan to shrink a log file which will remove all the index statistics and file allocation was affected.
Instead go for periodical Transactional Log backup which will reduce your Log File Size along with maintaining your indexes and file system managed properly.
Instead go for periodical Transactional Log backup which will reduce your Log File Size along with maintaining your indexes and file system managed properly.
ASKER
Now i am really confused. What are my steps? can some one guide my step by step thru this process please.
All i want to do is remove extra log files and keep only one.
All i want to do is remove extra log files and keep only one.
After you take a Full backup of your database, do the things below;
1. Delete your Existing Database.
2. Create a new database with only one Transactional Log file.
3. Now restore that backup into this new Database.
Or have an alternative approach from dankangr.
1. Take a Full Database backup
2. Go to SSMS --> Right Click urdb, Choose Files and Delete the Additional Log Files, That's it.
That's it
1. Delete your Existing Database.
2. Create a new database with only one Transactional Log file.
3. Now restore that backup into this new Database.
Or have an alternative approach from dankangr.
1. Take a Full Database backup
2. Go to SSMS --> Right Click urdb, Choose Files and Delete the Additional Log Files, That's it.
That's it
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I was able to delete the extra log files with this solution.
ASKER
I still have two log files. When i tried to delete the log file in directory E;\Microsoft swl server\MSSQL\Data\mydb.ldf
Remove the other log file not the primary and change the path of that log,
if you can't change the path then de-attach the database , move the primary log to D and re-attach the database and point the LOG file on D
if you can't change the path then de-attach the database , move the primary log to D and re-attach the database and point the LOG file on D
ASKER
Forgive me for my ignorence, but how do i de-attach the database?
Right Click on database, tasks, De-attach
and to attach
Right Click On the Databases, Attach
and to attach
Right Click On the Databases, Attach
ASKER
Thank you very much!
u r welcome
1. Take a complete backup of your Database.
2. Now Delete your Existing Database.
3. Create a new database with only one Transactional Log file.
4. Now restore that backup into this new Database.
After that reconfigure your Maintenance plans and Transactional logs accordingly for the single Log file.
It should not be an issue.