March 2, 2012 at 8:21 am
I'm the member who asked about calling the Shrink task in a backup job. I've heard loud and clear; don't do that.
We've got a test server where I try out new things, and one of them was the new backup jobs. Following what the last DBA did (without the Shrink db task), I now do a database integrity check, backup the database and the transaction logs. The sytem DBs are checked up once a day. The user databases have a full backup done early morning, then starting at 7 AM and running once an hour until 7 PM it does transaction log backup.
I've been watching the free disk space on that server, and have noticed that we've lost 4.4 GB of free disk space in the last 3 days. For a machine that doesn't get hit much at all, I find this surprising. Have I done something wrong?
Kindest Regards, Rod Connect with me on LinkedIn.
March 2, 2012 at 8:42 am
Sounds like a good plan.
Did you allow for the backups themselves? They take space. Typically I would be moving the fulls to tape (or another server) ASAP, and then I'd keep 1 on that machine only if I have another (2 total) on a separate server. For logs, keep all since the earliest full you have, but you can move those to another server as well.
Make sure you have a maintenance cleanup job to delete the older backups (after newer ones are complete). Once you have that, space should be fairly stable.
If you're doing this, then you need to dig around and see where space is being used. Find all the new files in the last 3 days (search in Explorer), see if something stands out. If not, look for growths in your files.
March 2, 2012 at 8:43 am
How big are the databases being backed up? Are you doing any backup cleanup in your maintenance plans?
If you don't do cleanup then the number of files just grows..
A database with little activity won't grow much and the transaction logs won't be large. But look at the files you have and see whats going on.
CEWII
March 2, 2012 at 8:54 am
Also, don't back up to the same disks that your databases are on, if you can avoid it. If you lose the disk, you've lost the backup as well as the databases.
John
March 2, 2012 at 8:58 am
Agreed, as a policy I backup to a network drive, I don't even want to worry about getting the dead server running..
CEWII
March 2, 2012 at 9:29 am
WOW, everybody, great feedback! I love this website/forum!
OK, here's what I've got. None of our databases are all that large. The smallest one is only 5.44 MB in size, the largest one is 467.3 MB. Believe me, discussions about large data, Haloop, etc, just don't apply to our situation.
This server for our test database, is just a desktop that was flatten and Windows 2003 Server installed on it. There's no backup of the data, system, etc, to anywhere. Out production databases are different. The .bak and .trn files are all backed up to tape.
Steve you mentioned the clean up jobs. Yes, I do have one, and that might be what's going on. I've got a maintenance job that cleanrs the history, backup files transaction logs, but it only runs once a week (Saturdays), and it keeps the last 8 weeks of data. It hasn't run yet. Now I'm feeling kinda silly, as I bet that's what's going on. The regular backup job keeps data for 2 weeks, I think I'll change that cleanup routine to 4 weeks. Since the backups are now running more frequently, I'll be accumulating files faster than before. I think I'll still look through Explorer to see what's new on the HD in the last 3 days, but my guess its just the new number of .bak's and .trn's.
Thank you, Steve and Elliot.
Kindest Regards, Rod Connect with me on LinkedIn.
March 2, 2012 at 9:47 am
Yeah, it looks like it was the number of new .bak and .trn files. I just used Windows Explorer to check what was new in the last 3 days. Of course there's several things related to the OS and temporary stuff like that, but the big hitters are the number of new .bak and .trn files. Man, I feel foolish. Of well, better safe than sorry.
Thanks again, everyone.
P.S.: I've just checked the clean up routine to only save the last 2 weeks, but leave it to run only once a week. I think I'll continue to watch it for another couple of weeks, and if it gets a little too low on disk space towards the end of a week, then I'll change that clean up job to run more often.
Kindest Regards, Rod Connect with me on LinkedIn.
March 2, 2012 at 11:04 am
2 weeks.. yikes.. I wish I had enough disk space to save 2 weeks of backups but I jsut don't see a good ROI on 40TB of storage to accomplish it..
With that said I try to keep three days of full backups at LEAST and usually at least a full day of transaction logs. I'd like to keep a bit more but in reality I'm not going to go back more than a couple of days to recover. And some of my really critical databases are restored elsewhere everyday so I have other copies..
Also, why are you backing up to tape?
CEWII
March 2, 2012 at 1:45 pm
Why are we backing up to tape? Good question. I believe the answer is simple; because that's what we've always done. Really, that's the primary reason. Secondly, we only have 1 network share, and it's not big enough to hold all that is a part of the backup.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply