December 31, 2008 at 8:02 am
GilaMonster (12/31/2008)
Full backup's not going to fix the problems the shrink caused,
Gail, i see where Paresh has gone with this. He advises that after truncating the log and breaking the log chain he does a full backup so its unbroken again and log backups may resume. As you say though it dosent get round the shrink part and whatever happens backup wise you still dont want to be just throwing log records away!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 31, 2008 at 8:08 am
Perry Whittle (12/31/2008)
Gail, i see where Paresh has gone with this. He advises that after truncating the log and breaking the log chain he does a full backup so its unbroken again and log backups may resume.
Yes, but why truncate in the first place, let along recommend "Reguraly take transaction log with truncate only"?
Truncate should be an exceptionally rare operation (not to mention that it's deprecated and doesn't even work on SQL 2008). It's another of those last resorts that people keep recommending as a first step without mentioning all of the downsides, conditions and risks.
Moreover, in the first post there was no mention of full backups.
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 8:12 am
I honestly think this thread has gotten a little out of control. I think Brano just needs to read Gail's link she posted earlier and determine whether they need to add regular transaction log backups or change the db to Simple mode and forget about it. We have Sharepoint here and we perform transaction log backups every 30 minutes and a full backup at midnight. That seems to keep the Sharepoint config database log file at a reasonable size.
December 31, 2008 at 8:13 am
Gail, who knows i'm with you on this!
BTW happy new year 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 2, 2009 at 7:47 am
Thank you very much to all,
It is very insightful, even parts of discussion not 100% connected with my topic. I use every information to learn something, never know when you will need something.
What confused meat first is Microsoft :(, since their explanation is:
"...A full database backup backs up the whole database. This includes part of the transaction log so that the full database backup can be recovered. Full database backups represent the database at the time the backup finished...."
That's why I thought all the time that I am backing transaction log as well. Now is much clearer, plus good start with Gail's article.
My solution will be based on transaction log backup as often as needed. I a will inform you soon about results here.
Regards, and Happy New Year :)!
Brano
January 3, 2009 at 7:08 am
branovuk (1/2/2009)
What confused meat first is Microsoft :(, since their explanation is:"...A full database backup backs up the whole database. This includes part of the transaction log so that the full database backup can be recovered. Full database backups represent the database at the time the backup finished...."
That's why I thought all the time that I am backing transaction log as well.
🙂 I'll explain.
A full backup does backup the transaction log, but not the entire thing. Just enough so that when the DB is restored, it can be brought into a transactionally consistent state.
What the full backup does not do, it truncate the transaction log. It's the truncation that discards inactive log records and allows the space in the log to be reused. The only thing that truncates the log in full recovery is a BACKUP LOG.
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
January 3, 2009 at 3:48 pm
Thank you Gail, it worked. And I read your article, it is great too. With backup transactional log couple couple times per day for last three days, log is reduced to just 10-15 MBs. It is nice, comparing to 150GBs (some of December logs). And as always, some new question are arising. Let me ask here, and not open new topic: what is best combination for backup: full+diff, or some combination with log backup as well. In addition, any good article? Thanks, Brano
January 3, 2009 at 6:55 pm
excellent post...i have learned many new things...thank you Gail for sharing your knowledge with us..with this i just need to say one thing...one of the developers told me that he created a db with 270Gb assisgned to log file..i think he did it bcoz there were many transactions gng on..he asked is there a way to get this log file down....first i suggested him to do a full backup and then restore it with a new name...this will create a new db with all data with default size for log..Gail i dnt know whether i was right or not..from my understanding of this post..he cld only stop increasing the size of his log fule by doing frequent log backups ...but wat should he do if he needs to reduce the log file size..thanks in advance and wish you all a very happy and prosperous new year..thanks
January 3, 2009 at 7:18 pm
Iqtedar: Does your developer need to do a point in time restore with his database..only in that case does he need to put the database in full recovery mode..which calls for frequent transaction log backups..if not he could use the simple recovery mode and be fine with just doing a full nightly backup....Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 4, 2009 at 2:35 am
iqtedar (1/3/2009)
but wat should he do if he needs to reduce the log file size..thanks in advance and wish you all a very happy and prosperous new year..thanks
A once off shrink file. Look up DBCC ShrinkFile in Books Online.
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
January 4, 2009 at 8:05 am
but the log file was assigned 270gb when the db is created...i don't think i can shrink the size of log file below 270 gb...and..if i just take a full backup and restore it immediately..that might reduce the size of log file..suggest me..thx
January 4, 2009 at 8:48 am
iqtedar (1/3/2009)...one of the developers told me that he created a db with 270Gb assisgned to log file.
Firstly, the root cause of the issue is having allowed a developer to create a database, that should never happen.
On the other hand, you can workaround the issue by...
1- Taking a full backup.
2- Dettaching all datafiles on the offending database
3- Delete or rename TLog datafiles so SQL Server would not be able to find it/them.
4- Creating a new database "for attach" with no TLog so SQL Server would build a new one for you.
_____________________________________
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.January 4, 2009 at 5:59 pm
ok ..i will ask him to do...so then what would be the default size of the log file...thanks..
January 4, 2009 at 11:32 pm
branovuk (1/3/2009)
Let me ask here, and not open new topic: what is best combination for backup: full+diff, or some combination with log backup as well.
Sorry, just noticed this now.
It depends.
The best combination for backups depends on how much time you have to make backups, how much down time you are permitted if you need to restore and how much data you are allowed to lose in the case of a disaster.
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
January 5, 2009 at 12:26 am
iqtedar (1/4/2009)
ok ..i will ask him to do...so then what would be the default size of the log file...thanks..
:w00t: It looks to me that you are planning to fix the issue of having developers creating databases by asking developers to do database maintenance.
Your life would be much easier if you take out those privileges from developers, let them do what they are supposed to do and try to keep them away from the database side. Just my 2 cents. 😉
_____________________________________
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.Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply