Second Transaction Log File - Microsoft SQL Server

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

Jul 18 '06 #1

Subscribe Post Reply

5912

Erland Sommarskog

laststubborn (ar*********@gmail.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, es****@sommarskog.se

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

Jul 18 '06 #2

laststubborn

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

Thanks,

Erland Sommarskog wrote:

laststubborn (ar*********@gmail.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, es****@sommarskog.se

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

Jul 19 '06 #3

Erland Sommarskog

laststubborn (ar*********@gmail.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, es****@sommarskog.se

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

Jul 19 '06 #4

laststubborn

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 (ar*********@gmail.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, es****@sommarskog.se

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

Jul 19 '06 #5

Erland Sommarskog

laststubborn (ar*********@gmail.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, es****@sommarskog.se

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

Jul 19 '06 #6

Similar topics

4 Transaction Log questions

by: TZoner | last post by:

1) Can one find the location of the 'Transaction Log' at: <Hard Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf? 2) Is it safe to delete it, as SQL will create a new Transaction...

Microsoft SQL Server

Transaction log shrinking... weird behavior?!?!?!?

by: jeffreyv | last post by:

Hi! I'm studying to have my MCSE 70-228 certification and I'm trying some things with backing up transaction logs and shrinking it. Here's what I do: There is no activity in the database by...

Microsoft SQL Server

How can know the transaction per second

by: richardshen | last post by:

What command should should I use get the load on DB2? SNAPSHOT? EVENTMONITOR?

DB2 Database

Please help with a transaction/comment report?

by: Ron | last post by:

Hi All I've got 2 files, transactions and comments, linked by a clientID as a FK in the clients file and as a FK in the comments file (PK in the 'client' file of course). The comments don't...

Microsoft Access / VBA

Performing a Transaction in 2 systems

by: Chris Fink | last post by:

I have the need to perform an atomic operation in two systems, a database and an ftp site. The process is two step; 1. insert a row into oracle, 2. delete a file from a remote ftp site. I need to...

C# / C Sharp

recycled transaction log file: too much?

by: Hubert Fröhlich | last post by:

Hi list, when doing a large Insert I get in the logfile: 2004-08-03 07:23:46 LOG: recycled transaction log file "000001BA000000BD" 2004-08-03 07:34:00 LOG: recycled transaction log file...

PostgreSQL Database

SQLite (with APSW) and transaction separate

by: Christian Stooker | last post by:

Part one: ====== Hi ! I want to use SQLite database like the FireBird database: with big isolation level. What's that meaning ? I have an application that periodically check some input...

Python

Transaction log keeps growing

by: yashgt | last post by:

Hi, We have created a SQL server 2000 database. We observe that the transaction log keeps growing over time. We are now about to run out of space. We have been periodically shrinking the...

Microsoft SQL Server

C# File + DB Transaction

by: =?Utf-8?B?Q2hyaXMgRmluaw==?= | last post by:

I have the following requirements and am looking to make this into a c# transaction. 1. Read from a file into a memory stream 2. Insert the stream into a DB 3. Delete the file Is it possible...

C# / C Sharp

Cloud Servers without Credit Card and Email Registration: A Simpler Way to Get on the Cloud

by: CloudSolutions | last post by:

Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...

General

One-click Importing Excel Data into a*Database

by: ryjfgjl | last post by:

In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...

Microsoft Excel

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware