September 21, 2008 at 7:31 pm
I am an "accidental DBA" at my company. Very limited DBA experience.
Our transaction logs are growing rapidly.
Veritas is our backup software and the logs are not being shrunk after the completes.
What causes transaction logs to grow rapidly? (30 GB per day)
We use SQL server 2005 as our Datawarehouse ( we extract data from peoplsoft (CRM system) and QAD ( ERP system ).
Is it OK to run this command to shrink the log files
USE [DWData]
GO
backup log DWData with truncate_only
DBCC Shrinkfile (DWData_Log);
September 21, 2008 at 7:39 pm
Make sure that Veritas is backing up the logs correctly.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 22, 2008 at 12:44 am
Are you backing up the transaction logs? I suspect that you are only doing full backups, which will not affect the t-logs. Make sure that you are backing up T-logs.
The backup command with truncate_only will clear the log and break any restore sequence. Think carefully about using it - its is not something to do unless you have to. If you have to use it make sure you backup straight after. Shrinking may be a bad idea as the log may then re-grow - possibly hitting your service.
September 22, 2008 at 1:17 am
What's the recovery requirements for that database? If the DB crashes at 1pm, is it acceptable to restore to the last full backup?
If not, then you need to run transaction log backups. As well as keeping the log small, they give you the ability to restore to the point of failure (assuming the log chain is not broken)
If you truncate the log, you break the recovery chain and will need another full backup afterwards.
It may also be useful to see how veritas is backing up the DB. It it's doing a file-system backup then the backup may not be usable. Have you tested restoring a backup ever?
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
September 22, 2008 at 1:32 am
Growth of Log file depends on Recovery model. There are three type of recovery mode
a)Full -- Rapid growth of log file
b)Bulk and -- less
c)Simple -- least grwoth
Please change your recovery model as per your business need and resouses.
If you want to continue with "Full" model and you have less space, then I will suggest to:
Step a) Take "Transaction Log" backup frequently.
Step b) It will reduce size of log file
Step c) If still size of log is big, you can shrink it.
Step d) To smooth running, please create a maintenance plan which will do all of above for you without any human involvement.
I hope it will help you to overcome the issue.
😎
September 22, 2008 at 2:29 am
James Morrison (9/21/2008)
Veritas is our backup software and the logs are not being shrunk after the completes.
A backup alone will not shrink the log files - ever.
You can free up space in the log file by running a backup, but to actually shrink the file, you need to run a shrink task after the backup. However, this is not necessarily a good idea, since your server will often just end up having to expand the log file again shortly thereafter, in which case you're just wasting time and resources shrinking and expanding the same files over and over - and making a big mess on your disk in the process, since your DB and log files will become more and more fragmented.
A better approach - if you need to be in full recovery mode - will be to schedule regular transaction log backups (I'd say at least once an hour), then try to figure out how big of a log file you need to hold transactions in between backups and just leave it at that size - permanently.
James Morrison (9/21/2008)
What causes transaction logs to grow rapidly? (30 GB per day)
When in recovery model Full, your transaction log will contain enough information to fully recreate every single transaction which is run on your database. If you have lots of updates, that's going to be a lot of logging - and recovery model Full means that the log file will only be emptied when you make a backup of it. So, if there is no transaction log backup (or it's only run like once a day or so like the full backup), the log file will just keep growing...
James Morrison (9/21/2008)
Is it OK to run this command to shrink the log filesUSE [DWData]
GO
backup log DWData with truncate_only
DBCC Shrinkfile (DWData_Log);
This will shrink the file, but it will not make any backup, since you're backing up with the "truncate_only" parameter - which means you're actually just trashing the entire log. If you just want your log to be smaller and you don't care about backups, you could run this once and then change the recovery model of the database to Simple. If, on the other hand, you do need log backups, then "truncate_only" is never a good idea - just schedule regular log backups and let them handle it.
30 GB a day translates into an average of 1,25 GB an hour, probably more at certain times of the day, less at others. There's really no way to know exactly except watching and learning. For starters, if you have hourly log backups, you could try manually setting the log size to 3 GB or so and see how it runs. Good luck to you!
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
September 22, 2008 at 2:41 am
Shubh Chandra Jha (9/22/2008)
Growth of Log file depends on Recovery model. There are three type of recovery modea)Full -- Rapid growth of log file
b)Bulk and -- less
c)Simple -- least grwoth
Please change your recovery model as per your business need and resouses.
If you want to continue with "Full" model and you have less space, then I will suggest to:
Step a) Take "Transaction Log" backup frequently.
Step b) It will reduce size of log file
Sorry sir, but that post is inaccurate. The backup will not actually reduce the size of the log file, just free up space within the log file and thereby help keep it from growing even further. If you actually need the file to be smaller, you have to shrink it after backup. However, as I just stated in the above post, it's generally not a very good idea to shrink everything as small as you can. Instead, if you have to shrink, try to find a reasonable size for your log file and leave it at that size. Yes, you can make it smaller and have the server autogrow the file up to where it needs to be, but be aware that each autogrow creates a new logical log file, so this can generate lots of overhead if the file is grown a whole bunch of times along the way.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
September 22, 2008 at 11:08 am
I too am an "accidental/involuntary DBA" facing similar issues as the OP. After considering our company's tolerance for data loss, I decided to go with Simple recovery, daily full backups and hourly differential backups. How does this strategy differ from being in Full recovery with daily full backups and hourly T-log backups? Wouldn't both allow for up to the hour recovery?
September 22, 2008 at 11:35 am
Here is what I found out.
Recovery Model is Full
Database backup is Full ( run at 9 PM)
Transaction Log backups are daily at 12 PM CST
I will work with backup team and confirm that the Transaction logs are backed up and truncated after the backup
September 22, 2008 at 11:36 am
los (9/22/2008)
I too am an "accidental/involuntary DBA" facing similar issues as the OP. After considering our company's tolerance for data loss, I decided to go with Simple recovery, daily full backups and hourly differential backups. How does this strategy differ from being in Full recovery with daily full backups and hourly T-log backups? Wouldn't both allow for up to the hour recovery?
With log backup you can do restore to a point of time. With differential backup you can’t. If for some reason you need to get the data just as it was at Sep 22 15:24, then you can get it with the log backup that was done at Sep 22 16:00, but with differential backup you’ll be able to get only the same state as it was at 15:00 or at 16:00 (assuming according to your example that you do the log or differential backup every hour on the hour).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 22, 2008 at 11:58 am
James Morrison (9/22/2008)
Here is what I found out.Recovery Model is Full
Database backup is Full ( run at 9 PM)
Transaction Log backups are daily at 12 PM CST
I will work with backup team and confirm that the Transaction logs are backed up and truncated after the backup
You can check it for yourself. Each time that any type of backup is done, it being logged in backupset table in MSDB (you can read about this table at BOL). Just run a query on this table and you’ll see if the log is backed up or not on a regular basis (my guess that it isn’t).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 22, 2008 at 1:34 pm
James Morrison (9/22/2008)
Here is what I found out.Recovery Model is Full
Database backup is Full ( run at 9 PM)
Transaction Log backups are daily at 12 PM CST
This is why your log is growing so big - it's only backed up once a day, so it grows and grows all day before being truncated.
If you need to stay with recovery model full, scheduling more frequent log backups should fix your "problem". 😉
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
September 23, 2008 at 2:29 am
los (9/22/2008)
I too am an "accidental/involuntary DBA" facing similar issues as the OP. After considering our company's tolerance for data loss, I decided to go with Simple recovery, daily full backups and hourly differential backups. How does this strategy differ from being in Full recovery with daily full backups and hourly T-log backups? Wouldn't both allow for up to the hour recovery?
Not quite. Both allow for up to the hour, yes, but the transaction log backup allows for even more than that. As has already been explained by Adi Cohn, what is unique with the transaction log is that you can use it to perform a point in time restore. Remember, with recovery model full, your transaction log will hold enough info to recreate every single transaction that's been run on your database. So, you can restore the database from a full or differential backup - and then use transaction log backups to "roll forward" from that point - essentially rerunning transactions up to a certain point in time, thus recreating the database exactly as it was at that point.
Note: Keep in mind though that in order for this to work, you must have an unbroken transaction log backup chain from the time of your most recent preceding full or differential backup and up to the point in time to which you want to restore.
A differential backup is a backup of all pages changed since your last full backup. In a way, you can think of this as a new "snapshot" of everything that's been changed since your last full backup, but it will not show what changes have been made in the meantime - only what the end result was. This means that unlike the transaction log, you can not restore only parts of this backup and roll forward from, say 10:00 to 10:42. You either have to stop at 10:00 or you have to go all the way up to 11:00 (assuming you're running differential backups on the hour). So, with recovery model simple and differential backups every hour, the absolute worst case is you might lose 59 minutes and 59 seconds, which will probably be quite acceptable in many cases. But if you have transaction log backups on top of that, you may even be able to restore those last minutes.
The advantage of having differential backups instead of just transaction log backups is that the differential backups are typically much, much smaller - and therefore can be restored faster. Remember, when restoring from a transaction log backup, you're actually rerunning transactions, whilst a differential backup lets you jump right ahead and grab a newer version of your data without having to rerun all the transactions that came in between. In a busy production environment with lots of online transactions, this could save you a significant amount of time in a restore situation, thereby reducing downtime and getting your system back up and running faster.
So, in summary, your strategy of running differential backups every hour is a sound one. If anything bad happens, you'll be able to restore up to the hour - and fast - which will satisfy many organizations, possibly most. But if you need to do even better than that, you could go to recovery model Full and schedule transaction log backups every 10 or 15 minutes, for example.
Now, lets have a look at how this could run in a disaster recovery situation:
Let's assume that your full backup runs at midnight and that you have differential backups every hour and transaction log backups every 15 minutes. Now let's assume that something really bad happens at 15:39 (3:39 pm) and you need to restore your database.
You have a full backup from midnight (00:00) the night before.
Your most recent differential backup is from 15:00 (you only need the most recent one since it holds the latest "version" of all the updated pages). Finally, you have transaction log backups from 15:15 and 15:30 (you only need the ones that are newer than your newest differential backup).
Now, before we start our restore, if your database is still online, you can get one last transaction log backup - which will allow you to to restore even the last 9 minutes. If not, then we're restoring to 15:30.
Let's assume you got a final transaction log backup at 15:45.
You can then run a point in time restore, specifying 15:39 as the time to stop (or 15:38 if you're not sure all was well at 15:39). What actually happens during this restore is that your SQL Server will first restore the full backup from 00:00, then proceed to restore the differential backup from 15:00, and then finally restore the transaction log backups from 15:15, 15:30 and 15:45, but stopping at 15:39.
When this restore is done, you'll have your database back right down to the last minute before the really bad thing happened - which kind of tends to impress people. :satisfied:
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
September 23, 2008 at 8:01 am
@Vegard
WOW! That was a very informative reply. I truly appreciate it. I can sleep a little easier now knowing that the backup strategy I have in place will work. Though, after rethinking it a bit, I think I'll increase (or is it decrease 😉 ) the frequency of my differentials from one hour to 30 minutes. Since a differential is essentially a "snapshot" of the data at the time is it necessary to retain all of the previous differentials? I understand that I should probably keep at least the differential just before the most recent one in case the most recent one is corrupt; but is it necessary to keep the entire days worth?
BTW... love the SELECT statement in your signature 🙂
September 23, 2008 at 8:26 am
Since a differential is essentially a "snapshot" of the data at the time is it necessary to retain all of the previous differentials?
It's not a snapshot of the data at that time, that's what a full backup is. A diff is all the pages changed since the last full backup.
You don't need to keep all of them, unless you want the ability to restore to an earlier time.
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 15 total)
You must be logged in to reply to this topic. Login to reply