SQL Server Performance Forum – Threads Archive

Database File management

Hi Experts,
I’ve a databse with one data file and three log file.[V]
I want the databse with just one transaction log file, what procedure should
i follow.

I’ve never had such a problem, but I found this and I think it makes sense. Generally, you should run DBCC SHRINKFILE with EMPTYFILE option to empty the log file (there can be no active transactions in this particular file) and then run ALTER DATABASE with REMOVE FILE. — Rediscover the web
http://www.mozilla.org/firefox/

Hi chopeen i will highly appreciate if u could give an example on alter database with remove file.
As i’ve never solved this kind of problem.

Check the location of log files and ensure those were added previously to address any free space issues on Transaction log. Check all the jobs and ensure the Tlog size is set to optimal size to avoid any shenanigans in future. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

if Tlogs are not placed on diff RAID groups to distribute I/O, you can remove them. Take transaction log backup to empty them
can use DBCC shrinkfile with empty file option, incase something is there in TLog
delete TL file, using alter db statement or directly from interprise manager. –set one Tlog size as per requirments, this depends on average usage/fillup on daily or your TL/complete backup policy. Deepak Kumar –An eye for an eye and everyone shall be blind

quote:Originally posted by ranjitjain Hi chopeen i will highly appreciate if u could give an example on alter database with remove file.
As i’ve never solved this kind of problem.

This only shows removing a log file (it must made empty first): CREATE DATABASE foo
ON
PRIMARY
(
NAME = foo_Data1,
FILENAME = ‘c: empfoo_Data1.mdf’
)
LOG ON
(
NAME = foo_Log1,
FILENAME = ‘c: empfoo_Log1.ldf’
),
(
NAME = foo_Log2,
FILENAME = ‘c: empfoo_Log2.ldf’
)
GO ALTER DATABASE foo REMOVE FILE foo_Log2
GO
— Rediscover the web
http://www.mozilla.org/firefox/

Hi Experts,
This issue is still pending.
My db has three log files-
vb_log d:….. 15354 MB
vb_1_log e:… 2864 MB
vb_2_log d:… 895 MB Do i need to dbcc shrinkfle with emptyfile one by one on each log file?
if the log2 gets empty then will by DB will work fine or can create issues.
After shrinking the log where those log events in 3 log file will go.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by ranjitjain Do i need to dbcc shrinkfle with emptyfile one by one on each log file? if the log2 gets empty then will by DB will work fine or can create issues. After shrinking the log where those log events in 3 log file will go.<hr height="1" noshade id="quote"></blockquote id="quote"> You should run DBCC SHRINKFILE for 2 out of your 3 log files. As long as you backup log2 before emtying it, your database will work fine. Before emptying the files you will backup them (won’t you? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]), so all the data (on condition that there are no active transaction in the particular log file) from the log files will be moved to the backup file. – Rediscover the web http://www.mozilla.org/firefox/</a>

Hi chopean,
i took the backup of transaction log3 and ran dbcc shrinkfile(4,emptyfile)
then i ran alter database to remove it and the 3rd log file got deleted.
The same procedure i ran for second log file.
While deleting the third file it displays the message as the log file
can not be removed because it is not empty.
What has gone wrong for second log file.

while shrinking database from Enterprises manager..select FILES and choose below Radio button first to empty TL file Empty the file (data will migrate to other files in the file group) First empty this file and then try to delete again. Deepak Kumar –An eye for an eye and everyone shall be blind

hi experts,
Thanx to all for solving and helping me here again.
I ran the command two more times for second log and took the backup.
After that i fired alter statement and done.
Thanx once again.

]]>