Hey Folks,
I was looking for some documents/articles/links to get a better
understanding of Multiple Transaction Logs for SQL Server 2000/7. For
example, if I create two transaction logs for a database, how are they used?
Round-Robin method until each one is full? Or use one at a time until the
second log file is needed?
I also wanted to learn about backing up and restore multiple transaction
logs. How to delete the second log file? Is one log active and the other
in-active, etc.
Hope to get some good reading material.
Thanks,
DeeJay
DeeJay,
Have you looked in Books Online? I think that there's a pretty good section about these things. Not
that complicated, though:
> For
> example, if I create two transaction logs for a database, how are they used?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
One at a time. Think about the log as a snake rattling though the file(s). Each time you backup log,
you cut the tail of the snake. If you have several files, and you are at then end of the current
file (and it is full), the head of the snake moves to the beginning of the next file.
> I also wanted to learn about backing up and restore multiple transaction
> logs.
You don't backup the log file. You perform a transaction log backup for the database. To SQL Server,
the transaction log is one logical construct (which happens to be spread out over several physical
files). At restore, though, you need to have as many files as when you performed the backup.
> How to delete the second log file?
DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALTER DATABASE to remove it.
> Is one log active and the other
> in-active, etc.
Not the files per se.
One thing you want to look into is the concept of VLF (Virtual Log Files). The log file is divided
up in a number of VLF, and a VLF can be active (log records on it) or inactive (empty, not used).
Use DBCC LOGINFO for this. I mention a little bit about this in
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
> Hey Folks,
> I was looking for some documents/articles/links to get a better
> understanding of Multiple Transaction Logs for SQL Server 2000/7. For
> example, if I create two transaction logs for a database, how are they used?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
> I also wanted to learn about backing up and restore multiple transaction
> logs. How to delete the second log file? Is one log active and the other
> in-active, etc.
> Hope to get some good reading material.
> Thanks,
> DeeJay
|||Tibor,
Thanks for the reply.
I was already familiar with these concepts (the snake analogy is a good
one). I am also familiar with the VLFs.
I was not sure about deleting the EMPTY log files. Thanks for that.
Thanks for the undocumented DBCC LOGINFO command!
I am getting cross-trained in DB2 and DB2 has a slightly different way of
handling its transaction logs (hence the terminology active/inactive). The
DB2 DBA had asked about it and I wanted to make sure I was able to explain it
to him properly.
Thanks,
DeeJay
"Tibor Karaszi" wrote:
> DeeJay,
> Have you looked in Books Online? I think that there's a pretty good section about these things. Not
> that complicated, though:
>
> One at a time. Think about the log as a snake rattling though the file(s). Each time you backup log,
> you cut the tail of the snake. If you have several files, and you are at then end of the current
> file (and it is full), the head of the snake moves to the beginning of the next file.
>
> You don't backup the log file. You perform a transaction log backup for the database. To SQL Server,
> the transaction log is one logical construct (which happens to be spread out over several physical
> files). At restore, though, you need to have as many files as when you performed the backup.
>
> DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALTER DATABASE to remove it.
>
> Not the files per se.
> One thing you want to look into is the concept of VLF (Virtual Log Files). The log file is divided
> up in a number of VLF, and a VLF can be active (log records on it) or inactive (empty, not used).
> Use DBCC LOGINFO for this. I mention a little bit about this in
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
> news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
>
>
Showing posts with label logs. Show all posts
Showing posts with label logs. Show all posts
Monday, March 19, 2012
Multiple Transaction Logs
I have just come from an Oracle background and am trying to equate SQL server methodologies to Oracle. I have a couple of questions based on the same principle which is the usage of multiple transaction logs. This is what I have been able to find out from the docs and other posts:
If you have multiple transaction logs they will all be used in a sequential manner before being wrapped around. That is, they will all be filled before they are reused.
My first question is will they be used sequentially or concurrently. SQL Server seems to stripe everything so I am inclined to believe the latter. This question has been asked before but from other posts/docs there seems to be a difference of opinion. I am of course equating transaction logs to redo/archived logs so I want to know categorically (links would be greatly appreciated) as it will have a huge impact on any disaster and recovery plans that I implement.
The second question is a spin off and may or may not be relevant dependant on the answer of the first. If the transaction logs are written to concurrently, that is, entries are striped, why bother with multiple logs? I don't see any benefits. In fact, in a recovery scenario recovering transactions from mutliple striped logs would appear to adversely affect MTTR.
Feel free to point out the errors of my ways as I am all ears and eager to learn.
Thanks in advance.My advice is to get the poop straight from the horse (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_3t2d.asp).
I'd also recommend two books, the first is the SQL 2000 Resource Kit (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=lL5zATKAxC&isbn=0735612668&itm=2) which has a great chapter on Oracle to MS-SQL, along with a lot of other VERY useful information. The second is Inside Microsoft SQL Server 2000 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=lL5zATKAxC&isbn=0735609985&itm=1).
Peruse a bit, then come back... I'm sure that you'll still have questions, you'll just be able to form them a bit better!
-PatP|||OK, found the answer, I think. The logs are used sequentially. In case anybody else wants the answer, when there are multiple log files, the first one fills up, then the logical log moves on to the second physical logfile etc. When the last physical log is filled up it then wraps around to the beginning of the first physical log again. This is the best description that I have found and it implies, but does not say categorically confirm that if you have declared two logfiles in the create database statement, i.e.
log on
(name='test1_log1',
filename='E:\SQLServer\test1\Logs\test1_log1.LDF',
size=5mb,
maxsize=10mb),
(name='test1_log2',
filename='E:\SQLServer\test1\Logs\test1_log2.LDF',
size=5mb,
maxsize=10mb)
test1_log1 would be used, then test1_log2 before wrapping around back to test1_log1.
I had actually researched this issue quite considerably before I posted this question, including reading 3 SQLServer books. The purpose and use of the log is described, but not this issue. If you look at the following post you will can see that there is a difference of opinion where some think they are used concurrently and some think that they are used sequentially:
http://www.mcse.ms/showthread.php?threadid=1184072&perpage=10&pagenumber=1
I tried to imply this before when I posted the thread. My main problem was that folks couldn't agree between themselves how they were used so I was hoping for some clarification.
Thans for responding anyway.|||No problemo!
I've never seen an instance where more than one log file was ever in use at one time. Theoretically there is a tiny window when two adjacent (sequential) log files might be active for the duration of one DML statement, but I've never seen that happen in the real world.
I've found lots of discussions of log files, and how people think those files are used. I've never seen anything to contradict any of the sources that I sited in my previous posting. I don't believe that there is any way to have more than one log file active for a given database at one time, beyond the tiny window theoretically allowed by the log sequencing mechanism.
-PatP|||That was the reason why I was looking into it. All of the configurations we have here have one log, but the docs say you can specify more than one. In Oracle more than one redo log group is mandatory and the reason is quite straight forward. In SQL Server the transaction log can expand, so one of the main reasons for having more than one is eliminated. I was wondering why you would want more than one, other than the lack of disk space to allow it to expand or manual duplexing (which obviously not the case as I have discovered). I was thinking that it may provide more flexibility in a recovery scenario or maybe performance reasons when it comes to truncating and backing up the transaction log(s). I have see some posts where this action can sometimes adversely effect user performance.
Maybe I am digging a bit too deep too early in the process, and I'm sure everything will become clear with the passage of time, but any pointers on this subject would still be appreciated.|||There are probably a gazillion different reasons for allowing multiple log files, but the one that I've hit most frequently is when someone sets up a database (and of course its log file too) on a drive that is "bigger than they'll ever need". Through experience, I've discovered that only infinity is really infinity... For one reason or another, you eventually use "more than you'll ever need" in almost every case! When the database consumes all of the available disk, you can create a new log file on a different disk drive in order to get enough breathing room to fix the underlying problem.
I'm sure that there are lots of other reasons, but that one alone is enough to justify the functionality to me.
There is a lot of information within BOL (SQL Server Books Online), but the real meaty stuff is in the books that I cited in my previous posting. Kalen has forgotten more about indexing than I'll ever know, and the Resource Kit ought to be required reading for someone with your technical background... It gets into the "care and feeding" that an Oracle DBA is accustomed to, explains which things are important to SQL Server and which are irrelevant.
If you are in a major metro area, it doesn't hurt to look for a user group. Even if you aren't in a major metro, check out PASS (http://www.sqlpass.org) for more insight.
-PatP|||That's the conclusion I was coming to. I appreciate the input. I'm coming up to speed quite nicely on the fundamentals of SQLServer but still receptive to any direction that might facilitate this process more easily. Thanks.|||Sure,
SQL Server is simply easier than Oracle...
Notice I didn't say better...
The multiple Redo Logs allow for some pretty substantial amount of transactions...
In all cases I've ever seen in SQL Server, ther's only 1..
I dump tranny logs every 10 minutes in some environments...so in that cas etheres more than 1...I guess that's where the confusion comes in...
Redo Logs are like partitioned logs...which sql server doesn't have...I don't think (And you know, that happens waaaay to much)|||From Books Online:
Transaction Logs
A database in Microsoft SQL Server 2000 has at least one data file and one transaction log file. Data and transaction log information is never mixed on the same file, and individual files are used by only one database.
SQL Server uses the transaction log of each database to recover transactions. The transaction log is a serial record of all modifications that have occurred in the database as well as the transaction that performed each modification. The transaction log records the start of each transaction. It records the changes to the data and enough information to undo the modifications (if necessary later) made during each transaction. For some large operations, such as CREATE INDEX, the transaction log instead records the fact that the operation took place. The log grows continuously as logged operations occur in the database.
The transaction log records the allocation and deallocation of pages and the commit or rollback of each transaction. This allows SQL Server either to apply (roll forward) or back out (roll back) each transaction in the following ways:
A transaction is rolled forward when you apply a transaction log. SQL Server copies the after image of every modification to the database or reruns statements such as CREATE INDEX. These actions are applied in the same sequence in which they originally occurred. At the end of this process, the database is in the same state it was in at the time the transaction log was backed up.
A transaction is rolled back when you back out an incomplete transaction. SQL Server copies the before images of all modifications to the database since the BEGIN TRANSACTION. If it encounters transaction log records indicating that a CREATE INDEX was performed, it performs operations that logically reverse the statement. These before images and CREATE INDEX reversals are applied in the reverse of their original sequence.
At a checkpoint, SQL Server ensures that all transaction log records and database pages modified are written to disk. During the recovery process of each database that occurs when SQL Server is restarted, a transaction must be rolled forward only when it is not known whether all the data modifications in the transaction were actually written from the SQL Server buffer cache to disk. Because a checkpoint forces all modified pages to disk, it represents the point at which the startup recovery must start rolling forward transactions. Because all pages modified before the checkpoint are guaranteed to be on disk, there is no need to roll forward anything done before the checkpoint.
Transaction log backups enable you to recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure. Transaction log backups should be a consideration in your media recovery strategy. For more information,|||Data devices are written to in concurrent fashion (if more than 1 is defined), while log devices are ... well, that was already mentioned. There is a very good reason to have multiple small sized trx log files defined for servers with 5+ user databases and 2+ disk arrays dedicated to log devices.|||Hey Brett, what's up with citing BOL article that has nothing to do with the question?
If you have multiple transaction logs they will all be used in a sequential manner before being wrapped around. That is, they will all be filled before they are reused.
My first question is will they be used sequentially or concurrently. SQL Server seems to stripe everything so I am inclined to believe the latter. This question has been asked before but from other posts/docs there seems to be a difference of opinion. I am of course equating transaction logs to redo/archived logs so I want to know categorically (links would be greatly appreciated) as it will have a huge impact on any disaster and recovery plans that I implement.
The second question is a spin off and may or may not be relevant dependant on the answer of the first. If the transaction logs are written to concurrently, that is, entries are striped, why bother with multiple logs? I don't see any benefits. In fact, in a recovery scenario recovering transactions from mutliple striped logs would appear to adversely affect MTTR.
Feel free to point out the errors of my ways as I am all ears and eager to learn.
Thanks in advance.My advice is to get the poop straight from the horse (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_3t2d.asp).
I'd also recommend two books, the first is the SQL 2000 Resource Kit (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=lL5zATKAxC&isbn=0735612668&itm=2) which has a great chapter on Oracle to MS-SQL, along with a lot of other VERY useful information. The second is Inside Microsoft SQL Server 2000 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=lL5zATKAxC&isbn=0735609985&itm=1).
Peruse a bit, then come back... I'm sure that you'll still have questions, you'll just be able to form them a bit better!
-PatP|||OK, found the answer, I think. The logs are used sequentially. In case anybody else wants the answer, when there are multiple log files, the first one fills up, then the logical log moves on to the second physical logfile etc. When the last physical log is filled up it then wraps around to the beginning of the first physical log again. This is the best description that I have found and it implies, but does not say categorically confirm that if you have declared two logfiles in the create database statement, i.e.
log on
(name='test1_log1',
filename='E:\SQLServer\test1\Logs\test1_log1.LDF',
size=5mb,
maxsize=10mb),
(name='test1_log2',
filename='E:\SQLServer\test1\Logs\test1_log2.LDF',
size=5mb,
maxsize=10mb)
test1_log1 would be used, then test1_log2 before wrapping around back to test1_log1.
I had actually researched this issue quite considerably before I posted this question, including reading 3 SQLServer books. The purpose and use of the log is described, but not this issue. If you look at the following post you will can see that there is a difference of opinion where some think they are used concurrently and some think that they are used sequentially:
http://www.mcse.ms/showthread.php?threadid=1184072&perpage=10&pagenumber=1
I tried to imply this before when I posted the thread. My main problem was that folks couldn't agree between themselves how they were used so I was hoping for some clarification.
Thans for responding anyway.|||No problemo!
I've never seen an instance where more than one log file was ever in use at one time. Theoretically there is a tiny window when two adjacent (sequential) log files might be active for the duration of one DML statement, but I've never seen that happen in the real world.
I've found lots of discussions of log files, and how people think those files are used. I've never seen anything to contradict any of the sources that I sited in my previous posting. I don't believe that there is any way to have more than one log file active for a given database at one time, beyond the tiny window theoretically allowed by the log sequencing mechanism.
-PatP|||That was the reason why I was looking into it. All of the configurations we have here have one log, but the docs say you can specify more than one. In Oracle more than one redo log group is mandatory and the reason is quite straight forward. In SQL Server the transaction log can expand, so one of the main reasons for having more than one is eliminated. I was wondering why you would want more than one, other than the lack of disk space to allow it to expand or manual duplexing (which obviously not the case as I have discovered). I was thinking that it may provide more flexibility in a recovery scenario or maybe performance reasons when it comes to truncating and backing up the transaction log(s). I have see some posts where this action can sometimes adversely effect user performance.
Maybe I am digging a bit too deep too early in the process, and I'm sure everything will become clear with the passage of time, but any pointers on this subject would still be appreciated.|||There are probably a gazillion different reasons for allowing multiple log files, but the one that I've hit most frequently is when someone sets up a database (and of course its log file too) on a drive that is "bigger than they'll ever need". Through experience, I've discovered that only infinity is really infinity... For one reason or another, you eventually use "more than you'll ever need" in almost every case! When the database consumes all of the available disk, you can create a new log file on a different disk drive in order to get enough breathing room to fix the underlying problem.
I'm sure that there are lots of other reasons, but that one alone is enough to justify the functionality to me.
There is a lot of information within BOL (SQL Server Books Online), but the real meaty stuff is in the books that I cited in my previous posting. Kalen has forgotten more about indexing than I'll ever know, and the Resource Kit ought to be required reading for someone with your technical background... It gets into the "care and feeding" that an Oracle DBA is accustomed to, explains which things are important to SQL Server and which are irrelevant.
If you are in a major metro area, it doesn't hurt to look for a user group. Even if you aren't in a major metro, check out PASS (http://www.sqlpass.org) for more insight.
-PatP|||That's the conclusion I was coming to. I appreciate the input. I'm coming up to speed quite nicely on the fundamentals of SQLServer but still receptive to any direction that might facilitate this process more easily. Thanks.|||Sure,
SQL Server is simply easier than Oracle...
Notice I didn't say better...
The multiple Redo Logs allow for some pretty substantial amount of transactions...
In all cases I've ever seen in SQL Server, ther's only 1..
I dump tranny logs every 10 minutes in some environments...so in that cas etheres more than 1...I guess that's where the confusion comes in...
Redo Logs are like partitioned logs...which sql server doesn't have...I don't think (And you know, that happens waaaay to much)|||From Books Online:
Transaction Logs
A database in Microsoft SQL Server 2000 has at least one data file and one transaction log file. Data and transaction log information is never mixed on the same file, and individual files are used by only one database.
SQL Server uses the transaction log of each database to recover transactions. The transaction log is a serial record of all modifications that have occurred in the database as well as the transaction that performed each modification. The transaction log records the start of each transaction. It records the changes to the data and enough information to undo the modifications (if necessary later) made during each transaction. For some large operations, such as CREATE INDEX, the transaction log instead records the fact that the operation took place. The log grows continuously as logged operations occur in the database.
The transaction log records the allocation and deallocation of pages and the commit or rollback of each transaction. This allows SQL Server either to apply (roll forward) or back out (roll back) each transaction in the following ways:
A transaction is rolled forward when you apply a transaction log. SQL Server copies the after image of every modification to the database or reruns statements such as CREATE INDEX. These actions are applied in the same sequence in which they originally occurred. At the end of this process, the database is in the same state it was in at the time the transaction log was backed up.
A transaction is rolled back when you back out an incomplete transaction. SQL Server copies the before images of all modifications to the database since the BEGIN TRANSACTION. If it encounters transaction log records indicating that a CREATE INDEX was performed, it performs operations that logically reverse the statement. These before images and CREATE INDEX reversals are applied in the reverse of their original sequence.
At a checkpoint, SQL Server ensures that all transaction log records and database pages modified are written to disk. During the recovery process of each database that occurs when SQL Server is restarted, a transaction must be rolled forward only when it is not known whether all the data modifications in the transaction were actually written from the SQL Server buffer cache to disk. Because a checkpoint forces all modified pages to disk, it represents the point at which the startup recovery must start rolling forward transactions. Because all pages modified before the checkpoint are guaranteed to be on disk, there is no need to roll forward anything done before the checkpoint.
Transaction log backups enable you to recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure. Transaction log backups should be a consideration in your media recovery strategy. For more information,|||Data devices are written to in concurrent fashion (if more than 1 is defined), while log devices are ... well, that was already mentioned. There is a very good reason to have multiple small sized trx log files defined for servers with 5+ user databases and 2+ disk arrays dedicated to log devices.|||Hey Brett, what's up with citing BOL article that has nothing to do with the question?
Multiple Transaction Logs
Hey Folks,
I was looking for some documents/articles/links to get a better
understanding of Multiple Transaction Logs for SQL Server 2000/7. For
example, if I create two transaction logs for a database, how are they used?
Round-Robin method until each one is full? Or use one at a time until the
second log file is needed?
I also wanted to learn about backing up and restore multiple transaction
logs. How to delete the second log file? Is one log active and the other
in-active, etc.
Hope to get some good reading material.
Thanks,
DeeJayDeeJay,
Have you looked in Books Online? I think that there's a pretty good section
about these things. Not
that complicated, though:
> For
> example, if I create two transaction logs for a database, how are they use
d?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
One at a time. Think about the log as a snake rattling though the file(s). E
ach time you backup log,
you cut the tail of the snake. If you have several files, and you are at the
n end of the current
file (and it is full), the head of the snake moves to the beginning of the n
ext file.
> I also wanted to learn about backing up and restore multiple transaction
> logs.
You don't backup the log file. You perform a transaction log backup for the
database. To SQL Server,
the transaction log is one logical construct (which happens to be spread out
over several physical
files). At restore, though, you need to have as many files as when you perfo
rmed the backup.
> How to delete the second log file?
DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALTER
DATABASE to remove it.
> Is one log active and the other
> in-active, etc.
Not the files per se.
One thing you want to look into is the concept of VLF (Virtual Log Files). T
he log file is divided
up in a number of VLF, and a VLF can be active (log records on it) or inacti
ve (empty, not used).
Use DBCC LOGINFO for this. I mention a little bit about this in
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
> Hey Folks,
> I was looking for some documents/articles/links to get a better
> understanding of Multiple Transaction Logs for SQL Server 2000/7. For
> example, if I create two transaction logs for a database, how are they use
d?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
> I also wanted to learn about backing up and restore multiple transaction
> logs. How to delete the second log file? Is one log active and the other
> in-active, etc.
> Hope to get some good reading material.
> Thanks,
> DeeJay|||Tibor,
Thanks for the reply.
I was already familiar with these concepts (the snake analogy is a good
one). I am also familiar with the VLFs.
I was not sure about deleting the EMPTY log files. Thanks for that.
Thanks for the undocumented DBCC LOGINFO command!
I am getting cross-trained in DB2 and DB2 has a slightly different way of
handling its transaction logs (hence the terminology active/inactive). The
DB2 DBA had asked about it and I wanted to make sure I was able to explain i
t
to him properly.
Thanks,
DeeJay
"Tibor Karaszi" wrote:
> DeeJay,
> Have you looked in Books Online? I think that there's a pretty good sectio
n about these things. Not
> that complicated, though:
>
> One at a time. Think about the log as a snake rattling though the file(s).
Each time you backup log,
> you cut the tail of the snake. If you have several files, and you are at t
hen end of the current
> file (and it is full), the head of the snake moves to the beginning of the
next file.
>
> You don't backup the log file. You perform a transaction log backup for th
e database. To SQL Server,
> the transaction log is one logical construct (which happens to be spread o
ut over several physical
> files). At restore, though, you need to have as many files as when you per
formed the backup.
>
> DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALT
ER DATABASE to remove it.
>
> Not the files per se.
> One thing you want to look into is the concept of VLF (Virtual Log Files).
The log file is divided
> up in a number of VLF, and a VLF can be active (log records on it) or inac
tive (empty, not used).
> Use DBCC LOGINFO for this. I mention a little bit about this in
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
> news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
>
>
I was looking for some documents/articles/links to get a better
understanding of Multiple Transaction Logs for SQL Server 2000/7. For
example, if I create two transaction logs for a database, how are they used?
Round-Robin method until each one is full? Or use one at a time until the
second log file is needed?
I also wanted to learn about backing up and restore multiple transaction
logs. How to delete the second log file? Is one log active and the other
in-active, etc.
Hope to get some good reading material.
Thanks,
DeeJayDeeJay,
Have you looked in Books Online? I think that there's a pretty good section
about these things. Not
that complicated, though:
> For
> example, if I create two transaction logs for a database, how are they use
d?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
One at a time. Think about the log as a snake rattling though the file(s). E
ach time you backup log,
you cut the tail of the snake. If you have several files, and you are at the
n end of the current
file (and it is full), the head of the snake moves to the beginning of the n
ext file.
> I also wanted to learn about backing up and restore multiple transaction
> logs.
You don't backup the log file. You perform a transaction log backup for the
database. To SQL Server,
the transaction log is one logical construct (which happens to be spread out
over several physical
files). At restore, though, you need to have as many files as when you perfo
rmed the backup.
> How to delete the second log file?
DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALTER
DATABASE to remove it.
> Is one log active and the other
> in-active, etc.
Not the files per se.
One thing you want to look into is the concept of VLF (Virtual Log Files). T
he log file is divided
up in a number of VLF, and a VLF can be active (log records on it) or inacti
ve (empty, not used).
Use DBCC LOGINFO for this. I mention a little bit about this in
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
> Hey Folks,
> I was looking for some documents/articles/links to get a better
> understanding of Multiple Transaction Logs for SQL Server 2000/7. For
> example, if I create two transaction logs for a database, how are they use
d?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
> I also wanted to learn about backing up and restore multiple transaction
> logs. How to delete the second log file? Is one log active and the other
> in-active, etc.
> Hope to get some good reading material.
> Thanks,
> DeeJay|||Tibor,
Thanks for the reply.
I was already familiar with these concepts (the snake analogy is a good
one). I am also familiar with the VLFs.
I was not sure about deleting the EMPTY log files. Thanks for that.
Thanks for the undocumented DBCC LOGINFO command!
I am getting cross-trained in DB2 and DB2 has a slightly different way of
handling its transaction logs (hence the terminology active/inactive). The
DB2 DBA had asked about it and I wanted to make sure I was able to explain i
t
to him properly.
Thanks,
DeeJay
"Tibor Karaszi" wrote:
> DeeJay,
> Have you looked in Books Online? I think that there's a pretty good sectio
n about these things. Not
> that complicated, though:
>
> One at a time. Think about the log as a snake rattling though the file(s).
Each time you backup log,
> you cut the tail of the snake. If you have several files, and you are at t
hen end of the current
> file (and it is full), the head of the snake moves to the beginning of the
next file.
>
> You don't backup the log file. You perform a transaction log backup for th
e database. To SQL Server,
> the transaction log is one logical construct (which happens to be spread o
ut over several physical
> files). At restore, though, you need to have as many files as when you per
formed the backup.
>
> DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALT
ER DATABASE to remove it.
>
> Not the files per se.
> One thing you want to look into is the concept of VLF (Virtual Log Files).
The log file is divided
> up in a number of VLF, and a VLF can be active (log records on it) or inac
tive (empty, not used).
> Use DBCC LOGINFO for this. I mention a little bit about this in
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
> news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
>
>
Multiple Transaction Logs
Hey Folks,
I was looking for some documents/articles/links to get a better
understanding of Multiple Transaction Logs for SQL Server 2000/7. For
example, if I create two transaction logs for a database, how are they used?
Round-Robin method until each one is full? Or use one at a time until the
second log file is needed?
I also wanted to learn about backing up and restore multiple transaction
logs. How to delete the second log file? Is one log active and the other
in-active, etc.
Hope to get some good reading material.
Thanks,
DeeJayDeeJay,
Have you looked in Books Online? I think that there's a pretty good section about these things. Not
that complicated, though:
> For
> example, if I create two transaction logs for a database, how are they used?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
One at a time. Think about the log as a snake rattling though the file(s). Each time you backup log,
you cut the tail of the snake. If you have several files, and you are at then end of the current
file (and it is full), the head of the snake moves to the beginning of the next file.
> I also wanted to learn about backing up and restore multiple transaction
> logs.
You don't backup the log file. You perform a transaction log backup for the database. To SQL Server,
the transaction log is one logical construct (which happens to be spread out over several physical
files). At restore, though, you need to have as many files as when you performed the backup.
> How to delete the second log file?
DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALTER DATABASE to remove it.
> Is one log active and the other
> in-active, etc.
Not the files per se.
One thing you want to look into is the concept of VLF (Virtual Log Files). The log file is divided
up in a number of VLF, and a VLF can be active (log records on it) or inactive (empty, not used).
Use DBCC LOGINFO for this. I mention a little bit about this in
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
> Hey Folks,
> I was looking for some documents/articles/links to get a better
> understanding of Multiple Transaction Logs for SQL Server 2000/7. For
> example, if I create two transaction logs for a database, how are they used?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
> I also wanted to learn about backing up and restore multiple transaction
> logs. How to delete the second log file? Is one log active and the other
> in-active, etc.
> Hope to get some good reading material.
> Thanks,
> DeeJay|||Tibor,
Thanks for the reply.
I was already familiar with these concepts (the snake analogy is a good
one). I am also familiar with the VLFs.
I was not sure about deleting the EMPTY log files. Thanks for that.
Thanks for the undocumented DBCC LOGINFO command!
I am getting cross-trained in DB2 and DB2 has a slightly different way of
handling its transaction logs (hence the terminology active/inactive). The
DB2 DBA had asked about it and I wanted to make sure I was able to explain it
to him properly.
Thanks,
DeeJay
"Tibor Karaszi" wrote:
> DeeJay,
> Have you looked in Books Online? I think that there's a pretty good section about these things. Not
> that complicated, though:
> > For
> > example, if I create two transaction logs for a database, how are they used?
> > Round-Robin method until each one is full? Or use one at a time until the
> > second log file is needed?
> One at a time. Think about the log as a snake rattling though the file(s). Each time you backup log,
> you cut the tail of the snake. If you have several files, and you are at then end of the current
> file (and it is full), the head of the snake moves to the beginning of the next file.
>
> > I also wanted to learn about backing up and restore multiple transaction
> > logs.
> You don't backup the log file. You perform a transaction log backup for the database. To SQL Server,
> the transaction log is one logical construct (which happens to be spread out over several physical
> files). At restore, though, you need to have as many files as when you performed the backup.
>
> > How to delete the second log file?
> DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALTER DATABASE to remove it.
> > Is one log active and the other
> > in-active, etc.
> Not the files per se.
> One thing you want to look into is the concept of VLF (Virtual Log Files). The log file is divided
> up in a number of VLF, and a VLF can be active (log records on it) or inactive (empty, not used).
> Use DBCC LOGINFO for this. I mention a little bit about this in
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
> news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
> > Hey Folks,
> >
> > I was looking for some documents/articles/links to get a better
> > understanding of Multiple Transaction Logs for SQL Server 2000/7. For
> > example, if I create two transaction logs for a database, how are they used?
> > Round-Robin method until each one is full? Or use one at a time until the
> > second log file is needed?
> >
> > I also wanted to learn about backing up and restore multiple transaction
> > logs. How to delete the second log file? Is one log active and the other
> > in-active, etc.
> >
> > Hope to get some good reading material.
> >
> > Thanks,
> >
> > DeeJay
>
>
I was looking for some documents/articles/links to get a better
understanding of Multiple Transaction Logs for SQL Server 2000/7. For
example, if I create two transaction logs for a database, how are they used?
Round-Robin method until each one is full? Or use one at a time until the
second log file is needed?
I also wanted to learn about backing up and restore multiple transaction
logs. How to delete the second log file? Is one log active and the other
in-active, etc.
Hope to get some good reading material.
Thanks,
DeeJayDeeJay,
Have you looked in Books Online? I think that there's a pretty good section about these things. Not
that complicated, though:
> For
> example, if I create two transaction logs for a database, how are they used?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
One at a time. Think about the log as a snake rattling though the file(s). Each time you backup log,
you cut the tail of the snake. If you have several files, and you are at then end of the current
file (and it is full), the head of the snake moves to the beginning of the next file.
> I also wanted to learn about backing up and restore multiple transaction
> logs.
You don't backup the log file. You perform a transaction log backup for the database. To SQL Server,
the transaction log is one logical construct (which happens to be spread out over several physical
files). At restore, though, you need to have as many files as when you performed the backup.
> How to delete the second log file?
DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALTER DATABASE to remove it.
> Is one log active and the other
> in-active, etc.
Not the files per se.
One thing you want to look into is the concept of VLF (Virtual Log Files). The log file is divided
up in a number of VLF, and a VLF can be active (log records on it) or inactive (empty, not used).
Use DBCC LOGINFO for this. I mention a little bit about this in
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
> Hey Folks,
> I was looking for some documents/articles/links to get a better
> understanding of Multiple Transaction Logs for SQL Server 2000/7. For
> example, if I create two transaction logs for a database, how are they used?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
> I also wanted to learn about backing up and restore multiple transaction
> logs. How to delete the second log file? Is one log active and the other
> in-active, etc.
> Hope to get some good reading material.
> Thanks,
> DeeJay|||Tibor,
Thanks for the reply.
I was already familiar with these concepts (the snake analogy is a good
one). I am also familiar with the VLFs.
I was not sure about deleting the EMPTY log files. Thanks for that.
Thanks for the undocumented DBCC LOGINFO command!
I am getting cross-trained in DB2 and DB2 has a slightly different way of
handling its transaction logs (hence the terminology active/inactive). The
DB2 DBA had asked about it and I wanted to make sure I was able to explain it
to him properly.
Thanks,
DeeJay
"Tibor Karaszi" wrote:
> DeeJay,
> Have you looked in Books Online? I think that there's a pretty good section about these things. Not
> that complicated, though:
> > For
> > example, if I create two transaction logs for a database, how are they used?
> > Round-Robin method until each one is full? Or use one at a time until the
> > second log file is needed?
> One at a time. Think about the log as a snake rattling though the file(s). Each time you backup log,
> you cut the tail of the snake. If you have several files, and you are at then end of the current
> file (and it is full), the head of the snake moves to the beginning of the next file.
>
> > I also wanted to learn about backing up and restore multiple transaction
> > logs.
> You don't backup the log file. You perform a transaction log backup for the database. To SQL Server,
> the transaction log is one logical construct (which happens to be spread out over several physical
> files). At restore, though, you need to have as many files as when you performed the backup.
>
> > How to delete the second log file?
> DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALTER DATABASE to remove it.
> > Is one log active and the other
> > in-active, etc.
> Not the files per se.
> One thing you want to look into is the concept of VLF (Virtual Log Files). The log file is divided
> up in a number of VLF, and a VLF can be active (log records on it) or inactive (empty, not used).
> Use DBCC LOGINFO for this. I mention a little bit about this in
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
> news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
> > Hey Folks,
> >
> > I was looking for some documents/articles/links to get a better
> > understanding of Multiple Transaction Logs for SQL Server 2000/7. For
> > example, if I create two transaction logs for a database, how are they used?
> > Round-Robin method until each one is full? Or use one at a time until the
> > second log file is needed?
> >
> > I also wanted to learn about backing up and restore multiple transaction
> > logs. How to delete the second log file? Is one log active and the other
> > in-active, etc.
> >
> > Hope to get some good reading material.
> >
> > Thanks,
> >
> > DeeJay
>
>
Multiple Trans Log Restores
Is there a way to restore multiple transactions logs when
you don't know the name of the logs files (i.e. the last 8
characters of the log file changes)?
Thanks,
JoshYou could either use xp_cmdshell and DIR into a temp table and work your code from there, or base
your restore on the backup history tables in msdb (assuming that the backup was taken on the same
machine). Both options requires a bit of coding.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Joshua Lakin" <LakinJ@.NOSPAMPLEASE.bsci.com> wrote in message
news:00a501c3c58f$8474d3f0$a401280a@.phx.gbl...
> Is there a way to restore multiple transactions logs when
> you don't know the name of the logs files (i.e. the last 8
> characters of the log file changes)?
> Thanks,
> Josh
you don't know the name of the logs files (i.e. the last 8
characters of the log file changes)?
Thanks,
JoshYou could either use xp_cmdshell and DIR into a temp table and work your code from there, or base
your restore on the backup history tables in msdb (assuming that the backup was taken on the same
machine). Both options requires a bit of coding.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Joshua Lakin" <LakinJ@.NOSPAMPLEASE.bsci.com> wrote in message
news:00a501c3c58f$8474d3f0$a401280a@.phx.gbl...
> Is there a way to restore multiple transactions logs when
> you don't know the name of the logs files (i.e. the last 8
> characters of the log file changes)?
> Thanks,
> Josh
Subscribe to:
Posts (Atom)