August 21, 2008 at 5:57 pm
which TLog backup should be we keep then, the first backup that removed only the last logical file , or the second backup that shrinks the size
I'm not following you here. No transaction log backup will ever do either of the operations you mentioned (remove virtual log files, or shrink the size of the transaction log file). Both operations were performed by the DBCC SHRINKFILE command.
You should keep both transaction log backups, to maintain the LSN restore chain.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 22, 2008 at 8:42 am
"I'm not following you here. No transaction log backup will ever do either of the operations you mentioned (remove virtual log files, or shrink the size of the transaction log file). Both operations were performed by the DBCC SHRINKFILE command."
OK here i go....
1-)Took the TLog backup (say backup1)
2-)Shrink the TLog -- gave error log file in use
3-)TLog doesnt shrink ('cause of the reason you explained in your website of backup&restore, active record was in the second last virtual file)
4-) run 'SELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = 'ABC'" -- which said "Log_backup"
4-)Took the TLog backup again (say Backup2)
5-)Shrink the TLog -- file was shrinked and reduced to 1 MB from 228 GB
So i asked which backup shud we keep (backup1 or backup2)??
also if i have scheduled a maintanence plan for this activity (backup & shrink).....it means that i will have to do it twice to get the file actually shrinked??
August 22, 2008 at 8:53 am
usman.tanveer (8/22/2008)
"I'm not following you here. No transaction log backup will ever do either of the operations you mentioned (remove virtual log files, or shrink the size of the transaction log file). Both operations were performed by the DBCC SHRINKFILE command."OK here i go....
1-)Took the TLog backup (say backup1)
2-)Shrink the TLog -- gave error log file in use
3-)TLog doesnt shrink ('cause of the reason you explained in your website of backup&restore, active record was in the second last virtual file)
4-) run 'SELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = 'ABC'" -- which said "Log_backup"
4-)Took the TLog backup again (say Backup2)
5-)Shrink the TLog -- file was shrinked and reduced to 1 MB from 228 GB
So i asked which backup shud we keep (backup1 or backup2)??
also if i have scheduled a maintanence plan for this activity (backup & shrink).....it means that i will have to do it twice to get the file actually shrinked??
You need to keep both transaction log backups.
0-) ...sometime prior to step 1, Full Backup
1-)Took the TLog backup (say backup1)
2-)Shrink the TLog -- gave error log file in use
3-)TLog doesnt shrink ('cause of the reason you explained in your website of backup&restore, active record was in the second last virtual file)
4-) run 'SELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = 'ABC'" -- which said "Log_backup"
4-)Took the TLog backup again (say Backup2)
5-)Shrink the TLog -- file was shrinked and reduced to 1 MB from 228 GB
6-) ... subsequent tlog backup(s)
7-) ... hard disk failure, need to restore database. Need most current full backup and ALL transaction log backups.
😎
August 28, 2008 at 7:23 pm
Make sure you donot have any open transactions that are currently running that can lock out any shrink options for the tran log file.
August 28, 2008 at 8:00 pm
if i have scheduled the T Log backups on regular basis (after every 1 hour) do i still need to shrink the log files after every backup??or sql server will automatically keep the log size in limits?
August 28, 2008 at 8:26 pm
Backing up the transaction log allows committed transactions to be truncated from the transaction log. Truncation doesn't affect the physical size of the transaction log.
SQL Server will shrink the transaction log automatically if the database's autoshrink option is set. Note that autoshrink will try to shrink both data and log files. Otherwise, you would need to shrink it manually.
Before that, see here for reasons why shrinking the database files may not be a good idea.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 28, 2008 at 10:05 pm
No you do not have to. You might also want to select the option of removing inactive entries from the tran logs after every backup.
August 29, 2008 at 6:19 am
No need to set auto-shrink. As Ray Mond says, it can cause more harm than good.
If you backup your Transaction Logs frequently enough that is typically adequate to stave off any uncontrolled growth. Keeping your Transaction Log at a fairly static size will enhance performance because it is neither growing or shrinking and the Database can "concentrate" on the data parts.
For the purpose of recovery, I would not recommend selecting the "Truncate Transaction Log" option in the Full Backup Maintenance Plan screen. You can do lots more with a 2 day old Full Database Backup and a bunch of Transaction Logs to bring a database to current/date time than you can with a LSN chain broken by dumping the T-log after backup up the full database.
It all depends on what your sponsors acceptable data loss expectation are.
Regards, Irish
August 29, 2008 at 6:39 am
Jeffrey Irish (8/29/2008)
No need to set auto-shrink. As Ray Mond says, it can cause more harm than good.If you backup your Transaction Logs frequently enough that is typically adequate to stave off any uncontrolled growth. Keeping your Transaction Log at a fairly static size will enhance performance because it is neither growing or shrinking and the Database can "concentrate" on the data parts.
For the purpose of recovery, I would not recommend selecting the "Truncate Transaction Log" option in the Full Backup Maintenance Plan screen. You can do lots more with a 2 day old Full Database Backup and a bunch of Transaction Logs to bring a database to current/date time than you can with a LSN chain broken by dumping the T-log after backup up the full database.
It all depends on what your sponsors acceptable data loss expectation are.
Solid advice. Do NOT set auto-shrink on, it's not a part of best practices. And, as stated, T-log backups on a regular basis is the best method of keeping your log file down to a manageable size (unless you can go with "simple" mode....) What are your user's expectation with respect to data loss? You need to ensure you can meet their expectations and plan accordingly!
-- You can't be late until you show up.
August 29, 2008 at 9:47 am
change the schema name to yor database schema and run below scripts to truncate log (without backup log file) and shrink log file:
BACKUP LOG schemaname WITH TRUNCATE_ONLY
GO
DBCC SHRINKDATABASE ( schemaname ,1, TRUNCATEONLY )
GO
August 29, 2008 at 9:54 am
:w00t: This syntax has been deprecated!!!
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 29, 2008 at 9:57 am
As it should be. Not a part of "best practices" as most of us are aware. It should only be used in rare circumstances, if at all. Size your log file accordingly and backup as often as necessary to keep the size to a minimum.
-- You can't be late until you show up.
August 29, 2008 at 11:11 am
Although i m not very much convinced in not shrinking the log file (may be because of lack of exposure to the practicle problems of shrinking)......but just in case....what should be the size of the log file then? how would we estimate that ok this could be the max limit of the log file size and btw according to my understanding auto growth will be off in this case...so if we didnt set the max size to the right value we will start getting the errors of 'log file full'
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply