optimization

I cannot make the database optimize properly. I have a
maintenance plan setup specifically to do just that, but
it is never able to run and our database is becoming
extremely slow. Any ideas?What errors are being reported by sqlmaint - should be in one of the txt =filesin the sql server logs directory. Youcan find the filename buy =looking at the details of the scheduled job that runs the optimisatons =job.
If the optimisations job i snever running it will man indexes etc may be =less than optimal but your phrase "extremely slow" makes me suspect =something else is wrong as well.
Mike John
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message =news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
> I cannot make the database optimize properly. I have a > maintenance plan setup specifically to do just that, but > it is never able to run and our database is becoming > extremely slow. Any ideas?|||As far as I can tell, there are no errors, it is just not
doing it. In the History file, it tells me that it is
checking the db, but it is not able to rebuild indexes,
which my boss thinks is the reason the db is so very slow.
It backs up the db without any problem, but it cannot
back up the transaction log...
As you can probably tell, I am fairly new to SQL server...
Anything I can do to fix it?
>--Original Message--
>What errors are being reported by sqlmaint - should be
in one of the txt filesin the sql server logs directory.
Youcan find the filename buy looking at the details of
the scheduled job that runs the optimisatons job.
>If the optimisations job i snever running it will man
indexes etc may be less than optimal but your
phrase "extremely slow" makes me suspect something else
is wrong as well.
>Mike John
>
>"Kristen" <anonymous@.discussions.microsoft.com> wrote in
message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
>> I cannot make the database optimize properly. I have
a
>> maintenance plan setup specifically to do just that,
but
>> it is never able to run and our database is becoming
>> extremely slow. Any ideas?
>.
>|||> As far as I can tell, there are no errors, it is just not
> doing it. In the History file, it tells me that it is
> checking the db, but it is not able to rebuild indexes,
How do you know it isn't rebuilding indexes if there are no error messages?
We need the error message from the rtp file.
> It backs up the db without any problem, but it cannot
> back up the transaction log...
That is probably not related. My guess is that the database is in simple
recovery mode. read more in Books Online about backup to understand recovery
mode and ability to do log backup.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message
news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
> As far as I can tell, there are no errors, it is just not
> doing it. In the History file, it tells me that it is
> checking the db, but it is not able to rebuild indexes,
> which my boss thinks is the reason the db is so very slow.
> It backs up the db without any problem, but it cannot
> back up the transaction log...
> As you can probably tell, I am fairly new to SQL server...
> Anything I can do to fix it?
> >--Original Message--
> >What errors are being reported by sqlmaint - should be
> in one of the txt filesin the sql server logs directory.
> Youcan find the filename buy looking at the details of
> the scheduled job that runs the optimisatons job.
> >
> >If the optimisations job i snever running it will man
> indexes etc may be less than optimal but your
> phrase "extremely slow" makes me suspect something else
> is wrong as well.
> >
> >Mike John
> >
> >
> >"Kristen" <anonymous@.discussions.microsoft.com> wrote in
> message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
> >> I cannot make the database optimize properly. I have
> a
> >> maintenance plan setup specifically to do just that,
> but
> >> it is never able to run and our database is becoming
> >> extremely slow. Any ideas?
> >.
> >|||Kristen
Have you tried using dbcc showcontig, this will show you
if your tables are fragmented. (See BOL for details). If
they are bably fragmented you will know the main plan is
not working.
Another thought is that in your maint plan have you chosen
the option 'attempt to repair minor problems' if you have
remove it. This regularly causes problems with maint
plans. Any open connections and your plan will not run
properly with this option.
Hope this helps
John|||The reason I know it is not rebuilding indexes is because
the history tells me so. There is always a red X next to
rebuild indexes indicating it was not done. I looked
through every error log at the times it reported that
rebuilding indexes was not done and it said nothing about
rebuilding indexes. That is why I say there is no error
message. If there is a different place I should be
looking please tell me because I do not know what an rtp
file is.
>--Original Message--
>> As far as I can tell, there are no errors, it is just
not
>> doing it. In the History file, it tells me that it is
>> checking the db, but it is not able to rebuild indexes,
>How do you know it isn't rebuilding indexes if there are
no error messages?
>We need the error message from the rtp file.
>
>> It backs up the db without any problem, but it cannot
>> back up the transaction log...
>That is probably not related. My guess is that the
database is in simple
>recovery mode. read more in Books Online about backup to
understand recovery
>mode and ability to do log backup.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Kristen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
>> As far as I can tell, there are no errors, it is just
not
>> doing it. In the History file, it tells me that it is
>> checking the db, but it is not able to rebuild indexes,
>> which my boss thinks is the reason the db is so very
slow.
>> It backs up the db without any problem, but it cannot
>> back up the transaction log...
>> As you can probably tell, I am fairly new to SQL
server...
>> Anything I can do to fix it?
>> >--Original Message--
>> >What errors are being reported by sqlmaint - should be
>> in one of the txt filesin the sql server logs
directory.
>> Youcan find the filename buy looking at the details of
>> the scheduled job that runs the optimisatons job.
>> >
>> >If the optimisations job i snever running it will man
>> indexes etc may be less than optimal but your
>> phrase "extremely slow" makes me suspect something else
>> is wrong as well.
>> >
>> >Mike John
>> >
>> >
>> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
in
>> message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
>> >> I cannot make the database optimize properly. I
have
>> a
>> >> maintenance plan setup specifically to do just that,
>> but
>> >> it is never able to run and our database is becoming
>> >> extremely slow. Any ideas?
>> >.
>> >
>
>.
>|||Hi,
Thanks for the suggestion about the showcontig. I will
try that later. I checked the 'attempt to repair minor
problems' in all three of our maint plans and none of
them have this option checked.
Thanks,
Kristen
>--Original Message--
>Kristen
>Have you tried using dbcc showcontig, this will show you
>if your tables are fragmented. (See BOL for details). If
>they are bably fragmented you will know the main plan is
>not working.
>Another thought is that in your maint plan have you
chosen
>the option 'attempt to repair minor problems' if you
have
>remove it. This regularly causes problems with maint
>plans. Any open connections and your plan will not run
>properly with this option.
>Hope this helps
>John
>.
>|||The main wizard can create a report file. Check the config of the maint
plan, make sure that you have specified a report file and check that report
file for error messages.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message
news:024701c3a87a$cdfe2fd0$a301280a@.phx.gbl...
> The reason I know it is not rebuilding indexes is because
> the history tells me so. There is always a red X next to
> rebuild indexes indicating it was not done. I looked
> through every error log at the times it reported that
> rebuilding indexes was not done and it said nothing about
> rebuilding indexes. That is why I say there is no error
> message. If there is a different place I should be
> looking please tell me because I do not know what an rtp
> file is.
>
> >--Original Message--
> >> As far as I can tell, there are no errors, it is just
> not
> >> doing it. In the History file, it tells me that it is
> >> checking the db, but it is not able to rebuild indexes,
> >
> >How do you know it isn't rebuilding indexes if there are
> no error messages?
> >We need the error message from the rtp file.
> >
> >
> >> It backs up the db without any problem, but it cannot
> >> back up the transaction log...
> >
> >That is probably not related. My guess is that the
> database is in simple
> >recovery mode. read more in Books Online about backup to
> understand recovery
> >mode and ability to do log backup.
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Kristen" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
> >> As far as I can tell, there are no errors, it is just
> not
> >> doing it. In the History file, it tells me that it is
> >> checking the db, but it is not able to rebuild indexes,
> >> which my boss thinks is the reason the db is so very
> slow.
> >>
> >> It backs up the db without any problem, but it cannot
> >> back up the transaction log...
> >>
> >> As you can probably tell, I am fairly new to SQL
> server...
> >>
> >> Anything I can do to fix it?
> >>
> >> >--Original Message--
> >> >What errors are being reported by sqlmaint - should be
> >> in one of the txt filesin the sql server logs
> directory.
> >> Youcan find the filename buy looking at the details of
> >> the scheduled job that runs the optimisatons job.
> >> >
> >> >If the optimisations job i snever running it will man
> >> indexes etc may be less than optimal but your
> >> phrase "extremely slow" makes me suspect something else
> >> is wrong as well.
> >> >
> >> >Mike John
> >> >
> >> >
> >> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
> in
> >> message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
> >> >> I cannot make the database optimize properly. I
> have
> >> a
> >> >> maintenance plan setup specifically to do just that,
> >> but
> >> >> it is never able to run and our database is becoming
> >> >> extremely slow. Any ideas?
> >> >.
> >> >
> >
> >
> >.
> >|||Thank you!
Here it is:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What does it mean?
>--Original Message--
>The main wizard can create a report file. Check the
config of the maint
>plan, make sure that you have specified a report file
and check that report
>file for error messages.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Kristen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:024701c3a87a$cdfe2fd0$a301280a@.phx.gbl...
>> The reason I know it is not rebuilding indexes is
because
>> the history tells me so. There is always a red X next
to
>> rebuild indexes indicating it was not done. I looked
>> through every error log at the times it reported that
>> rebuilding indexes was not done and it said nothing
about
>> rebuilding indexes. That is why I say there is no
error
>> message. If there is a different place I should be
>> looking please tell me because I do not know what an
rtp
>> file is.
>>
>> >--Original Message--
>> >> As far as I can tell, there are no errors, it is
just
>> not
>> >> doing it. In the History file, it tells me that it
is
>> >> checking the db, but it is not able to rebuild
indexes,
>> >
>> >How do you know it isn't rebuilding indexes if there
are
>> no error messages?
>> >We need the error message from the rtp file.
>> >
>> >
>> >> It backs up the db without any problem, but it
cannot
>> >> back up the transaction log...
>> >
>> >That is probably not related. My guess is that the
>> database is in simple
>> >recovery mode. read more in Books Online about backup
to
>> understand recovery
>> >mode and ability to do log backup.
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
>> >> As far as I can tell, there are no errors, it is
just
>> not
>> >> doing it. In the History file, it tells me that it
is
>> >> checking the db, but it is not able to rebuild
indexes,
>> >> which my boss thinks is the reason the db is so very
>> slow.
>> >>
>> >> It backs up the db without any problem, but it
cannot
>> >> back up the transaction log...
>> >>
>> >> As you can probably tell, I am fairly new to SQL
>> server...
>> >>
>> >> Anything I can do to fix it?
>> >>
>> >> >--Original Message--
>> >> >What errors are being reported by sqlmaint -
should be
>> >> in one of the txt filesin the sql server logs
>> directory.
>> >> Youcan find the filename buy looking at the details
of
>> >> the scheduled job that runs the optimisatons job.
>> >> >
>> >> >If the optimisations job i snever running it will
man
>> >> indexes etc may be less than optimal but your
>> >> phrase "extremely slow" makes me suspect something
else
>> >> is wrong as well.
>> >> >
>> >> >Mike John
>> >> >
>> >> >
>> >> >"Kristen" <anonymous@.discussions.microsoft.com>
wrote
>> in
>> >> message news:025e01c3a7d4$d3af1d00
$a501280a@.phx.gbl...
>> >> >> I cannot make the database optimize properly. I
>> have
>> >> a
>> >> >> maintenance plan setup specifically to do just
that,
>> >> but
>> >> >> it is never able to run and our database is
becoming
>> >> >> extremely slow. Any ideas?
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||It means that you either has a view with index on or a computed column with
an index on. And for these to be manipulated (index rebuilt in this case),
the session which executes that manipulation command (maintenance wizard
executing DBCC DBREINDEX in this case) need to have the SET commands
mentioned in the error message turned on. Unfortunately, the maint wizard
doesn't do that. Your options it to remove the reorg from the maint wizard
and do it yourself from an Agent TSQL jobstep (having the correct SET
options), or remove those indexes.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message
news:047f01c3a894$427adc00$a301280a@.phx.gbl...
> Thank you!
> Here it is:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
> failed because the following SET options have incorrect
> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What does it mean?
> >--Original Message--
> >The main wizard can create a report file. Check the
> config of the maint
> >plan, make sure that you have specified a report file
> and check that report
> >file for error messages.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Kristen" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:024701c3a87a$cdfe2fd0$a301280a@.phx.gbl...
> >> The reason I know it is not rebuilding indexes is
> because
> >> the history tells me so. There is always a red X next
> to
> >> rebuild indexes indicating it was not done. I looked
> >> through every error log at the times it reported that
> >> rebuilding indexes was not done and it said nothing
> about
> >> rebuilding indexes. That is why I say there is no
> error
> >> message. If there is a different place I should be
> >> looking please tell me because I do not know what an
> rtp
> >> file is.
> >>
> >>
> >> >--Original Message--
> >> >> As far as I can tell, there are no errors, it is
> just
> >> not
> >> >> doing it. In the History file, it tells me that it
> is
> >> >> checking the db, but it is not able to rebuild
> indexes,
> >> >
> >> >How do you know it isn't rebuilding indexes if there
> are
> >> no error messages?
> >> >We need the error message from the rtp file.
> >> >
> >> >
> >> >> It backs up the db without any problem, but it
> cannot
> >> >> back up the transaction log...
> >> >
> >> >That is probably not related. My guess is that the
> >> database is in simple
> >> >recovery mode. read more in Books Online about backup
> to
> >> understand recovery
> >> >mode and ability to do log backup.
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at:
> >> >http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
> in
> >> message
> >> >news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
> >> >> As far as I can tell, there are no errors, it is
> just
> >> not
> >> >> doing it. In the History file, it tells me that it
> is
> >> >> checking the db, but it is not able to rebuild
> indexes,
> >> >> which my boss thinks is the reason the db is so very
> >> slow.
> >> >>
> >> >> It backs up the db without any problem, but it
> cannot
> >> >> back up the transaction log...
> >> >>
> >> >> As you can probably tell, I am fairly new to SQL
> >> server...
> >> >>
> >> >> Anything I can do to fix it?
> >> >>
> >> >> >--Original Message--
> >> >> >What errors are being reported by sqlmaint -
> should be
> >> >> in one of the txt filesin the sql server logs
> >> directory.
> >> >> Youcan find the filename buy looking at the details
> of
> >> >> the scheduled job that runs the optimisatons job.
> >> >> >
> >> >> >If the optimisations job i snever running it will
> man
> >> >> indexes etc may be less than optimal but your
> >> >> phrase "extremely slow" makes me suspect something
> else
> >> >> is wrong as well.
> >> >> >
> >> >> >Mike John
> >> >> >
> >> >> >
> >> >> >"Kristen" <anonymous@.discussions.microsoft.com>
> wrote
> >> in
> >> >> message news:025e01c3a7d4$d3af1d00
> $a501280a@.phx.gbl...
> >> >> >> I cannot make the database optimize properly. I
> >> have
> >> >> a
> >> >> >> maintenance plan setup specifically to do just
> that,
> >> >> but
> >> >> >> it is never able to run and our database is
> becoming
> >> >> >> extremely slow. Any ideas?
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >