OpenXML Identity Insert: optimizations

Showing posts with label optimizations. 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 Plan Slow

I am looking for some general best practices regarding how often to run an
optimations plan/job, and what options I should choose. Specifically, I am
have very inconsistent results on a nightly job I have set to run
optimizations on my database, ranging anywhere from 5 minutes to 100+
minutes. The daily changes to the database itself do not vary *that*
drastically (it's basically the same activity each day), so I'm trying to
figure out 1) why it takes so long in the first place, and 2) why the
duration varies so much from day to day.
The job is set to run nightly, around 4AM. I have "Reorganize data and
index pages" checked, w/ the "Change free space per page percentage to"
option set to 10%. I also have "Remove unused space from database files"
checked, w/ the "Shrink database when it grows beyond" option set to 50MB.
Currently, the size of the entire database is between 1GB and 2GB, though
this used to be over 4GB until I dropped/readded an index on a table that
has its records archived frequently -- this alone freed up almost 3GB.
The reason I need to minimize the time it takes for this job, is because
while the job is running, my web site is pretty much hosed. At one time,
4AM was off-peak hours, so this was not as much an issue. But now, the site
gets quite a bit of activity from another part of the globe, so 4AM is no
longer "off-peak" -- there really isn't an off-peak now. So this job is
causing problems for the non-regional visitors during this time.
I'm not sure if this is enough info for you to provide valuable feedback,
but if there's anything else I could provide to help you help me, let me
know.
Thanks in advance.
JeradPartial answer:
You want to avoid fragmenting SQL Server database files on disk if at all
possible. Database files that expand, especially in relatively small
increments, will become fragmented. The varying degree of fragmentation MAY
contribute to your varying run times.
I would suggest expanding your database to a reasonable size, (preferably to
it's expected stable size, or at least allowing 12 month's growth). Then
remove the SHRINK database options.
For a database this size do you really need to re-establish the index
fill-factor on a daily basis? That is, do users' experience performance
degradation within hours? If not, then you may be advised to schedule this
reindexing work less frequently, perhaps at weekends which are often
quieter. At the end of the working day run DBCC SHOWCONTIG - if the Scan
Density will fall below, say, 40% byb the nd of week, then index work may be
needed. Even then, consider DBCC INDEXDEFRAG which is an online operation-
less intrusive and can be interrupted. (See BOL for further info.)
Good luck.
Mike P.
"Jerad Rose" <no@.spam.com> wrote in message
news:OwZe2xGuFHA.4080@.TK2MSFTNGP12.phx.gbl...
> I am looking for some general best practices regarding how often to run an
> optimations plan/job, and what options I should choose. Specifically, I
am
> have very inconsistent results on a nightly job I have set to run
> optimizations on my database, ranging anywhere from 5 minutes to 100+
> minutes. The daily changes to the database itself do not vary *that*
> drastically (it's basically the same activity each day), so I'm trying to
> figure out 1) why it takes so long in the first place, and 2) why the
> duration varies so much from day to day.
> The job is set to run nightly, around 4AM. I have "Reorganize data and
> index pages" checked, w/ the "Change free space per page percentage to"
> option set to 10%. I also have "Remove unused space from database files"
> checked, w/ the "Shrink database when it grows beyond" option set to 50MB.
> Currently, the size of the entire database is between 1GB and 2GB, though
> this used to be over 4GB until I dropped/readded an index on a table that
> has its records archived frequently -- this alone freed up almost 3GB.
> The reason I need to minimize the time it takes for this job, is because
> while the job is running, my web site is pretty much hosed. At one time,
> 4AM was off-peak hours, so this was not as much an issue. But now, the
site
> gets quite a bit of activity from another part of the globe, so 4AM is no
> longer "off-peak" -- there really isn't an off-peak now. So this job is
> causing problems for the non-regional visitors during this time.
> I'm not sure if this is enough info for you to provide valuable feedback,
> but if there's anything else I could provide to help you help me, let me
> know.
> Thanks in advance.
> Jerad
>|||Hi Mike.
Thanks for the response. What you said made a lot of sense, and got me to
doing a little more research on index fragments. I found a really good
article that helped me understand why index fragments occur, and what can be
done to prevent/minimze them:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
I have since disabled this job, and have set up a view (report) that I now
monitor to check for excessive index fragments, and then use DBCC
INDEXDEFRAG to reduce the fragments on those tables. The article discusses
an article on BOL that provides a script you can use to automatically defrag
any indexes that has fragments exceeding a given threshold. But for now, I
want to monitor my tables to see which ones are frequently being fragmented,
and maybe redesign the table/indexes to reduce the fragments, instead of
constantly defragging these indexes -- if possible.
Thanks again for your help.
Jerad
"Mike P" <mikep@.aicorporation.com__NO_SPAM> wrote in message
news:uJzcNiIuFHA.2848@.TK2MSFTNGP10.phx.gbl...
> Partial answer:
> You want to avoid fragmenting SQL Server database files on disk if at all
> possible. Database files that expand, especially in relatively small
> increments, will become fragmented. The varying degree of fragmentation
> MAY
> contribute to your varying run times.
> I would suggest expanding your database to a reasonable size, (preferably
> to
> it's expected stable size, or at least allowing 12 month's growth). Then
> remove the SHRINK database options.
> For a database this size do you really need to re-establish the index
> fill-factor on a daily basis? That is, do users' experience performance
> degradation within hours? If not, then you may be advised to schedule
> this
> reindexing work less frequently, perhaps at weekends which are often
> quieter. At the end of the working day run DBCC SHOWCONTIG - if the Scan
> Density will fall below, say, 40% byb the nd of week, then index work may
> be
> needed. Even then, consider DBCC INDEXDEFRAG which is an online
> operation-
> less intrusive and can be interrupted. (See BOL for further info.)
> Good luck.
> Mike P.
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OwZe2xGuFHA.4080@.TK2MSFTNGP12.phx.gbl...
>> I am looking for some general best practices regarding how often to run
>> an
>> optimations plan/job, and what options I should choose. Specifically, I
> am
>> have very inconsistent results on a nightly job I have set to run
>> optimizations on my database, ranging anywhere from 5 minutes to 100+
>> minutes. The daily changes to the database itself do not vary *that*
>> drastically (it's basically the same activity each day), so I'm trying to
>> figure out 1) why it takes so long in the first place, and 2) why the
>> duration varies so much from day to day.
>> The job is set to run nightly, around 4AM. I have "Reorganize data and
>> index pages" checked, w/ the "Change free space per page percentage to"
>> option set to 10%. I also have "Remove unused space from database files"
>> checked, w/ the "Shrink database when it grows beyond" option set to
>> 50MB.
>> Currently, the size of the entire database is between 1GB and 2GB, though
>> this used to be over 4GB until I dropped/readded an index on a table that
>> has its records archived frequently -- this alone freed up almost 3GB.
>> The reason I need to minimize the time it takes for this job, is because
>> while the job is running, my web site is pretty much hosed. At one time,
>> 4AM was off-peak hours, so this was not as much an issue. But now, the
> site
>> gets quite a bit of activity from another part of the globe, so 4AM is no
>> longer "off-peak" -- there really isn't an off-peak now. So this job is
>> causing problems for the non-regional visitors during this time.
>> I'm not sure if this is enough info for you to provide valuable feedback,
>> but if there's anything else I could provide to help you help me, let me
>> know.
>> Thanks in advance.
>> Jerad
>>
>sql

