June 4, 2011 at 2:15 am
Let me start off by saying I'm still extremely new to SQL Server and most of my interactions with it are usually "trial by fire" types...
So I have a maintenance plan set to do these things:
-Runs transaction logs of all db's (every 20 minutes)
-Runs full backups of all db's (once a day at a given time)
-Remove old backups
All backups are being dumped on another server.
We basically have 6 tiny db's running web apps we've purchased (for example, a photo gallery, a mailing list, etc, that can be measured in MB) and one MONSTER db running our course/learning management system that we purchased and installed (that is nearing the 200 GB mark).
-----------------------------------
Short Explanation of Problem:
Backups were running smoothly. Now they're not. Full backups of all DB's aren't running--something's getting stuck with MonsterDB. If I look at the folder of backups and the file creation times, I see four small ones ran and took about a minute a piece, and then MonsterDB ran and took four HOURS (normal) and then... no other backups (not normal). Any ideas on what's going on?
-----------------------------------
TMI/Long Explanation of Problem:
Backups ran smoothly up until a couple days ago, when for an unknown reason, the server backups were being dumped to suddenly disconnected itself from the network. We didn't realize it right away, and backups didn't run for about two days.
The monster DB grew 20GB OVERNIGHT--I'm guessing this is because transaction logs didn't run?--which is what alerted us to the disconnect problem. Anyway, we fixed the disconnect problem, and the transaction log part of the plan started to run as scheduled--the FIRST transaction log for MonsterDB was 20GB, and then each one after that was a normal/typical size.
When full backups ran, not all backups were run. Only four small ones ran, and then something seems like it got stuck on MonsterDB, though I don't see any errors. (Also? The actual MonsterDB is about 179GB, it's .bak file was only 126GB...)
I tried manually running a backup of just MonsterDB. Five hours later, it seemed to finish (.bak file didn't grow any larger at 135GB), and all the backups I was missing from the maintenance plan seemed to run/appear. Except, the screen that pops up to manually execute a maintenance plan was still saying "1 Remaining"/In Progress... like something was still running but nothing was going on...
------------------------------
If anyone can offer help/suggestions it would be greatly appreciated!!!!
Thanks,
Jen
June 4, 2011 at 9:34 am
It sounds like you are backing up across the network - which is most likely the largest part of the problem. If possible, change the backups to local storage and then copy the backups to the network location.
If that is not possible, I would then recommend looking at Redgates backup utilities. They will compress the backup files before sending the data across the wire and can be setup to run multiple threads which will speed up the backup.
If you are using maintenance plans, and have set the option to backup all databases - you might want to consider changing that and explicitly checking the box for each database. I recall issues with the maintenance plans that would cause issues if you were not on the latest patched version. What version are you on - you should be on at least SP3, but SP4 has been available for quite a while now.
And finally, there are a couple of other maintenance items that I didn't see in your plan. Are you performing daily integrity checks, rebuilding indexes that are fragmented and updating statistics on a regular basis? This won't change how fast the backups are - but it can help with performance. The integrity checks are the most important - as that tells you whether or not there is any corruption.
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
June 6, 2011 at 11:05 am
Thank you so much for your reply!! You've given me a lot to look into.
We ARE backing up across the network, but unfortunately, that's unavoidable. We'll definitely look into Redgate!!
We're on SP3, so I changed the "Backup All" option to "Selected DB's" and checked all the databases like you said... that seems to have solved that particular problem!
Integrity testing, rebuilding indexes, and updating statistics... are things I was not aware of. (I'm an experienced web dev gal, but somewhat of a noob at SQL Server...) Thank you so much for mentioning these--we'll be building them into our maintenance plan now! 😀
Thanks again!!!!!
Jen
June 6, 2011 at 11:24 am
jen 40584 (6/4/2011)(Also? The actual MonsterDB is about 179GB, it's .bak file was only 126GB...)
The full backup only backs up data pages in use plus a piece of the log, not the free space. Your mdf file might be 179gb but the data size is only using 126gb so your backup will be ~126gb.
June 6, 2011 at 11:27 am
use [MonsterDB]
go
exec sp_spaceused
This will give you an idea of how much space your database is using.
(I am not implying nor am I recommending shrinking your database)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply