Clear the resultset during batch processing

Hello SQL Experts,
I have a Batch of Select statments which runs in a while loop and i use
the @.@.rowcount to get the rows returned to manipulate furture.
the problem is after some time in the middle of the process i get the
below error
"The query has exceeded the maximum number of result sets that can be
displayed in the results grid. Only the first 100 result sets are
displayed in the grid."
Is there a way by which i can stop the results being displayed in the
message tab or clear the diplayed result or result set as said in the
error messge. as after the error message the task stops on it own.
i dont know if there is any thing using SET statment
/soni
hi,
This means essentially you do not need these results generated by the SQLs?
is this the case? can you send some sample SQLs so something can be
suggested close to your needs.
rgds
Falik
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164947463.012890.94570@.f1g2000cwa.googlegrou ps.com...
> Hello SQL Experts,
> I have a Batch of Select statments which runs in a while loop and i use
> the @.@.rowcount to get the rows returned to manipulate furture.
> the problem is after some time in the middle of the process i get the
> below error
>
> "The query has exceeded the maximum number of result sets that can be
> displayed in the results grid. Only the first 100 result sets are
> displayed in the grid."
> Is there a way by which i can stop the results being displayed in the
> message tab or clear the diplayed result or result set as said in the
> error messge. as after the error message the task stops on it own.
> i dont know if there is any thing using SET statment
> /soni
>
|||Hello Falik,
Thanks for showing a helping hand
something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:[vbcol=seagreen]
> hi,
> This means essentially you do not need these results generated by the SQLs?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegrou ps.com...
|||Hello Falik,
Thanks for showing a helping hand
something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:[vbcol=seagreen]
> hi,
> This means essentially you do not need these results generated by the SQLs?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegrou ps.com...
|||Hi Soni,
can u produce the sampel data , expected result and SQLs (may use the pubs
database for this), as I can;t understand the why count is not used or group
by can;t be used for this?
as down the line we need to stop the unwanted result-set generated by the
selects......
thnx
Falik
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164955286.981884.286430@.j44g2000cwa.googlegr oups.com...
> Hello Falik,
> Thanks for showing a helping hand
> something like this
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'A'
> -- this will return 2100 rows
> Select @.ARank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'B'
> -- this will return 1000 rows
> Select @.BRank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'C'
> -- this will return 1000 rows
> Select @.CRank = @.@.rowcount
> this way the code is runing in a while loop logic. I cannot use count
> () function as there is data mismatch because of Duplicate records in
> some table.
> /soni
> Falik Sher wrote:
>
sqlsql

Clear the resultset during batch processing

Hello SQL Experts,
I have a Batch of Select statments which runs in a while loop and i use
the @.@.rowcount to get the rows returned to manipulate furture.
the problem is after some time in the middle of the process i get the
below error
"The query has exceeded the maximum number of result sets that can be
displayed in the results grid. Only the first 100 result sets are
displayed in the grid."
Is there a way by which i can stop the results being displayed in the
message tab or clear the diplayed result or result set as said in the
error messge. as after the error message the task stops on it own.
i dont know if there is any thing using SET statment
/sonihi,
This means essentially you do not need these results generated by the SQLs?
is this the case? can you send some sample SQLs so something can be
suggested close to your needs.
rgds
Falik
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...
> Hello SQL Experts,
> I have a Batch of Select statments which runs in a while loop and i use
> the @.@.rowcount to get the rows returned to manipulate furture.
> the problem is after some time in the middle of the process i get the
> below error
>
> "The query has exceeded the maximum number of result sets that can be
> displayed in the results grid. Only the first 100 result sets are
> displayed in the grid."
> Is there a way by which i can stop the results being displayed in the
> message tab or clear the diplayed result or result set as said in the
> error messge. as after the error message the task stops on it own.
> i dont know if there is any thing using SET statment
> /soni
>|||Hello Falik,
Thanks for showing a helping hand :)
something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:
> hi,
> This means essentially you do not need these results generated by the SQLs?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...
> > Hello SQL Experts,
> >
> > I have a Batch of Select statments which runs in a while loop and i use
> > the @.@.rowcount to get the rows returned to manipulate furture.
> >
> > the problem is after some time in the middle of the process i get the
> > below error
> >
> >
> > "The query has exceeded the maximum number of result sets that can be
> > displayed in the results grid. Only the first 100 result sets are
> > displayed in the grid."
> >
> > Is there a way by which i can stop the results being displayed in the
> > message tab or clear the diplayed result or result set as said in the
> > error messge. as after the error message the task stops on it own.
> >
> > i dont know if there is any thing using SET statment
> >
> > /soni
> >|||Hello Falik,
Thanks for showing a helping hand :)
something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:
> hi,
> This means essentially you do not need these results generated by the SQLs?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...
> > Hello SQL Experts,
> >
> > I have a Batch of Select statments which runs in a while loop and i use
> > the @.@.rowcount to get the rows returned to manipulate furture.
> >
> > the problem is after some time in the middle of the process i get the
> > below error
> >
> >
> > "The query has exceeded the maximum number of result sets that can be
> > displayed in the results grid. Only the first 100 result sets are
> > displayed in the grid."
> >
> > Is there a way by which i can stop the results being displayed in the
> > message tab or clear the diplayed result or result set as said in the
> > error messge. as after the error message the task stops on it own.
> >
> > i dont know if there is any thing using SET statment
> >
> > /soni
> >|||Hi Soni,
can u produce the sampel data , expected result and SQLs (may use the pubs
database for this), as I can;t understand the why count is not used or group
by can;t be used for this'
as down the line we need to stop the unwanted result-set generated by the
selects......
thnx
Falik
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164955286.981884.286430@.j44g2000cwa.googlegroups.com...
> Hello Falik,
> Thanks for showing a helping hand :)
> something like this
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'A'
> -- this will return 2100 rows
> Select @.ARank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'B'
> -- this will return 1000 rows
> Select @.BRank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'C'
> -- this will return 1000 rows
> Select @.CRank = @.@.rowcount
> this way the code is runing in a while loop logic. I cannot use count
> () function as there is data mismatch because of Duplicate records in
> some table.
> /soni
> Falik Sher wrote:
>> hi,
>> This means essentially you do not need these results generated by the
>> SQLs?
>> is this the case? can you send some sample SQLs so something can be
>> suggested close to your needs.
>> rgds
>> Falik
>>
>> <chandresh.x.soni@.sonyericsson.com> wrote in message
>> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...
>> > Hello SQL Experts,
>> >
>> > I have a Batch of Select statments which runs in a while loop and i use
>> > the @.@.rowcount to get the rows returned to manipulate furture.
>> >
>> > the problem is after some time in the middle of the process i get the
>> > below error
>> >
>> >
>> > "The query has exceeded the maximum number of result sets that can be
>> > displayed in the results grid. Only the first 100 result sets are
>> > displayed in the grid."
>> >
>> > Is there a way by which i can stop the results being displayed in the
>> > message tab or clear the diplayed result or result set as said in the
>> > error messge. as after the error message the task stops on it own.
>> >
>> > i dont know if there is any thing using SET statment
>> >
>> > /soni
>> >
>

Clear the resultset during batch processing

Hello SQL Experts,
I have a Batch of Select statments which runs in a while loop and i use
the @.@.rowcount to get the rows returned to manipulate furture.
the problem is after some time in the middle of the process i get the
below error
"The query has exceeded the maximum number of result sets that can be
displayed in the results grid. Only the first 100 result sets are
displayed in the grid."
Is there a way by which i can stop the results being displayed in the
message tab or clear the diplayed result or result set as said in the
error messge. as after the error message the task stops on it own.
i dont know if there is any thing using SET statment
/sonihi,
This means essentially you do not need these results generated by the SQLs?
is this the case? can you send some sample SQLs so something can be
suggested close to your needs.
rgds
Falik
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...
> Hello SQL Experts,
> I have a Batch of Select statments which runs in a while loop and i use
> the @.@.rowcount to get the rows returned to manipulate furture.
> the problem is after some time in the middle of the process i get the
> below error
>
> "The query has exceeded the maximum number of result sets that can be
> displayed in the results grid. Only the first 100 result sets are
> displayed in the grid."
> Is there a way by which i can stop the results being displayed in the
> message tab or clear the diplayed result or result set as said in the
> error messge. as after the error message the task stops on it own.
> i dont know if there is any thing using SET statment
> /soni
>|||Hello Falik,
Thanks for showing a helping hand
something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:[vbcol=seagreen]
> hi,
> This means essentially you do not need these results generated by the SQLs
?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...|||Hello Falik,
Thanks for showing a helping hand
something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:[vbcol=seagreen]
> hi,
> This means essentially you do not need these results generated by the SQLs
?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...|||Hi Soni,
can u produce the sampel data , expected result and SQLs (may use the pubs
database for this), as I can;t understand the why count is not used or group
by can;t be used for this'
as down the line we need to stop the unwanted result-set generated by the
selects......
thnx
Falik
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164955286.981884.286430@.j44g2000cwa.googlegroups.com...
> Hello Falik,
> Thanks for showing a helping hand
> something like this
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'A'
> -- this will return 2100 rows
> Select @.ARank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'B'
> -- this will return 1000 rows
> Select @.BRank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'C'
> -- this will return 1000 rows
> Select @.CRank = @.@.rowcount
> this way the code is runing in a while loop logic. I cannot use count
> () function as there is data mismatch because of Duplicate records in
> some table.
> /soni
> Falik Sher wrote:
>

clean up data - when process runs next time

In a integration project I am moving data from A to B.

First time is fine - since table B is empty.

However next time I run the process, I would like to delete all records in B before I run the project again.

What is the best way to delete / clean up data when you re run the process ?

Cheers, T

Issue a DELETE or TRUNCATE from an Execute SQL Task.

-Jamie

Child Package Fails when called from parent

So I have a parent package that calls another package using the Execute Package Task. When I run the child it runs fine but when I run it from the parent i get this msg...Any ideas?

Error: 0xC00220E4 at Execute VR Account Load: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.

Pls take a look at this post to see whether the investigations and solutions there helps. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=241941&SiteID=1

thanks

wenyang

|||

Wenyang,

Thanks for the reply but the solution did not work. I might have found a bug here because the child package uses package configurations and even though I have disabled the package config on the child when I execute it from the parent the output window says that it is trying to load the package configurations. Not sure if this has anything to do with it.

Information: 0x40016040 at VR Load Account: The package is attempting to configure from SQL Server using the configuration string ""localhost.CALLMIS";"[dbo].[SSIS Configurations]";"MISLoads_ServerName";".

Thx

|||

Disregard last msg I posted. It appears the problem was that there was a bad connection guid or something like that still hanging around in the child? I recreated the package and it appears to be working now...

Not my idea of fun...

Checking Settings?

I have an existing view, I didn't create. It runs, but I was running it
through a 3rd party tool Toad to do some tuning and it gve me an error
saying, "SELECT failed because the following SET options have incorrect
settings: 'ANSI_NULLS., CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS,
ANSI_PADDING'.] ERROR **"
Looking around I see that the setting below should be set when created a
view in some cases. I was wondering if I can extract what these were set to
when the view was created?
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YEILDS_NULL
QUOTED_IDENTIFIERS
Session options that must be off
NUMERIC_ROUNDABORT
Thanks!The only two options that are "sticky" options are ANSI_NULLS and QUOTED_IDE
NTIFIERS. You can check
what value the options had when the view was created using the OBJECTPROPERT
Y function.
My guess is that it is an indexed view and that Toad doesn't have appropriat
e settings in the
connection that it is using. Hard to be more specific without having used th
at tool myself.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian" <brian@.nospam.com> wrote in message news:Oii1UKqIGHA.424@.TK2MSFTNGP12.phx.gbl...[co
lor=darkred]
> I have an existing view, I didn't create. It runs, but I was running it
through a 3rd party tool
> Toad to do some tuning and it gve me an error saying, "SELECT failed becau
se the following SET
> options have incorrect settings: 'ANSI_NULLS., CONCAT_NULL_YIELDS_NULL, AN
SI_WARNINGS,
> ANSI_PADDING'.] ERROR **"
> Looking around I see that the setting below should be set when created a
view in some cases. I
> was wondering if I can extract what these were set to when the view was cr
eated?
> ANSI_NULLS
> ANSI_PADDING
> ANSI_WARNINGS
> ARITHABORT
> CONCAT_NULL_YEILDS_NULL
> QUOTED_IDENTIFIERS
> Session options that must be off
> NUMERIC_ROUNDABORT
> Thanks!
>[/color]|||> I have an existing view, I didn't create. It runs, but I was running it
> through a 3rd party tool Toad to do some tuning and it gve me an error
> saying, "SELECT failed because the following SET options have incorrect
> settings: 'ANSI_NULLS., CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS,
> ANSI_PADDING'.] ERROR **"
> Looking around I see that the setting below should be set when created a
> view in some cases. I was wondering if I can extract what these were set
to
> when the view was created?
> ANSI_NULLS
> ANSI_PADDING
> ANSI_WARNINGS
> ARITHABORT
> CONCAT_NULL_YEILDS_NULL
> QUOTED_IDENTIFIERS
> Session options that must be off
> NUMERIC_ROUNDABORT
There are WAY smarter people here than I, who can answer your actual
question, but it seems to me that perhaps you can just recompile the view
and be done with it.
Or am I missing something?
Peace & happy computing,
Mike Labosh, MCSD MCT
"Escriba coda ergo sum." -- vbSensei

Checking job status using SQL-DMO

I have a question I hope someone can help me with.

My situation:
I have a single-step job in SQL Server, which runs a stored procedure, A. This stored procedure invokes another stored procedure, B. In B, one of the statements is a 'BACKUP DATABASE' command, and a database is backed up to a file.

The job is started by an application. Once started, the application then uses the SQL-DMO property CurrentRunStatus to periodically check the status of the job. When the property returns the value SQLDMOJobExecution_Idle (indicating the job has completed), the application code then continues processing, and attempts to access the .dat file produced by the 'BACKUP DATABASE' command.

My problem:
On occasion, the application will hit the problem where either the backup file cannot be located, or the file is still being locked by another process (Error=The process cannot access the file because it is being used by another process).

Is anyone able to shed some light on this?

I assume that the job will only return a completed status after:
(a) both A and B have completed execution, and
(b) the BACKUP operation has completed

Is it possible that even though SQL Server indicates the job has finished, that the BACKUP operation still hasn't completely ended?

Thanks for any help,
AndrewDoesn't anyone have any ideas?

Should I be posting this elsewhere instead?|||Will VB/VBA (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ex02_1gv7.asp) do?

-PatP

Checking at the end of the Cursor if error ocuured

I have a scheduled job which runs daily at 7:00 am. Now, this job has a
cursor in it whcih updates and inserts some tables depending on the conditions
i want to make an entry into a table called monitor (which tracks if the job
ran successfully or not) checking if the job ran successfully or not. Please
advice if their is a way to check if an error occurred or not
thanks
samay
You could create one job step that would only execute if the job step that
runs the statement fails. That job step (lets call it "run on failure")
could do whatever processing or notification that you desire if the job step
fails.
Keith
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:6C8C2715-F6BB-49AF-A5CE-C69260E666CF@.microsoft.com...
> I have a scheduled job which runs daily at 7:00 am. Now, this job has a
> cursor in it whcih updates and inserts some tables depending on the
conditions
> i want to make an entry into a table called monitor (which tracks if the
job
> ran successfully or not) checking if the job ran successfully or not.
Please
> advice if their is a way to check if an error occurred or not
> thanks
> samay

Checking at the end of the Cursor if error ocuured

I have a scheduled job which runs daily at 7:00 am. Now, this job has a
cursor in it whcih updates and inserts some tables depending on the conditions
i want to make an entry into a table called monitor (which tracks if the job
ran successfully or not) checking if the job ran successfully or not. Please
advice if their is a way to check if an error occurred or not
thanks
samayYou could create one job step that would only execute if the job step that
runs the statement fails. That job step (lets call it "run on failure")
could do whatever processing or notification that you desire if the job step
fails.
--
Keith
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:6C8C2715-F6BB-49AF-A5CE-C69260E666CF@.microsoft.com...
> I have a scheduled job which runs daily at 7:00 am. Now, this job has a
> cursor in it whcih updates and inserts some tables depending on the
conditions
> i want to make an entry into a table called monitor (which tracks if the
job
> ran successfully or not) checking if the job ran successfully or not.
Please
> advice if their is a way to check if an error occurred or not
> thanks
> samay

Checking at the end of the Cursor if error ocuured

I have a scheduled job which runs daily at 7:00 am. Now, this job has a
cursor in it whcih updates and inserts some tables depending on the conditio
ns
i want to make an entry into a table called monitor (which tracks if the job
ran successfully or not) checking if the job ran successfully or not. Please
advice if their is a way to check if an error occurred or not
thanks
samayYou could create one job step that would only execute if the job step that
runs the statement fails. That job step (lets call it "run on failure")
could do whatever processing or notification that you desire if the job step
fails.
Keith
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:6C8C2715-F6BB-49AF-A5CE-C69260E666CF@.microsoft.com...
> I have a scheduled job which runs daily at 7:00 am. Now, this job has a
> cursor in it whcih updates and inserts some tables depending on the
conditions
> i want to make an entry into a table called monitor (which tracks if the
job
> ran successfully or not) checking if the job ran successfully or not.
Please
> advice if their is a way to check if an error occurred or not
> thanks
> samay

CHECKDB causes tempdb to grow

We run a DBCC CHECKDB statement against each of our
databases once a week (during off-hours). It runs
successfully, but each morning after it runs I end up
having to shrink tempdb's data and log files.
I know that CHECKDB uses alot of tempdb, but why doesn't
it shrink back down on its own when CHECKDB is finished?
Thank you!
Susan Jones
DBCC checkdb ususally doesn't use a lot of space like DBCC DBREINDEX. But if
tempdb does not shrink automatically, check open transactions using DBCC
OPENTRAN.
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
|||Unless autoshrink is turned on, no database will automatically shrink...
This uses lots of resources, and fragments the databases ( an issue for user
databases.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
|||IN addition to the other comments why would you want to shrink the files
when it obviously needs to be that large to operate properly. By shrinking
the files you incur overhead in both the grow and shrink phases which is
unnecessary if you just leave it alone.
Andrew J. Kelly SQL MVP
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones

CHECKDB causes tempdb to grow

We run a DBCC CHECKDB statement against each of our
databases once a week (during off-hours). It runs
successfully, but each morning after it runs I end up
having to shrink tempdb's data and log files.
I know that CHECKDB uses alot of tempdb, but why doesn't
it shrink back down on its own when CHECKDB is finished?
Thank you!
Susan JonesDBCC checkdb ususally doesn't use a lot of space like DBCC DBREINDEX. But if
tempdb does not shrink automatically, check open transactions using DBCC
OPENTRAN.
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones|||Unless autoshrink is turned on, no database will automatically shrink...
This uses lots of resources, and fragments the databases ( an issue for user
databases.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones|||IN addition to the other comments why would you want to shrink the files
when it obviously needs to be that large to operate properly. By shrinking
the files you incur overhead in both the grow and shrink phases which is
unnecessary if you just leave it alone.
--
Andrew J. Kelly SQL MVP
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones

CHECKDB causes tempdb to grow

We run a DBCC CHECKDB statement against each of our
databases once a week (during off-hours). It runs
successfully, but each morning after it runs I end up
having to shrink tempdb's data and log files.
I know that CHECKDB uses alot of tempdb, but why doesn't
it shrink back down on its own when CHECKDB is finished?
Thank you!
Susan JonesDBCC checkdb ususally doesn't use a lot of space like DBCC DBREINDEX. But if
tempdb does not shrink automatically, check open transactions using DBCC
OPENTRAN.
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones|||Unless autoshrink is turned on, no database will automatically shrink...
This uses lots of resources, and fragments the databases ( an issue for user
databases.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones|||IN addition to the other comments why would you want to shrink the files
when it obviously needs to be that large to operate properly. By shrinking
the files you incur overhead in both the grow and shrink phases which is
unnecessary if you just leave it alone.
Andrew J. Kelly SQL MVP
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones