September 19, 2010 at 12:10 pm
GilaMonster (9/19/2010)
May I suggest that you take a minute to look in books online for the sys.databases view to familiarise yourself with it before continuing.
Absolutely - thank you
September 19, 2010 at 5:37 pm
SkyBox (9/19/2010)
GilaMonster (9/19/2010)
May I suggest that you take a minute to look in books online for the sys.databases view to familiarise yourself with it before continuing.
Absolutely - thank you
GilaMonster,
The log_reuse_wait_desc is "LOG_BACKUP" when selecting on the appropriate database_id.
Note from BOL:
If the reason is LOG_BACKUP, it may take two backups to actually free the space.
Ideally, my company cannot afford to lose more than 15 minutes of transactions. So I do plan on saving the logs for recovering as closely as possible to the point of failure.
But first things first, I need to get my transaction log in order and stop shrinking it. I will go ahead and disable my dbcc shrinkfile job and let the log grow.
I look forward to your next post. Thanks
September 19, 2010 at 5:50 pm
Gail *may* have gone to bed already, she's across the pond, so I'll try to help in the meanwhile until she can bring her expertise back to bear. 🙂
This article may help you some, it's about the different reasons that logs may be stalled:
http://msdn.microsoft.com/en-us/library/ms345414.aspx
Handy little link.
Anyway, from that page, in specific to log_backup:
A log backup is required to move the head of the log forward (full or bulk-logged recovery models only). When the log backup is completed, the head of the log is moved forward, and some log space might become reusable.
Note: Log backups do not prevent truncation.
So, in english, you need to do backups to move the active transactions to the front of the file so you can shrink. You'll also want to remove the auto-shrinking, yes. As Gail mentioned above, you're going to be constantly reallocating space after a backup/shrink to let it grow back to your 15 minute mark.
Usually, logs get heavy during overnight loads. Get your 15 minute backups going, and then let it run for a day or two and see where it levels off at space wise.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 19, 2010 at 6:38 pm
Craig Farrell (9/19/2010)
Gail *may* have gone to bed already, she's across the pond, so I'll try to help in the meanwhile until she can bring her expertise back to bear. 🙂Get your 15 minute backups going, and then let it run for a day or two and see where it levels off at space wise.
When I create the backup transaction log job, it seems like I want to avoid overwriting or appending to the last trans log backup, correct?
Is my only option to create a script that names the trans log backups with a unique name using "getdate" or something?
Thanks
September 19, 2010 at 7:14 pm
You should be able to build a maintenance plan to do all that for you, and then automate it via SQL Agent.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 19, 2010 at 11:19 pm
SkyBox (9/19/2010)
The log_reuse_wait_desc is "LOG_BACKUP" when selecting on the appropriate database_id.
Ok.... Is it still the same after doing a log backup? If so, what's the script you're using to do the log 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
September 20, 2010 at 6:47 am
As mentioned by those above, those regular log backup will allow you you reuse the space in the log. If the log is too large, meaning that based on the interval you choose for log backups, it's way too large, then you can shrink it, but that should be a one time thing. It's possible that because of where the log is writing, you can't get it as low as you'd like. In that case, you can use this script to shrink it: http://www.sqlservercentral.com/scripts/30026/
For naming the log files, the maintenance plan can do that, or there are scripts on this site that will help you get a unique name. You do need one, otherwise you might be compromising your ability to recover.
September 20, 2010 at 9:08 am
GilaMonster
Ok.... Is it still the same after doing a log backup? If so, what's the script you're using to do the log backups?
So after backing up the trans log, the log_reuse_wait_desc is now "NOTHING".
The DBCC LogInfo returns only one row with the status of "2".
The trans log file was 2.2gb before I backed it up twice and it is still at 2.2gb.
backup script:
-- Declares a string
declare @sql nvarchar(255)
-- Declares the filename.
declare @bkdbName varchar(50)
set @bkdbName = 'DB_LOG_BKUP_' + Convert(varchar(50),getdate(),110)
-- declares and set the path.
declare @bkName varchar(255)
set @bkName = '\\Server\e$\SQLBackups\Logs\'
-- set path and db name
set @bkName = @bkName + @bkdbName
-- Sets the backup running by executing the @sql string.
set @sql = 'BACKUP LOG filename TO DISK = '''+@bkName+'.TRN'''
print @sql
exec sp_executesql @sql
September 20, 2010 at 9:14 am
Run the first log backup followed immediately by another then check DBCC Loginfo
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 20, 2010 at 9:15 am
SkyBox (9/20/2010)
GilaMonster
Ok.... Is it still the same after doing a log backup? If so, what's the script you're using to do the log backups?
So after backing up the trans log, the log_reuse_wait_desc is now "NOTHING".
The DBCC LogInfo returns only one row with the status of "2".
Excellent
The trans log file was 2.2gb before I backed it up twice and it is still at 2.2gb.
Well, yes it will. As I stated at the beginning of this thread, backups don't shrink the log, they just make the space reusable. Now that the log is being backed up properly, you can do a once-off shrink and drop it down to a reasonable size. DO NOT shrink it to 0. Shrink it to a size that's reasonable for the activity you see on your system.
Remove any automated shrink jobs, remove any jobs that explicitly truncate the 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
September 20, 2010 at 9:39 am
GilaMonster (9/20/2010)
Now that the log is being backed up properly, you can do a once-off shrink
and drop it down to a reasonable size. DO NOT shrink it to 0. Shrink it to a size that's reasonable for the activity you see on your system.
Remove any automated shrink jobs, remove any jobs that explicitly truncate the log.
I have removed the automated shrink/trunc jobs. How do I determine my reasonable size? Should I monitor it for a couple of days?
Thanks
September 20, 2010 at 10:36 am
You can look at your log backups. The size of those is a rough level of how much you are writing to the logs in between your backup interval. Give yourself some pad from your largest interval and set the log there.
Make sure that you check your maintenance operations, like index rebuilds, and see how big the log backup is after those. It might be your largest log backup
September 20, 2010 at 2:02 pm
SkyBox (9/20/2010)
backup script:-- Declares a string
declare @sql nvarchar(255)
-- Declares the filename.
declare @bkdbName varchar(50)
set @bkdbName = 'AMCH_GOLD_LOG_BKUP_' + Convert(varchar(50),getdate(),110)
...
There's a problem with the backup script you posted if you intend to backup the log every 15 minutes: the name you generate will be the same for all 96 backups on any given day, because the conversion style you are using will product a date without any time (style 110 doesn't include time, only date); you can fix this by changing the conversion style from 110 to 120.
However, I think you would do better to use the maintenance plan wizard to generate your backup script, because that will also take care of how many backups you retain, deleting backups older than a limit, and doing full backups (and incremaental backups if you want them) as well as log backups. Automatically getting rid of old backups is not particularly difficult to code, but why reinvent the wheel when MS has already done it for you?
As for size of the log file, you should shrink it once and then leave it for a few days with log backups happening at whatever interval you believe is correct for your recoverability requirement. (A few days means long enough that any frequent but not every day thing that happens to the db gets a chance to happen.) Then look at the size of the log: that's about how big it needs to be if you use that log backup interval - but you probably want to add some margin for times of unusually high activity, maybe 20% or maybe 100% depending on how much the throughput is expected to vary.
Tom
September 20, 2010 at 6:46 pm
Tom.Thomson (9/20/2010)
There's a problem with the backup script you posted if you intend to backup the log every 15 minutes: the name you generate will be the same for all 96 backups on any given day, because the conversion style you are using will product a date without any time (style 110 doesn't include time, only date); you can fix this by changing the conversion style from 110 to 120.
I was planning on modifying this, just could not get to it today. The conversion style is exactly what I was missing. Thanks
However, I think you would do better to use the maintenance plan wizard to generate your backup script, because that will also take care of how many backups you retain, deleting backups older than a limit, and doing full backups (and incremaental backups if you want them) as well as log backups. Automatically getting rid of old backups is not particularly difficult to code, but why reinvent the wheel when MS has already done it for you?
I considered using the Maint plan wizard, but couldn't decide if I should use the append vs. overwrite option. What am I missing here? I did notice the option to set the number of days to retain backups.
Thanks
September 20, 2010 at 7:21 pm
Not to throw a curve in, but I'd recommend looking at Ola Hallengren's maintenance scripts which are provided for free and regularly updated at http://ola.hallengren.com/. Better than maintenance plans in my opinion. At the least you can see how the naming is done.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply