Second Transaction Log File

[laststubborn]

Hi All,

I have a dillema that I created 2 transaction file before I started to
take log back. Now I changed my recovery model from simple to full. Do
I still need to keep my second transaction file? If I remove it would
it be a problem for my system and log back up?

If somebody responds me I really appreciate it

Regards

[Erland Sommarskog]

Re: Second Transaction Log File

laststubborn (arafatsalih@gm ail.com) writes:

I have a dillema that I created 2 transaction file before I started to
take log back. Now I changed my recovery model from simple to full. Do
I still need to keep my second transaction file? If I remove it would
it be a problem for my system and log back up?

You can remove it if you like, but you should do it properly with
DBCC SHRINKFILE (EMPTYFILE) and ALTER DATABASE.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[laststubborn]

Re: Second Transaction Log File

Could you please give me an example how to do it properly?

Thanks,

Erland Sommarskog wrote:

laststubborn (arafatsalih@gm ail.com) writes:

I have a dillema that I created 2 transaction file before I started to
take log back. Now I changed my recovery model from simple to full. Do
I still need to keep my second transaction file? If I remove it would
it be a problem for my system and log back up?

>
You can remove it if you like, but you should do it properly with
DBCC SHRINKFILE (EMPTYFILE) and ALTER DATABASE.
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Re: Second Transaction Log File

laststubborn (arafatsalih@gm ail.com) writes:

Could you please give me an example how to do it properly?

Let's see, then I would have to look up the exact syntax in Books Online,
since I don't know it by heart. As I said, use DBCC SHIRNKFILE with the
EMPTYFILE option, and then ALTER DATABASE with REMOVE FILE. Please check
Books Online for the exact syntax. There is also some good text that
explains what EMPTYFILE is all about.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[laststubborn]

Re: Second Transaction Log File

What do you think is it good or bad to remove the second log file ?
what would you do keep the log file or delete it? Is it gonna have any
negative effect on my log back and its size?

by the way this is the code that you recommended

-- Empty the data file
DBCC SHRINKFILE ('Test1data', EMPTYFILE);
GO
-- Remove the data file from the database
ALTER DATABASE AdventureWorks
REMOVE FILE Test1data;
GO


Erland Sommarskog wrote:

laststubborn (arafatsalih@gm ail.com) writes:

Could you please give me an example how to do it properly?

>
Let's see, then I would have to look up the exact syntax in Books Online,
since I don't know it by heart. As I said, use DBCC SHIRNKFILE with the
EMPTYFILE option, and then ALTER DATABASE with REMOVE FILE. Please check
Books Online for the exact syntax. There is also some good text that
explains what EMPTYFILE is all about.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Re: Second Transaction Log File

laststubborn (arafatsalih@gm ail.com) writes:

What do you think is it good or bad to remove the second log file ?
what would you do keep the log file or delete it? Is it gonna have any
negative effect on my log back and its size?

The main reason for having multiple log files is to spread the load
over disks. Or handle the situation when the transaction log outgrows
its disk.

The log backup will not be very much affected of whether there are one or
two log files. The size of the log backup will depend on much is written
to the transaction log.

I can't really say whether you should keep the second log file, because
I don't know what your database is about, nor do I know the sizes of the
database or the log. But if the log grew, because you failed to back it
up, there is a fair chance that you by now have an over-dimensioned log.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx