December 30, 2008 at 12:57 pm
Hi,
I have one problem with production database. Transaction log is growing fast. I have daily backup. And it worked if I backup log file only, two times, than shrink command.
But, what is solution for it, without my every week intervention or so? How much I can limit growing transaction log (or restrict file grow) in properties and not destroying any data in same time? Any other suggestion?
Regards,
Brano
December 30, 2008 at 1:08 pm
you need to size the log file appropriately to handle transactions between backups.
what is the recovery model of the database set to at the moment?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 30, 2008 at 1:13 pm
Restricting growth will cause the database to become pretty much inoperable (except for reads) if it maxes out due to your limitations. Size it accordingly, backup it up regularly and be done with it. Growths and shrinks aren't do you any good. More overhead/manual maintenance than what it's worth in the long run.
-- You can't be late until you show up.
December 30, 2008 at 1:30 pm
Thank you for responses. Backup type is full, and it is daily. So, that part is looking good. But it is not helping in managing the log file - it is growing all the time. I did not want to change that, I was just wondering if I can limit file grow for it. I read articles about how "Restricting growth will cause the database to become pretty much inoperable" - it is scary :), but I couldn't find any specifics of how to use that "Restricted file growth" option, if it is possible.
December 30, 2008 at 1:39 pm
branovuk (12/30/2008)
Hi,I have one problem with production database. Transaction log is growing fast. I have daily backup. And it worked if I backup log file only, two times, than shrink command.
If I'm reading that correctly, you're not doing transaction log backups. Is that correct?
In full recovery, you need to be running transaction log backups regularly. If you don't the log will grow until it fills the drive.
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
December 30, 2008 at 1:49 pm
The other question would be is the database being set to FULL even necessary for this database? Would there be a time that you may need to go to a point in time restore on the database or would the nightly FULL backup always be ok. If you can change the database model to SIMPLE that would help eliminate your transaction log growth at the expensive of not having the point in time restore option.
December 30, 2008 at 1:53 pm
After reading Gail's article, you need to analyze what your business needs truly are. How much data loss can you withstand? If 30 minutes, backup your logs at a 30 minute interval, at a minimum. One hour, then hourly log backups. If point in time is unnecessary, in your business, set the database to simple and forget about it. It really depends on YOUR needs. Gail's article gives a great foundation for what the log can/will do for you. The real question, after understanding the facts, is what does your business need from you?
--edit - Sorry Steve, I was typing while you were posting.....
-- You can't be late until you show up.
December 30, 2008 at 2:23 pm
I am doing full backup, if I understand well, that means log file backup too?
The problem with this database is next: it is one of config database for SharePoint, so it is part of SharePoint application databases. And I do not know if I can go with simple recovery model, because of that. So I tried to focus on "Restricted file growth" if it is possible. Or other solution, I don't know, maybe differential backup couple times per day?
December 30, 2008 at 2:30 pm
branovuk (12/30/2008)
I am doing full backup, if I understand well, that means log file backup too?
No.
Full backup means just that. Full database backup. It does not back the log up, it does not truncate the log. Neither do differential backups.
Did you read the article I posted? If not, read it, then go to Books Online and read the section "Backup Log", as well as the sections on transaction log management.
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
December 30, 2008 at 2:44 pm
I have SharePoint on one of my servers and I cannot put it in simple mode due to the volume of data being added. Read Gail's article and see my last post. What amount of data loss can your business tolerate? You may need to go to your users, get the facts from them, memorialize it and post it to all the "powers that be" just to cover your butt in the event of failure. Your data, your job. Hope for the best but be prepared for the worst - IMO.
-- You can't be late until you show up.
December 30, 2008 at 3:14 pm
Thank you all, I will analyze all your posts (and article) before choosing solution.
Now it is time to go home :), till tomorrow!
Regards,
Brano
December 30, 2008 at 11:12 pm
branovuk (12/30/2008)
Hi,I have one problem with production database. Transaction log is growing fast. I have daily backup. And it worked if I backup log file only, two times, than shrink command.
But, what is solution for it, without my every week intervention or so? How much I can limit growing transaction log (or restrict file grow) in properties and not destroying any data in same time? Any other suggestion?
Regards,
Brano
--Check any transaction is open or not when taking a full bacup ar trn log backup, if there teh kill them.
--Reguraly take transaction log with truncate only and shrink database files.
--If u r not taking trn log backups then make recovery simple of those dataabses.
December 31, 2008 at 12:23 am
This is all-around some of the worst advice I've seen recently regarding transaction logs in quite a while.
Paresh Prajapati (12/30/2008)
--Check any transaction is open or not when taking a full bacup ar trn log backup, if there teh kill them.
Without checking what the transaction is? What if it's a critical update that must not fail? What if it's the HR people applying your salary raise or bonus?
Killing transactions is something that should only be done if the transaction is causing problems, and only after careful investigation to determine what it's running and if it's safe to roll back
--Reguraly take transaction log with truncate only and shrink database files.
Absolutely not.
Truncate only breaks the log chain. It means that any log backup taken after that point will fail. It also means that if the database faile and has to be restored, the best that can be done is restoring to the last full backup.
How many businesses will accept losing a full day's data because of a database failure?
If you shrink the log, it will just grow again when the database is next used. When the tran log grows, all queries within the DB will be affected and will run slow until the grow as completed. In addition, frequent small grows (which is what typically happen after a shrink) will cause internal log fragmentation that makes backups and restores slower than they need to be, and external file-system fragmentation, which is hard to fix.
In my opinion, truncate should never be run and shrink should be an exceptionally rare operation.
--If u r not taking trn log backups then make recovery simple of those dataabses.
Providing the business is happy with recovery constraints of simple - no point in time recovery of the database. If there's a failure, the only possible restore path is using the last full/diff backup. That typically will result in hours of data loss. Make sure that's acceptable before switching to simple.
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
December 31, 2008 at 1:27 am
GilaMonster (12/31/2008)
This is all-around some of the worst advice I've seen recently regarding transaction logs in quite a while.Paresh Prajapati (12/30/2008)
--Check any transaction is open or not when taking a full bacup ar trn log backup, if there teh kill them.Without checking what the transaction is? What if it's a critical update that must not fail? What if it's the HR people applying your salary raise or bonus?
Killing transactions is something that should only be done if the transaction is causing problems, and only after careful investigation to determine what it's running and if it's safe to roll back
--Reguraly take transaction log with truncate only and shrink database files.
Absolutely not.
Truncate only breaks the log chain. It means that any log backup taken after that point will fail. It also means that if the database faile and has to be restored, the best that can be done is restoring to the last full backup.
How many businesses will accept losing a full day's data because of a database failure?
If you shrink the log, it will just grow again when the database is next used. When the tran log grows, all queries within the DB will be affected and will run slow until the grow as completed. In addition, frequent small grows (which is what typically happen after a shrink) will cause internal log fragmentation that makes backups and restores slower than they need to be, and external file-system fragmentation, which is hard to fix.
In my opinion, truncate should never be run and shrink should be an exceptionally rare operation.
After complete this process , we should take full backup..so i think it is not risky.
--If u r not taking trn log backups then make recovery simple of those dataabses.
Providing the business is happy with recovery constraints of simple - no point in time recovery of the database. If there's a failure, the only possible restore path is using the last full/diff backup. That typically will result in hours of data loss. Make sure that's acceptable before switching to simple.
I have talk about oldest activate transactions which are remain to close or rollback/committ.
After complete truncate log and shrink databases process , we should take full backup..so i think it is not risky.
yes, recovery is simple vaible, if database usage is less or not important.
December 31, 2008 at 7:41 am
Paresh Prajapati (12/31/2008)
I have talk about oldest activate transactions which are remain to close or rollback/committ.
"Remain to close"?
Regardless of how long the transaction has been running, you don't just kill it without checking what it is and whether is can be safely rolled back.
After complete truncate log and shrink databases process , we should take full backup..so i think it is not risky.
Full backup's not going to fix the problems the shrink caused, and you said nothing about full backup in your original post.
If you are going to truncate the log on a regular basis, why is the database even in full recovery?
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 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply