OpenXML Identity Insert: maintenance

Showing posts with label maintenance. Show all posts

Wednesday, March 28, 2012

Optimizing

Hi,
In Enterprise Manager 2000 as part of a Maintenance Plan. There is a Optimizations Tab. Running this and choosing Reorganize data w/original amt of free space, the same as Running dbreindex on the tables?
Any help would be appreciated.
Thank You
Essentially yes.

Andrew J. Kelly
SQL Server MVP
"Marc305" <anonymous@.discussions.microsoft.com> wrote in message
news:C49CF28B-DD22-4EB4-AFFB-EA22FFE83838@.microsoft.com...
> Hi,
> In Enterprise Manager 2000 as part of a Maintenance Plan. There is a
Optimizations Tab. Running this and choosing Reorganize data w/original amt
of free space, the same as Running dbreindex on the tables?
> Any help would be appreciated.
> Thank You

Optimizing

Hi,
In Enterprise Manager 2000 as part of a Maintenance Plan. There is a Optimiz
ations Tab. Running this and choosing Reorganize data w/original amt of free
space, the same as Running dbreindex on the tables?
Any help would be appreciated.
Thank YouEssentially yes.
Andrew J. Kelly
SQL Server MVP
"Marc305" <anonymous@.discussions.microsoft.com> wrote in message
news:C49CF28B-DD22-4EB4-AFFB-EA22FFE83838@.microsoft.com...
> Hi,
> In Enterprise Manager 2000 as part of a Maintenance Plan. There is a
Optimizations Tab. Running this and choosing Reorganize data w/original amt
of free space, the same as Running dbreindex on the tables?
> Any help would be appreciated.
> Thank You

Optimizing

Hi
In Enterprise Manager 2000 as part of a Maintenance Plan. There is a Optimizations Tab. Running this and choosing Reorganize data w/original amt of free space, the same as Running dbreindex on the tables
Any help would be appreciated
Thank YouEssentially yes.
Andrew J. Kelly
SQL Server MVP
"Marc305" <anonymous@.discussions.microsoft.com> wrote in message
news:C49CF28B-DD22-4EB4-AFFB-EA22FFE83838@.microsoft.com...
> Hi,
> In Enterprise Manager 2000 as part of a Maintenance Plan. There is a
Optimizations Tab. Running this and choosing Reorganize data w/original amt
of free space, the same as Running dbreindex on the tables?
> Any help would be appreciated.
> Thank You

Friday, March 23, 2012

optimizations job for db maintenance plan failed

This is happening on two of our servers.
We get the warning in the application log as seen here:
http://support.microsoft.com/kb/902388/
But we don't get the SQL Server log entry that is mentioned in that KB
article.
Here are the commands from the jobs (after adding the
option -SupportComputedColumn , as recommended in the KB article) :
Server 1:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
Server 2:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
"G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
10 -SupportComputedColumn '
Any suggestions, anyone?
Regards,
JimDo you have the right service pack to support that?
--
Andrew J. Kelly SQL MVP
"Jim Moon" <please.reply@.group> wrote in message
news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> This is happening on two of our servers.
> We get the warning in the application log as seen here:
> http://support.microsoft.com/kb/902388/
> But we don't get the SQL Server log entry that is mentioned in that KB
> article.
> Here are the commands from the jobs (after adding the
> option -SupportComputedColumn , as recommended in the KB article) :
> Server 1:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
> 10 -SupportComputedColumn '
> Server 2:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
> "G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
> 10 -SupportComputedColumn '
> Any suggestions, anyone?
> Regards,
> Jim
>|||Does anyone at all have any ideas?
I would be very grateful for any relevant feedback! :-)
Thanks,
Jim
"Jim Moon" <please.reply@.group> wrote in message
news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> This is happening on two of our servers.
> We get the warning in the application log as seen here:
> http://support.microsoft.com/kb/902388/
> But we don't get the SQL Server log entry that is mentioned in that KB
> article.
> Here are the commands from the jobs (after adding the
> option -SupportComputedColumn , as recommended in the KB article) :
> Server 1:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
> 10 -SupportComputedColumn '
> Server 2:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
> "G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
> 10 -SupportComputedColumn '
> Any suggestions, anyone?
> Regards,
> Jim
>|||Why would that not be relevant? This feature was not a part of SQL Server
until Service Pack 4. Since you did not post what version you are using it
is quite feasible that it is not working because that service pack does not
support it.
--
Andrew J. Kelly SQL MVP
"Jim Moon" <please.reply@.group> wrote in message
news:%23KBVO%23xXGHA.3496@.TK2MSFTNGP05.phx.gbl...
> Does anyone at all have any ideas?
> I would be very grateful for any relevant feedback! :-)
> Thanks,
> Jim
> "Jim Moon" <please.reply@.group> wrote in message
> news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> This is happening on two of our servers.
>> We get the warning in the application log as seen here:
>> http://support.microsoft.com/kb/902388/
>> But we don't get the SQL Server log entry that is mentioned in that KB
>> article.
>> Here are the commands from the jobs (after adding the
>> option -SupportComputedColumn , as recommended in the KB article) :
>> Server 1:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
>> 10 -SupportComputedColumn '
>> Server 2:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
>> "G:\MSSQL\MSSQL\LOG\User DB
>> aintenance0.txt" -WriteHistory -UpdOptiStats 10 -SupportComputedColumn '
>> Any suggestions, anyone?
>> Regards,
>> Jim
>|||Hi Andrew,
It is SQL Server SP4.
Regards,
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OElKDt1XGHA.4476@.TK2MSFTNGP05.phx.gbl...
> Why would that not be relevant? This feature was not a part of SQL Server
> until Service Pack 4. Since you did not post what version you are using
> it is quite feasible that it is not working because that service pack does
> not support it.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Moon" <please.reply@.group> wrote in message
> news:%23KBVO%23xXGHA.3496@.TK2MSFTNGP05.phx.gbl...
>> Does anyone at all have any ideas?
>> I would be very grateful for any relevant feedback! :-)
>> Thanks,
>> Jim
>> "Jim Moon" <please.reply@.group> wrote in message
>> news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> This is happening on two of our servers.
>> We get the warning in the application log as seen here:
>> http://support.microsoft.com/kb/902388/
>> But we don't get the SQL Server log entry that is mentioned in that KB
>> article.
>> Here are the commands from the jobs (after adding the
>> option -SupportComputedColumn , as recommended in the KB article) :
>> Server 1:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
>> 10 -SupportComputedColumn '
>> Server 2:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
>> "G:\MSSQL\MSSQL\LOG\User DB
>> intenance0.txt" -WriteHistory -UpdOptiStats 10 -SupportComputedColumn '
>> Any suggestions, anyone?
>> Regards,
>> Jim
>>
>|||Jim,
SP4 and that option was supposed to fix those issues and I haven't heard of
anyone else using them and it not working as expected. Unless someone does
pipe in with something else you really have two options. One is to call MS
PSS and report this. If it is a bug they will refund your initial trouble
ticket charge. If not it will cost about $225. The other and recommended
option is to forget about the Maintenance plans either altogether or at
least for this part. It looks like you are having issues with the updating
of the stats. First I would ask why you are using the MP for that? Do you
also reindex each table either with DBCC DBREINDEX or with the maintenance
plans? If so then that will update the stats for you and this step is
unnecessary. Otherwise you can create your own job and simply run
sp_updatestats and be done with it.
--
Andrew J. Kelly SQL MVP
"Jim Moon" <please.reply@.group> wrote in message
news:uScFlH%23XGHA.508@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> It is SQL Server SP4.
> Regards,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OElKDt1XGHA.4476@.TK2MSFTNGP05.phx.gbl...
>> Why would that not be relevant? This feature was not a part of SQL
>> Server until Service Pack 4. Since you did not post what version you are
>> using it is quite feasible that it is not working because that service
>> pack does not support it.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jim Moon" <please.reply@.group> wrote in message
>> news:%23KBVO%23xXGHA.3496@.TK2MSFTNGP05.phx.gbl...
>> Does anyone at all have any ideas?
>> I would be very grateful for any relevant feedback! :-)
>> Thanks,
>> Jim
>> "Jim Moon" <please.reply@.group> wrote in message
>> news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> This is happening on two of our servers.
>> We get the warning in the application log as seen here:
>> http://support.microsoft.com/kb/902388/
>> But we don't get the SQL Server log entry that is mentioned in that KB
>> article.
>> Here are the commands from the jobs (after adding the
>> option -SupportComputedColumn , as recommended in the KB article) :
>> Server 1:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
>> 10 -SupportComputedColumn '
>> Server 2:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
>> "G:\MSSQL\MSSQL\LOG\User DB
>> ntenance0.txt" -WriteHistory -UpdOptiStats 10 -SupportComputedColumn '
>> Any suggestions, anyone?
>> Regards,
>> Jim
>>
>>
>

optimizations job for db maintenance plan failed

Optimizations job failure - SQLServer2000

Hi all,
I created a database maintenance plan with backup + transsactionlog backup+optimizations. The first two jobs work fine but the Optimizations job is failing with error:
sqlmaint.exe failed. SQLSTATE 42000, Error: 22029. The step failed.

User is admin on local box and "sysadmin" in db server role. Any suggestions to resolve this?

Thanks
Vinnie...check out this link.

http://support.microsoft.com/default.aspx?scid=kb;en-us;326485

Optimizations job failure

Anyone's help is appreciated in resolving this error:

I created a Database Maintenance plan with backup+transactionlog backup+ optimizations. The first two jobs run fine but optimizations job fails with:

sqlmaint.exe failed [SQLSTATE 42000] ( Error 22029). The step failed.

The user that is running all the jobs is "sysadmin" in database and member of administrator on local machine.

I am trying to run this when the users are using the system.

Any thoughts how I can fix this?

Thanks
VinnieIn the Job History dialog box, check the "show details" box in the upper right corner. If you know why the job failed, you'll have a much better shot at fixing it!

-PatP|||Thanks for the suggestion. The error message I wrote down is from the steps details after checking the box on right hand corner and looking through the steps information why it failed. The step 1 gave that info
sqlmaint.exe failed. SQLSTATE 42000 error 22029. The step failed.

Any other thoughts?

Thanks
Vinnie|||OK What's the second line say?|||This is the second line.

The first line was: The job Failed. The Job was invoked by User HBOCD01\VKaramc. The last step to run was step 1 (Step 1).|||...I think you've posted this subject twice. Check out the link I posted in the
other post.|||Ohhh..you mean this Link (http://support.microsoft.com/default.aspx?scid=kb;en-us;326485)

Why don't you show us the code in one step that didn't fail, and one that did...|||Thanks for the link. Though the resolutions expressed in the link text have only 2 workarounds for my problem, I guess I can work it out.

Vinnie

Optimizations Job and Shrink DB creating HUGE transaction log file

I've noticed for the past two weeks that during the time when my
Optimizations Job and Shrink Database job from my Database Maintenance Plan
run, they are creating some HUGE transaction log file backups. For a 13GB
db, the Optimizations is making a 2+GB tran log. The Shrink job made a 10GB
tran log this morning.
I've never noticed such huge logs before so I'm wondering how I can figure
out why those 2 jobs have just started doing this. I know its these jobs due
to the timing being identical the past two weeks.Rich
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.|||The rebuilding of indexes is normally a fully logged operation as long as
you are in FULL recovery mode. The shrinking is always fully logged. Both of
these can generate lots of log entries. It may be that you have an open long
running tran that is preventing the log files from being truncated and thus
are seeing larger than normal file size. But the real question is why are
you shrinking the DB in the first place. That is destroying all that you
just did by reindexing. See here for more details:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.|||Thanks to both of you for posting that article. So should I just turn the
shrink off completely? or maybe only do it once in a great while. I see the
points both of you brought up and the ones brought up in the article. Here
is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
reason its setup to do that is because the person before me set it up like
that...trying to figure out what would be best now.
"Andrew J. Kelly" wrote:
> The rebuilding of indexes is normally a fully logged operation as long as
> you are in FULL recovery mode. The shrinking is always fully logged. Both of
> these can generate lots of log entries. It may be that you have an open long
> running tran that is preventing the log files from being truncated and thus
> are seeing larger than normal file size. But the real question is why are
> you shrinking the DB in the first place. That is destroying all that you
> just did by reindexing. See here for more details:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> > I've noticed for the past two weeks that during the time when my
> > Optimizations Job and Shrink Database job from my Database Maintenance
> > Plan
> > run, they are creating some HUGE transaction log file backups. For a 13GB
> > db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> > 10GB
> > tran log this morning.
> > I've never noticed such huge logs before so I'm wondering how I can figure
> > out why those 2 jobs have just started doing this. I know its these jobs
> > due
> > to the timing being identical the past two weeks.
>
>|||Rich
Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
> Thanks to both of you for posting that article. So should I just turn the
> shrink off completely? or maybe only do it once in a great while. I see
> the
> points both of you brought up and the ones brought up in the article.
> Here
> is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> reason its setup to do that is because the person before me set it up like
> that...trying to figure out what would be best now.
> "Andrew J. Kelly" wrote:
>> The rebuilding of indexes is normally a fully logged operation as long as
>> you are in FULL recovery mode. The shrinking is always fully logged. Both
>> of
>> these can generate lots of log entries. It may be that you have an open
>> long
>> running tran that is preventing the log files from being truncated and
>> thus
>> are seeing larger than normal file size. But the real question is why are
>> you shrinking the DB in the first place. That is destroying all that you
>> just did by reindexing. See here for more details:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Andrew J. Kelly SQL MVP
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
>> > I've noticed for the past two weeks that during the time when my
>> > Optimizations Job and Shrink Database job from my Database Maintenance
>> > Plan
>> > run, they are creating some HUGE transaction log file backups. For a
>> > 13GB
>> > db, the Optimizations is making a 2+GB tran log. The Shrink job made a
>> > 10GB
>> > tran log this morning.
>> > I've never noticed such huge logs before so I'm wondering how I can
>> > figure
>> > out why those 2 jobs have just started doing this. I know its these
>> > jobs
>> > due
>> > to the timing being identical the past two weeks.
>>|||Also, here's what my optimizations job says. maybe this will help make it
clearer.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
55FB40C3-34D7-4E24-84CC-A21DB53F752C -WriteHistory -RebldIdx 100
-RmUnusedSpace 10 1 '
"Rich" wrote:
> Thanks to both of you for posting that article. So should I just turn the
> shrink off completely? or maybe only do it once in a great while. I see the
> points both of you brought up and the ones brought up in the article. Here
> is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> reason its setup to do that is because the person before me set it up like
> that...trying to figure out what would be best now.
> "Andrew J. Kelly" wrote:
> > The rebuilding of indexes is normally a fully logged operation as long as
> > you are in FULL recovery mode. The shrinking is always fully logged. Both of
> > these can generate lots of log entries. It may be that you have an open long
> > running tran that is preventing the log files from being truncated and thus
> > are seeing larger than normal file size. But the real question is why are
> > you shrinking the DB in the first place. That is destroying all that you
> > just did by reindexing. See here for more details:
> >
> > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> > "Rich" <Rich@.discussions.microsoft.com> wrote in message
> > news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> > > I've noticed for the past two weeks that during the time when my
> > > Optimizations Job and Shrink Database job from my Database Maintenance
> > > Plan
> > > run, they are creating some HUGE transaction log file backups. For a 13GB
> > > db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> > > 10GB
> > > tran log this morning.
> > > I've never noticed such huge logs before so I'm wondering how I can figure
> > > out why those 2 jobs have just started doing this. I know its these jobs
> > > due
> > > to the timing being identical the past two weeks.
> >
> >
> >|||yeah, it sounds like i should just kill my shrink job completely. But how
would I know in the future if i need to shrink it manually? Is there a good
way to tell?
"Uri Dimant" wrote:
> Rich
> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
> > Thanks to both of you for posting that article. So should I just turn the
> > shrink off completely? or maybe only do it once in a great while. I see
> > the
> > points both of you brought up and the ones brought up in the article.
> > Here
> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> > reason its setup to do that is because the person before me set it up like
> > that...trying to figure out what would be best now.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> The rebuilding of indexes is normally a fully logged operation as long as
> >> you are in FULL recovery mode. The shrinking is always fully logged. Both
> >> of
> >> these can generate lots of log entries. It may be that you have an open
> >> long
> >> running tran that is preventing the log files from being truncated and
> >> thus
> >> are seeing larger than normal file size. But the real question is why are
> >> you shrinking the DB in the first place. That is destroying all that you
> >> just did by reindexing. See here for more details:
> >>
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> >> > I've noticed for the past two weeks that during the time when my
> >> > Optimizations Job and Shrink Database job from my Database Maintenance
> >> > Plan
> >> > run, they are creating some HUGE transaction log file backups. For a
> >> > 13GB
> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> >> > 10GB
> >> > tran log this morning.
> >> > I've never noticed such huge logs before so I'm wondering how I can
> >> > figure
> >> > out why those 2 jobs have just started doing this. I know its these
> >> > jobs
> >> > due
> >> > to the timing being identical the past two weeks.
> >>
> >>
> >>
>
>|||Rich
If you run out of space on disk so that's is probably time to shrink the
data but it is short term solution as you know you shrin the file will be
grown again.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
> yeah, it sounds like i should just kill my shrink job completely. But how
> would I know in the future if i need to shrink it manually? Is there a
> good
> way to tell?
> "Uri Dimant" wrote:
>> Rich
>> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
>> > Thanks to both of you for posting that article. So should I just turn
>> > the
>> > shrink off completely? or maybe only do it once in a great while. I
>> > see
>> > the
>> > points both of you brought up and the ones brought up in the article.
>> > Here
>> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0).
>> > The
>> > reason its setup to do that is because the person before me set it up
>> > like
>> > that...trying to figure out what would be best now.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> The rebuilding of indexes is normally a fully logged operation as long
>> >> as
>> >> you are in FULL recovery mode. The shrinking is always fully logged.
>> >> Both
>> >> of
>> >> these can generate lots of log entries. It may be that you have an
>> >> open
>> >> long
>> >> running tran that is preventing the log files from being truncated and
>> >> thus
>> >> are seeing larger than normal file size. But the real question is why
>> >> are
>> >> you shrinking the DB in the first place. That is destroying all that
>> >> you
>> >> just did by reindexing. See here for more details:
>> >>
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
>> >> > I've noticed for the past two weeks that during the time when my
>> >> > Optimizations Job and Shrink Database job from my Database
>> >> > Maintenance
>> >> > Plan
>> >> > run, they are creating some HUGE transaction log file backups. For
>> >> > a
>> >> > 13GB
>> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job
>> >> > made a
>> >> > 10GB
>> >> > tran log this morning.
>> >> > I've never noticed such huge logs before so I'm wondering how I can
>> >> > figure
>> >> > out why those 2 jobs have just started doing this. I know its these
>> >> > jobs
>> >> > due
>> >> > to the timing being identical the past two weeks.
>> >>
>> >>
>> >>
>>|||ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part in
my Optimizations job ok or would that need to be removed as well. the full
command is in my previous posts.
"Uri Dimant" wrote:
> Rich
> If you run out of space on disk so that's is probably time to shrink the
> data but it is short term solution as you know you shrin the file will be
> grown again.
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
> > yeah, it sounds like i should just kill my shrink job completely. But how
> > would I know in the future if i need to shrink it manually? Is there a
> > good
> > way to tell?
> >
> > "Uri Dimant" wrote:
> >
> >> Rich
> >> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
> >> > Thanks to both of you for posting that article. So should I just turn
> >> > the
> >> > shrink off completely? or maybe only do it once in a great while. I
> >> > see
> >> > the
> >> > points both of you brought up and the ones brought up in the article.
> >> > Here
> >> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0).
> >> > The
> >> > reason its setup to do that is because the person before me set it up
> >> > like
> >> > that...trying to figure out what would be best now.
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> The rebuilding of indexes is normally a fully logged operation as long
> >> >> as
> >> >> you are in FULL recovery mode. The shrinking is always fully logged.
> >> >> Both
> >> >> of
> >> >> these can generate lots of log entries. It may be that you have an
> >> >> open
> >> >> long
> >> >> running tran that is preventing the log files from being truncated and
> >> >> thus
> >> >> are seeing larger than normal file size. But the real question is why
> >> >> are
> >> >> you shrinking the DB in the first place. That is destroying all that
> >> >> you
> >> >> just did by reindexing. See here for more details:
> >> >>
> >> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> >> >> > I've noticed for the past two weeks that during the time when my
> >> >> > Optimizations Job and Shrink Database job from my Database
> >> >> > Maintenance
> >> >> > Plan
> >> >> > run, they are creating some HUGE transaction log file backups. For
> >> >> > a
> >> >> > 13GB
> >> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job
> >> >> > made a
> >> >> > 10GB
> >> >> > tran log this morning.
> >> >> > I've never noticed such huge logs before so I'm wondering how I can
> >> >> > figure
> >> >> > out why those 2 jobs have just started doing this. I know its these
> >> >> > jobs
> >> >> > due
> >> >> > to the timing being identical the past two weeks.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Well you should be careful of editing the job itself. I would open the
wizard and uncheck the options there and the wizard will edit the
appropriate jobs to account for it.
--
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:80288B98-E7ED-4270-93D5-9F90914D1F6A@.microsoft.com...
> ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part
> in
> my Optimizations job ok or would that need to be removed as well. the
> full
> command is in my previous posts.
> "Uri Dimant" wrote:
>> Rich
>> If you run out of space on disk so that's is probably time to shrink the
>> data but it is short term solution as you know you shrin the file will be
>> grown again.
>>
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
>> > yeah, it sounds like i should just kill my shrink job completely. But
>> > how
>> > would I know in the future if i need to shrink it manually? Is there a
>> > good
>> > way to tell?
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Rich
>> >> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
>> >>
>> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
>> >> > Thanks to both of you for posting that article. So should I just
>> >> > turn
>> >> > the
>> >> > shrink off completely? or maybe only do it once in a great while.
>> >> > I
>> >> > see
>> >> > the
>> >> > points both of you brought up and the ones brought up in the
>> >> > article.
>> >> > Here
>> >> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0).
>> >> > The
>> >> > reason its setup to do that is because the person before me set it
>> >> > up
>> >> > like
>> >> > that...trying to figure out what would be best now.
>> >> >
>> >> > "Andrew J. Kelly" wrote:
>> >> >
>> >> >> The rebuilding of indexes is normally a fully logged operation as
>> >> >> long
>> >> >> as
>> >> >> you are in FULL recovery mode. The shrinking is always fully
>> >> >> logged.
>> >> >> Both
>> >> >> of
>> >> >> these can generate lots of log entries. It may be that you have an
>> >> >> open
>> >> >> long
>> >> >> running tran that is preventing the log files from being truncated
>> >> >> and
>> >> >> thus
>> >> >> are seeing larger than normal file size. But the real question is
>> >> >> why
>> >> >> are
>> >> >> you shrinking the DB in the first place. That is destroying all
>> >> >> that
>> >> >> you
>> >> >> just did by reindexing. See here for more details:
>> >> >>
>> >> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >> >>
>> >> >> --
>> >> >> Andrew J. Kelly SQL MVP
>> >> >>
>> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
>> >> >> > I've noticed for the past two weeks that during the time when my
>> >> >> > Optimizations Job and Shrink Database job from my Database
>> >> >> > Maintenance
>> >> >> > Plan
>> >> >> > run, they are creating some HUGE transaction log file backups.
>> >> >> > For
>> >> >> > a
>> >> >> > 13GB
>> >> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job
>> >> >> > made a
>> >> >> > 10GB
>> >> >> > tran log this morning.
>> >> >> > I've never noticed such huge logs before so I'm wondering how I
>> >> >> > can
>> >> >> > figure
>> >> >> > out why those 2 jobs have just started doing this. I know its
>> >> >> > these
>> >> >> > jobs
>> >> >> > due
>> >> >> > to the timing being identical the past two weeks.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||OK, thanks. I just wanted to make sure that I should get rid of BOTH the
Shrink job and that remove unused space part.
"Andrew J. Kelly" wrote:
> Well you should be careful of editing the job itself. I would open the
> wizard and uncheck the options there and the wizard will edit the
> appropriate jobs to account for it.
> --
> Andrew J. Kelly SQL MVP
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:80288B98-E7ED-4270-93D5-9F90914D1F6A@.microsoft.com...
> > ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part
> > in
> > my Optimizations job ok or would that need to be removed as well. the
> > full
> > command is in my previous posts.
> >
> > "Uri Dimant" wrote:
> >
> >> Rich
> >> If you run out of space on disk so that's is probably time to shrink the
> >> data but it is short term solution as you know you shrin the file will be
> >> grown again.
> >>
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
> >> > yeah, it sounds like i should just kill my shrink job completely. But
> >> > how
> >> > would I know in the future if i need to shrink it manually? Is there a
> >> > good
> >> > way to tell?
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Rich
> >> >> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
> >> >>
> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> >> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
> >> >> > Thanks to both of you for posting that article. So should I just
> >> >> > turn
> >> >> > the
> >> >> > shrink off completely? or maybe only do it once in a great while.
> >> >> > I
> >> >> > see
> >> >> > the
> >> >> > points both of you brought up and the ones brought up in the
> >> >> > article.
> >> >> > Here
> >> >> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0).
> >> >> > The
> >> >> > reason its setup to do that is because the person before me set it
> >> >> > up
> >> >> > like
> >> >> > that...trying to figure out what would be best now.
> >> >> >
> >> >> > "Andrew J. Kelly" wrote:
> >> >> >
> >> >> >> The rebuilding of indexes is normally a fully logged operation as
> >> >> >> long
> >> >> >> as
> >> >> >> you are in FULL recovery mode. The shrinking is always fully
> >> >> >> logged.
> >> >> >> Both
> >> >> >> of
> >> >> >> these can generate lots of log entries. It may be that you have an
> >> >> >> open
> >> >> >> long
> >> >> >> running tran that is preventing the log files from being truncated
> >> >> >> and
> >> >> >> thus
> >> >> >> are seeing larger than normal file size. But the real question is
> >> >> >> why
> >> >> >> are
> >> >> >> you shrinking the DB in the first place. That is destroying all
> >> >> >> that
> >> >> >> you
> >> >> >> just did by reindexing. See here for more details:
> >> >> >>
> >> >> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >> >> >>
> >> >> >> --
> >> >> >> Andrew J. Kelly SQL MVP
> >> >> >>
> >> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> >> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> >> >> >> > I've noticed for the past two weeks that during the time when my
> >> >> >> > Optimizations Job and Shrink Database job from my Database
> >> >> >> > Maintenance
> >> >> >> > Plan
> >> >> >> > run, they are creating some HUGE transaction log file backups.
> >> >> >> > For
> >> >> >> > a
> >> >> >> > 13GB
> >> >> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job
> >> >> >> > made a
> >> >> >> > 10GB
> >> >> >> > tran log this morning.
> >> >> >> > I've never noticed such huge logs before so I'm wondering how I
> >> >> >> > can
> >> >> >> > figure
> >> >> >> > out why those 2 jobs have just started doing this. I know its
> >> >> >> > these
> >> >> >> > jobs
> >> >> >> > due
> >> >> >> > to the timing being identical the past two weeks.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Optimizations Job and Shrink DB creating HUGE transaction log file

Wednesday, March 21, 2012

Optimization Jobs Fails

My SQL Server 2000 database optimization job fails each time it runs. This
job was created from the SQL Server database maintenance plan.
The "Reorganize data and index pages" and "Reorganize pages with the
original amount of free space" are checked parameters.
Error
Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
[Error 22029]. The step failed.
Please help me resolve the error listed above.
Thanks,
That error message doesn't give us anything to go on, it is only Agent telling us it failed. Specify
a report file for the plan and look in that report file. Or open the maint wiz folder in EM, and
look at the history for a failed execution from there.
My guess is that you have some indexes on views or computed columns and maint wiz doesn't set the
needed SET setting in order to reorg such indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9343D047-7572-4A1F-A9BA-0CD9644B6550@.microsoft.com...
> My SQL Server 2000 database optimization job fails each time it runs. This
> job was created from the SQL Server database maintenance plan.
> The "Reorganize data and index pages" and "Reorganize pages with the
> original amount of free space" are checked parameters.
> Error
> Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
> [Error 22029]. The step failed.
> Please help me resolve the error listed above.
> Thanks,
>

Optimization Jobs Fails

My SQL Server 2000 database optimization job fails each time it runs. This
job was created from the SQL Server database maintenance plan.
The "Reorganize data and index pages" and "Reorganize pages with the
original amount of free space" are checked parameters.
Error
Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 420
00]
[Error 22029]. The step failed.
Please help me resolve the error listed above.
Thanks,That error message doesn't give us anything to go on, it is only Agent telli
ng us it failed. Specify
a report file for the plan and look in that report file. Or open the maint w
iz folder in EM, and
look at the history for a failed execution from there.
My guess is that you have some indexes on views or computed columns and main
t wiz doesn't set the
needed SET setting in order to reorg such indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9343D047-7572-4A1F-A9BA-0CD9644B6550@.microsoft.com...
> My SQL Server 2000 database optimization job fails each time it runs. Thi
s
> job was created from the SQL Server database maintenance plan.
> The "Reorganize data and index pages" and "Reorganize pages with the
> original amount of free space" are checked parameters.
> Error
> Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 4
2000]
> [Error 22029]. The step failed.
> Please help me resolve the error listed above.
> Thanks,
>

Optimization Jobs Fails

My SQL Server 2000 database optimization job fails each time it runs. This
job was created from the SQL Server database maintenance plan.
The "Reorganize data and index pages" and "Reorganize pages with the
original amount of free space" are checked parameters.
Error
Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
[Error 22029]. The step failed.
Please help me resolve the error listed above.
Thanks,That error message doesn't give us anything to go on, it is only Agent telling us it failed. Specify
a report file for the plan and look in that report file. Or open the maint wiz folder in EM, and
look at the history for a failed execution from there.
My guess is that you have some indexes on views or computed columns and maint wiz doesn't set the
needed SET setting in order to reorg such indexes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9343D047-7572-4A1F-A9BA-0CD9644B6550@.microsoft.com...
> My SQL Server 2000 database optimization job fails each time it runs. This
> job was created from the SQL Server database maintenance plan.
> The "Reorganize data and index pages" and "Reorganize pages with the
> original amount of free space" are checked parameters.
> Error
> Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
> [Error 22029]. The step failed.
> Please help me resolve the error listed above.
> Thanks,
>

Optimization job problem

I have this problem with my optimization job seems to fail all the time.
I have this set up as a sql maintenance plan and this is run 1 every week. i have checked for things that could comme in conflict but theirs nothing. here is the error i am getting from the job history step.

Executed as user: SAPCORP\adminsg. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

PLZ HELP>Check by profiler what is going on on server during this job and what last statement was executed without success.|||How do u do this iam a beginner|||Originally posted by hillcat
How do u do this iam a beginner

From EM click on tools/sql profiler. Find last one from start/programs/microsoft sql server/sql profiler. Try books on line - i hope you do have it. It is difficult to describe how to use profiler in couple words - but it is really simple. For future you will have very powerful tool.

Optimization Job fails

Hi everyone,
We have more than 100 databases on one SQL Server instance and all databases
are backed up and optimised in the nightly maintenance plan.
I copied a new database in this instance that has some computed columns in
few tables. Now, I'm getting this error in daily maintenance plan's
optimization job that it failed because "Quoted_Identifier" is OFF.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Any idea/help to overcome this situation. I have some other databases with
computed columns and have same set of database options. There is no problem
with those databases. Only one database is causing this. Any idea ?
Thanks in advance.Sqlmaint.exe does not set quoted_identified on by default. You should create
a T-SQL job which sets this option. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;301292 for a similar
problem.
Adrian
"Ahmed" <Ahmed@.discussions.microsoft.com> wrote in message
news:1E48F884-3DBD-465E-BDB9-0D0B110BA832@.microsoft.com...
> Hi everyone,
> We have more than 100 databases on one SQL Server instance and all
> databases
> are backed up and optimised in the nightly maintenance plan.
> I copied a new database in this instance that has some computed columns in
> few tables. Now, I'm getting this error in daily maintenance plan's
> optimization job that it failed because "Quoted_Identifier" is OFF.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]UPDATE STATISTICS failed because the following
> SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Any idea/help to overcome this situation. I have some other databases with
> computed columns and have same set of database options. There is no
> problem
> with those databases. Only one database is causing this. Any idea ?
> Thanks in advance.
>

Optimization Job fails

Hi everyone,
We have more than 100 databases on one SQL Server instance and all databases
are backed up and optimised in the nightly maintenance plan.
I copied a new database in this instance that has some computed columns in
few tables. Now, I'm getting this error in daily maintenance plan's
optimization job that it failed because "Quoted_Identifier" is OFF.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Any idea/help to overcome this situation. I have some other databases with
computed columns and have same set of database options. There is no problem
with those databases. Only one database is causing this. Any idea ?
Thanks in advance.
Sqlmaint.exe does not set quoted_identified on by default. You should create
a T-SQL job which sets this option. See
http://support.microsoft.com/default...b;en-us;301292 for a similar
problem.
Adrian
"Ahmed" <Ahmed@.discussions.microsoft.com> wrote in message
news:1E48F884-3DBD-465E-BDB9-0D0B110BA832@.microsoft.com...
> Hi everyone,
> We have more than 100 databases on one SQL Server instance and all
> databases
> are backed up and optimised in the nightly maintenance plan.
> I copied a new database in this instance that has some computed columns in
> few tables. Now, I'm getting this error in daily maintenance plan's
> optimization job that it failed because "Quoted_Identifier" is OFF.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]UPDATE STATISTICS failed because the following
> SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Any idea/help to overcome this situation. I have some other databases with
> computed columns and have same set of database options. There is no
> problem
> with those databases. Only one database is causing this. Any idea ?
> Thanks in advance.
>

Optimization Job fails

Hi everyone,
We have more than 100 databases on one SQL Server instance and all databases
are backed up and optimised in the nightly maintenance plan.
I copied a new database in this instance that has some computed columns in
few tables. Now, I'm getting this error in daily maintenance plan's
optimization job that it failed because "Quoted_Identifier" is OFF.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]UPDATE STATISTICS failed because the following
SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Any idea/help to overcome this situation. I have some other databases with
computed columns and have same set of database options. There is no problem
with those databases. Only one database is causing this. Any idea ?
Thanks in advance.Sqlmaint.exe does not set quoted_identified on by default. You should create
a T-SQL job which sets this option. See
http://support.microsoft.com/defaul...kb;en-us;301292 for a similar
problem.
Adrian
"Ahmed" <Ahmed@.discussions.microsoft.com> wrote in message
news:1E48F884-3DBD-465E-BDB9-0D0B110BA832@.microsoft.com...
> Hi everyone,
> We have more than 100 databases on one SQL Server instance and all
> databases
> are backed up and optimised in the nightly maintenance plan.
> I copied a new database in this instance that has some computed columns in
> few tables. Now, I'm getting this error in daily maintenance plan's
> optimization job that it failed because "Quoted_Identifier" is OFF.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC
> SQL
> Server Driver][SQL Server]UPDATE STATISTICS failed because the followi
ng
> SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Any idea/help to overcome this situation. I have some other databases with
> computed columns and have same set of database options. There is no
> problem
> with those databases. Only one database is causing this. Any idea ?
> Thanks in advance.
>

Optimization issues with a Database Maintenance Plan

Hello all:

I need some help with what the Reorganize data and index pages with change free space per page percentage to 10% does.

Scenario: I have a replicated database using transactional replication with another server. Whenever this part of the maintenance plan runs, our application malfunctions until this part of the SQL maintenance plan completes. When finished, our application runs just fine...

After disabling this portion of the Database Maintenance Plan, all issues went away.

SQL Version: SQL2000 Standard Edition Service Pack 4

Servers: Dual Xeon 3.0 GHz hyper threaded with 4GB of RAM in each server.

I originally thought that this basically did a re-index or defrag/re-index of our database. Until looking at the job step, I found I was wrong, it does a -RebldIdx 10, I assume a rebuild of the indexes.

Questions:

Does this procedure lock tables in the database preventing access to them, thus causing lousy performance in our application as well as application failures?

Does this cause massive database changes which will be replicated to the secondary server or this independent of replication? Reason for asking, I do not recall seeing this behavior on some of our other systems that are on servers that do not replicate.

What is the difference between the rebuilding of indexes and just re-indexing?

Is this important to run against a database to keep it "streamlined" and if so how often should I run this?

Any help, comments, or opinions will be most appreciated.

Thank you - JPD.

JPD,

The tables are locked sequentially in the order they appear in the object list for the database. Each index for that table then has its index(s) rebuilt completely. During this time, the data in the underlying table cannot be accessed by another process.

DBCC DBREINDEX is what is used by the jobs created by the Maintenance Plan Wizard within Enterprise Manager. The maintenance plan actually calls the sqlmaint utility which then issues the TSQL. The -Rebldidx sqlmaint parameter within the maintenance job tells sqlmaint to run DBCC DBREINDEX command against all of the tables in the database, one at a time at which time all of the indexes for each table are rebuilt with fill factor inverse of the amount specified. -rebldix 10 is the same as specifying fill factor of 90%. If no amount is specified, then the value used when the index was created will be used.

Please note that while a tables indexes are being rebuilt by DBCC DBREINDEX (our implementation jobs and maintenance plan jobs) the table is LOCKED. This will cause errors and delays in the application component waiting on data from that table or a view referencing that table. The information below explains this and is cut and paste from Microsoft TechNet (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx).

DBCC DBREINDEX

DBCC DBREINDEX can be used to rebuild one or more indexes for a specific table. DBCC DBREINDEX is an offline operation. While this operation is running, the underlying table is unavailable to users of the database. DBCC DBREINDEX rebuilds indexes dynamically. You do not have to know anything about the underlying table structure, nor any PRIMARY KEY or UNIQUE constraints; these are preserved automatically during the rebuild. DBCC DBREINDEX completely rebuilds the indexes, so it restores the page density levels to the original fill factor (default); or you can choose another target value for the page density. Internally, running DBCC DBREINDEX is very similar to using Transact-SQL statements to drop and re-create the indexes manually.

All work done by DBCC DBREINDEX occurs as a single, atomic transaction. The new indexes must be completely built and in place before the old index pages are released. Performing the rebuild requires adequate free space in the data file(s). With not enough free space in the data file(s), DBCC DBREINDEX may be unable to rebuild the indexes, or the indexes may be rebuilt with logical fragmentation values above zero. The amount of free space needed varies and is dependent on the number of indexes being created in the transaction. For clustered indexes, a good guideline is: Required free space = 1.2 * (average rowsize) * (number of rows).

DBCC INDEXDEFRAG

DBCC INDEXDEFRAG allows you to rebuild a specific index. Similar to using DBCC DBREINDEX, you do not have to know about the underlying table structure; however, with DBCC INDEXDEFRAG you cannot rebuild all indexes with a single statement. You must run DBCC INDEXDEFRAG once for each index you want to defragment.

Unlike DBCC DBREINDEX, DBCC INDEXDEFRAG is an online operation; therefore, the table and indexes are available while the index is being defragmented. Another major difference is that DBCC INDEXDEFRAG can be stopped and restarted without losing any work. The entire DBCC DBREINDEX operation runs as one atomic transaction. This means if you stop DBCC DBREINDEX the entire operation is rolled back, and you must start over. However, if you stop DBCC INDEXDEFRAG it stops instantly and no work is lost, because each unit of work performed by DBCC INDEXDEFRAG occurs as a separate transaction.

DBCC INDEXDEFRAG consists of two phases:
1. Compact the pages and attempt to adjust the page density to the fillfactor that was specified when the index was created. DBCC INDEXDEFRAG attempts to raise the page-density level of pages to the original fillfactor. DBCC INDEXDEFRAG does not, however, reduce page density levels on pages that currently have a higher page density than the original fillfactor.

2. Defragment the index by shuffling the pages so that the physical ordering matches the logical ordering of the leaf nodes of the index. This is performed as a series of small discrete transactions; therefore, the work done by DBCC INDEXDEFRAG has a small impact to overall system performance. Figure 8 shows the page movements performed during the defragmentation phase of DBCC INDEXDEFRAG.

Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG

One last consideration is the difference in the amount of data that is written to the transaction log when using DBCC INDEXDEFRAG and DBCC DBREINDEX. The amount of information logged by DBCC INDEXDEFRAG depends on the level of fragmentation and the amount of work that is performed. The tests resulted in DBCC INDEXDEFRAG logging significantly less than DBCC DBREINDEX when the database was in full recovery mode. The amount of data logged by DBCC INDEXDEFRAG, however, can vary greatly. This is because the amount of work that DBCC INDEXDEFRAG performs when defragmenting indexes is dependent on the number of page movements and the amount of page compaction necessary. You can reclaim the log space used by DBCC INDEXDEFRAG by backing up the log because the work performed is a series of small transactions.

With respect to log usage, DBCC DBREINDEX behaves slightly differently from DBCC INDEXDEFRAG, with the biggest difference being the amount of log used when in bulk logged recovery mode. When in full recovery mode, DBCC DBREINDEX logs images of each index page, which does not occur when in bulk logged mode. For this reason, in full recovery mode, the log space required by DBCC DBREINDEX is roughly equivalent to the number of index pages multiplied by 8 KB. You can use DBCC SHOWCONTIG to determine the number of pages in a given index. For large-scale environments, consider changing the recovery mode to bulk logged when you run DBCC DBREINDEX. Then return to full recovery mode after index maintenance has been performed.

Optimization Issue To Big

Sql 2003

Database Optimization not getting done.

We have a weekly maintenance window and a database that we cannot get optimization to finish timely.

In paticular we have a 70 gig table (indexes included) and the optimization fails because of log file filling up or just runs plain to long (8+ hours)

The table is part of a 3rd party transactional database that cannot be motified.

Is this just the way it has to be or our we missing something.a 70GB table?

What's it used for and why do you need to reorg it?

And why can't some of the data be moved off?|||Looking a creatation of an archieve process for some of the data on this table however need all the information to be accessible to the application. This will take 3rd party involvement not an easy thing.

The reorganization is a good question one which i have asked and can get no valid answer for. My only guess is that this being a transactional system instant retrieve is required by the key value. This table is a EAV table.

I server as a Database Analyist and not an Administator on this machine.|||Like as in

http://en.wikipedia.org/wiki/Entity-Attribute-Value_model

I hate that model|||If diskspace is not an issue, can you get the data portion of the table into one filesystem on one set of physical disks, log (including tempdb log) onto another, tempdb data on a third, and the non-clustered indexes into a fourth? With minimum disk sizes these days around 70GB themselves, this may not be possible,m but it will help you, as you can then use the CREATE INDEX command with the SORT_IN_TEMPDB flag. You may want to play with that, anyway, even if you can only separate the data from tempdb on physical drives.

EDIT: Oh, and take a full backup before you start, switch to simple recovery, reindex, switch back to FULL recovery, and take another full backup. The backups do not need to be exactly in the window, but it is nice.|||what optimizations are you doing?

If you are dropping and recreating indices, you might consider switching your recovery to Bulk Logged during your optimization routines.

Optimization failure.

Sorry if this is a repost I don't think my first one went through.
I have optimization setup in my database maintenance plan. It fails
everytime. It creates a LDF file which stops when it hits 2.5gb. This
causes the error "Log File is Full". At that point noone can connect to the
DB until I run a copy and delte query to move the info elsewhere.
Two questions:
1) Can I change the size the LDF file is allowed to grow too and where, or
can I tell it not to create the LDFfile?
2) Cani choose where the LDF file is located, I need it written to another
drive where I have more room for it to grow.
Thanks in aedvance.UPDATE: Ok so when I look at the properties for one of the databases and
look at the Transaction Log tab, I see where the LDF record is coming from.
I also see where it is restricted to 2500 mb.
I would like to change the maximum file size to Unrestricted file growth.
But I need to move where the log file is saved to a different drive. When I
click to change the location I get the message "A transaction log file's
physical filename cannot be changed once the transaction log file has been
created."
How do I change where the physical location of the log file is?
Thanks,
"Kevin Porter" wrote:
> Sorry if this is a repost I don't think my first one went through.
> I have optimization setup in my database maintenance plan. It fails
> everytime. It creates a LDF file which stops when it hits 2.5gb. This
> causes the error "Log File is Full". At that point noone can connect to the
> DB until I run a copy and delte query to move the info elsewhere.
> Two questions:
> 1) Can I change the size the LDF file is allowed to grow too and where, or
> can I tell it not to create the LDFfile?
> 2) Cani choose where the LDF file is located, I need it written to another
> drive where I have more room for it to grow.
> Thanks in aedvance.
>|||Kevin,
This KB has instructions for various SQL versons and databases, so it should
have your answer.
http://support.microsoft.com/kb/224071/en-us
RLF
"Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...
> UPDATE: Ok so when I look at the properties for one of the databases and
> look at the Transaction Log tab, I see where the LDF record is coming
> from.
> I also see where it is restricted to 2500 mb.
> I would like to change the maximum file size to Unrestricted file growth.
> But I need to move where the log file is saved to a different drive. When
> I
> click to change the location I get the message "A transaction log file's
> physical filename cannot be changed once the transaction log file has been
> created."
> How do I change where the physical location of the log file is?
> Thanks,
> "Kevin Porter" wrote:
>> Sorry if this is a repost I don't think my first one went through.
>> I have optimization setup in my database maintenance plan. It fails
>> everytime. It creates a LDF file which stops when it hits 2.5gb. This
>> causes the error "Log File is Full". At that point noone can connect to
>> the
>> DB until I run a copy and delte query to move the info elsewhere.
>> Two questions:
>> 1) Can I change the size the LDF file is allowed to grow too and where,
>> or
>> can I tell it not to create the LDFfile?
>> 2) Cani choose where the LDF file is located, I need it written to
>> another
>> drive where I have more room for it to grow.
>> Thanks in aedvance.
>>|||Thank you Russell. That looks to be exactly what I need. I figured it would
involve detaching the database, but walkthroughs are nice when you're just
learning like me.
Thanks again for the quick response.
"Russell Fields" wrote:
> Kevin,
> This KB has instructions for various SQL versons and databases, so it should
> have your answer.
> http://support.microsoft.com/kb/224071/en-us
> RLF
> "Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
> news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...
> > UPDATE: Ok so when I look at the properties for one of the databases and
> > look at the Transaction Log tab, I see where the LDF record is coming
> > from.
> > I also see where it is restricted to 2500 mb.
> >
> > I would like to change the maximum file size to Unrestricted file growth.
> > But I need to move where the log file is saved to a different drive. When
> > I
> > click to change the location I get the message "A transaction log file's
> > physical filename cannot be changed once the transaction log file has been
> > created."
> >
> > How do I change where the physical location of the log file is?
> >
> > Thanks,
> >
> > "Kevin Porter" wrote:
> >
> >> Sorry if this is a repost I don't think my first one went through.
> >>
> >> I have optimization setup in my database maintenance plan. It fails
> >> everytime. It creates a LDF file which stops when it hits 2.5gb. This
> >> causes the error "Log File is Full". At that point noone can connect to
> >> the
> >> DB until I run a copy and delte query to move the info elsewhere.
> >>
> >> Two questions:
> >>
> >> 1) Can I change the size the LDF file is allowed to grow too and where,
> >> or
> >> can I tell it not to create the LDFfile?
> >>
> >> 2) Cani choose where the LDF file is located, I need it written to
> >> another
> >> drive where I have more room for it to grow.
> >>
> >> Thanks in aedvance.
> >>
> >>
>
>|||On Wed, 18 Jul 2007 09:40:02 -0700, Kevin Porter wrote:
>Sorry if this is a repost I don't think my first one went through.
>I have optimization setup in my database maintenance plan. It fails
>everytime. It creates a LDF file which stops when it hits 2.5gb. This
>causes the error "Log File is Full". At that point noone can connect to the
>DB until I run a copy and delte query to move the info elsewhere.
>Two questions:
>1) Can I change the size the LDF file is allowed to grow too and where, or
>can I tell it not to create the LDFfile?
>2) Cani choose where the LDF file is located, I need it written to another
>drive where I have more room for it to grow.
>Thanks in aedvance.
>
Hi Kevin,
Your questions are already answered, but you might still need to adress
the reason of your ever-growing log file.
Do you know that a database with recovery model full (the default) or
bulk-logged will continue to grow the log file until you take a bacup of
the log file? If you need full (or bulk-logged) recovery, then you
should schedule regular log backups. If you don't need the ability to
restore beyond the last full database backup, you should change the
recovery model to simple.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Wednesday, March 28, 2012

Optimizing

Optimizing

Optimizing

Friday, March 23, 2012

optimizations job for db maintenance plan failed

optimizations job for db maintenance plan failed

Optimizations job failure - SQLServer2000

Optimizations job failure

Optimizations Job and Shrink DB creating HUGE transaction log file

Optimizations Job and Shrink DB creating HUGE transaction log file

Optimizations Job and Shrink DB creating HUGE transaction log file

Wednesday, March 21, 2012

Optimization Jobs Fails

Optimization Jobs Fails

Optimization Jobs Fails

Optimization job problem

Optimization Job fails

Optimization Job fails

Optimization Job fails

Optimization issues with a Database Maintenance Plan

Optimization Issue To Big

Optimization failure.

OpenXML Identity Insert

Blog Archive

About Me