Monday, February 20, 2012

Maintenance Plan woes

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 on this server, and at
least 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.>>...The maintenance plan first makes a backup
of all databases to a local RAID. Then it does the same thing to a
network server...<<
Why the double work? Redundancy is fine, but this seems like overkill.
You may also want to investigate Litespeed or a similar product for
compressed/faster backup and recovery...especially with that 200 GB
database.
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Matt" <matthewsatkins@.gmail.com> wrote in message
news:ac1d8d14-018c-4f3d-96c9-1870873024c1@.m34g2000hsf.googlegroups.com...
>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 on this server, and at
> least 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.|||Hi,
Firts, Do you need the backups in local disk for 5 days, if you have one
copy in tape?
second. You software backup is not possible to integrate with SQL?
anyware,
The better is develop all tasks in differents plans, this mean
1.Perform backups every days (1 plan)
2. Reorg indexes, once per week or per month it isn't necessary reorg.
Indexes every day. (1 or more plan)
But not all in the same day for exampl, Yor will reorg. indexes in 5 or 6
databases per day, another day others databases.
3. You can Perform Update statistics every day (if you want) (1 or more plan)
4. Create Jobs with t-sql to perform maintenance on your databases, one Job
for database or task. in this way you can to take control of your maintenance
plans.
Regards,
"Matt" wrote:
> 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 on this server, and at
> least 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.
>

No comments:

Post a Comment