Showing posts with label job. Show all posts
Showing posts with label job. Show all posts

Wednesday, March 28, 2012

Optimizing Job fails on tables with computed fields

Hi,
we have some tables in SQL Server 2000 with computed fields. When we try to
run an optimization job, it return the error
"Rebuilding indexes for table 'FI_Period'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'"
After removing the field the job runs without errors. I cannot see any link
between a computed field and the Quoted Identifier setting.
Any input is appreciated.
TIA, Norbert Meiss
See MSKB 301292 <http://support.microsoft.com/kb/q301292/>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:436B7DE5-21CC-413B-A0D6-854FD3721894@.microsoft.com...
> Hi,
> we have some tables in SQL Server 2000 with computed fields. When we try
> to
> run an optimization job, it return the error
> "Rebuilding indexes for table 'FI_Period'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
> have
> incorrect settings: 'QUOTED_IDENTIFIER'"
> After removing the field the job runs without errors. I cannot see any
> link
> between a computed field and the Quoted Identifier setting.
> Any input is appreciated.
> TIA, Norbert Meiss

Optimizing Job fails on tables with computed fields

Hi,
we have some tables in SQL Server 2000 with computed fields. When we try to
run an optimization job, it return the error
"Rebuilding indexes for table 'FI_Period'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'"
After removing the field the job runs without errors. I cannot see any link
between a computed field and the Quoted Identifier setting.
Any input is appreciated.
TIA, Norbert MeissSee MSKB 301292 <http://support.microsoft.com/kb/q301292/>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:436B7DE5-21CC-413B-A0D6-854FD3721894@.microsoft.com...
> Hi,
> we have some tables in SQL Server 2000 with computed fields. When we try
> to
> run an optimization job, it return the error
> "Rebuilding indexes for table 'FI_Period'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
> have
> incorrect settings: 'QUOTED_IDENTIFIER'"
> After removing the field the job runs without errors. I cannot see any
> link
> between a computed field and the Quoted Identifier setting.
> Any input is appreciated.
> TIA, Norbert Meiss

Optimizing Job fails on tables with computed fields

Hi,
we have some tables in SQL Server 2000 with computed fields. When we try to
run an optimization job, it return the error
"Rebuilding indexes for table 'FI_Period'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options
have
incorrect settings: 'QUOTED_IDENTIFIER'"
After removing the field the job runs without errors. I cannot see any link
between a computed field and the Quoted Identifier setting.
Any input is appreciated.
TIA, Norbert MeissSee MSKB 301292 <http://support.microsoft.com/kb/q301292/>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:436B7DE5-21CC-413B-A0D6-854FD3721894@.microsoft.com...
> Hi,
> we have some tables in SQL Server 2000 with computed fields. When we try
> to
> run an optimization job, it return the error
> "Rebuilding indexes for table 'FI_Period'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC
> SQL
> Server Driver][SQL Server]DBCC failed because the following SET option
s
> have
> incorrect settings: 'QUOTED_IDENTIFIER'"
> After removing the field the job runs without errors. I cannot see any
> link
> between a computed field and the Quoted Identifier setting.
> Any input is appreciated.
> TIA, Norbert Meiss

Monday, March 26, 2012

Optimize this query?

I have a nightly job that aggregates data into a summary table. Each night, an additional item may or may not be added, so the table is cleared and repopulated. I've got this working fine. Now, I am being asked to track when an item first appears in the summary table. Here is the code I am using to do this. I realize it is very poor performance, but I don't know how to make it better.

MyTable Columns:
id INT PK
,v1 VARCHAR(4)
,v2 VARCHAR(10
,v3 DECIMAL
,item1 BIT
,item2 BIT
,item3 BIT

MyTable2 Columns:
v1 VARCHAR(4)
,v2 VARCHAR(10
,v3 DECIMAL
,item INT
,FirstKnownDate DATETIME

All columns are NOT NULL.

1DECLARE @.iNextRowIDINT2 ,@.iCurrentRowIDINT3 ,@.iLoopControlINT4 ,@.v1VARCHAR(4)5 ,@.v2VARCHAR(10)6 ,@.v3DECIMAL78/* This is a loop that executes for a dynamic number of items, eg item1=1, then item2=1, etc */9SET @.iLoopControl=110SELECT @.iNextRowID=MIN(id)FROM MyTableWHERE item1=11112IFISNULL(@.iNextRowID,0) <> 0BEGIN13 SELECT @.iCurrentRowID = id14 ,@.v1 = v115 ,@.v2 = v216 ,@.v3 = v317FROM MyTable18WHERE id=@.iNextRowID1920WHILE @.iLoopControl = 1BEGIN21 IFNOT EXISTS(SELECT *FROM MyTable2WHERE v1=@.v2AND v2=@.v2AND v3=@.v3AND item=1)BEGIN22 INSERT INTO MyTable223/* COLUMN */ (v1,v2,v3,item,firstknowndate)24SELECT @.v1, @.v2, @.v3, 1,GETDATE()25END2627 SELECT @.iNextRowID =NULL28SELECT @.iNextRowID =MIN(id)FROM MyTableWHERE id>@.iCurrentRowIDAND item1=12930IFISNULL(@.iNextRowID,0) = 0BEGIN31 BREAK32 END3334 SELECT @.iCurrentRowID = id35 ,@.v1 = v136 ,@.v2 = v237 ,@.v3 = v338FROM MyTable39WHERE id=@.iNextRowID40END41END
42/* This is the end of the items loop */
This query takes 5 minutes to do straight inserts without the test for exists in the loop for 3 items.  MyTable has just under 96,000 rows in it. MyTable2 has 121,857 rows in it. It is not about 55 minutes and it is still running with the exists so that a row isn't inserted twice. Is there anything I can do to optimize this?
 EDIT: The query just finished running again with the test for exists on each row. It shouldn't have inserted any new rows, but it took 57 minutes and inserted another 114,115 rows. Not good. Anyone see what is wrong with the query?

Have a look at the execution plan and see what costs are associated with it.

|||

This should be obvious just by looking at the query. There are so many select statements, at least 125,000 exists statements. Obviously it won't finish in a few seconds. I just mean, is there a better way to loop through each row and insert or do nothing based on whether that row exists in the second table?

|||

I just meant that it would show you exactly where the bottlenecks exist. As for improving the query, you could probably just join to the second table and perform inserts for all the missing rows.

|||

Code sample? Eliminating the exists is really what is needed.

Friday, March 23, 2012

optimize queries.

how to optimize the following queries.
1) SELECT * FROM employe
WHERE job_id not IN ( SELECT job_i
FROM job
WHERE min_lvl = 25
2) select * from tbla where name like '%F
Regard
sanjaysanjay
It is to hard to say . You did not provide any info about how many data
exists in the table?
How often you query the table?
What is indexes on the table?
My guess.
1) SELECT * FROM employee
WHERE NOT EXISTS
( SELECT jobs .job_id
FROM jobs WHERE employee.job_id
=jobs.job_id AND min_lvl = 25 )
> 2) select * from tbla where name like '%F'
This one may prevent query optimyzer for using an index on the name column
unless you re-write it as 'F%'.
Consider to use covering indexes instead of select * from table .
For more details please refer to BOL
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:19EA7547-8EBC-43FF-9981-3AF635B34A03@.microsoft.com...
> how to optimize the following queries..
> 1) SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
> 2) select * from tbla where name like '%F'
> Regards
> sanjay|||h
im using not In in my query..which as per documents/sqlserver etc..is not the best way to use and it hampers performance...how i can avoide not In.
execution plan of Not In/ Not exists are same.
suppost i ve 1 million records in this tables and its being heavily used.
both are normal tables from pUbs database.
=========2) if we rewrite the query as 'F%' this will change the logic of the query..keeping same logic how can we modify the query|||sanjay
1) Look , another approach but you will have to compare all methods .
1) SELECT * FROM employee E LEFT JOIN jobs J
ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
2)
http://www.sql-server-performance.com/covering_indexes.asp
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:AFD7096E-56CF-4C1C-B71D-F4862A8EA60D@.microsoft.com...
> hi
> im using not In in my query..which as per documents/sqlserver etc..is not
the best way to use and it hampers performance...how i can avoide not In..
> execution plan of Not In/ Not exists are same..
> suppost i ve 1 million records in this tables and its being heavily used..
> both are normal tables from pUbs database..
> ==========> 2) if we rewrite the query as 'F%' this will change the logic of the
query..keeping same logic how can we modify the query.
>|||hi
i tried ..but seems some problem.
SELECT * FROM employee
WHERE job_id not IN ( SELECT job_id
FROM jobs
WHERE min_lvl = 25 )
the above query returned 34 rows...whereas the below query only 9 rows...i think its using left join bcoz of that..
SELECT * FROM employee E LEFT JOIN jobs J
ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
any further updates would be highly appreciated..
regards|||sanjay
Can post DDL + sample data + expected result and then I can answer you why
it returns 9 rows and why it returns 34 rows.
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:60500162-DCCE-4D7A-9B3F-BE4A3C50B05C@.microsoft.com...
> hi
> i tried ..but seems some problem.
> SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
> the above query returned 34 rows...whereas the below query only 9 rows...i
think its using left join bcoz of that..
> SELECT * FROM employee E LEFT JOIN jobs J
> ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
> any further updates would be highly appreciated..
> regards
>|||Try below query :
select e.* from employee e, ( SELECT job_id
FROM jobs
WHERE min_lvl <> 25 ) a
where e.job_id = a.job_id
This would increase performance, as for each of the job_id, it wont scan
jobs table.
- Ketan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OFu0osW6DHA.2460@.TK2MSFTNGP09.phx.gbl...
> sanjay
> Can post DDL + sample data + expected result and then I can answer you
why
> it returns 9 rows and why it returns 34 rows.
>
>
> "sanjay" <anonymous@.discussions.microsoft.com> wrote in message
> news:60500162-DCCE-4D7A-9B3F-BE4A3C50B05C@.microsoft.com...
> > hi
> > i tried ..but seems some problem.
> > SELECT * FROM employee
> > WHERE job_id not IN ( SELECT job_id
> > FROM jobs
> > WHERE min_lvl = 25 )
> >
> > the above query returned 34 rows...whereas the below query only 9
rows...i
> think its using left join bcoz of that..
> >
> > SELECT * FROM employee E LEFT JOIN jobs J
> > ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
> >
> > any further updates would be highly appreciated..
> > regards
> >
>|||sanjay wrote:
> how to optimize the following queries..
> 1) SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
1) keep it as it is
2) use EXISTS
SELECT *
FROM Employee
WHERE NOT EXISTS (
SELECT 1
FROM Jobs
WHERE Jobs.job_id = Employee.job_id
AND min_lvl=25
)
3) use OUTER JOIN
SELECT * -- Add DISTINCT if necessary
FROM Employee
LEFT JOIN Jobs
ON Jobs.job_id = Employee.job_id
AND Jobs.min_lvl = 25
> 2) select * from tbla where name like '%F'
ALTER TABLE tbla
ADD NameReverse varchar(8000) not null -- use same definition as column
Name
go
UPDATE tbla
SET NameReverse = REVERSE(Name)
go
CREATE INDEX IX_tbla_NameReverse ON tbla(NameReverse)
go
SELECT *
FROM tbla
WHERE NameReverse like REVERSE('F')+'%'
Hope this helps,
Gert-Jan|||Submitted too fast. Of course, the line "WHERE Jobs.job_id IS NULL"
needs to be added to solution 3...
Gert-Jan
Gert-Jan Strik wrote:
> sanjay wrote:
> >
> > how to optimize the following queries..
> > 1) SELECT * FROM employee
> > WHERE job_id not IN ( SELECT job_id
> > FROM jobs
> > WHERE min_lvl = 25 )
> 1) keep it as it is
> 2) use EXISTS
> SELECT *
> FROM Employee
> WHERE NOT EXISTS (
> SELECT 1
> FROM Jobs
> WHERE Jobs.job_id = Employee.job_id
> AND min_lvl=25
> )
> 3) use OUTER JOIN
> SELECT * -- Add DISTINCT if necessary
> FROM Employee
> LEFT JOIN Jobs
> ON Jobs.job_id = Employee.job_id
> AND Jobs.min_lvl = 25
> > 2) select * from tbla where name like '%F'
> ALTER TABLE tbla
> ADD NameReverse varchar(8000) not null -- use same definition as column
> Name
> go
> UPDATE tbla
> SET NameReverse = REVERSE(Name)
> go
> CREATE INDEX IX_tbla_NameReverse ON tbla(NameReverse)
> go
> SELECT *
> FROM tbla
> WHERE NameReverse like REVERSE('F')+'%'
> Hope this helps,
> Gert-Jansql

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 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

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...
>|||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...
>|||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...
>sql

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...
>
>
|||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...[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||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...
>
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
>
|||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...[vbcol=seagreen]
> 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:

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

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 du
e
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.sql