January 24, 2011 at 9:04 am
Steve Jones - SSC Editor (1/24/2011)
If you are set to simple mode, transaction log backups do not work or matter. Each checkpoint (every few minutes) results in a clearing of committed transactions from the log.
Then why does the log grow to fill all storage space, to the point where the owner of the company needs to Truncate the log to reclaim storage space on the drive? I agree that the logs don't matter, but they do indeed work.
Steve Jones - SSC Editor (1/24/2011)2GB RAM is low. The backup doesn't run through the buffer pool, but I would suspect that your SQL Server might be somewhat starved for memory. Have you checked target v total SQL Server memory? If it's not close to equal, I might beg for some $$ to add memory. That's a fairly cheap change and might dramatically improve performance. Course, if it's just the backup that's slow and not performance, maybe it doesn't matter.[/QUOTE]
ok - you're over my head here with the inner workings of sql server. But I actually told him way back when he actually *bought* the equipment that 2GB for a server seemed a bit low, but again - not my call.
How do I gauge Target versus Total memory? The memory setting on the server properties is set to "Dynamically configure..." with 0 Min, 2047 Max and the the task manager is showing about 1.3GB used for the sqlservr.exe process
Steve Jones - SSC Editor (1/24/2011)If you are backing up to an external drive, the USB interface might be your bottleneck. However you'd have to experiment, and if you don't have any $$$, that might be something you live with.
I thought so too until I did the math on the transfer rates. usb 2.0 has a transfer rate of about 320Mb/s, so about 40MB/s, which for a 38GB file should about 15-20 minutes - i don't know how that translates to 12 hours. 🙁
January 24, 2011 at 9:21 am
What transfer rate are your differentials ?
January 24, 2011 at 9:47 am
The log can still grow if you have one large transaction. Without knowing what the process is, it's hard to tell, but log backups do not function in simple mode. If you have a series of committed transactions occurring, even a lot of them, then the log will not grow crazily in simple mode.
If you are in simple code, then you can't run transaction log backups. If you set a database to simple, then you get this:
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
You have something misconfigured in your system, or you are misunderstanding what is happening here.
Target v total server memory are both performance monitor counters. They should have similar values. If not, check buffer cache hit ratio and page life counters. Those can help you determine memory issues. If the former is < 90% and the latter is in seconds or low minutes, you don't have enough memory.
January 24, 2011 at 12:06 pm
Steve Jones - SSC Editor (1/24/2011)
If you are in simple code, then you can't run transaction log backups. If you set a database to simple, then you get this:
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
You have something misconfigured in your system, or you are misunderstanding what is happening here.
[/QUOTE]
I make no claim to be a DBA in any way, so it very possible i'm missing a LOT. Be that as is may, the transaction do grow, and they do create .TRN files via the backups I setup via the maintenance plans.
Steve Jones - SSC Editor (1/24/2011)
Target v total server memory are both performance monitor counters. They should have similar values. If not, check buffer cache hit ratio and page life counters. Those can help you determine memory issues. If the former is < 90% and the latter is in seconds or low minutes, you don't have enough memory.
For what it's worth the Target vs Total = are in sync, the perfmon graphs overlay each other.
the Buffer Cache hit Ratio was ranging between 99.2 and 99.6
But I found the bottleneck, Long story short - don't believe everything you're told. After consulting the manufacturers website for the system docs, the system USB controllers are version 1.1
So thanks again Steve and everyone for the insights - I did learn a lot. Funny how it always seems to go back the RTFM.
January 24, 2011 at 12:45 pm
Thanks for the update. I might go back and look at the tran log backups and databases. If the .trns are there, you might be in full mode. Not that it matters, but it is good to know for recovery time.
January 24, 2011 at 1:22 pm
fnostro (1/24/2011)
I make no claim to be a DBA in any way, so it very possible i'm missing a LOT. Be that as is may, the transaction do grow, and they do create .TRN files via the backups I setup via the maintenance plans.
Then the database is either in full or bulk-logged recovery model.
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 24, 2011 at 2:28 pm
Hey, wasn't trying to show off or anything - what I wanted to show was that you can get those backup times way down with the right configuration.
And, to be honest - I read a document from a person who's requirement was to be able to restore the system in under an hour. Their database was at 2TB and growing - and what he had to do to get the backups (and restore) down to about 30 minutes was incredible.
I understand about not having money, however - you need to realise that spending a couple of thousand dollars to reduce the backup times could save the company ten's of thousands of dollars in the long run.
With the backup taking 8+ hours, the restore is going to be at least that long and usually 1.5x as long as the backup. Can the business be down that long waiting for the database to come back up? Not sure, but not something I would be willing to bet my livelihood on.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 24, 2011 at 2:57 pm
Hey Jeffrey,
I know what you were getting at - the jibes were all good natured in intent, no worries.
if you read my last post I found the cause of the bottleneck. what I thought was a USB 2 connection was actually a USB 1.x connection. Huge difference between a 40MB/s and 1.5MB/s
So he'll have to scrap the USB drive as a b/u solution. Fortunatley there is a cheap solution for him, an SATA controller + a pair of 1.5TB drives...amazing the cost of these things today.
January 24, 2011 at 3:21 pm
fnostro (1/24/2011)
Hey Jeffrey,I know what you were getting at - the jibes were all good natured in intent, no worries.
if you read my last post I found the cause of the bottleneck. what I thought was a USB 2 connection was actually a USB 1.x connection. Huge difference between a 40MB/s and 1.5MB/s
So he'll have to scrap the USB drive as a b/u solution. Fortunatley there is a cheap solution for him, an SATA controller + a pair of 1.5TB drives...amazing the cost of these things today.
Didn't see that - but yeah, would make sense. And once you add those additional drives I would expect the backup times to drop significantly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 25, 2011 at 2:45 am
Oddly, I was about to mention that 12 hours to backup 38Gb sounds like USB 1.1 transfer rates, but I see you've found that out for yourself. 🙂
January 25, 2011 at 9:36 am
Yes, I'm still banging my head against the wall on that one.
Cheers...
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply