Hi,
SQL Server returned me error 8623 "Internal Query Processor Error: The query processor could not produce a query plan." I've looked for info in the SQL books, but there's no info about this error. I would appreciate it a lot if somebody could tell me what this error means, how is it triggered, and if it's possible, how to avoid it.
Thanks a lot,
FedericoWhere are you doing this?
What's the query?
It's dynamic, isn't it...|||It would be better if you quote the query involved as per Brett's reply.
This KBA1 (http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729) and KBA2 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;286255) refers about 863 error.
HTH|||I can't reproduce this error. So I think it's sporadyc. Anyway I'll check my SELECT clauses to see if there's something similar to what Satya's link mention. Again, thanks a lot|||PROFILER may help you to track the process of query against database, just in case if you don't know.
Showing posts with label plan. Show all posts
Showing posts with label plan. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
Indexing with null or zero
I am adding an int field to a table that will be used to link it to another
table when it has a number in it. I plan to index it for faster access but
wondered if my default for that table should be NULL or zero or if it makes
a difference. Thanks.
DavidTo an index, NULL is just a value (as is 0). So use whatever makes most sens
e to you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.252@.TK2MSFTNGP12.ph
x.gbl...
>I am adding an int field to a table that will be used to link it to another
table when it has a
>number in it. I plan to index it for faster access but wondered if my defa
ult for that table
>should be NULL or zero or if it makes a difference. Thanks.
> David
>|||I have to say that it would be nice if there was a built-in means to create
a
unique constraint on a column such that nulls are ignored. Going the route o
f
indexed views or triggers is a colossal pain.
Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse
> to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uy%238XzdSFHA.252@.TK2MSFTNGP12.phx.gbl...
>|||Tibor,
I don't think this is quite true. A column's nullability (assuming
that is part of the choice here) affects what query plans can be
considered. Also, indexes aside, there can be different and
unexpected results depending on whether NULL or 0 is used.
An example we often see in the newsgroups is
select this, that
from T
where T.parent not in (
select parent
from T_children
)
which returns no rows even when there are T.parent values
not present in the T_children table.
There are other differences less likely to be an issue, like
calculating percentages:
select
T.parent,
sum(case when parent = T_children.parent then 1.00 else 0.00 end) /
count(T_children.parent) as percentage
from T join T_children
on T.parent = T_children.parent
group by T.parent
Unfortunately, while using NULL is probably truer to the principles
of good modeling, using 0 may have some practical advantages, if
the referring column can then be declared as NOT NULL. To maintain
referential integrity via a foreign key constraint, NULL is a better choice,
since 0 requires putting a dummy entry into the referenced table.
Steve Kass
Drew University
Tibor Karaszi wrote:
>To an index, NULL is just a value (as is 0). So use whatever makes most sen
se to you.
>
>|||
Thomas wrote:
>I have to say that it would be nice if there was a built-in means to create
a
>unique constraint on a column such that nulls are ignored. Going the route
of
>indexed views or triggers is a colossal pain.
>
>
There is at least one solution using an indexed column instead of
an indexed view. Functionally, it is much the same, but you might
find it easier to maintain.
http://groups.google.co.uk/groups?q...B8-B7567063D1CC
SK
>Thomas
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
>
>
>|||Hmm..That's an clever solution. For the purposes of other people reading thi
s
thread, the idea is to create a computed column that either equals the colum
n on
which you really want a unique index or the primary key when that value is n
ull.
You would then put the unique index on this computed column.
Granted, it does mean dealing with computed columns which can be a persnicke
ty
but it does get around the issue.
Thomas
"Steve Kass" <skass@.drew.edu> wrote in message
news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
> Thomas wrote:
>
> There is at least one solution using an indexed column instead of
> an indexed view. Functionally, it is much the same, but you might
> find it easier to maintain.
> http://groups.google.co.uk/groups?q...B8-B7567063D1CC
> SK
>|||If a Foreign Key column allows null, then this is definitely NOT the same as
putting a zero in the column. A value of zero MUST exist in the reference
table, a null value does NOT have to (indeed it cannot exist) as PK in the
reference table. The distinction is critical, it is the dfference between a
[one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
relationship.
Example, In a [one]-to-[zero or many] Employees have zero o many timecard
punches, but for each time card punch there must be one employee - and only
one an employee.
In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
have zero or many books checked out to them, and each book can be checked
out to zero or one library patron...
The only way to model this distinction is by allowing, (and using) null
values in the Foreign Key column on the many side of the DRI constraint.
"Tibor Karaszi" wrote:
> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.25
2@.TK2MSFTNGP12.phx.gbl...
>
>|||When I want a Foreign Key Column to be unique, but Allow any number of Nulls
,
(that's a [One]-to-[Zero or One] Relationship, I use an extra table
CREATE TABLE [TabA] (
[AID] [int] NOT NULL ,
[Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_TabA] PRIMARY KEY CLUSTERED
([AID]) ON [PRIMARY]
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabB] (
[BID] [int] NOT NULL ,
[AID] [int] NULL ,
CONSTRAINT [PK_TabB] PRIMARY KEY CLUSTERED
( [BID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabB_TabA] FOREIGN KEY
([AID]) REFERENCES [TabA] ([AID])
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabC] (
[BID] [int] NOT NULL ,
[AID] [int] NOT NULL ,
CONSTRAINT [PK_TabC] PRIMARY KEY CLUSTERED
([BID]) ON [PRIMARY] ,
CONSTRAINT [IX_TabCUniqueAID] UNIQUE NONCLUSTERED
([AID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabC_TabB] FOREIGN KEY
([BID]) REFERENCES [TabB] ([BID])
) ON [PRIMARY]
GO
-- Then I add ALL the child records to TabB, Both those with null values of
AID, and Non-Null values of AID, but only add the Non-Null AID Records to
TabC... TabB is teh real table, and has all the other attriobutes in it,
TabC is only there to enforce uniqueness on the Non-Null Values of AID in Ta
bB
"Thomas" wrote:
> Hmm..That's an clever solution. For the purposes of other people reading t
his
> thread, the idea is to create a computed column that either equals the col
umn on
> which you really want a unique index or the primary key when that value is
null.
> You would then put the unique index on this computed column.
> Granted, it does mean dealing with computed columns which can be a persnic
kety
> but it does get around the issue.
>
> Thomas
>
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
>|||Perhaps I was in a bit too much hurry when responding. I was looking at it p
urely from the index'
perspective, how the data is stored in the index. You definitely want to thi
nk through semantics
carefully. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
> If a Foreign Key column allows null, then this is definitely NOT the same
as
> putting a zero in the column. A value of zero MUST exist in the referenc
e
> table, a null value does NOT have to (indeed it cannot exist) as PK in the
> reference table. The distinction is critical, it is the dfference between
a
> [one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
> relationship.
>
> Example, In a [one]-to-[zero or many] Employees have zero o many timecard
> punches, but for each time card punch there must be one employee - and on
ly
> one an employee.
> In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
> have zero or many books checked out to them, and each book can be checked
> out to zero or one library patron...
> The only way to model this distinction is by allowing, (and using) null
> values in the Foreign Key column on the many side of the DRI constraint.
> "Tibor Karaszi" wrote:
>|||Tibor,
Neglected to make clear that details in my post,were, of course, not
directed at you... figured you just overlooked that. I slip into
pedanticLand way too easily...
Respectfully, Charly
"Tibor Karaszi" wrote:
> Perhaps I was in a bit too much hurry when responding. I was looking at it
purely from the index'
> perspective, how the data is stored in the index. You definitely want to t
hink through semantics
> carefully. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
>
>
table when it has a number in it. I plan to index it for faster access but
wondered if my default for that table should be NULL or zero or if it makes
a difference. Thanks.
DavidTo an index, NULL is just a value (as is 0). So use whatever makes most sens
e to you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.252@.TK2MSFTNGP12.ph
x.gbl...
>I am adding an int field to a table that will be used to link it to another
table when it has a
>number in it. I plan to index it for faster access but wondered if my defa
ult for that table
>should be NULL or zero or if it makes a difference. Thanks.
> David
>|||I have to say that it would be nice if there was a built-in means to create
a
unique constraint on a column such that nulls are ignored. Going the route o
f
indexed views or triggers is a colossal pain.
Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse
> to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uy%238XzdSFHA.252@.TK2MSFTNGP12.phx.gbl...
>|||Tibor,
I don't think this is quite true. A column's nullability (assuming
that is part of the choice here) affects what query plans can be
considered. Also, indexes aside, there can be different and
unexpected results depending on whether NULL or 0 is used.
An example we often see in the newsgroups is
select this, that
from T
where T.parent not in (
select parent
from T_children
)
which returns no rows even when there are T.parent values
not present in the T_children table.
There are other differences less likely to be an issue, like
calculating percentages:
select
T.parent,
sum(case when parent = T_children.parent then 1.00 else 0.00 end) /
count(T_children.parent) as percentage
from T join T_children
on T.parent = T_children.parent
group by T.parent
Unfortunately, while using NULL is probably truer to the principles
of good modeling, using 0 may have some practical advantages, if
the referring column can then be declared as NOT NULL. To maintain
referential integrity via a foreign key constraint, NULL is a better choice,
since 0 requires putting a dummy entry into the referenced table.
Steve Kass
Drew University
Tibor Karaszi wrote:
>To an index, NULL is just a value (as is 0). So use whatever makes most sen
se to you.
>
>|||
Thomas wrote:
>I have to say that it would be nice if there was a built-in means to create
a
>unique constraint on a column such that nulls are ignored. Going the route
of
>indexed views or triggers is a colossal pain.
>
>
There is at least one solution using an indexed column instead of
an indexed view. Functionally, it is much the same, but you might
find it easier to maintain.
http://groups.google.co.uk/groups?q...B8-B7567063D1CC
SK
>Thomas
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
>
>
>|||Hmm..That's an clever solution. For the purposes of other people reading thi
s
thread, the idea is to create a computed column that either equals the colum
n on
which you really want a unique index or the primary key when that value is n
ull.
You would then put the unique index on this computed column.
Granted, it does mean dealing with computed columns which can be a persnicke
ty
but it does get around the issue.
Thomas
"Steve Kass" <skass@.drew.edu> wrote in message
news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
> Thomas wrote:
>
> There is at least one solution using an indexed column instead of
> an indexed view. Functionally, it is much the same, but you might
> find it easier to maintain.
> http://groups.google.co.uk/groups?q...B8-B7567063D1CC
> SK
>|||If a Foreign Key column allows null, then this is definitely NOT the same as
putting a zero in the column. A value of zero MUST exist in the reference
table, a null value does NOT have to (indeed it cannot exist) as PK in the
reference table. The distinction is critical, it is the dfference between a
[one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
relationship.
Example, In a [one]-to-[zero or many] Employees have zero o many timecard
punches, but for each time card punch there must be one employee - and only
one an employee.
In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
have zero or many books checked out to them, and each book can be checked
out to zero or one library patron...
The only way to model this distinction is by allowing, (and using) null
values in the Foreign Key column on the many side of the DRI constraint.
"Tibor Karaszi" wrote:
> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.25
2@.TK2MSFTNGP12.phx.gbl...
>
>|||When I want a Foreign Key Column to be unique, but Allow any number of Nulls
,
(that's a [One]-to-[Zero or One] Relationship, I use an extra table
CREATE TABLE [TabA] (
[AID] [int] NOT NULL ,
[Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_TabA] PRIMARY KEY CLUSTERED
([AID]) ON [PRIMARY]
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabB] (
[BID] [int] NOT NULL ,
[AID] [int] NULL ,
CONSTRAINT [PK_TabB] PRIMARY KEY CLUSTERED
( [BID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabB_TabA] FOREIGN KEY
([AID]) REFERENCES [TabA] ([AID])
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabC] (
[BID] [int] NOT NULL ,
[AID] [int] NOT NULL ,
CONSTRAINT [PK_TabC] PRIMARY KEY CLUSTERED
([BID]) ON [PRIMARY] ,
CONSTRAINT [IX_TabCUniqueAID] UNIQUE NONCLUSTERED
([AID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabC_TabB] FOREIGN KEY
([BID]) REFERENCES [TabB] ([BID])
) ON [PRIMARY]
GO
-- Then I add ALL the child records to TabB, Both those with null values of
AID, and Non-Null values of AID, but only add the Non-Null AID Records to
TabC... TabB is teh real table, and has all the other attriobutes in it,
TabC is only there to enforce uniqueness on the Non-Null Values of AID in Ta
bB
"Thomas" wrote:
> Hmm..That's an clever solution. For the purposes of other people reading t
his
> thread, the idea is to create a computed column that either equals the col
umn on
> which you really want a unique index or the primary key when that value is
null.
> You would then put the unique index on this computed column.
> Granted, it does mean dealing with computed columns which can be a persnic
kety
> but it does get around the issue.
>
> Thomas
>
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
>|||Perhaps I was in a bit too much hurry when responding. I was looking at it p
urely from the index'
perspective, how the data is stored in the index. You definitely want to thi
nk through semantics
carefully. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
> If a Foreign Key column allows null, then this is definitely NOT the same
as
> putting a zero in the column. A value of zero MUST exist in the referenc
e
> table, a null value does NOT have to (indeed it cannot exist) as PK in the
> reference table. The distinction is critical, it is the dfference between
a
> [one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
> relationship.
>
> Example, In a [one]-to-[zero or many] Employees have zero o many timecard
> punches, but for each time card punch there must be one employee - and on
ly
> one an employee.
> In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
> have zero or many books checked out to them, and each book can be checked
> out to zero or one library patron...
> The only way to model this distinction is by allowing, (and using) null
> values in the Foreign Key column on the many side of the DRI constraint.
> "Tibor Karaszi" wrote:
>|||Tibor,
Neglected to make clear that details in my post,were, of course, not
directed at you... figured you just overlooked that. I slip into
pedanticLand way too easily...
Respectfully, Charly
"Tibor Karaszi" wrote:
> Perhaps I was in a bit too much hurry when responding. I was looking at it
purely from the index'
> perspective, how the data is stored in the index. You definitely want to t
hink through semantics
> carefully. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
>
>
Wednesday, March 7, 2012
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
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>
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:
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:
Friday, February 24, 2012
Indexes :-
hi,
one small ques on indexes, i added one composite index on table to avoud clus index scan(cost 70%) , when i checked executioin plan it is showing me like clus index seek using the above defined index + bookmark lookup operator also..
so which soln should i go for clus index scan or bookmark operator using clus index seek (for composit index0
--
SanjuRun the query both ways in Query Analyzer and look at the Total Plan cost
... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju
one small ques on indexes, i added one composite index on table to avoud clus index scan(cost 70%) , when i checked executioin plan it is showing me like clus index seek using the above defined index + bookmark lookup operator also..
so which soln should i go for clus index scan or bookmark operator using clus index seek (for composit index0
--
SanjuRun the query both ways in Query Analyzer and look at the Total Plan cost
... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju
Indexes :-
hi,
one small ques on indexes, i added one composite index on table to avoud clu
s index scan(cost 70%) , when i checked executioin plan it is showing me lik
e clus index seek using the above defined index + bookmark lookup operator a
lso..
so which soln should i go for clus index scan or bookmark operator using clu
s index seek (for composit index0
SanjuRun the query both ways in Query Analyzer and look at the Total Plan cost
... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju
one small ques on indexes, i added one composite index on table to avoud clu
s index scan(cost 70%) , when i checked executioin plan it is showing me lik
e clus index seek using the above defined index + bookmark lookup operator a
lso..
so which soln should i go for clus index scan or bookmark operator using clu
s index seek (for composit index0
SanjuRun the query both ways in Query Analyzer and look at the Total Plan cost
... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju
Indexes :-
hi,
one small ques on indexes, i added one composite index on table to avoud clus index scan(cost 70%) , when i checked executioin plan it is showing me like clus index seek using the above defined index + bookmark lookup operator also..
so which soln should i go for clus index scan or bookmark operator using clus index seek (for composit index0
Sanju
Run the query both ways in Query Analyzer and look at the Total Plan cost
.... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju
one small ques on indexes, i added one composite index on table to avoud clus index scan(cost 70%) , when i checked executioin plan it is showing me like clus index seek using the above defined index + bookmark lookup operator also..
so which soln should i go for clus index scan or bookmark operator using clus index seek (for composit index0
Sanju
Run the query both ways in Query Analyzer and look at the Total Plan cost
.... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju
Subscribe to:
Posts (Atom)