ehaley
asked on
Best practices: Truncate Trans log during backup.
Hello,
Just wanted to get some feedback from the community on the best way to accomplish the task of truncating the transaction log on my SQL2k DBs during our nightly dumps..
Right now was have several nightly scheduled jobs that look pretty much like below:
BACKUP DATABASE [MYDATABASE] TO DISK = N'D:\SQL2K\MSSQL\BACKUP\My Database_b k.bak' WITH INIT , NOUNLOAD , NAME = N'MyDB backup daily', NOSKIP , STATS = 10, NOFORMAT
Obviously this doesn't truncate the log. Using SQL's builtin maintenance plan wizard has been unreliable since v6.5, and I refuse to use it.
Would the best was to truncate the log nightly be to add a second step in the scheduled job that does it only if the backup was successful?
Maybe something like:
backup log MyDatabase with truncate_only
DBCC SHRINKFILE(MyDatabase_Log, 2)
Though I've been told the using DBCC is pretty sloppy..
Suggestions, Comments all appreciated..
Thanks!
-Mike-
Just wanted to get some feedback from the community on the best way to accomplish the task of truncating the transaction log on my SQL2k DBs during our nightly dumps..
Right now was have several nightly scheduled jobs that look pretty much like below:
BACKUP DATABASE [MYDATABASE] TO DISK = N'D:\SQL2K\MSSQL\BACKUP\My
Obviously this doesn't truncate the log. Using SQL's builtin maintenance plan wizard has been unreliable since v6.5, and I refuse to use it.
Would the best was to truncate the log nightly be to add a second step in the scheduled job that does it only if the backup was successful?
Maybe something like:
backup log MyDatabase with truncate_only
DBCC SHRINKFILE(MyDatabase_Log,
Though I've been told the using DBCC is pretty sloppy..
Suggestions, Comments all appreciated..
Thanks!
-Mike-
Okay, a simple fix:
Dont do it.
why ?
By truncating the transaction log every night and shrinking it down you will be causing file level fragmentation on the HDDs with the continous shrinks/expansions of the transaction log.
It is recommended to size your Transaction log to the size required for a typical day/s ensuring that the free space within the log file is sufficient for your needs.
[Try defragmentating the database files without stopping SQL Server -- it doesnt work !... So if you don't want the down time of your database to guarantee good IO I would not do this]
Autogrowth of transaction logs should not normally be considered with shrinkages unless you are very short on disk space and need to jugle the databases until you get more storage capacity on-site.
Dont do it.
why ?
By truncating the transaction log every night and shrinking it down you will be causing file level fragmentation on the HDDs with the continous shrinks/expansions of the transaction log.
It is recommended to size your Transaction log to the size required for a typical day/s ensuring that the free space within the log file is sufficient for your needs.
[Try defragmentating the database files without stopping SQL Server -- it doesnt work !... So if you don't want the down time of your database to guarantee good IO I would not do this]
Autogrowth of transaction logs should not normally be considered with shrinkages unless you are very short on disk space and need to jugle the databases until you get more storage capacity on-site.
danblake's "Don't do it" is good advice.
But also, assuming the database is in full recovery mode, the truncate_only after a full backup precludes you from using subsequent log backups.
But also, assuming the database is in full recovery mode, the truncate_only after a full backup precludes you from using subsequent log backups.
ASKER
I think this is what I'm gonna do;
Each database's nightly job wil have two steps:
Step 1:
BACKUP DATABASE [MYDATABASE] TO DISK = N'D:\SQL2K\MSSQL\BACKUP\My Database_b k.bak' WITH INIT , NOUNLOAD , NAME = N'MyDB backup daily', NOSKIP , STATS = 10, NOFORMAT
Go to Step 2 Only if Step 1 Successfull
Step 2:
backup log MyDatabase with truncate_only
END
This is what I'm gonna run on a nightly basis. I will do a one time only DBCC SHRINKFILE just to knock the tranny logs down to a normal size.
I cannot set the tranaction logs to a fixed size cause that would cause MAJOR problems.
Good point on the disk defragmentation comment.
Any other comments or suggestions?
Each database's nightly job wil have two steps:
Step 1:
BACKUP DATABASE [MYDATABASE] TO DISK = N'D:\SQL2K\MSSQL\BACKUP\My
Go to Step 2 Only if Step 1 Successfull
Step 2:
backup log MyDatabase with truncate_only
END
This is what I'm gonna run on a nightly basis. I will do a one time only DBCC SHRINKFILE just to knock the tranny logs down to a normal size.
I cannot set the tranaction logs to a fixed size cause that would cause MAJOR problems.
Good point on the disk defragmentation comment.
Any other comments or suggestions?
Look, listen learn:
Running: backup log <database> with truncate_only
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.
....
Sounds like a bad plan so far what we have suggested. (I.e. your backups will become invalid ...)
Why keep shrinking the log file ?
It costs time/ops to grow the log file, costs time/ops to shrink the log file, costs time/ops to backup the log file.
Above plan = logs of fragmentation.
Why is setting the transaction logs to a larger size going to cause a problem, where they have sufficient space within the file itself for the normal workloads of your system. (including reindex operatons which require a large log file to be assigned or will grow the file quite large).
Running: backup log <database> with truncate_only
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.
....
Sounds like a bad plan so far what we have suggested. (I.e. your backups will become invalid ...)
Why keep shrinking the log file ?
It costs time/ops to grow the log file, costs time/ops to shrink the log file, costs time/ops to backup the log file.
Above plan = logs of fragmentation.
Why is setting the transaction logs to a larger size going to cause a problem, where they have sufficient space within the file itself for the normal workloads of your system. (including reindex operatons which require a large log file to be assigned or will grow the file quite large).
ASKER
OK now I'm really confused!!!
So I should truncate the tran log BEFORE doin the dumps?
"Sounds like a bad plan so far what we have suggested. (I.e. your backups will become invalid ...)"
Are you balking at you own reccommendations????
"Why keep shrinking the log file ?"
I'm ONLY doing a single ONE TIME ONLY Shrink of the transaction log to bring it down to a starting size, From there it's AutoGrow.. It should grown as large as the busiest day.
Why are you telling me to set my transaction log to a fixed size? What would happen if I under estimated the size needed for a really busy day?
-Mike-
So I should truncate the tran log BEFORE doin the dumps?
"Sounds like a bad plan so far what we have suggested. (I.e. your backups will become invalid ...)"
Are you balking at you own reccommendations????
"Why keep shrinking the log file ?"
I'm ONLY doing a single ONE TIME ONLY Shrink of the transaction log to bring it down to a starting size, From there it's AutoGrow.. It should grown as large as the busiest day.
Why are you telling me to set my transaction log to a fixed size? What would happen if I under estimated the size needed for a really busy day?
-Mike-
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please close the question with a self answered result.
Nope.. .I'm pretty certain I've guided you to your answer on this one ehalvey; not a self answered with no help.
ASKER
Dan-
While I really appreciate all of the time you spent on this post, I do disagree with some of the fundamentals of your suggestions. Your comments probably did help lead me to the solution, and I will award you the points on this primary as a thank you for your efforts. However, for the sake of SQL Admins in the future that may find this thread as a possible solution to their problems I want to make sure the record reflects the fact that your solution was not what I ended up using or agree with.
Our final solution, based on Microsoft documentation and Microsoft Certification Training I took a couple weeks ago is:
Set all databases to Full recovery mode
Nightly Backup all databases to disk
Immediately after the database backups, backup the transaction log for each database to disk which defines the inactive transactions
Periodic transaction logs backups during the day.
I disagree with the idea of setting a transaction log on a system critical database to fixed size. What you refer to as "worst case scenario" is certainly worst case and should not be considered an acceptable risk in a production environment in my opinion. When a transaction log runs out of space the entire database becomes read-only which immediately stops production, company wide. We ran a transaction log backup then shrank the transaction log. This reduced the log file substantially, since it had grown out of control. We left autogrow on which caused the log expand throughout the first day it was operational. Assuming that first day was a typical day, the log file would then be large enough to handle a typical day's transactions.
As soon as the backup job runs the first night the majority of the log file is marked as inactive and is able to be written over. This makes room for the next day's transactions. If the second day is busier that the first day, the log will grow again to make room for the busier day, but very quickly you should reach a log file size large enough to handle even your busiest day's transactions, at which point the file should never grow again (in theory) and will therefore not cause OS Layer fragmentation. You are correct that fragmentation would have occurred if we had run DBCC shrinkfile every night after the backup, but the solution to the problem was not to make it a fixed size, it was to properly understand the function of the logfile backup.
You are correct in your final statement that if we had started running the log file backups every hour we would never have run out of log space. As I said, the problem was really that we did not fully understand that the difference between the log truncate only and the full log backup command.
Thank you for your time, and I hope you understand that for the sake of future visitors to this thread I wanted to clarify what exactly we found and what we now believe to be correct.
While I really appreciate all of the time you spent on this post, I do disagree with some of the fundamentals of your suggestions. Your comments probably did help lead me to the solution, and I will award you the points on this primary as a thank you for your efforts. However, for the sake of SQL Admins in the future that may find this thread as a possible solution to their problems I want to make sure the record reflects the fact that your solution was not what I ended up using or agree with.
Our final solution, based on Microsoft documentation and Microsoft Certification Training I took a couple weeks ago is:
Set all databases to Full recovery mode
Nightly Backup all databases to disk
Immediately after the database backups, backup the transaction log for each database to disk which defines the inactive transactions
Periodic transaction logs backups during the day.
I disagree with the idea of setting a transaction log on a system critical database to fixed size. What you refer to as "worst case scenario" is certainly worst case and should not be considered an acceptable risk in a production environment in my opinion. When a transaction log runs out of space the entire database becomes read-only which immediately stops production, company wide. We ran a transaction log backup then shrank the transaction log. This reduced the log file substantially, since it had grown out of control. We left autogrow on which caused the log expand throughout the first day it was operational. Assuming that first day was a typical day, the log file would then be large enough to handle a typical day's transactions.
As soon as the backup job runs the first night the majority of the log file is marked as inactive and is able to be written over. This makes room for the next day's transactions. If the second day is busier that the first day, the log will grow again to make room for the busier day, but very quickly you should reach a log file size large enough to handle even your busiest day's transactions, at which point the file should never grow again (in theory) and will therefore not cause OS Layer fragmentation. You are correct that fragmentation would have occurred if we had run DBCC shrinkfile every night after the backup, but the solution to the problem was not to make it a fixed size, it was to properly understand the function of the logfile backup.
You are correct in your final statement that if we had started running the log file backups every hour we would never have run out of log space. As I said, the problem was really that we did not fully understand that the difference between the log truncate only and the full log backup command.
Thank you for your time, and I hope you understand that for the sake of future visitors to this thread I wanted to clarify what exactly we found and what we now believe to be correct.
I believe here you have migrated from your original question; and the answers that I provided are for the question that was asked.
you asked for the best practices of truncating the transaction log + then shrinking of the actual transaction log itsslf.
As you will note; I preventing you from truncating the transaction log after a full backup ; removing record entrys requiring you to create a full backup afterwards as you were using the truncate_only option. The shrink you are no longer running, and should not be running.
So I should truncate the tran log BEFORE doin the dumps?
"NO --- is what this post has done; even the MS advice gives that out !"
The advice posted here, I still stand by it is a shame we could not as what I would say reach a common communicaiton language that both of us was happy to understand.
The solution given here from MS of:
ur final solution, based on Microsoft documentation and Microsoft Certification Training I took a couple weeks ago is:
Set all databases to Full recovery mode
Nightly Backup all databases to disk
Immediately after the database backups, backup the transaction log for each database to disk which defines the inactive transactions
Periodic transaction logs backups during the day
Is exactly the solution I have given above of:
<So you would be doing the following>
Step 1: <Weekly>
BACKUP DATABASE [MYDATABASE] TO DISK = N'D:\SQL2K\MSSQL\BACKUP\My Database_b k.bak' WITH INIT , NOUNLOAD , NAME = N'MyDB backup daily', NOSKIP , STATS = 10, NOFORMAT
Step 1: <Hourly or every two hours>
backup log MyDatabase
It is still possible to create a more problematic DOS attack by filling up the Log files and the hard-disk drives completly if you are not having a maximum fixed size.
All said, I'm standing by what I've posted -- its sometimes difficult with forums to guage the posters level of knowledge and what they do / dont understand.
All in- all I'm happy with the fact I've preventing you having a backup / restore scenario that would render your backups inoperable for restoration use.
you asked for the best practices of truncating the transaction log + then shrinking of the actual transaction log itsslf.
As you will note; I preventing you from truncating the transaction log after a full backup ; removing record entrys requiring you to create a full backup afterwards as you were using the truncate_only option. The shrink you are no longer running, and should not be running.
So I should truncate the tran log BEFORE doin the dumps?
"NO --- is what this post has done; even the MS advice gives that out !"
The advice posted here, I still stand by it is a shame we could not as what I would say reach a common communicaiton language that both of us was happy to understand.
The solution given here from MS of:
ur final solution, based on Microsoft documentation and Microsoft Certification Training I took a couple weeks ago is:
Set all databases to Full recovery mode
Nightly Backup all databases to disk
Immediately after the database backups, backup the transaction log for each database to disk which defines the inactive transactions
Periodic transaction logs backups during the day
Is exactly the solution I have given above of:
<So you would be doing the following>
Step 1: <Weekly>
BACKUP DATABASE [MYDATABASE] TO DISK = N'D:\SQL2K\MSSQL\BACKUP\My
Step 1: <Hourly or every two hours>
backup log MyDatabase
It is still possible to create a more problematic DOS attack by filling up the Log files and the hard-disk drives completly if you are not having a maximum fixed size.
All said, I'm standing by what I've posted -- its sometimes difficult with forums to guage the posters level of knowledge and what they do / dont understand.
All in- all I'm happy with the fact I've preventing you having a backup / restore scenario that would render your backups inoperable for restoration use.
I run the shrinkfile every morning and have had no issues. It is part of my morning routine - coffee, check the backups, check the jobs, perform a restore, shrink the log files....etc.