May 3, 2011 at 10:06 am
Hi all,
I want to set my default log size to 1KB. Then each night, after a checkpoint and full backup, I would do the following:
set recovery simple
dbcc shrinkfile
set recovery full
The above would shrink the log file back to 1kb. Is there any reason to keep log info after a full backup?
Thanks,
Mike
May 3, 2011 at 10:12 am
After latest full backup there is no need of previous log or previous differential backup they are useless,whenever you change the database model then you should take first full backup then able to take log or differential,if you dont have required log or point time recovery then you can schedule log backup after every 15 mins and then truncate this log file,even simple model can increase log file size
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 3, 2011 at 10:15 am
What is your data recoverability requirement?
You are in full but not performing log backups which would indicate to me that you don't want the info in the transaction logs. In which case, I would explore leaving the database in simple recovery.
I would eliminate the shrinkfile that you are doing and grow the log file to an appropriate size, regardless of recovery model.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2011 at 10:18 am
Syed Jahanzaib Bin hassan (5/3/2011)
After latest full backup there is no need of previous log or previous differential backup they are useless,
I disagree with this statement. Point in time recovery prior to the latest full backup is far from useless. Also, the full backup does not negate the log chain and thus if the full backup is corrupt - you would still want to have those log backups - if they were done in the first place.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2011 at 10:29 am
All,
I am currently doing a full backup at 3am and log transaction backups every hour from 5 am - 6pm. I also have a second server running with all production databases mirrored to the second server.
Will reducing the log shrink the backup file size?
Mike
May 3, 2011 at 10:36 am
If you truncate the t-log you will not be able to use a previous full backup to start a recovery if a more current full backup file turns out to be corrupt.
Also, shrinking the t-log every night just requires SQL Server to grow it again the next day. You should size the transaction log such that it is large enough to handle your daily transactions without having to grow daily. The constant grow/shrink routine can also result in file fragmentation that can impact performance.
May 3, 2011 at 10:36 am
mike 57299 (5/3/2011)
All,I am currently doing a full backup at 3am and log transaction backups every hour from 5 am - 6pm. I also have a second server running with all production databases mirrored to the second server.
Will reducing the log shrink the backup file size?
Mike
If you mean shrinking the transaction log prior to performing a full backup - it could. It depends on how many transactions occur between the time of last tran log backup and the full backup. It is best to leave the tran log at an appropriate size so that you don't need to shrink it and that the tran log doesn't grow.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2011 at 10:40 am
SQLRNNR (5/3/2011)
Syed Jahanzaib Bin hassan (5/3/2011)
After latest full backup there is no need of previous log or previous differential backup they are useless,I disagree with this statement. Point in time recovery prior to the latest full backup is far from useless. Also, the full backup does not negate the log chain and thus if the full backup is corrupt - you would still want to have those log backups - if they were done in the first place.
Fully agree with you Jason.
Edit: Bit by the quote bug. Fixed with appropriate quote.
May 3, 2011 at 10:43 am
SQLRNNR (5/3/2011)
mike 57299 (5/3/2011)
All,I am currently doing a full backup at 3am and log transaction backups every hour from 5 am - 6pm. I also have a second server running with all production databases mirrored to the second server.
Will reducing the log shrink the backup file size?
Mike
If you mean shrinking the transaction log prior to performing a full backup - it could. It depends on how many transactions occur between the time of last tran log backup and the full backup. It is best to leave the tran log at an appropriate size so that you don't need to shrink it and that the tran log doesn't grow.
I have had to do this at times at a previous employer when attempting to restore to a space restricted server. I would restore to an intermediate server, truncate and shrink the t-log, then take another full backup of that database before I could restore to final target server.
May 3, 2011 at 11:15 am
If you truncate the t-log you will not be able to use a previous full backup to start a recovery if a more current full backup file turns out to be corrupt.
If I had to, I thought I could restore 2 days ago full .bak file, then apply all transaction log .trn files bypassing the backup from last night. Why would this not work if yesterday's full .bak was corrupt?
May 3, 2011 at 11:20 am
It should wok if you have all the tran logs and db is not in simple mode.
May 3, 2011 at 11:23 am
mike 57299 (5/3/2011)
If you truncate the t-log you will not be able to use a previous full backup to start a recovery if a more current full backup file turns out to be corrupt.
If I had to, I thought I could restore 2 days ago full .bak file, then apply all transaction log .trn files bypassing the backup from last night. Why would this not work if yesterday's full .bak was corrupt?
If you are truncating the transaction log and switching the recovery model to simple and then back to full, you are breaking the log chain.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2011 at 11:33 am
mike 57299 (5/3/2011)
If you truncate the t-log you will not be able to use a previous full backup to start a recovery if a more current full backup file turns out to be corrupt.
If I had to, I thought I could restore 2 days ago full .bak file, then apply all transaction log .trn files bypassing the backup from last night. Why would this not work if yesterday's full .bak was corrupt?
It'll work just fine, providing you have not truncated the log (switch to simple recovery or an explicit log truncation). That's why people are advising not truncating the log.
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply