I want to remove an extra/unused ldf file from a SQL Server 2005 database
I've inherited a database that has 2 ldf filles.
The previous owner added the 2nd as a trial for some other task.
I know it is not needed but I can't remove it.
I am a beginner/intermeadiate user of SQL server.
The database has many tables and views so I can't recreate it with the 1 ldf easily.
I'v attached the prop sheet showing the files
Can some one help me understand how to remove it so it does not it come back?
escalatorldf.JPG
The previous owner added the 2nd as a trial for some other task.
I know it is not needed but I can't remove it.
I am a beginner/intermeadiate user of SQL server.
The database has many tables and views so I can't recreate it with the 1 ldf easily.
I'v attached the prop sheet showing the files
Can some one help me understand how to remove it so it does not it come back?
escalatorldf.JPG
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
1) Backup the log file http://msdn.microsoft.com/en-us/library/ms186865%28v=sql.90%29.aspx
2) Put the database in single user mode http://msdn.microsoft.com/
3) Use DBCC SHRINKFILE to migrate data of the ldf (use EMPTYFILE option) http://msdn.microsoft.com/en-us/library/ms189493.aspx
4) Use ALTER DATABASE with REMOVEFILE option to drop the file http://msdn.microsoft.com/en-us/library/ms174269%28v=sql.90%29.aspx
5) Use ALTER DATABASE with MULTI_USER option to set database back to multi user mode (or use the UI as per step 2) http://msdn.microsoft.com/en-us/library/ms174269%28v=sql.90%29.aspx
2) Put the database in single user mode http://msdn.microsoft.com/
3) Use DBCC SHRINKFILE to migrate data of the ldf (use EMPTYFILE option) http://msdn.microsoft.com/en-us/library/ms189493.aspx
4) Use ALTER DATABASE with REMOVEFILE option to drop the file http://msdn.microsoft.com/en-us/library/ms174269%28v=sql.90%29.aspx
5) Use ALTER DATABASE with MULTI_USER option to set database back to multi user mode (or use the UI as per step 2) http://msdn.microsoft.com/en-us/library/ms174269%28v=sql.90%29.aspx
You may need to put the DB into simple backup mode too...
ASKER
Is there a way to see what is in the 2 ldf files ?
In case there is a chance that I am wrong, I dont want to delete a file in use
In case there is a chance that I am wrong, I dont want to delete a file in use
I believe these are popular tools to explore the logs:
http://www.apexsql.com/sql_tools_log.aspx
http://www.red-gate.com/products/dba/sql-log-rescue/
However!... why? The log files are write ahead transaction log for the databases. If the log files are backed up and the backups properly managed then you can rollback this change by recreating the log file and restoring both the database and log files (to a particular point in time if your backup model supports this).
http://www.apexsql.com/sql_tools_log.aspx
http://www.red-gate.com/products/dba/sql-log-rescue/
However!... why? The log files are write ahead transaction log for the databases. If the log files are backed up and the backups properly managed then you can rollback this change by recreating the log file and restoring both the database and log files (to a particular point in time if your backup model supports this).
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Hi Scott,
Thanks so much for your suggestion, and the help to understand what is going on.
I did a back up of the DB and then a back up of the Log and ran the alter command as you suggested. I actually set it up in the wizzard, scripted the codes and ran them from the query window so I would I understand what was happening.
I went into the folder that the database is stored and the second ldf was gone :-) but
When I go into properties for the db the second log file still shows.
I tried with the wizard to 'Remove' but got the attached message.
Is there someplace that the db remembers and re-creates this file?
EscalatorFiles.JPG
Thanks so much for your suggestion, and the help to understand what is going on.
I did a back up of the DB and then a back up of the Log and ran the alter command as you suggested. I actually set it up in the wizzard, scripted the codes and ran them from the query window so I would I understand what was happening.
I went into the folder that the database is stored and the second ldf was gone :-) but
When I go into properties for the db the second log file still shows.
I tried with the wizard to 'Remove' but got the attached message.
Is there someplace that the db remembers and re-creates this file?
EscalatorFiles.JPG
No.
SQL gets the metadata (table details, file details, column details, etc.) you see from a cache area and from areas of memory, which sometimes run a little behind reality. SQL only periodically makes sure everything syncs up.
If the file is physically gone, SQL will internally eventually "realize" and catch up, and you won't see the file in SQL anymore. It's nothing to worry about.
SQL gets the metadata (table details, file details, column details, etc.) you see from a cache area and from areas of memory, which sometimes run a little behind reality. SQL only periodically makes sure everything syncs up.
If the file is physically gone, SQL will internally eventually "realize" and catch up, and you won't see the file in SQL anymore. It's nothing to worry about.
ASKER
Thank you again for your patience and assistance. Your suggestions were easy to understand and follow and I learned along the way.
ASKER
I did a full back up first.
How can I tell which log is being used and commit all transactions so I can run the above command??