Optimizations Plan Slow

I am looking for some general best practices regarding how often to run an
optimations plan/job, and what options I should choose. Specifically, I am
have very inconsistent results on a nightly job I have set to run
optimizations on my database, ranging anywhere from 5 minutes to 100+
minutes. The daily changes to the database itself do not vary *that*
drastically (it's basically the same activity each day), so I'm trying to
figure out 1) why it takes so long in the first place, and 2) why the
duration varies so much from day to day.
The job is set to run nightly, around 4AM. I have "Reorganize data and
index pages" checked, w/ the "Change free space per page percentage to"
option set to 10%. I also have "Remove unused space from database files"
checked, w/ the "Shrink database when it grows beyond" option set to 50MB.
Currently, the size of the entire database is between 1GB and 2GB, though
this used to be over 4GB until I dropped/readded an index on a table that
has its records archived frequently -- this alone freed up almost 3GB.
The reason I need to minimize the time it takes for this job, is because
while the job is running, my web site is pretty much hosed. At one time,
4AM was off-peak hours, so this was not as much an issue. But now, the site
gets quite a bit of activity from another part of the globe, so 4AM is no
longer "off-peak" -- there really isn't an off-peak now. So this job is
causing problems for the non-regional visitors during this time.
I'm not sure if this is enough info for you to provide valuable feedback,
but if there's anything else I could provide to help you help me, let me
know.
Thanks in advance.
Jerad
Partial answer:
You want to avoid fragmenting SQL Server database files on disk if at all
possible. Database files that expand, especially in relatively small
increments, will become fragmented. The varying degree of fragmentation MAY
contribute to your varying run times.
I would suggest expanding your database to a reasonable size, (preferably to
it's expected stable size, or at least allowing 12 month's growth). Then
remove the SHRINK database options.
For a database this size do you really need to re-establish the index
fill-factor on a daily basis? That is, do users' experience performance
degradation within hours? If not, then you may be advised to schedule this
reindexing work less frequently, perhaps at weekends which are often
quieter. At the end of the working day run DBCC SHOWCONTIG - if the Scan
Density will fall below, say, 40% byb the nd of week, then index work may be
needed. Even then, consider DBCC INDEXDEFRAG which is an online operation-
less intrusive and can be interrupted. (See BOL for further info.)
Good luck.
Mike P.
"Jerad Rose" <no@.spam.com> wrote in message
news:OwZe2xGuFHA.4080@.TK2MSFTNGP12.phx.gbl...
> I am looking for some general best practices regarding how often to run an
> optimations plan/job, and what options I should choose. Specifically, I
am
> have very inconsistent results on a nightly job I have set to run
> optimizations on my database, ranging anywhere from 5 minutes to 100+
> minutes. The daily changes to the database itself do not vary *that*
> drastically (it's basically the same activity each day), so I'm trying to
> figure out 1) why it takes so long in the first place, and 2) why the
> duration varies so much from day to day.
> The job is set to run nightly, around 4AM. I have "Reorganize data and
> index pages" checked, w/ the "Change free space per page percentage to"
> option set to 10%. I also have "Remove unused space from database files"
> checked, w/ the "Shrink database when it grows beyond" option set to 50MB.
> Currently, the size of the entire database is between 1GB and 2GB, though
> this used to be over 4GB until I dropped/readded an index on a table that
> has its records archived frequently -- this alone freed up almost 3GB.
> The reason I need to minimize the time it takes for this job, is because
> while the job is running, my web site is pretty much hosed. At one time,
> 4AM was off-peak hours, so this was not as much an issue. But now, the
site
> gets quite a bit of activity from another part of the globe, so 4AM is no
> longer "off-peak" -- there really isn't an off-peak now. So this job is
> causing problems for the non-regional visitors during this time.
> I'm not sure if this is enough info for you to provide valuable feedback,
> but if there's anything else I could provide to help you help me, let me
> know.
> Thanks in advance.
> Jerad
>
|||Hi Mike.
Thanks for the response. What you said made a lot of sense, and got me to
doing a little more research on index fragments. I found a really good
article that helped me understand why index fragments occur, and what can be
done to prevent/minimze them:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
I have since disabled this job, and have set up a view (report) that I now
monitor to check for excessive index fragments, and then use DBCC
INDEXDEFRAG to reduce the fragments on those tables. The article discusses
an article on BOL that provides a script you can use to automatically defrag
any indexes that has fragments exceeding a given threshold. But for now, I
want to monitor my tables to see which ones are frequently being fragmented,
and maybe redesign the table/indexes to reduce the fragments, instead of
constantly defragging these indexes -- if possible.
Thanks again for your help.
Jerad
"Mike P" <mikep@.aicorporation.com__NO_SPAM> wrote in message
news:uJzcNiIuFHA.2848@.TK2MSFTNGP10.phx.gbl...
> Partial answer:
> You want to avoid fragmenting SQL Server database files on disk if at all
> possible. Database files that expand, especially in relatively small
> increments, will become fragmented. The varying degree of fragmentation
> MAY
> contribute to your varying run times.
> I would suggest expanding your database to a reasonable size, (preferably
> to
> it's expected stable size, or at least allowing 12 month's growth). Then
> remove the SHRINK database options.
> For a database this size do you really need to re-establish the index
> fill-factor on a daily basis? That is, do users' experience performance
> degradation within hours? If not, then you may be advised to schedule
> this
> reindexing work less frequently, perhaps at weekends which are often
> quieter. At the end of the working day run DBCC SHOWCONTIG - if the Scan
> Density will fall below, say, 40% byb the nd of week, then index work may
> be
> needed. Even then, consider DBCC INDEXDEFRAG which is an online
> operation-
> less intrusive and can be interrupted. (See BOL for further info.)
> Good luck.
> Mike P.
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OwZe2xGuFHA.4080@.TK2MSFTNGP12.phx.gbl...
> am
> site
>

Optimizations Plan Slow

I am looking for some general best practices regarding how often to run an
optimations plan/job, and what options I should choose. Specifically, I am
have very inconsistent results on a nightly job I have set to run
optimizations on my database, ranging anywhere from 5 minutes to 100+
minutes. The daily changes to the database itself do not vary *that*
drastically (it's basically the same activity each day), so I'm trying to
figure out 1) why it takes so long in the first place, and 2) why the
duration varies so much from day to day.
The job is set to run nightly, around 4AM. I have "Reorganize data and
index pages" checked, w/ the "Change free space per page percentage to"
option set to 10%. I also have "Remove unused space from database files"
checked, w/ the "Shrink database when it grows beyond" option set to 50MB.
Currently, the size of the entire database is between 1GB and 2GB, though
this used to be over 4GB until I dropped/readded an index on a table that
has its records archived frequently -- this alone freed up almost 3GB.
The reason I need to minimize the time it takes for this job, is because
while the job is running, my web site is pretty much hosed. At one time,
4AM was off-peak hours, so this was not as much an issue. But now, the site
gets quite a bit of activity from another part of the globe, so 4AM is no
longer "off-peak" -- there really isn't an off-peak now. So this job is
causing problems for the non-regional visitors during this time.
I'm not sure if this is enough info for you to provide valuable feedback,
but if there's anything else I could provide to help you help me, let me
know.
Thanks in advance.
JeradPartial answer:
You want to avoid fragmenting SQL Server database files on disk if at all
possible. Database files that expand, especially in relatively small
increments, will become fragmented. The varying degree of fragmentation MAY
contribute to your varying run times.
I would suggest expanding your database to a reasonable size, (preferably to
it's expected stable size, or at least allowing 12 month's growth). Then
remove the SHRINK database options.
For a database this size do you really need to re-establish the index
fill-factor on a daily basis? That is, do users' experience performance
degradation within hours? If not, then you may be advised to schedule this
reindexing work less frequently, perhaps at weekends which are often
quieter. At the end of the working day run DBCC SHOWCONTIG - if the Scan
Density will fall below, say, 40% byb the nd of week, then index work may be
needed. Even then, consider DBCC INDEXDEFRAG which is an online operation-
less intrusive and can be interrupted. (See BOL for further info.)
Good luck.
Mike P.
"Jerad Rose" <no@.spam.com> wrote in message
news:OwZe2xGuFHA.4080@.TK2MSFTNGP12.phx.gbl...
> I am looking for some general best practices regarding how often to run an
> optimations plan/job, and what options I should choose. Specifically, I
am
> have very inconsistent results on a nightly job I have set to run
> optimizations on my database, ranging anywhere from 5 minutes to 100+
> minutes. The daily changes to the database itself do not vary *that*
> drastically (it's basically the same activity each day), so I'm trying to
> figure out 1) why it takes so long in the first place, and 2) why the
> duration varies so much from day to day.
> The job is set to run nightly, around 4AM. I have "Reorganize data and
> index pages" checked, w/ the "Change free space per page percentage to"
> option set to 10%. I also have "Remove unused space from database files"
> checked, w/ the "Shrink database when it grows beyond" option set to 50MB.
> Currently, the size of the entire database is between 1GB and 2GB, though
> this used to be over 4GB until I dropped/readded an index on a table that
> has its records archived frequently -- this alone freed up almost 3GB.
> The reason I need to minimize the time it takes for this job, is because
> while the job is running, my web site is pretty much hosed. At one time,
> 4AM was off-peak hours, so this was not as much an issue. But now, the
site
> gets quite a bit of activity from another part of the globe, so 4AM is no
> longer "off-peak" -- there really isn't an off-peak now. So this job is
> causing problems for the non-regional visitors during this time.
> I'm not sure if this is enough info for you to provide valuable feedback,
> but if there's anything else I could provide to help you help me, let me
> know.
> Thanks in advance.
> Jerad
>|||Hi Mike.
Thanks for the response. What you said made a lot of sense, and got me to
doing a little more research on index fragments. I found a really good
article that helped me understand why index fragments occur, and what can be
done to prevent/minimze them:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
I have since disabled this job, and have set up a view (report) that I now
monitor to check for excessive index fragments, and then use DBCC
INDEXDEFRAG to reduce the fragments on those tables. The article discusses
an article on BOL that provides a script you can use to automatically defrag
any indexes that has fragments exceeding a given threshold. But for now, I
want to monitor my tables to see which ones are frequently being fragmented,
and maybe redesign the table/indexes to reduce the fragments, instead of
constantly defragging these indexes -- if possible.
Thanks again for your help.
Jerad
"Mike P" <mikep@.aicorporation.com__NO_SPAM> wrote in message
news:uJzcNiIuFHA.2848@.TK2MSFTNGP10.phx.gbl...
> Partial answer:
> You want to avoid fragmenting SQL Server database files on disk if at all
> possible. Database files that expand, especially in relatively small
> increments, will become fragmented. The varying degree of fragmentation
> MAY
> contribute to your varying run times.
> I would suggest expanding your database to a reasonable size, (preferably
> to
> it's expected stable size, or at least allowing 12 month's growth). Then
> remove the SHRINK database options.
> For a database this size do you really need to re-establish the index
> fill-factor on a daily basis? That is, do users' experience performance
> degradation within hours? If not, then you may be advised to schedule
> this
> reindexing work less frequently, perhaps at weekends which are often
> quieter. At the end of the working day run DBCC SHOWCONTIG - if the Scan
> Density will fall below, say, 40% byb the nd of week, then index work may
> be
> needed. Even then, consider DBCC INDEXDEFRAG which is an online
> operation-
> less intrusive and can be interrupted. (See BOL for further info.)
> Good luck.
> Mike P.
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OwZe2xGuFHA.4080@.TK2MSFTNGP12.phx.gbl...
> am
> site
>

Optimizations on System Tables

Hi

I have an issue where my system tables are heavily fragmented. Currently our system creates between 200 and 400 new logins daily (its the method the application handles it security). However we have had execution times on this of between 13ms and 1900ms, very inconsistent.

I have seen in a previous thread http://dbforums.com/arch/70/2003/11/965415
that its Kalen Delaney's opinion that fragmentation doesn't really matter unless you have 'several dozen' pages

Here is an example of our sysxlogins table's report :

DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 162
- Extents Scanned.......................: 35
- Extent Switches.......................: 156
- Avg. Pages per Extent..................: 4.6
- Scan Density [Best Count:Actual Count]......: 13.38% [21:157]
- Logical Scan Fragmentation ..............: 49.38%
- Extent Scan Fragmentation ...............: 34.29%
- Avg. Bytes Free per Page................: 5694.6
- Avg. Page Density (full)................: 29.64%

As you can see, its got 162 pages, which probably counts as 'several' dozen.

I would like to know if its possible for us to defrag/reindex this table, as you can see, it has a VERY low Scan Density, and a rather high Fragmentation level.

Opinions, help, undocumented functionality all welcome
TiaanI am facing a same issue.
I have noticed that my system table are extremly fragmented. Take a look. Any suggestions? Or does it not matter that they are like that?

DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 23
- Extents Scanned.......................: 7
- Extent Switches.......................: 21
- Avg. Pages per Extent..................: 3.3
- Scan Density [Best Count:Actual Count]......: 13.64% [3:22]
- Logical Scan Fragmentation ..............: 43.48%
- Extent Scan Fragmentation ...............: 71.43%
- Avg. Bytes Free per Page................: 2744.6
- Avg. Page Density (full)................: 66.09%

DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3019.0
- Avg. Page Density (full)................: 62.70%

DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 85
- Extents Scanned.......................: 17
- Extent Switches.......................: 81
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 13.41% [11:82]
- Logical Scan Fragmentation ..............: 44.71%
- Extent Scan Fragmentation ...............: 70.59%
- Avg. Bytes Free per Page................: 3348.5
- Avg. Page Density (full)................: 58.63%

DBCC SHOWCONTIG scanning 'syscomments' table...
Table: 'syscomments' (6); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 984
- Extents Scanned.......................: 159
- Extent Switches.......................: 769
- Avg. Pages per Extent..................: 6.2
- Scan Density [Best Count:Actual Count]......: 15.97% [123:770]
- Logical Scan Fragmentation ..............: 46.24%
- Extent Scan Fragmentation ...............: 18.24%
- Avg. Bytes Free per Page................: 3261.0
- Avg. Page Density (full)................: 59.71%

DBCC SHOWCONTIG scanning 'syspermissions' table...
Table: 'syspermissions' (9); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 3
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.3
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 3771.5
- Avg. Page Density (full)................: 53.40%

DBCC SHOWCONTIG scanning 'sysdepends' table...
Table: 'sysdepends' (12); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 29
- Extents Scanned.......................: 8
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 3.6
- Scan Density [Best Count:Actual Count]......: 14.81% [4:27]
- Logical Scan Fragmentation ..............: 48.28%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3096.8
- Avg. Page Density (full)................: 61.74%

DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 10
- Extents Scanned.......................: 4
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 2.5
- Scan Density [Best Count:Actual Count]......: 28.57% [2:7]
- Logical Scan Fragmentation ..............: 60.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3522.2
- Avg. Page Density (full)................: 56.48%

DBCC SHOWCONTIG scanning 'sysmessages' table...
Table: 'sysmessages' (36); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 169
- Extents Scanned.......................: 23
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 7.3
- Scan Density [Best Count:Actual Count]......: 81.48% [22:27]
- Logical Scan Fragmentation ..............: 1.78%
- Extent Scan Fragmentation ...............: 13.04%
- Avg. Bytes Free per Page................: 3926.9
- Avg. Page Density (full)................: 51.48%

DBCC SHOWCONTIG scanning 'syslanguages' table...
Table: 'syslanguages' (44); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.5
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3206.0
- Avg. Page Density (full)................: 60.39%

DBCC SHOWCONTIG scanning 'syscharsets' table...
Table: 'syscharsets' (45); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 33
- Extents Scanned.......................: 6
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 5.5
- Scan Density [Best Count:Actual Count]......: 62.50% [5:8]
- Logical Scan Fragmentation ..............: 24.24%
- Extent Scan Fragmentation ...............: 16.67%
- Avg. Bytes Free per Page................: 2896.6
- Avg. Page Density (full)................: 64.21%

DBCC SHOWCONTIG scanning 'sysaltfiles' table...
Table: 'sysaltfiles' (94); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 14.29%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 4283.4
- Avg. Page Density (full)................: 47.08%

DBCC SHOWCONTIG scanning 'spt_values' table...
Table: 'spt_values' (85575343); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 5
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
- Logical Scan Fragmentation ..............: 28.57%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2671.7
- Avg. Page Density (full)................: 66.99%|||You need not worry about fragmentation on System tables and use DBCC UPDATEUSAGE in order to correct the inconsistency.

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

Optimizations for machine dedicated to processing

Hello,

We have a machine entirely dedicated to processing SSAS 2005 cubes, and then other machines sync from it and provide client access.

I notice that sometimes during processing, the processor usage hovers at about 15% and there is no paging or disk activity, which makes me wonder if SSAS is somehow limiting resources used for processing. Are there any optimizations that can be made to tell SSAS to assume that it doesn't have to serve clients, and can devote all resources to processing? I'm looking specifically at the Thread Pool / Process / Priority Ratio, which it says is an advanced property not to be changed without guidance--could increasing this work?

Thanks for any ideas.

Tom

You should be able to use several tools to monitor what is happening during processing of Analysis Services cubes:

SQL Profiler- will show you trace events
ActivityViewer sample app- will show you current connections, sessions..

Try to see what stage of processing is going slow on your machine. In several cases I have seen Analysis Server sitting and waiting for data to come from relational database.

Processing of partition has several stages:
Read Data , Write Data , Build Index ...

See which part is under suspicion.

I wouldnt go changing advanced sever properties without talking to product support.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Wednesday, March 21, 2012

Optimizations & Integrity Check Jobs failing?

Thanks in advance for any info.
What would cause Optimization & Integrity Check jobs to fail? The agent info
shows SQL State = 42000, which, according to BOL is "Syntax error,
permission violation, or other nonspecific error. " Hmm... where to go from
here?
Thanks,
MorganI think I have found out what it is...
After further investigation, the server is running SQL Server personal
edition and was exceeding the 5 user limit, which I am guessing is causing
the jobs to not run and reporting back a generic error message.
"Morgan" <abcd@.senditon.com> wrote in message
news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Thanks in advance for any info.
> What would cause Optimization & Integrity Check jobs to fail? The agent
info
> shows SQL State = 42000, which, according to BOL is "Syntax error,
> permission violation, or other nonspecific error. " Hmm... where to go
from
> here?
>
> Thanks,
> Morgan
>
>|||The "5 user limit" shouldn't cause this. That is only a throttling mechanism
(very misunderstood, though). Are you using the maint wizard? If so, specify
a report file in the maint plan and check for the real error message in that
report file.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Morgan" <abcd@.senditon.com> wrote in message
news:OPPPEXM7DHA.2300@.TK2MSFTNGP10.phx.gbl...
> I think I have found out what it is...
> After further investigation, the server is running SQL Server personal
> edition and was exceeding the 5 user limit, which I am guessing is causing
> the jobs to not run and reporting back a generic error message.
> "Morgan" <abcd@.senditon.com> wrote in message
> news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > Thanks in advance for any info.
> >
> > What would cause Optimization & Integrity Check jobs to fail? The agent
> info
> > shows SQL State = 42000, which, according to BOL is "Syntax error,
> > permission violation, or other nonspecific error. " Hmm... where to go
> from
> > here?
> >
> >
> > Thanks,
> >
> > Morgan
> >
> >
> >
>|||Thanks, Tibor. Unfortunately we had to burn the SQL Server install and
install EE on the box. If it starts happening again, we'll hit the logs for
more detail.
Thanks again.
Morgan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfAd4CO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> The "5 user limit" shouldn't cause this. That is only a throttling
mechanism
> (very misunderstood, though). Are you using the maint wizard? If so,
specify
> a report file in the maint plan and check for the real error message in
that
> report file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Morgan" <abcd@.senditon.com> wrote in message
> news:OPPPEXM7DHA.2300@.TK2MSFTNGP10.phx.gbl...
> > I think I have found out what it is...
> > After further investigation, the server is running SQL Server personal
> > edition and was exceeding the 5 user limit, which I am guessing is
causing
> > the jobs to not run and reporting back a generic error message.
> >
> > "Morgan" <abcd@.senditon.com> wrote in message
> > news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > > Thanks in advance for any info.
> > >
> > > What would cause Optimization & Integrity Check jobs to fail? The
agent
> > info
> > > shows SQL State = 42000, which, according to BOL is "Syntax error,
> > > permission violation, or other nonspecific error. " Hmm... where to go
> > from
> > > here?
> > >
> > >
> > > Thanks,
> > >
> > > Morgan
> > >
> > >
> > >
> >
> >
>

Wednesday, March 28, 2012

Optimizing

Optimizing

Optimizing

Friday, March 23, 2012

Optimizations Plan Slow

Optimizations Plan Slow

Optimizations Plan Slow

Optimizations on System Tables

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

Optimizations for machine dedicated to processing

Wednesday, March 21, 2012

Optimizations & Integrity Check Jobs failing?

OpenXML Identity Insert

Blog Archive

About Me