October 15, 2008 at 11:45 am
Hi Friends,
I know this may sound stupid but my question and understanding is that whatever the log-size you have for a database (the actual .ldf file size) will be a part of the backup file. That is the reason lot of DBA's backup the transaction log and shrink the log file size to min and then take a full backup.
Is it true that only the active portion of the log file included in the backup file. Say the log file size (doo.ldf) is around 100GB and the active log is 19GB so only 19GB will the part of the full back(.bak)
Thanks in advance!!!1
Razi, M.
http://questivity.com/it-training.html
October 17, 2008 at 4:24 pm
when the full backup of the database is fired, the time the it started to the time it completed, that part of the transaction is backed up in a fullbackup.
You can only shrink the transaction log backup after taking a backup of the transaction log
October 17, 2008 at 5:01 pm
Thanks- I didnt get you. I know that when you fire a full backup the active portion of the log is also backed up. But my question was is the actual .ldf file also included in the .bak file.
Since I happened to restore a backup on some other server and the ldf there was similar to the size on source:::)
Razi, M.
http://questivity.com/it-training.html
October 17, 2008 at 5:02 pm
Please read the following article: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
You should not shrink the transaction log on a regular basis. And no, a database backup does not back up the transaction log. The only way to backup the transaction log is to run BACKUP LOG.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 17, 2008 at 7:52 pm
You are partly correct.
Here is what happens on a full database backup
1. lock database blocking all transactions
2. place a mark in the tran log
3. release the database lock
4. back up all data pages in the database
5. lock database, mark transaction log, unlock database
6. write all the transactions between the two marks to the full backup file (but they also stay in the log file)
As someone wrote earlier, the only way to backup a log file is to specifically backup the log file.
Cheers!
Craig
Craig Outcalt
October 19, 2008 at 2:09 am
SQLBOT (10/17/2008)
As someone wrote earlier, the only way to backup a log file is to specifically backup the log file.
to clarify further the only way to truncate log at backup is to use
BACKUP LOG
only available whilst recovery mode is set to FULL or BULK-LOGGED
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2008 at 11:35 am
And don't shrink the log or database regularly. This is an emergency or rare utility for when something irregular happens.
You need to have regular full and LOG backups.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 19, 2008 at 1:48 pm
i slightly differ on that...if it is full logged and your disk is getting full, shrink is the option left. Not regularly but when have a downtime try to shrink..it is your decision based on the space available in disk and the rate the tx log is growing..
any contradictions or suggestions ??
October 19, 2008 at 4:37 pm
nilmov (10/19/2008)
i slightly differ on that...if it is full logged and your disk is getting full, shrink is the option left. Not regularly but when have a downtime try to shrink..it is your decision based on the space available in disk and the rate the tx log is growing..any contradictions or suggestions ??
Actually, I do disagree with this. If you are managing your transaction log appropriately (e.g. regular schedule transaction log backups) and you are running out of disk space - you need more disk space. If you shrink the transaction log, it is just going to grow again and this time will probably fill the disk completely.
The only time you should ever consider shrinking is if something out of the ordinary has occurred. If so, then - once that issue has been identified and resolved - you can shrink the file back to the normal size.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 19, 2008 at 5:07 pm
Jeffrey Williams (10/19/2008)
nilmov (10/19/2008)
i slightly differ on that...if it is full logged and your disk is getting full, shrink is the option left. Not regularly but when have a downtime try to shrink..it is your decision based on the space available in disk and the rate the tx log is growing..any contradictions or suggestions ??
Actually, I do disagree with this. If you are managing your transaction log appropriately (e.g. regular schedule transaction log backups) and you are running out of disk space - you need more disk space. If you shrink the transaction log, it is just going to grow again and this time will probably fill the disk completely.
The only time you should ever consider shrinking is if something out of the ordinary has occurred. If so, then - once that issue has been identified and resolved - you can shrink the file back to the normal size.
In addition, if this is the case, you may still want to look at shrinking the transaction log, but also look at increasing the frequency of your transaction log backups to keep the log from growing too much.
😎
October 20, 2008 at 7:24 am
Shrinking the transaction log regularly doesn't buy you time, it grows again as the two above mentioned.
The only time you shrink it is when some rare, one-time event (big load, update, etc.) causes it to grow abnormally and you want to regain that space for other purposes.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 20, 2008 at 9:59 am
Steve Jones - Editor (10/20/2008)
Shrinking the transaction log regularly doesn't buy you time, it grows again as the two above mentioned.The only time you shrink it is when some rare, one-time event (big load, update, etc.) causes it to grow abnormally and you want to regain that space for other purposes.
Actually the biggest reason I have seen for needing to shrink the tlog file is because the user has done only FULL backups on a database in FULL recovery mode. I see this 1-2 times per week on average on other forums. :w00t:
Also to answer on of the OPs early questions, AFAIK the tlog will be as big as it physically is (even if completely empty of actual transactions) at backup time when you do a restore.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 20, 2008 at 11:04 am
i have a citrix server database and it has mostly inactive transactions and also it grows tremendosly . i have the disk space of 200 GB for log drive and it reaches 200GB around a month . though i do full backup everyday and tx log backup every 4 hrs, the drive grows fast.
is there any clue what can be done other than frequent shrinks..
October 20, 2008 at 11:13 am
nilmov (10/20/2008)
i have a citrix server database and it has mostly inactive transactions and also it grows tremendosly . i have the disk space of 200 GB for log drive and it reaches 200GB around a month . though i do full backup everyday and tx log backup every 4 hrs, the drive grows fast.is there any clue what can be done other than frequent shrinks..
Assuming you really are doing full and tlog backups as stated the only reason I can see for a tlog file to continue to grow is that there are transactions held open for very long periods of time that continually add data to the log. otherwise the tlog backup should flush committed transactions from disk and release that space back for future use, thus preventing continuous growth.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 21, 2008 at 6:50 am
my this has gone a little OT hasnt it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply