Indexing questions

Conceptual questions here...In BOL it mentions 'Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified.'. I'm wondering how this works as non-clustered indexes are built on top of clustered ones - is there in this case a significant slowdown in the speed of accessing non-clustered indexes? Also, I'm guessing here, but is the reason this might be useful is for when you want to disable and rebuild the nonclustered indexes in order to save space?
Cheers,
Paul Ibison, SQL Server MVP

Conceptually speaking, the leaf row of a non-clustered index needs to identify the data row uniquely. There are two ways to do it. First,you can use RID. The down side is that if the data row moves, the non-clustered index entry needs to be updated. Second, you can use clustered index key, if it is unique. The benefit is that if the data row moves say due to rebuild of a clustered index, nothing needs to be changed in the non-clustered index. Of course, in this case, to access data using non-clustered idnex, you will need to traverse the non-clustered index and then need to traverse the clustered index tree. Assuming that clustered index tree will be frequently accessed, it is lkely to be in the buffer pool. So you will incur few extra logical IOs per row access.

I doubt if the second method can be used to save space as it is likely that the clustered index key is longer than RID. The main benefit of the second method is that data row move does not cause the update of non-clustered index as long as the clustered key column value is not changed.

Let us see what others say.

Thanks

|||

Index rebuild (except for disabled index) does not change the logical structure of an index
(like changing a key, adding a new column, or change the sort order), it only changes the physical layout of an index on the disk. You can think of a physical index restructure, restoring

the original fillfactor and allocating the index pages in a right order removing fragmentation and compacting the index space. Therefore there is no need to rebuild the non-clustered index(es) when rebuilding a clustered index. This functionality is fully implemented in SQL Server 2005, and was already partially implemented in SQL Server 2000. We have added the keyword ALL to make sure customers can rebuild all indexes for a given table using one command (ease of use purpose). Clustered or non-clustered index rebuild may increase performance if the index is fragmented, especially when queries use

index scans.
Disabling a clustered index does not really save space. We still need to keep the old clustered index (marked as disabled) to make sure we can rebuild it. Disabling a non-clustered (NC) index brings some space benefit since we drop the old index and keep only its metadata, so when we rebuild an NC index we use either the base table or a clustered index. However this benefit can be easily reached by using two commands DROP NC index and CREATE NC index.

Thanks,

Mirek

|||Thanks Sunil,
this makes perfect sense. I hadn't seen it in this way before - if rebuilding the clustered index is only done to reorder the data (not to remove fragmentation) then there might be no performance gain from also rebuilding the non-clustered index, as the indexes are unlikely to be ordered in the same way.
Cheers,
Paul|||Thanks Mirek,
this makes sense. So in some cases where the table is large (disk space small) it might be useful to rebuild the clustered index without the ALL option, then subsequently disable and rebuild each non-clustered index.
Cheers,
Paul

indexing architechture

OK:

1. after rebuilding indexes, shouldn't sp_updatestats and DBCC UPDATEUSAGE be run for best performance?

2. What exactly are sp_updatestats and update usage doing? It looks like (from BOL) that updating usage would be updating the IAM and the page free space, and updating stats would just update the index/row pointers. Indexes are rebuilt nightly where I am currently working, however, unallocated space is consistently negative.

3. rebuilding or defragging the indexes should defrag the tables, right? As in, re-allocate free space depending on fillfactor...

4. for a reporting database, shouldn't the fillfactor be low? That way, you would have fewer page splits during loading, and as far as querying, by the time you are done with your loading, the engine should have evened out the allocation...

Rebuilding indexes (drop/create or DBCC DBREINDEX) will automatically update statistics.

If you are rebuilding indexes each night, then you probably don't need to worry about the statistics, unless you import bulk data throughout the day, truncate tables or significantly change the data distribution through large amounts of updates before the next index rebuild.

DBCC UPDATEUSAGE simply corrects inaccuracies in the sysindexes table.

sp_updatestats runs UPDATE STATISTICS on all user tables in the database.

If you rebuild a clustered index, it will effectively rebuild the table. There is an optional parameter in the reindex command to specify a new fill factor. If not specified, the original fill factor will be used for the reindex.

DEFRAG defragments the leaf level of an index using the original fill factor.

In summary, if you have the luxury of doing an index rebuild each night, then you're in a good position, and shouldn't really need to worry about defrag or statistics.

|||

THanks...however,

I don't quite understand what UPDATEUSAGE does...we have negative unallocated space on a continual basis. Our database is 156GB, and it shows up with 66GB as negative unallocated. THe only thing that fixes this is UPDATEUSEAGE -

1 - the query analyzer can't find the right query plan with -unallocated, right? I'm thinking that it doesn't have the correct IAM, but I don't know...

2- we do loads every night. Sometimes millions of rows. Unfortunately, the DBAs do the re-indexing BEFORE the load, with default fillfactor of 90. :(

(this was just a whine)

3- can someone tell me exactly what updating stats does that is different from updating useage? Inquiring minds want to know...

4- there are no updates during the day, it's read only for reporting. So, It seems to me that we should set the fillfactor low before the load - however, is it going to negatively impact the reporting? Doesn't SQL server start picking pages and extents with an algorithm that evenly distributes the data on imports and inserts?

5 - In what order should the above items be run?

Indexes not rebuilding on SQL 2K DB MAINT PLAN

I see errors in my DB Maint log that indicates indexes are not being rebuilt.
We are having production problems - time outs. Does the optimization plan
for DB Maintenence drop bad indexes - simply put, what is my best way to
handle back indexes on a production server?
--
Regards,
JamieError is:
Backup can not be performed on this database. This sub task is ignored
--
Regards,
Jamie
"thejamie" wrote:
> I see errors in my DB Maint log that indicates indexes are not being rebuilt.
> We are having production problems - time outs. Does the optimization plan
> for DB Maintenence drop bad indexes - simply put, what is my best way to
> handle back indexes on a production server?
> --
> Regards,
> Jamie|||thejamie wrote:
> Error is:
> Backup can not be performed on this database. This sub task is ignored
That has nothing to do with indexes... This error is telling you that
you are attempting to do a transaction log backup against a database
that is in Simple recovery mode.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||That particular database is a production database. Why would it be in simple
recovery mode?
--
Regards,
Jamie
"Tracy McKibben" wrote:
> thejamie wrote:
> > Error is:
> > Backup can not be performed on this database. This sub task is ignored
> That has nothing to do with indexes... This error is telling you that
> you are attempting to do a transaction log backup against a database
> that is in Simple recovery mode.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||thejamie wrote:
> That particular database is a production database. Why would it be in simple
> recovery mode?
Only you can answer that question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Can you give me a hint on this... it appears to be in normal mode - not
recovery mode.
--
Regards,
Jamie
"Tracy McKibben" wrote:
> thejamie wrote:
> > That particular database is a production database. Why would it be in simple
> > recovery mode?
> Only you can answer that question...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Open the database property window, select the option tab and check what
recovery mode your database on
vinu
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> Can you give me a hint on this... it appears to be in normal mode - not
> recovery mode.
> --
> Regards,
> Jamie
>
> "Tracy McKibben" wrote:
>> thejamie wrote:
>> > That particular database is a production database. Why would it be in
>> > simple
>> > recovery mode?
>> Only you can answer that question...
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||Does your maintenance plan include master databases, if so remove it from ur
maintenance plan darabase list..
vinu
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> Can you give me a hint on this... it appears to be in normal mode - not
> recovery mode.
> --
> Regards,
> Jamie
>
> "Tracy McKibben" wrote:
>> thejamie wrote:
>> > That particular database is a production database. Why would it be in
>> > simple
>> > recovery mode?
>> Only you can answer that question...
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||Thanks. I am in simple recovery mode. I would like to know I can run a
point in time restore. Do I need to switch to FULL recovery mode for this?
--
Regards,
Jamie
"vt" wrote:
> Does your maintenance plan include master databases, if so remove it from ur
> maintenance plan darabase list..
> vinu
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> > Can you give me a hint on this... it appears to be in normal mode - not
> > recovery mode.
> > --
> > Regards,
> > Jamie
> >
> >
> > "Tracy McKibben" wrote:
> >
> >> thejamie wrote:
> >> > That particular database is a production database. Why would it be in
> >> > simple
> >> > recovery mode?
> >>
> >> Only you can answer that question...
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com
> >>
>
>|||With simple recovery mode you will not be able to do point in time restore
read BOL (Book online) for more detail..
vinu
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
> Thanks. I am in simple recovery mode. I would like to know I can run a
> point in time restore. Do I need to switch to FULL recovery mode for
> this?
> --
> Regards,
> Jamie
>
> "vt" wrote:
>> Does your maintenance plan include master databases, if so remove it from
>> ur
>> maintenance plan darabase list..
>> vinu
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>> > Can you give me a hint on this... it appears to be in normal mode - not
>> > recovery mode.
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "Tracy McKibben" wrote:
>> >
>> >> thejamie wrote:
>> >> > That particular database is a production database. Why would it be
>> >> > in
>> >> > simple
>> >> > recovery mode?
>> >>
>> >> Only you can answer that question...
>> >>
>> >>
>> >> --
>> >> Tracy McKibben
>> >> MCDBA
>> >> http://www.realsqlguy.com
>> >>
>>|||Yes. That's right. Will the server need anything more than a reboot to
enter full recovery mode?
--
Regards,
Jamie
"vt" wrote:
> With simple recovery mode you will not be able to do point in time restore
> read BOL (Book online) for more detail..
> vinu
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
> > Thanks. I am in simple recovery mode. I would like to know I can run a
> > point in time restore. Do I need to switch to FULL recovery mode for
> > this?
> > --
> > Regards,
> > Jamie
> >
> >
> > "vt" wrote:
> >
> >> Does your maintenance plan include master databases, if so remove it from
> >> ur
> >> maintenance plan darabase list..
> >> vinu
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> >> > Can you give me a hint on this... it appears to be in normal mode - not
> >> > recovery mode.
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "Tracy McKibben" wrote:
> >> >
> >> >> thejamie wrote:
> >> >> > That particular database is a production database. Why would it be
> >> >> > in
> >> >> > simple
> >> >> > recovery mode?
> >> >>
> >> >> Only you can answer that question...
> >> >>
> >> >>
> >> >> --
> >> >> Tracy McKibben
> >> >> MCDBA
> >> >> http://www.realsqlguy.com
> >> >>
> >>
> >>
> >>
>
>|||Hi Jamie
You don't even need to reboot. Just change to FULL, then make a full db
backup.
From that point on, you will be able to make log backups, and use them to
restore to a specific point in time, when necessary.
Please read the documentation on log backups and recovery models.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
> Yes. That's right. Will the server need anything more than a reboot to
> enter full recovery mode?
> --
> Regards,
> Jamie
>
> "vt" wrote:
>> With simple recovery mode you will not be able to do point in time
>> restore
>> read BOL (Book online) for more detail..
>> vinu
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
>> > Thanks. I am in simple recovery mode. I would like to know I can
>> > run a
>> > point in time restore. Do I need to switch to FULL recovery mode for
>> > this?
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "vt" wrote:
>> >
>> >> Does your maintenance plan include master databases, if so remove it
>> >> from
>> >> ur
>> >> maintenance plan darabase list..
>> >> vinu
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>> >> > Can you give me a hint on this... it appears to be in normal mode -
>> >> > not
>> >> > recovery mode.
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "Tracy McKibben" wrote:
>> >> >
>> >> >> thejamie wrote:
>> >> >> > That particular database is a production database. Why would it
>> >> >> > be
>> >> >> > in
>> >> >> > simple
>> >> >> > recovery mode?
>> >> >>
>> >> >> Only you can answer that question...
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Tracy McKibben
>> >> >> MCDBA
>> >> >> http://www.realsqlguy.com
>> >> >>
>> >>
>> >>
>> >>
>>|||I don't think server will ask you to reboot
go ahead
vt
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
> Yes. That's right. Will the server need anything more than a reboot to
> enter full recovery mode?
> --
> Regards,
> Jamie
>
> "vt" wrote:
>> With simple recovery mode you will not be able to do point in time
>> restore
>> read BOL (Book online) for more detail..
>> vinu
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
>> > Thanks. I am in simple recovery mode. I would like to know I can
>> > run a
>> > point in time restore. Do I need to switch to FULL recovery mode for
>> > this?
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "vt" wrote:
>> >
>> >> Does your maintenance plan include master databases, if so remove it
>> >> from
>> >> ur
>> >> maintenance plan darabase list..
>> >> vinu
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>> >> > Can you give me a hint on this... it appears to be in normal mode -
>> >> > not
>> >> > recovery mode.
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "Tracy McKibben" wrote:
>> >> >
>> >> >> thejamie wrote:
>> >> >> > That particular database is a production database. Why would it
>> >> >> > be
>> >> >> > in
>> >> >> > simple
>> >> >> > recovery mode?
>> >> >>
>> >> >> Only you can answer that question...
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Tracy McKibben
>> >> >> MCDBA
>> >> >> http://www.realsqlguy.com
>> >> >>
>> >>
>> >>
>> >>
>>|||Thanks to all of you. This is a big load off my mind.
--
Regards,
Jamie
"vt" wrote:
> I don't think server will ask you to reboot
> go ahead
> vt
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
> > Yes. That's right. Will the server need anything more than a reboot to
> > enter full recovery mode?
> > --
> > Regards,
> > Jamie
> >
> >
> > "vt" wrote:
> >
> >> With simple recovery mode you will not be able to do point in time
> >> restore
> >> read BOL (Book online) for more detail..
> >>
> >> vinu
> >>
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
> >> > Thanks. I am in simple recovery mode. I would like to know I can
> >> > run a
> >> > point in time restore. Do I need to switch to FULL recovery mode for
> >> > this?
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "vt" wrote:
> >> >
> >> >> Does your maintenance plan include master databases, if so remove it
> >> >> from
> >> >> ur
> >> >> maintenance plan darabase list..
> >> >> vinu
> >> >>
> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> >> >> > Can you give me a hint on this... it appears to be in normal mode -
> >> >> > not
> >> >> > recovery mode.
> >> >> > --
> >> >> > Regards,
> >> >> > Jamie
> >> >> >
> >> >> >
> >> >> > "Tracy McKibben" wrote:
> >> >> >
> >> >> >> thejamie wrote:
> >> >> >> > That particular database is a production database. Why would it
> >> >> >> > be
> >> >> >> > in
> >> >> >> > simple
> >> >> >> > recovery mode?
> >> >> >>
> >> >> >> Only you can answer that question...
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >> Tracy McKibben
> >> >> >> MCDBA
> >> >> >> http://www.realsqlguy.com
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Run this in query analyzer:
alter database dbname
set recovery Full
No reboot required
thejamie wrote:
> Thanks to all of you. This is a big load off my mind.
> --
> Regards,
> Jamie
>
> "vt" wrote:
> > I don't think server will ask you to reboot
> > go ahead
> >
> > vt
> >
> >
> >
> > "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> > news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
> > > Yes. That's right. Will the server need anything more than a reboot to
> > > enter full recovery mode?
> > > --
> > > Regards,
> > > Jamie
> > >
> > >
> > > "vt" wrote:
> > >
> > >> With simple recovery mode you will not be able to do point in time
> > >> restore
> > >> read BOL (Book online) for more detail..
> > >>
> > >> vinu
> > >>
> > >>
> > >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> > >> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
> > >> > Thanks. I am in simple recovery mode. I would like to know I can
> > >> > run a
> > >> > point in time restore. Do I need to switch to FULL recovery mode for
> > >> > this?
> > >> > --
> > >> > Regards,
> > >> > Jamie
> > >> >
> > >> >
> > >> > "vt" wrote:
> > >> >
> > >> >> Does your maintenance plan include master databases, if so remove it
> > >> >> from
> > >> >> ur
> > >> >> maintenance plan darabase list..
> > >> >> vinu
> > >> >>
> > >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> > >> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> > >> >> > Can you give me a hint on this... it appears to be in normal mode -
> > >> >> > not
> > >> >> > recovery mode.
> > >> >> > --
> > >> >> > Regards,
> > >> >> > Jamie
> > >> >> >
> > >> >> >
> > >> >> > "Tracy McKibben" wrote:
> > >> >> >
> > >> >> >> thejamie wrote:
> > >> >> >> > That particular database is a production database. Why would it
> > >> >> >> > be
> > >> >> >> > in
> > >> >> >> > simple
> > >> >> >> > recovery mode?
> > >> >> >>
> > >> >> >> Only you can answer that question...
> > >> >> >>
> > >> >> >>
> > >> >> >> --
> > >> >> >> Tracy McKibben
> > >> >> >> MCDBA
> > >> >> >> http://www.realsqlguy.com
> > >> >> >>
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >|||"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:D8A1EEBD-95A0-4DB4-B40A-AD3AD0EA4E22@.microsoft.com...
> Thanks to all of you. This is a big load off my mind.
Umm, not quite.
Make sure you do transaction log backups.
And keep in mind a disaster recovery plan is far more than simply "turning
on backups".
It's managing them among other things.
How many often will you take a full backup? How long will you keep it?
How often will you take transaction backups. How long will you keep them?
Etc.
> --
> Regards,
> Jamie
>
> "vt" wrote:
>> I don't think server will ask you to reboot
>> go ahead
>> vt
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
>> > Yes. That's right. Will the server need anything more than a reboot
>> > to
>> > enter full recovery mode?
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "vt" wrote:
>> >
>> >> With simple recovery mode you will not be able to do point in time
>> >> restore
>> >> read BOL (Book online) for more detail..
>> >>
>> >> vinu
>> >>
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
>> >> > Thanks. I am in simple recovery mode. I would like to know I can
>> >> > run a
>> >> > point in time restore. Do I need to switch to FULL recovery mode
>> >> > for
>> >> > this?
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "vt" wrote:
>> >> >
>> >> >> Does your maintenance plan include master databases, if so remove
>> >> >> it
>> >> >> from
>> >> >> ur
>> >> >> maintenance plan darabase list..
>> >> >> vinu
>> >> >>
>> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>> >> >> > Can you give me a hint on this... it appears to be in normal
>> >> >> > mode -
>> >> >> > not
>> >> >> > recovery mode.
>> >> >> > --
>> >> >> > Regards,
>> >> >> > Jamie
>> >> >> >
>> >> >> >
>> >> >> > "Tracy McKibben" wrote:
>> >> >> >
>> >> >> >> thejamie wrote:
>> >> >> >> > That particular database is a production database. Why would
>> >> >> >> > it
>> >> >> >> > be
>> >> >> >> > in
>> >> >> >> > simple
>> >> >> >> > recovery mode?
>> >> >> >>
>> >> >> >> Only you can answer that question...
>> >> >> >>
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tracy McKibben
>> >> >> >> MCDBA
>> >> >> >> http://www.realsqlguy.com
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||And what kind of testing will you do to make sure you can smoothly recover
from your backups when the need arises?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23zodPZgAHHA.3836@.TK2MSFTNGP02.phx.gbl...
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:D8A1EEBD-95A0-4DB4-B40A-AD3AD0EA4E22@.microsoft.com...
>> Thanks to all of you. This is a big load off my mind.
> Umm, not quite.
> Make sure you do transaction log backups.
> And keep in mind a disaster recovery plan is far more than simply "turning
> on backups".
> It's managing them among other things.
> How many often will you take a full backup? How long will you keep it?
> How often will you take transaction backups. How long will you keep them?
> Etc.
>
>> --
>> Regards,
>> Jamie
>>
>> "vt" wrote:
>> I don't think server will ask you to reboot
>> go ahead
>> vt
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
>> > Yes. That's right. Will the server need anything more than a reboot
>> > to
>> > enter full recovery mode?
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "vt" wrote:
>> >
>> >> With simple recovery mode you will not be able to do point in time
>> >> restore
>> >> read BOL (Book online) for more detail..
>> >>
>> >> vinu
>> >>
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
>> >> > Thanks. I am in simple recovery mode. I would like to know I
>> >> > can
>> >> > run a
>> >> > point in time restore. Do I need to switch to FULL recovery mode
>> >> > for
>> >> > this?
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "vt" wrote:
>> >> >
>> >> >> Does your maintenance plan include master databases, if so remove
>> >> >> it
>> >> >> from
>> >> >> ur
>> >> >> maintenance plan darabase list..
>> >> >> vinu
>> >> >>
>> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>> >> >> > Can you give me a hint on this... it appears to be in normal
>> >> >> > mode -
>> >> >> > not
>> >> >> > recovery mode.
>> >> >> > --
>> >> >> > Regards,
>> >> >> > Jamie
>> >> >> >
>> >> >> >
>> >> >> > "Tracy McKibben" wrote:
>> >> >> >
>> >> >> >> thejamie wrote:
>> >> >> >> > That particular database is a production database. Why would
>> >> >> >> > it
>> >> >> >> > be
>> >> >> >> > in
>> >> >> >> > simple
>> >> >> >> > recovery mode?
>> >> >> >>
>> >> >> >> Only you can answer that question...
>> >> >> >>
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tracy McKibben
>> >> >> >> MCDBA
>> >> >> >> http://www.realsqlguy.com
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>|||We're running simulations on virtual servers. Thanks again everyone for your
help.
--
Regards,
Jamie
"Kalen Delaney" wrote:
> And what kind of testing will you do to make sure you can smoothly recover
> from your backups when the need arises?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
> news:%23zodPZgAHHA.3836@.TK2MSFTNGP02.phx.gbl...
> >
> > "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> > news:D8A1EEBD-95A0-4DB4-B40A-AD3AD0EA4E22@.microsoft.com...
> >> Thanks to all of you. This is a big load off my mind.
> >
> > Umm, not quite.
> >
> > Make sure you do transaction log backups.
> >
> > And keep in mind a disaster recovery plan is far more than simply "turning
> > on backups".
> >
> > It's managing them among other things.
> >
> > How many often will you take a full backup? How long will you keep it?
> >
> > How often will you take transaction backups. How long will you keep them?
> >
> > Etc.
> >
> >
> >> --
> >> Regards,
> >> Jamie
> >>
> >>
> >> "vt" wrote:
> >>
> >> I don't think server will ask you to reboot
> >> go ahead
> >>
> >> vt
> >>
> >>
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
> >> > Yes. That's right. Will the server need anything more than a reboot
> >> > to
> >> > enter full recovery mode?
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "vt" wrote:
> >> >
> >> >> With simple recovery mode you will not be able to do point in time
> >> >> restore
> >> >> read BOL (Book online) for more detail..
> >> >>
> >> >> vinu
> >> >>
> >> >>
> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> >> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
> >> >> > Thanks. I am in simple recovery mode. I would like to know I
> >> >> > can
> >> >> > run a
> >> >> > point in time restore. Do I need to switch to FULL recovery mode
> >> >> > for
> >> >> > this?
> >> >> > --
> >> >> > Regards,
> >> >> > Jamie
> >> >> >
> >> >> >
> >> >> > "vt" wrote:
> >> >> >
> >> >> >> Does your maintenance plan include master databases, if so remove
> >> >> >> it
> >> >> >> from
> >> >> >> ur
> >> >> >> maintenance plan darabase list..
> >> >> >> vinu
> >> >> >>
> >> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> >> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> >> >> >> > Can you give me a hint on this... it appears to be in normal
> >> >> >> > mode -
> >> >> >> > not
> >> >> >> > recovery mode.
> >> >> >> > --
> >> >> >> > Regards,
> >> >> >> > Jamie
> >> >> >> >
> >> >> >> >
> >> >> >> > "Tracy McKibben" wrote:
> >> >> >> >
> >> >> >> >> thejamie wrote:
> >> >> >> >> > That particular database is a production database. Why would
> >> >> >> >> > it
> >> >> >> >> > be
> >> >> >> >> > in
> >> >> >> >> > simple
> >> >> >> >> > recovery mode?
> >> >> >> >>
> >> >> >> >> Only you can answer that question...
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Tracy McKibben
> >> >> >> >> MCDBA
> >> >> >> >> http://www.realsqlguy.com
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >
> >
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eEBNUqgAHHA.1220@.TK2MSFTNGP04.phx.gbl...
> And what kind of testing will you do to make sure you can smoothly recover
> from your backups when the need arises?
>
Oh yeah. Good point. :-)
(We do home rolled log-shipping of most of our databases, which is a real
good check that backups are working etc. ;-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message news:%23zodPZgAHHA.3836@.TK2MSFTNGP02.phx.gbl...
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:D8A1EEBD-95A0-4DB4-B40A-AD3AD0EA4E22@.microsoft.com...
>> Thanks to all of you. This is a big load off my mind.
>> Umm, not quite.
>> Make sure you do transaction log backups.
>> And keep in mind a disaster recovery plan is far more than simply
>> "turning on backups".
>> It's managing them among other things.
>> How many often will you take a full backup? How long will you keep it?
>> How often will you take transaction backups. How long will you keep
>> them?
>> Etc.
>>
>> --
>> Regards,
>> Jamie
>>
>> "vt" wrote:
>> I don't think server will ask you to reboot
>> go ahead
>> vt
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
>> > Yes. That's right. Will the server need anything more than a reboot
>> > to
>> > enter full recovery mode?
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "vt" wrote:
>> >
>> >> With simple recovery mode you will not be able to do point in time
>> >> restore
>> >> read BOL (Book online) for more detail..
>> >>
>> >> vinu
>> >>
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
>> >> > Thanks. I am in simple recovery mode. I would like to know I
>> >> > can
>> >> > run a
>> >> > point in time restore. Do I need to switch to FULL recovery mode
>> >> > for
>> >> > this?
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "vt" wrote:
>> >> >
>> >> >> Does your maintenance plan include master databases, if so remove
>> >> >> it
>> >> >> from
>> >> >> ur
>> >> >> maintenance plan darabase list..
>> >> >> vinu
>> >> >>
>> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>> >> >> > Can you give me a hint on this... it appears to be in normal
>> >> >> > mode -
>> >> >> > not
>> >> >> > recovery mode.
>> >> >> > --
>> >> >> > Regards,
>> >> >> > Jamie
>> >> >> >
>> >> >> >
>> >> >> > "Tracy McKibben" wrote:
>> >> >> >
>> >> >> >> thejamie wrote:
>> >> >> >> > That particular database is a production database. Why
>> >> >> >> > would it
>> >> >> >> > be
>> >> >> >> > in
>> >> >> >> > simple
>> >> >> >> > recovery mode?
>> >> >> >>
>> >> >> >> Only you can answer that question...
>> >> >> >>
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tracy McKibben
>> >> >> >> MCDBA
>> >> >> >> http://www.realsqlguy.com
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>

Indexes not rebuilding on SQL 2K DB MAINT PLAN

I see errors in my DB Maint log that indicates indexes are not being rebuilt.
We are having production problems - time outs. Does the optimization plan
for DB Maintenence drop bad indexes - simply put, what is my best way to
handle back indexes on a production server?
Regards,
Jamie
Error is:
Backup can not be performed on this database. This sub task is ignored
Regards,
Jamie
"thejamie" wrote:

> I see errors in my DB Maint log that indicates indexes are not being rebuilt.
> We are having production problems - time outs. Does the optimization plan
> for DB Maintenence drop bad indexes - simply put, what is my best way to
> handle back indexes on a production server?
> --
> Regards,
> Jamie
|||thejamie wrote:
> Error is:
> Backup can not be performed on this database. This sub task is ignored
That has nothing to do with indexes... This error is telling you that
you are attempting to do a transaction log backup against a database
that is in Simple recovery mode.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||That particular database is a production database. Why would it be in simple
recovery mode?
Regards,
Jamie
"Tracy McKibben" wrote:

> thejamie wrote:
> That has nothing to do with indexes... This error is telling you that
> you are attempting to do a transaction log backup against a database
> that is in Simple recovery mode.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||thejamie wrote:
> That particular database is a production database. Why would it be in simple
> recovery mode?
Only you can answer that question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Can you give me a hint on this... it appears to be in normal mode - not
recovery mode.
Regards,
Jamie
"Tracy McKibben" wrote:

> thejamie wrote:
> Only you can answer that question...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||Open the database property window, select the option tab and check what
recovery mode your database on
vinu
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...[vbcol=seagreen]
> Can you give me a hint on this... it appears to be in normal mode - not
> recovery mode.
> --
> Regards,
> Jamie
>
> "Tracy McKibben" wrote:
|||Does your maintenance plan include master databases, if so remove it from ur
maintenance plan darabase list..
vinu
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...[vbcol=seagreen]
> Can you give me a hint on this... it appears to be in normal mode - not
> recovery mode.
> --
> Regards,
> Jamie
>
> "Tracy McKibben" wrote:
|||Thanks. I am in simple recovery mode. I would like to know I can run a
point in time restore. Do I need to switch to FULL recovery mode for this?
Regards,
Jamie
"vt" wrote:

> Does your maintenance plan include master databases, if so remove it from ur
> maintenance plan darabase list..
> vinu
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>
>
|||With simple recovery mode you will not be able to do point in time restore
read BOL (Book online) for more detail..
vinu
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...[vbcol=seagreen]
> Thanks. I am in simple recovery mode. I would like to know I can run a
> point in time restore. Do I need to switch to FULL recovery mode for
> this?
> --
> Regards,
> Jamie
>
> "vt" wrote:

Indexes

when dropping and recreating indexes, is there anything else that needs to
be done?
I keep hearing the phrase rebuilding indexes...
thanks
What we in the SQL Server world refer to as rebuilding an index means that we try to defragment the
index, for performance reasons.
This is typically done using the DBCC DBREINDEX command (in 2005 we use ALTER INDEX with the REBUILD
option instead). This means that SQL Server under the covers create a new index and then drop the
old one.
An alternative method is DBCC INDEXDEFRAG (ALTER INDEX with the REORGANIZE option). This work
differently internally.
See Books Online for details of what these commands does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g" <gregoranton_nospamplease_@.hotmail.com> wrote in message news:an3If.8988$bd4.6488@.edtnps84...
> when dropping and recreating indexes, is there anything else that needs to be done?
> I keep hearing the phrase rebuilding indexes...
> thanks
>
|||Thanks Tibor!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OK5j2UMMGHA.1760@.TK2MSFTNGP10.phx.gbl...
> What we in the SQL Server world refer to as rebuilding an index means that
> we try to defragment the index, for performance reasons.
> This is typically done using the DBCC DBREINDEX command (in 2005 we use
> ALTER INDEX with the REBUILD option instead). This means that SQL Server
> under the covers create a new index and then drop the old one.
> An alternative method is DBCC INDEXDEFRAG (ALTER INDEX with the REORGANIZE
> option). This work differently internally.
> See Books Online for details of what these commands does.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "g" <gregoranton_nospamplease_@.hotmail.com> wrote in message
> news:an3If.8988$bd4.6488@.edtnps84...
>