Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
twystedpair
Starting Member
5 Posts |
 Posted - 2008-01-10 : 11:10:39
|
| I am very new to SQL Server, and inherited the database administration for my company. It was setup by a very competent admin and has been fairly self-sufficient so far. I am having a problem with my main maintenance plan on my primary SQL server. The plan starts the backup of all of our databases on that server at 8pm. To complete all of the tasks in the maintenance plan is taking about 12 hours lately, spilling into the work day. The maintenance plan first makes a backup of all databases to a local RAID. Then it does the same thing to a network server, which is then copied to tape using our backup software. The maintenance plan then deletes all backup files older than 5 days. Then it Reorgs the all User DB Indexes, and Updates all User DB Stats. There are about 40 DBs total, and one of them I know for a fact is very large (> 200GB) What is best practice in this situation to make it so that all maintenance plans finish before the work day starts?? Thanks in advance. |
|
|
stowellg
Starting Member
9 Posts |
Posted - 2008-01-10 : 11:31:10
|
| Backs up to raid, then again to the network?Perchance you might want to backup once to raid, then use a copy command of sorts to just move that to the network? No real sense in having to do the backup twice.Or if possible, talk to the guy doing the backups - see if he can't just grab the copy thats backed up to the RAID drive, if not, just backup to the network drive (unless it gets wiped, and you need to keep a copy of the previous nights backup just in case).I have a stupid question for you... |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-01-10 : 11:37:53
|
quote:
Originally posted by twystedpair
The maintenance plan first makes a backup of all databases to a local RAID. Then it does the same thing to a network server,...
Not a good idea. The plan should only make one backup, and the resulting files should be copies out to the network. Having two consecutive backups takes up more server time, and confuses the disaster recovery plan.quote:
Originally posted by twystedpair
Then it Reorgs the all User DB Indexes, and Updates all User DB Stats.
Probably overkill for a nightly process.e4 d5 xd5 Nf6 |
|
|
|
twystedpair
Starting Member
5 Posts |
Posted - 2008-01-10 : 22:15:12
|
| Thank you very much for the replies. I am going to make the changes to the maintenance plans now. One question.. I tried to delete the maintenance plan and rereate the new ones before it started for the night, but I missed it. How do I stop a maintenance plan backup that has already started?Thanks again for the help. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-11 : 00:04:03
|
| You can stop the job in ssms or em. |
|
|
|
|
|
|
|
|
Maintenance Plan Woes