August 30, 2013 at 4:48 am
Hi All, Have a production database with two log files. The second ldf file is pointing to a wrong drive and we dont want to have a second ldf file. Hence need to delete the second log file.
Can any one please help me to achieve this?
August 30, 2013 at 5:55 am
1. Do a transaction log backup and truncate the log.
2. "Shrink the datafile by migrating the data to other files in the same filegroup" performed from within the file properties dialog box of the SHRINK FILE task.
3. From the FILES entry in the properties for the database, select the file you wish to remove and click "remove".
4. Finished.
August 30, 2013 at 6:20 am
No need to shrink, and shrinking will NOT have any effect.
Wait until the entire active portion of the log is in the file you want to keep (use DBCC LogInfo. Active VLFs are status 2, inactive are status 0, FileID maps to the fileID in sys.database_files)
Once that's the case you can just use ALTER DATABASE to drop the file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2013 at 6:21 am
kevaburg (8/30/2013)
2. "Shrink the datafile by migrating the data to other files in the same filegroup" performed from within the file properties dialog box of the SHRINK FILE task.
ShrinkFile with the EMPTYFILE option (which is what that dialog option is for) is only applicable for data files. It has no effect at all on log files. Nothing moves log records from one file to another.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2013 at 6:25 am
Surely by shrinking the file by moving its contents to another file would be a viable solution.
The backup with truncate would reduce its size but the proprtional fill algorithm would ensure boths datafiles have data in them. That is where the shrink would help before the file is finally removed.
Would your solution not overcome the issue of the contents of the log being distributred evenly across the file group?
August 30, 2013 at 6:25 am
GilaMonster (8/30/2013)
kevaburg (8/30/2013)
2. "Shrink the datafile by migrating the data to other files in the same filegroup" performed from within the file properties dialog box of the SHRINK FILE task.ShrinkFile with the EMPTYFILE option (which is what that dialog option is for) is only applicable for data files. It has no effect at all on log files. Nothing moves log records from one file to another.
I guess we wrote replies at the same time! Thanks for the clarification!
August 30, 2013 at 6:28 am
kevaburg (8/30/2013)
Surely by shrinking the file by moving its contents to another file would be a viable solution.The backup with truncate would reduce its size but the proprtional fill algorithm would ensure boths datafiles have data in them. That is where the shrink would help before the file is finally removed.
Would your solution not overcome the issue of the contents of the log being distributred evenly across the file group?
If we were talking about removing a data file, this would be correct.
Log files do not get proportional fill.
Log files are not part of a filegroup.
Shrink does not move log records from one file to another
Also, there's no need to truncate the log before dropping a second log file and explicitly truncating the log, breaking the log chain, preventing point in time restores is a really bad idea.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2013 at 6:31 am
GilaMonster (8/30/2013)
kevaburg (8/30/2013)
Surely by shrinking the file by moving its contents to another file would be a viable solution.The backup with truncate would reduce its size but the proprtional fill algorithm would ensure boths datafiles have data in them. That is where the shrink would help before the file is finally removed.
Would your solution not overcome the issue of the contents of the log being distributred evenly across the file group?
If we were talking about removing a data file, this would be correct.
Log files do not get proportional fill.
Log files are not part of a filegroup.
Shrink does not move log records from one file to another
Also, there's no need to truncate the log before dropping a second log file and explicitly truncating the log, breaking the log chain, preventing point in time restores is a really bad idea.
If log files don't get proportional fill then what would be the benefit of distributing them among multiple spindles?
August 30, 2013 at 6:33 am
kevaburg (8/30/2013)
If log files don't get proportional fill then what would be the benefit of distributing them among multiple spindles?
There is no benefit.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2013 at 6:38 am
There is no benefit.
Then I really must wonder why the ability to do it is there. A Microsoft-induced dream of illusional performance enhancement......
August 30, 2013 at 6:40 am
From Kimberley Tripp.....
3) Create only ONE transaction log file. Even though you can create multiple transaction log files, you only need one… SQL Server DOES not "stripe" across multiple transaction log files. Instead, SQL Server uses the transaction log files sequentially. While this might sound bad – it's not. If you want to get better performance out of the transaction log, place it on faster disks and/or a more performant disk (RAID) configuration. You could place the transaction log on a RAID 0 array but you'll likely want fault tolerance as well as performance. Isolation, as per number 1 is a good start but if you also need increased capacity then using hardware based RAID to combine disks for both better performance and redundancy is your next choice. See 4.
http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
August 30, 2013 at 6:42 am
kevaburg (8/30/2013)
There is no benefit.
Then I really must wonder why the ability to do it is there.
Many reasons. Performance is not one of them though
What happens when, on the huge scale, you need more than 2TB of log space?
What happens when the drive with the log fills up and you need a bit more log space to fix the problem?
A Microsoft-induced dream of illusional performance enhancement......
No. Nothing whatsoever to do with performance. No one who knows how logs work will ever recommend multiple log files for performance reasons
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2013 at 6:45 am
Back to my books.....
August 30, 2013 at 7:39 am
Thanks a ton for the help. I successfully deleted the log file
I followed the below steps:
1) Bcak up the T-log
2) Shrink the log file
3) Made sure status = 0 in dbcc log info
4) Used alter statement and removed the log file
August 30, 2013 at 7:42 am
You did not need to shrink, it would not have had any effect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply