Are multiple transaction logs used concurrently or sequentially?

It'd be nice to have redundant transaction logs, maybe even one on a
mapped drive, but I read in one post here that SQL Server considers
all transaction logs to be one big log file. Is this true?
Is there any way to have redundant transaction logs without mirroring>Hi,
No, SQL Server allows to create multiple Transaction logs for a database.
Eg: from books online:-
USE master
GO
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat2.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch3,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat3.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = Archlog1,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Archlog2,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archlog2.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
GO
Thanks
Hari
SQL Server MVP
"fumanchu" <leondobr@.verizon.net> wrote in message
news:rjvtn0tjdv3afv74bv33t3k9ueng4fg7ml@.4ax.com...
> It'd be nice to have redundant transaction logs, maybe even one on a
> mapped drive, but I read in one post here that SQL Server considers
> all transaction logs to be one big log file. Is this true?
> Is there any way to have redundant transaction logs without mirroring>|||On Tue, 26 Oct 2004 21:03:37 -0500, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hi,
>No, SQL Server allows to create multiple Transaction logs for a database.
>
I understand that you can have multiple transaction logs, but are they
concurrent, that is, interchangeable copies of one another, or are
they used one after another as continuation files?|||Hi,
They are interchangable.Check out the section "Transaction Log Physical
Architecture" in the SQL
Server Books Online.
Thanks
Hari
"fumanchu" <leondobr@.verizon.net> wrote in message
news:ii1un0h1o0tnk7s3ndmb66oc8v3nv7jb2j@.4ax.com...
> On Tue, 26 Oct 2004 21:03:37 -0500, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>>Hi,
>>No, SQL Server allows to create multiple Transaction logs for a database.
> I understand that you can have multiple transaction logs, but are they
> concurrent, that is, interchangeable copies of one another, or are
> they used one after another as continuation files?|||The logs are all in use, you would use multiple logs across multiple RAID
sets to gain better performance, say you had 3 log files, data would be
striped across all 3.
I sort of see what you might want to do what you ask, you could achieve this
by doing log shipping, but, the interval between log dumps would be your
potentially non-recovery window.
You could also look at clustering.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||The way the SQL Logs work is sequential... Even if you have 5 logs, SQL is
only using one at a time, writing serially to the end of the file... So
having multiple files does not improve performance.
Mirroring is probably the best way to get redundancy on the logs, and it is
a best practice that I suspect most people follow.
There is some file mirroring software out there ( I can't remember the
company ) that keeps files in sync real-time, and they claim this works with
SQL as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"fumanchu" <leondobr@.verizon.net> wrote in message
news:rjvtn0tjdv3afv74bv33t3k9ueng4fg7ml@.4ax.com...
> It'd be nice to have redundant transaction logs, maybe even one on a
> mapped drive, but I read in one post here that SQL Server considers
> all transaction logs to be one big log file. Is this true?
> Is there any way to have redundant transaction logs without mirroring>|||Hi Wayne,
If the database has multiple logs they are written to (striped) at the same
time - so, if you put them on the same logical disk they will grow together.
For example, if you create a database (see below) with 2 log files and write
a big table onto it, both log files will grow together, in my own test here
they both ended up 39MB.
The biggest problem here is that its striping (essentially RAID 0) so no
fault tolerance, if you put the logs on seperate drives and one goes then
you lose the log.
CREATE DATABASE testlog
ON PRIMARY
( NAME = testdb1,
FILENAME = N'e:\testlog1.mdf',
SIZE = 10MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
LOG ON
( NAME = testlog1,
FILENAME = N'h:\testlog1.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%),
( NAME = testlog2,
FILENAME = N'h:\testlog2.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
GO
Tony.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||Tony,
I think the question is about performance, and I have a feeling that SQL Server will not write
concurrently to these log files. AFAIK, SQL Server will grow one file (with z number of VLF's) fill
that, then grow the other file and fill that etc. I don't know HW enough to determine whether we
still could gain anything by having multiple log files, but assuming a simple install with one log
file per physical disk, I would assume that only one disk would be hit at a time.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:ezYBooCvEHA.568@.TK2MSFTNGP09.phx.gbl...
> Hi Wayne,
> If the database has multiple logs they are written to (striped) at the same time - so, if you put
> them on the same logical disk they will grow together.
> For example, if you create a database (see below) with 2 log files and write a big table onto it,
> both log files will grow together, in my own test here they both ended up 39MB.
> The biggest problem here is that its striping (essentially RAID 0) so no fault tolerance, if you
> put the logs on seperate drives and one goes then you lose the log.
> CREATE DATABASE testlog
> ON PRIMARY
> ( NAME = testdb1,
> FILENAME = N'e:\testlog1.mdf',
> SIZE = 10MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> LOG ON
> ( NAME = testlog1,
> FILENAME = N'h:\testlog1.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%),
> ( NAME = testlog2,
> FILENAME = N'h:\testlog2.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> GO
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>|||Hi Tibor,
Nope - try the example, SQL Server stripes across the 2 log files! It does
the same for multiple data files in a file group as well.
Create a database using the create db below and do something like this (from
a big table in another db)...
select *
into mytest
from yourotherdb..bigtable
where 1 = 0
go
insert mytest
select *
from yourotherdb..bigtable
Observe that the two log files end up the same size, in fact, watch them
grow - they grow at the same rate.
When multiple log (or data files) for a single db are placed on different
logical disks, the proportion of data striped to each file is dependant on
how much free space there is on the drives where they reside.
CREATE DATABASE testlog
ON PRIMARY
( NAME = testdb1,
FILENAME = N'e:\testlog1.mdf',
SIZE = 10MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
LOG ON
( NAME = testlog1,
FILENAME = N'h:\testlog1.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%),
( NAME = testlog2,
FILENAME = N'h:\testlog2.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||Hi Tony
I think we're not talking about growing the physical files, but how existing
log files are filled. Your script shows that once the log files have reached
their initially created size, then new space is allocated for each of them
in a striped fashion.
But, if you change your script to initially create your log files with some
reasonable size, and then use dbcc loginfo to examine them as the insert is
taking place, you will see that one of them gets filled completely before
the other one starts to be filled.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:%23YSfShEvEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi Tibor,
> Nope - try the example, SQL Server stripes across the 2 log files! It does
> the same for multiple data files in a file group as well.
> Create a database using the create db below and do something like this
> (from a big table in another db)...
> select *
> into mytest
> from yourotherdb..bigtable
> where 1 = 0
> go
> insert mytest
> select *
> from yourotherdb..bigtable
> Observe that the two log files end up the same size, in fact, watch them
> grow - they grow at the same rate.
> When multiple log (or data files) for a single db are placed on different
> logical disks, the proportion of data striped to each file is dependant on
> how much free space there is on the drives where they reside.
> CREATE DATABASE testlog
> ON PRIMARY
> ( NAME = testdb1,
> FILENAME = N'e:\testlog1.mdf',
> SIZE = 10MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> LOG ON
> ( NAME = testlog1,
> FILENAME = N'h:\testlog1.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%),
> ( NAME = testlog2,
> FILENAME = N'h:\testlog2.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>|||Sorry Kalen, didn't read the entire post.
I see that the first file is written to then it works from top to bottom on
the second file filling that first and then the first file until filled.
The allocation of space is done to both, which makes sense.
Sorry Wayne/Tibor !
Tony.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)