June 11, 2012 at 1:17 pm
Hi,
I got a call from a remote site. They say thier trans log is now >40Gig. They claim this happens during the day. Although this is a sharepoint Db, with few transactions.
They said up until last week, the log was always around 5gig (normal).
I remoted in and looked at the LDF file itself which shows 40gig. When I look at the disk usage -(report, disk usage) it only shows 2 gig.
So, I'm assuming the 40gig file is allocated?
How can I show that it's allocated and how do I shrink? (if that's what I should do next)
Can I set the allocation size?
note- they are not using SQL to backup, they have to use a product called 'net app' because it's a VM.
June 11, 2012 at 1:32 pm
I do hope they're not taking snapshot backups of the VM and expecting those to work as SQL backups. That backup 'strategy' is not supported.
Please read through this - Managing Transaction Logs[/url] and this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
June 11, 2012 at 1:50 pm
Thanks Lynn.
That is exactly what they are doing.
Anything I can do to help them?... Other than "not supported too bad".
And
Do you know of any documentation I can use to support "do not use the snapshot vm method"? I really will need something to show my boss.
June 11, 2012 at 1:56 pm
krypto69 (6/11/2012)
Thanks Lynn.That is exactly what they are doing.
Anything I can do to help them?... Other than "not supported too bad".
And
Do you know of any documentation I can use to support "do not use the snapshot vm method"? I really will need something to show my boss.
Except, Gail is the one who answered you, not me.
I'd look at setting up SQL backups for the databases.
June 11, 2012 at 1:58 pm
krypto69 (6/11/2012)
Thanks Lynn.
Err...???
That is exactly what they are doing.
Anything I can do to help them?... Other than "not supported too bad".
Yes, set up proper backups according to the business's RTO and RPO requirements.
"not supported too bad" = "I don't care if your system fails and you can't recover it"
Do you know of any documentation I can use to support "do not use the snapshot vm method"? I really will need something to show my boss.
Yes, and google should turn it up fairly easily. Microsoft Virtualisation support or similar
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
June 11, 2012 at 2:00 pm
Oops! Sorry my apologies to both...long day.
So, bottom line -
Using NetApp to backup is not supported by Microsoft, correct?
Only real solution is to create 'normal' SQL backps
June 11, 2012 at 2:04 pm
That's not what I said. Taking snapshot backups of the VM and expecting to be able to restore SQL with them at is not supported. Doesn't matter who or what is taking the snapshot backup.
If NetApp has a SQL Server agent that can back up the SQL Server databases (snapshot backups or normal backups) that's fine. Snapping the entire VM is not.
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
June 11, 2012 at 2:15 pm
Okay. My understanding is that they are only using NetApp to backup/snapshot those instances. Not the entire VM.
If the above is indeed the case, is there anything I can do in SQL to reduce the 40G log allocation? I thought about running a normal backup to see if the log is suddenly reduced, therefore telling me NetApp is not creating checkpoint (?).
My understanding is that if I fire off a normal SQL backup they will have to run a util in NetApp to create LSN's. I would prefer to stay out of NetApp, as I've never used before.
June 11, 2012 at 2:27 pm
krypto69 (6/11/2012)
Okay. My understanding is that they are only using NetApp to backup/snapshot those instances. Not the entire VM.
It needs to be a backup synced with SQL Server (hence appearing in the SQL backup history and error log). If it's not, then it is not a valid backup. Snapping the files without syncing with SQL gets you files that will sometimes attach and sometime not
If the above is indeed the case, is there anything I can do in SQL to reduce the 40G log allocation? I thought about running a normal backup to see if the log is suddenly reduced, therefore telling me NetApp is not creating checkpoint (?).
Full backups don't do squat to the log and SQL automatically runs checkpoints on a regular basis.
GilaMonster (6/11/2012)
Please read through this - Managing Transaction Logs[/url] and this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
My understanding is that if I fire off a normal SQL backup they will have to run a util in NetApp to create LSN's. I would prefer to stay out of NetApp, as I've never used before.
Errr... SQL creates LSNs, every time it writes a record to 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
June 11, 2012 at 2:41 pm
http://support.microsoft.com/?id=956893
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
June 11, 2012 at 2:46 pm
Removed until NetApp research completed... I was inaccurate. Depends on the NetApp setup.
Okay, NetApp CAN properly do backups and the like for SQL Server. What it can't do is snapshot, though it does function through SnapManager.
However, ALL documentation I'm finding on NetApp for SQL Server is completely SQL Server 2012 based. I'm not finding any effective or intelligent documentation on how to make it function in 2005. Plenty of marketing, but nothing that says "Do A, then B" type stuff.
In self-defense, until you can sit down with your Network Admins and work through making sure everything behaves itself (including log backups) on a test system, I'd schedule your own.
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
June 11, 2012 at 2:59 pm
Ahh...interesting stuff Craig.
Guess the bottom line is, I gotta setup 'normal' backups.
Thanks Lynn, Craig. Much appreciated.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply