| Author |
Topic |
|
pdset
Constraint Violating Yak Guru
310 Posts |
 Posted - 2010-07-13 : 00:21:40
|
| Recently the Database got grown more than 50 GB and got trimmed. After getting the Data files Shrunk at the time of procuring the regular backup the data files got gained the previous sizes before it got shrunk.The server is 2000, and I could not understand why as a regular backup will cause the MDF and LDF to hike in the sizes. Can anyone clarify this scenario.Many Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-07-13 : 00:41:46
|
| Yes I am also of that impression and never happened. I have checked out is there any other process involved but no, the moment I have shrunk after taking the backup I have cleared myself of growing the data files to its previous position.Any more help please. Thanks. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-13 : 02:58:01
|
| If you are taking regular transaction log backups look at the sizes of those files, when did they grow suddenly? what task ran at that time?Hopefully you are running tranaction log backups every 15 minutes, or maybe every hour, so that will narrow down the time when it occurred. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-13 : 13:22:46
|
Yes, I agree, that's the normal culprit - particularly as the Maintenance Wizard is only capable of (I believe??) rebuilding all indexes, every time  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-07-13 : 15:49:58
|
quote:
Originally posted by Kristen
Yes, I agree, that's the normal culprit - particularly as the Maintenance Wizard is only capable of (I believe??) rebuilding all indexes, every time
That was true with SQL 2000 and before maint plans, but in 2005 and later, you can select all or specific tables to be reindexed.You also have the option to do index defragmentation for all tables or specific tables.You do not have the option to defrag or rebuild specific indexes for a table, although you could to in in a TSQL Statement task within the maintanance plan.Maint plans in 2005 and later are very different than 2000 maint plans. 2000 maint plans are based on the master.dbo.xp_sqlmaint stored procedure, while 2005 maint plans are really a special kind of SSIS package. I would say that thay are more flexible at the cost of being a bit more work to setup.CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-07-13 : 15:55:29
|
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by Kristen
Yes, I agree, that's the normal culprit - particularly as the Maintenance Wizard is only capable of (I believe??) rebuilding all indexes, every time
That was true with SQL 2000 and before maint plans, but in 2005 and later, you can select all or specific tables to be reindexed.You also have the option to do index defragmentation for all tables or specific tables.You do not have the option to defrag or rebuild specific indexes for a table, although you could to in in a TSQL Statement task within the maintanance plan.
Right, but does it allow you to defragment at a certain threshold (say 50% fragmentation and greater than 1000 pages) and do it online where possible and offline in other cases. Only custom code can do that currently.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-07-13 : 16:04:11
|
quote:
Originally posted by tkizer
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by Kristen
Yes, I agree, that's the normal culprit - particularly as the Maintenance Wizard is only capable of (I believe??) rebuilding all indexes, every time
That was true with SQL 2000 and before maint plans, but in 2005 and later, you can select all or specific tables to be reindexed.You also have the option to do index defragmentation for all tables or specific tables.You do not have the option to defrag or rebuild specific indexes for a table, although you could to in in a TSQL Statement task within the maintanance plan.
Right, but does it allow you to defragment at a certain threshold (say 50% fragmentation and greater than 1000 pages) and do it online where possible and offline in other cases. Only custom code can do that currently.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
2005 maint plans do allow online indexing and sort in tempdb, but not the selection of tables to be re-indexed based on dynamic criteria. CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-07-13 : 16:14:21
|
| Right but does it make the correct decision each time? Or if you select online option, does it try to do it online for every index regardless if it isn't supported such as with a clustered index that has a blob data type?The dynamic criteria is critical. No need to rebuild indexes unless it meets certain criteria. On systems with high availability requirements, you want to do the least amount of work that is needed so you don't want to rebuild indexes on large tables that aren't fragmented enough.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-07-13 : 16:39:28
|
| Tara, the standard Rebuild Index Task in the maintenance plan does not have the kind of flexibility you are looking for. However, there is a task that is: Execute SQL Task - which allows you to call a stored procedure or any SQL code you want.I have found that for most systems, I can use the standard integrity checks task and backup tasks. For smaller systems where I have the maintenance window available, I'll use the rebuild indexes task and update statistics tasks weekly (usually don't need them more than that on smaller systems). On larger systems, I use an Execute SQL Task to perform 'smart' reindexing and will use an Execute SQL Task to update statistics. For this, I'll call sp_updatestats with the @resample parameter set - or I'll use a straight UPDATE STATISTICS code to update the way I want.So, basically - there really isn't anything you cannot do in SSMS maintenance plans now.One caveat: Always make sure you have the latest client tools available. If not, you definitely will have problems working with the maintenance plans. And, make sure you are on SP2+ (minimum would be: 9.00.3054) for the database engine. If not, I would not recommend maintenance plans be used either. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-07-13 : 23:45:24
|
quote:
Originally posted by tkizer
But what's the point of using a maintenance plan if you are going to call a stored procedure? Isn't it easier to just call the stored procedure from a job step?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Not necessarily - there are various reasons for using maintenance plans. First, they are graphical representations of the steps that are performed - which are generally easier to understand.Second, you can control whether or not tasks are run based upon completion only, success or failure. A good example is whether or not your plan continues after an error when rebuilding indexes or updating statistics. Should the job fail - or continue on and perform your backup?Third, you can setup and run tasks in parallel. You can have multiple backup tasks - one for each database, and then have a cleanup step or other processing that will run once all databases have completed successfully. Or, you can have multiple branches and multiple tasks run when a task fails, etc...And yes, you can do most of this in an agent job. But, in an agent job it's not as easy to set up - and, you can't get parallel processing with dependencies. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-07-14 : 01:20:36
|
Thanks all Patrons of this Forum. I have clarified many things from this as you have rightly quoted the Index rebuilds made during the time of Backups being procured.Thanks TKizer as you always profess that Maintenance Plans can be replaced with Scripts which is wonderful prospect but having said that the otherside of effects really wonderful.May be I need to remove the Optimizations while Backups being procured.Thanks All. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 02:40:09
|
| I think it sucks that there is now even more configurability of maintenance plans.What is needed is a tool that just does a "good job" of maintaining the database. It should work in all scenarios and require pretty much no input from the person setting it up - Target folder for the backup files, some indication of the criticality of the data, and when there is a quiet period (or if there isn't one). It should only rebuild indexes that are fragmented, and should use whatever option best suits the environment and table structure, and just get on with it. People like Tara have built just that for mega-corp sized databases, why the heck is "add more bells and whistles but you now need an expert skilled-in-the-art to set it up safely" MS's chosen-way? It certainly should not just rebuild-all-indexes - or even ask you which indexes you want rebuilt. Perhaps being able to tell it NOT to rebuild some things would be handy 9although I can't think why - if they get out of shape rebuild them, if they don't ... well ... they won't get rebuilt, and that won't bloat the LDF and Backup files ...People who need something more sophisticated can build such a tool (the current maintenance plan tools certainly isn't that!), but for small-shops, with no DBA and probably no IT people either, its just a disaster-waiting-to-happen.Sorry, off topic for the OP's question. |
|
|
|
LeeRain
Starting Member
1 Post |
Posted - 2010-07-14 : 12:25:18
|
| You do not have the option to defrag or rebuild specific indexes for a table, although you could to in in a TSQL Statement task within the maintanance planLee Rain |
|
|
|
|
Backup in 2000