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 |
|
geossl
Yak Posting Veteran
85 Posts |
 Posted - 2004-10-28 : 22:26:02
|
In the maintenance plan wizard, the reorganization jobs of reindex and remove free space is performed in one single step:master.dbo.xp_sqlmaint N'-PlanID 8xxxx-8xx-4xxx-xxx-xxxxxxxxxxxx -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 ' Is this a good way to do the database reorganization? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-28 : 22:28:03
|
| Well, it's not actually doing it all in one step. sqlmaint.exe is invoked for the maintenance plans and it does the multiple steps and all of the looping and stuff. But it's not a good idea to use the maintenance plans in the first place. Here are some stored procedures that are a great (hey I wrote them so I've got to say they're great) alternative to the maintenance plans:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxTara |
 |
|
|
geossl
Yak Posting Veteran
85 Posts |
Posted - 2004-10-29 : 02:42:01
|
What is the best schedule organization of reindexing and free space removal?quote:
Originally posted by tduggan
Well, it's not actually doing it all in one step. sqlmaint.exe is invoked for the maintenance plans and it does the multiple steps and all of the looping and stuff. But it's not a good idea to use the maintenance plans in the first place. Here are some stored procedures that are a great (hey I wrote them so I've got to say they're great) alternative to the maintenance plans:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxTara
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-29 : 12:26:08
|
| I don't understand the question.Tara |
|
|
|
geossl
Yak Posting Veteran
85 Posts |
Posted - 2004-10-31 : 23:54:48
|
E.g. There are a few jobs to do: - Full backup - Log backup - Reindex - Shrink database Full backup should at least to perform once every week and Log backup everyday. How to schedule the reindex and shrink database to be optimal? Extra full backup and/or log backup is also acceptable.quote:
Originally posted by tduggan
I don't understand the question.Tara
|
|
|
|
MuadDBA
628 Posts |
Posted - 2004-11-01 : 10:23:26
|
| The "best" schedule is going to be dependent on the way you use your database, and the criticality of your data.You COULD set up transaction logs every 15 minutes and full DB backups every day, but if you don't need it, you're jsut adding complexity to the recovery plan and possibly using up a lot of space you don't need.I think you will find that Tara will recommend against doing a shrink plan. The database grew to that size because something you or a user did caused it to. It will probably happen again, and each time the database has to alter its physical allocation, it incurs a performance cost while it grows. So there's not much point unless a one-time thing happened (someone did an insert with a cartesian product and grew your db and tlog by 20GB).Reindexing (or reorganizing data and index pages as it is called in the maintenance plan) also depends on how your system is structured. Some areas need to do it every week, others every month...tell us more about what your DB is for, how big it is, how much activity it sees, and we'll see what we can do to help you figure out the best way to proceed. |
|
|
|
geossl
Yak Posting Veteran
85 Posts |
Posted - 2004-11-02 : 20:23:13
|
There is a DB with full recovery mode. Every night, all the data is refreshed from a different source. As a result, the log and database grow continually and the DB size causes a trouble of not enough disk space. For safety, it is better to have a backup of the replicated data. Any recommendation for the backup and shrink job?quote:
Originally posted by crazyjoe
The "best" schedule is going to be dependent on the way you use your database, and the criticality of your data.You COULD set up transaction logs every 15 minutes and full DB backups every day, but if you don't need it, you're jsut adding complexity to the recovery plan and possibly using up a lot of space you don't need.I think you will find that Tara will recommend against doing a shrink plan. The database grew to that size because something you or a user did caused it to. It will probably happen again, and each time the database has to alter its physical allocation, it incurs a performance cost while it grows. So there's not much point unless a one-time thing happened (someone did an insert with a cartesian product and grew your db and tlog by 20GB).Reindexing (or reorganizing data and index pages as it is called in the maintenance plan) also depends on how your system is structured. Some areas need to do it every week, others every month...tell us more about what your DB is for, how big it is, how much activity it sees, and we'll see what we can do to help you figure out the best way to proceed.
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-02 : 21:30:12
|
| Change the recovery from full to simple. It makes little sense to log all of that if you're going refresh it every night. |
|
|
|
|
|
|
|
|
Better way to reindex and remove freespace