I have scheduled the optimization job to run everyday and sometimes it fails.
I get this:
Executed as user: test\test. ConnectionCheckForData (CheckforData()).
{SQLSTATE 01000} (Message 10054) General network error. Check your network
documentation. {SQLSTATE 08S01} (Error 11). The step failed.
It does not fail every night. I had it scheduled to run once a week, but it
was taken a long time to run so I thought I would run it more often to reduce
the time.
Also, I notice that when the optimization job is running the transaction log
backup fails.
Any help would be greatly appreciated.
Thank you!
Are you having network problems sporadically? Run ping -t all night several
nights in a row (dump to a text file) and see if you have any 'blips" in
connectivity.
"Orn" wrote:
> I have scheduled the optimization job to run everyday and sometimes it fails.
> I get this:
> Executed as user: test\test. ConnectionCheckForData (CheckforData()).
> {SQLSTATE 01000} (Message 10054) General network error. Check your network
> documentation. {SQLSTATE 08S01} (Error 11). The step failed.
> It does not fail every night. I had it scheduled to run once a week, but it
> was taken a long time to run so I thought I would run it more often to reduce
> the time.
> Also, I notice that when the optimization job is running the transaction log
> backup fails.
> Any help would be greatly appreciated.
> Thank you!
|||ok, i will try that.
Can you tell me what the network would have to do with an optimization job
running locally on the SQL server?
Thank you.
"Donna Lambert" wrote:
[vbcol=seagreen]
> Are you having network problems sporadically? Run ping -t all night several
> nights in a row (dump to a text file) and see if you have any 'blips" in
> connectivity.
>
> "Orn" wrote:
sql
Showing posts with label scheduled. Show all posts
Showing posts with label scheduled. Show all posts
Wednesday, March 21, 2012
Optimization job failing
I have scheduled the optimization job to run everyday and sometimes it fails
.
I get this:
Executed as user: test\test. ConnectionCheckForData (CheckforData()).
{SQLSTATE 01000} (Message 10054) General network error. Check your netw
ork
documentation. {SQLSTATE 08S01} (Error 11). The step failed.
It does not fail every night. I had it scheduled to run once a week, but it
was taken a long time to run so I thought I would run it more often to reduc
e
the time.
Also, I notice that when the optimization job is running the transaction log
backup fails.
Any help would be greatly appreciated.
Thank you!Are you having network problems sporadically? Run ping -t all night several
nights in a row (dump to a text file) and see if you have any 'blips" in
connectivity.
"Orn" wrote:
> I have scheduled the optimization job to run everyday and sometimes it fai
ls.
> I get this:
> Executed as user: test\test. ConnectionCheckForData (CheckforData()).
> {SQLSTATE 01000} (Message 10054) General network error. Check your ne
twork
> documentation. {SQLSTATE 08S01} (Error 11). The step failed.
> It does not fail every night. I had it scheduled to run once a week, but
it
> was taken a long time to run so I thought I would run it more often to red
uce
> the time.
> Also, I notice that when the optimization job is running the transaction l
og
> backup fails.
> Any help would be greatly appreciated.
> Thank you!|||ok, i will try that.
Can you tell me what the network would have to do with an optimization job
running locally on the SQL server?
Thank you.
"Donna Lambert" wrote:
[vbcol=seagreen]
> Are you having network problems sporadically? Run ping -t all night sever
al
> nights in a row (dump to a text file) and see if you have any 'blips" in
> connectivity.
>
> "Orn" wrote:
>
.
I get this:
Executed as user: test\test. ConnectionCheckForData (CheckforData()).
{SQLSTATE 01000} (Message 10054) General network error. Check your netw
ork
documentation. {SQLSTATE 08S01} (Error 11). The step failed.
It does not fail every night. I had it scheduled to run once a week, but it
was taken a long time to run so I thought I would run it more often to reduc
e
the time.
Also, I notice that when the optimization job is running the transaction log
backup fails.
Any help would be greatly appreciated.
Thank you!Are you having network problems sporadically? Run ping -t all night several
nights in a row (dump to a text file) and see if you have any 'blips" in
connectivity.
"Orn" wrote:
> I have scheduled the optimization job to run everyday and sometimes it fai
ls.
> I get this:
> Executed as user: test\test. ConnectionCheckForData (CheckforData()).
> {SQLSTATE 01000} (Message 10054) General network error. Check your ne
twork
> documentation. {SQLSTATE 08S01} (Error 11). The step failed.
> It does not fail every night. I had it scheduled to run once a week, but
it
> was taken a long time to run so I thought I would run it more often to red
uce
> the time.
> Also, I notice that when the optimization job is running the transaction l
og
> backup fails.
> Any help would be greatly appreciated.
> Thank you!|||ok, i will try that.
Can you tell me what the network would have to do with an optimization job
running locally on the SQL server?
Thank you.
"Donna Lambert" wrote:
[vbcol=seagreen]
> Are you having network problems sporadically? Run ping -t all night sever
al
> nights in a row (dump to a text file) and see if you have any 'blips" in
> connectivity.
>
> "Orn" wrote:
>
Optimization job failing
I have scheduled the optimization job to run everyday and sometimes it fails.
I get this:
Executed as user: test\test. ConnectionCheckForData (CheckforData()).
{SQLSTATE 01000} (Message 10054) General network error. Check your network
documentation. {SQLSTATE 08S01} (Error 11). The step failed.
It does not fail every night. I had it scheduled to run once a week, but it
was taken a long time to run so I thought I would run it more often to reduce
the time.
Also, I notice that when the optimization job is running the transaction log
backup fails.
Any help would be greatly appreciated.
Thank you!Are you having network problems sporadically? Run ping -t all night several
nights in a row (dump to a text file) and see if you have any 'blips" in
connectivity.
"Orn" wrote:
> I have scheduled the optimization job to run everyday and sometimes it fails.
> I get this:
> Executed as user: test\test. ConnectionCheckForData (CheckforData()).
> {SQLSTATE 01000} (Message 10054) General network error. Check your network
> documentation. {SQLSTATE 08S01} (Error 11). The step failed.
> It does not fail every night. I had it scheduled to run once a week, but it
> was taken a long time to run so I thought I would run it more often to reduce
> the time.
> Also, I notice that when the optimization job is running the transaction log
> backup fails.
> Any help would be greatly appreciated.
> Thank you!|||ok, i will try that.
Can you tell me what the network would have to do with an optimization job
running locally on the SQL server?
Thank you.
"Donna Lambert" wrote:
> Are you having network problems sporadically? Run ping -t all night several
> nights in a row (dump to a text file) and see if you have any 'blips" in
> connectivity.
>
> "Orn" wrote:
> > I have scheduled the optimization job to run everyday and sometimes it fails.
> > I get this:
> >
> > Executed as user: test\test. ConnectionCheckForData (CheckforData()).
> > {SQLSTATE 01000} (Message 10054) General network error. Check your network
> > documentation. {SQLSTATE 08S01} (Error 11). The step failed.
> >
> > It does not fail every night. I had it scheduled to run once a week, but it
> > was taken a long time to run so I thought I would run it more often to reduce
> > the time.
> >
> > Also, I notice that when the optimization job is running the transaction log
> > backup fails.
> >
> > Any help would be greatly appreciated.
> >
> > Thank you!
I get this:
Executed as user: test\test. ConnectionCheckForData (CheckforData()).
{SQLSTATE 01000} (Message 10054) General network error. Check your network
documentation. {SQLSTATE 08S01} (Error 11). The step failed.
It does not fail every night. I had it scheduled to run once a week, but it
was taken a long time to run so I thought I would run it more often to reduce
the time.
Also, I notice that when the optimization job is running the transaction log
backup fails.
Any help would be greatly appreciated.
Thank you!Are you having network problems sporadically? Run ping -t all night several
nights in a row (dump to a text file) and see if you have any 'blips" in
connectivity.
"Orn" wrote:
> I have scheduled the optimization job to run everyday and sometimes it fails.
> I get this:
> Executed as user: test\test. ConnectionCheckForData (CheckforData()).
> {SQLSTATE 01000} (Message 10054) General network error. Check your network
> documentation. {SQLSTATE 08S01} (Error 11). The step failed.
> It does not fail every night. I had it scheduled to run once a week, but it
> was taken a long time to run so I thought I would run it more often to reduce
> the time.
> Also, I notice that when the optimization job is running the transaction log
> backup fails.
> Any help would be greatly appreciated.
> Thank you!|||ok, i will try that.
Can you tell me what the network would have to do with an optimization job
running locally on the SQL server?
Thank you.
"Donna Lambert" wrote:
> Are you having network problems sporadically? Run ping -t all night several
> nights in a row (dump to a text file) and see if you have any 'blips" in
> connectivity.
>
> "Orn" wrote:
> > I have scheduled the optimization job to run everyday and sometimes it fails.
> > I get this:
> >
> > Executed as user: test\test. ConnectionCheckForData (CheckforData()).
> > {SQLSTATE 01000} (Message 10054) General network error. Check your network
> > documentation. {SQLSTATE 08S01} (Error 11). The step failed.
> >
> > It does not fail every night. I had it scheduled to run once a week, but it
> > was taken a long time to run so I thought I would run it more often to reduce
> > the time.
> >
> > Also, I notice that when the optimization job is running the transaction log
> > backup fails.
> >
> > Any help would be greatly appreciated.
> >
> > Thank you!
Optimization job causes database to grow
I have a scheduled optimization that interacts with a database (approximately 50GB) that when it runs, it causes it to grow significantly. I would think that the rebuilding of the indexes should compact the space, not expand it
The settings I have are:
index fill - 50
Remove unused space checke
> Shrink db when it grows beyong 50MB
> Leave 10% fre
The disk has 101 GB free, and the db should sit at about 40GB. Think the 50MB limit could be causing a problem
TIA
MikeMike,
First off a fill factor of 50% is usually too low for the average table.
That means each page will only be half full after the reorg and that is a
lot of wasted space. But to answer your question it's actually the
opposite of what you think. SQL Server needs lots of free space to rebuild
an index especially the clustered index. It essentially rebuilds the table
elsewhere in the database and then drops the original. So you want to
always have plenty of free space in the db at all times. Your actually
defeating the purpose of the reorg to some degree by telling the MP to
shrink the file when done. Shrinking the file causes data that is at the
end of the file to be moved to the beginning and will almost always
refragment your tables when doing so. There is no penalty for having too
much free space in the db but a big one for not enough. Turn off the
autoshrink and leave the db at a size that it finds comfortable and make
sure you always have plenty of free space.
--
Andrew J. Kelly
SQL Server MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:EB161D21-2302-4D30-A05A-C19066AF2853@.microsoft.com...
> I have a scheduled optimization that interacts with a database
(approximately 50GB) that when it runs, it causes it to grow significantly.
I would think that the rebuilding of the indexes should compact the space,
not expand it.
> The settings I have are:
> index fill - 50%
> Remove unused space checked
> > Shrink db when it grows beyong 50MB
> > Leave 10% free
> The disk has 101 GB free, and the db should sit at about 40GB. Think the
50MB limit could be causing a problem?
> TIA,
> Mike
The settings I have are:
index fill - 50
Remove unused space checke
> Shrink db when it grows beyong 50MB
> Leave 10% fre
The disk has 101 GB free, and the db should sit at about 40GB. Think the 50MB limit could be causing a problem
TIA
MikeMike,
First off a fill factor of 50% is usually too low for the average table.
That means each page will only be half full after the reorg and that is a
lot of wasted space. But to answer your question it's actually the
opposite of what you think. SQL Server needs lots of free space to rebuild
an index especially the clustered index. It essentially rebuilds the table
elsewhere in the database and then drops the original. So you want to
always have plenty of free space in the db at all times. Your actually
defeating the purpose of the reorg to some degree by telling the MP to
shrink the file when done. Shrinking the file causes data that is at the
end of the file to be moved to the beginning and will almost always
refragment your tables when doing so. There is no penalty for having too
much free space in the db but a big one for not enough. Turn off the
autoshrink and leave the db at a size that it finds comfortable and make
sure you always have plenty of free space.
--
Andrew J. Kelly
SQL Server MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:EB161D21-2302-4D30-A05A-C19066AF2853@.microsoft.com...
> I have a scheduled optimization that interacts with a database
(approximately 50GB) that when it runs, it causes it to grow significantly.
I would think that the rebuilding of the indexes should compact the space,
not expand it.
> The settings I have are:
> index fill - 50%
> Remove unused space checked
> > Shrink db when it grows beyong 50MB
> > Leave 10% free
> The disk has 101 GB free, and the db should sit at about 40GB. Think the
50MB limit could be causing a problem?
> TIA,
> Mike
Friday, March 9, 2012
Opinions needed on AutoMate to replace SQLAgent
We have been told by the director over the DBAs that we may be standardizing ALL scheduled jobs and tasks (including SQL jobs) onto 1 tool called AutoMate (by NetworkAutomation), although I suspect the decision has already been made. I've argued that a standard for batch jobs is good but SQL has a job scheduler designed for SQL and integrated with SQL that works extremely well, but don't think I'm getting through.
Has anyone used AutoMate as a replacement for SQLAgent? I am open to hearing both pros and cons please. Thank you.
Signed, Frustrated DBAI don't see anything on their site that even mentions sql server
What about sercurity?
Looks like you'd have to write bat file with osql and then schedule them
They're not thinking of disabling the agent are they?
That would be bad|||I guess it depends on how much functionality you are willing to give up, how much developer time you are willing to invest on an ongoing basis to support giving up that functionality, and how much the folks at AutoMate are willing to pay your managment.
It is their call on what tools to use. If they are willing to spend enough, they can have whatever they want. You might suggest hiring a 24 by 265 operations staff... That can be more powerful and flexible than any scheduling system, if you can afford it.
-PatP|||I'm a MSFT bigot, but have to admit their scheduling tools are in the minor leagues. I have zero experience with AutoMate, however I have worked in what was at the time the world's largest client server environment and we used DEC's (remember them?) high-end scheduler to manage our jobs.
One thing that high-end scheduler's have that SQL Agent lacks is the ease with which job dependencies can be created and monitored. Frankly, SQL Agent was an after-thought and until Microsoft gets serious about it, will remain nothing but an excellent DTS task scheduler.
It seems you can combine AutoMate and SQL Agent and have a much improved environment.
And, PatP, what about the other 100 days ;) With or without a scheduling system, you'll want operations staff available 24x7x365 for any signifcant system.|||I don't see anything on their site that even mentions sql server
<snip>
Looks like you'd have to write bat file with osql and then schedule them
This is essentially what we do, we just use both SQL Agent to schedule SQL Server jobs, and where we need to also run non-SQL Server jobs, we either schedule them from within the agent using a job of type "operating system command" that references (typically) a BAT file on the server somewhere, or go the other way 'round (BAT to SQL Server) as Brett mentions.
I'm not sure it really matters what TOOL you use to do the scheduling, but why buy a new tool when Uncle Bill has provided the basics to get the same stuff done? Either way, you're going to have a mix, since the tool can't get into SQL Server directly, and SQL Server can't get into the tool directly.|||And, PatP, what about the other 100 days ;) With or without a scheduling system, you'll want operations staff available 24x7x365 for any signifcant system.Ummm, I was thinking weekends... Yeah, that's the ticket!
Jeeze Louise! A guy makes a typo... ;)
-PatP|||MaxA,
I've had no complaints with job dependancy by using the Advanced tab of each step defined -OR- in the one case where that wasn't good enough I did a query against sysjobshistory prior to continuing several step later. It was a work around, but it did work.
OSCI|||I don't dispute that you can accomplish what you need with SQL Agent, however I do dispute whether or not the cost/benefit is there. I value people's time over the cost of a tool like this, thus I usually lean towards a 3rd party solution that would free your valuable time to solve more trying problems.
I also direct DBA's, so my view is a bit different than a DBA's view. However, I would ask the DBA's to make that decision and then support it 100% because I know they're a lot smarter than me. And, they're the ones who would have to deal with any problems.
How would you prefer to spend your time - managing SQL Agent jobs or improving your data architecture? I think your director has the right idea, but the wrong approach. Leading extremely talented people can be very challenging, and we all make mistakes (even PatP made a typo today - sorry PatP, but you are always spot on so I gotta give ya a hard time).|||(even PatP made a typo today - sorry PatP, but you are always spot on so I gotta give ya a hard time).Could I interest you in some wonderful waterfront real estate in Louisiana?
-PatP|||Actually, now that I think about it in more detail, I'd be willing to debate the value of a servicable and well understood standard scheduler versus a wiz-bang proprietary one.
I tend to value people's time more than the cost of a software package too. However, I try to keep my eye on the "big picture"... Every employee with SQL Server experience I've interviewed has a good understanding of SQL Agent. Every consultant I've ever dealt with does too.
Even if another package could save five percent of the job setup time due to the features it provided, I don't think I could ever recoup the time that I'd loose explaining the wiz-bang to everyone that needed to use it... The time we spend scheduling and managing jobs is tiny compared to the time we spend explaining the ins and outs of our systems to folks.
If you had a mostly closed shop, with few newcomers, and a huge number of scheduled jobs, you might be able to justify a scheduler with the power of some of the DEC (and more often DECUS) products. 99% of the jobs I see do just fine with the SQL Agent features, and they really couldn't make use of much more. Your milage may vary, but I'd be surprised if many shops could justify the additional complexity of a third party scheduler!
-PatP|||To me it looks like this tool is much more than just a scheduler; it can also be used for automated testing: are my servers up and running, do my client applications work as they should, or do I send some notification (email) that one of the servers is down or about to.
Would you have a single point of failure?
If this tool is to replace all schedulers (how many are there?), including the one in SQL Server, is it just to do that? I would hope management is clear on this, since I'd be making hours on finding out exactly what I can and cannot do with this tool.|||One thing that high-end scheduler's have that SQL Agent lacks is the ease with which job dependencies can be created and monitored. Frankly, SQL Agent was an after-thought and until Microsoft gets serious about it, will remain nothing but an excellent DTS task scheduler.
Can I have a hit of what you're smoking?
DTS? What the hell is that?|||I appreciate all of the feedback and hope to have more over the next few days. I would like to ask if you haven't voted in the poll to please do so if you find the poll an accurate measure for your opinion.
Thank you again.
OSCI
Has anyone used AutoMate as a replacement for SQLAgent? I am open to hearing both pros and cons please. Thank you.
Signed, Frustrated DBAI don't see anything on their site that even mentions sql server
What about sercurity?
Looks like you'd have to write bat file with osql and then schedule them
They're not thinking of disabling the agent are they?
That would be bad|||I guess it depends on how much functionality you are willing to give up, how much developer time you are willing to invest on an ongoing basis to support giving up that functionality, and how much the folks at AutoMate are willing to pay your managment.
It is their call on what tools to use. If they are willing to spend enough, they can have whatever they want. You might suggest hiring a 24 by 265 operations staff... That can be more powerful and flexible than any scheduling system, if you can afford it.
-PatP|||I'm a MSFT bigot, but have to admit their scheduling tools are in the minor leagues. I have zero experience with AutoMate, however I have worked in what was at the time the world's largest client server environment and we used DEC's (remember them?) high-end scheduler to manage our jobs.
One thing that high-end scheduler's have that SQL Agent lacks is the ease with which job dependencies can be created and monitored. Frankly, SQL Agent was an after-thought and until Microsoft gets serious about it, will remain nothing but an excellent DTS task scheduler.
It seems you can combine AutoMate and SQL Agent and have a much improved environment.
And, PatP, what about the other 100 days ;) With or without a scheduling system, you'll want operations staff available 24x7x365 for any signifcant system.|||I don't see anything on their site that even mentions sql server
<snip>
Looks like you'd have to write bat file with osql and then schedule them
This is essentially what we do, we just use both SQL Agent to schedule SQL Server jobs, and where we need to also run non-SQL Server jobs, we either schedule them from within the agent using a job of type "operating system command" that references (typically) a BAT file on the server somewhere, or go the other way 'round (BAT to SQL Server) as Brett mentions.
I'm not sure it really matters what TOOL you use to do the scheduling, but why buy a new tool when Uncle Bill has provided the basics to get the same stuff done? Either way, you're going to have a mix, since the tool can't get into SQL Server directly, and SQL Server can't get into the tool directly.|||And, PatP, what about the other 100 days ;) With or without a scheduling system, you'll want operations staff available 24x7x365 for any signifcant system.Ummm, I was thinking weekends... Yeah, that's the ticket!
Jeeze Louise! A guy makes a typo... ;)
-PatP|||MaxA,
I've had no complaints with job dependancy by using the Advanced tab of each step defined -OR- in the one case where that wasn't good enough I did a query against sysjobshistory prior to continuing several step later. It was a work around, but it did work.
OSCI|||I don't dispute that you can accomplish what you need with SQL Agent, however I do dispute whether or not the cost/benefit is there. I value people's time over the cost of a tool like this, thus I usually lean towards a 3rd party solution that would free your valuable time to solve more trying problems.
I also direct DBA's, so my view is a bit different than a DBA's view. However, I would ask the DBA's to make that decision and then support it 100% because I know they're a lot smarter than me. And, they're the ones who would have to deal with any problems.
How would you prefer to spend your time - managing SQL Agent jobs or improving your data architecture? I think your director has the right idea, but the wrong approach. Leading extremely talented people can be very challenging, and we all make mistakes (even PatP made a typo today - sorry PatP, but you are always spot on so I gotta give ya a hard time).|||(even PatP made a typo today - sorry PatP, but you are always spot on so I gotta give ya a hard time).Could I interest you in some wonderful waterfront real estate in Louisiana?
-PatP|||Actually, now that I think about it in more detail, I'd be willing to debate the value of a servicable and well understood standard scheduler versus a wiz-bang proprietary one.
I tend to value people's time more than the cost of a software package too. However, I try to keep my eye on the "big picture"... Every employee with SQL Server experience I've interviewed has a good understanding of SQL Agent. Every consultant I've ever dealt with does too.
Even if another package could save five percent of the job setup time due to the features it provided, I don't think I could ever recoup the time that I'd loose explaining the wiz-bang to everyone that needed to use it... The time we spend scheduling and managing jobs is tiny compared to the time we spend explaining the ins and outs of our systems to folks.
If you had a mostly closed shop, with few newcomers, and a huge number of scheduled jobs, you might be able to justify a scheduler with the power of some of the DEC (and more often DECUS) products. 99% of the jobs I see do just fine with the SQL Agent features, and they really couldn't make use of much more. Your milage may vary, but I'd be surprised if many shops could justify the additional complexity of a third party scheduler!
-PatP|||To me it looks like this tool is much more than just a scheduler; it can also be used for automated testing: are my servers up and running, do my client applications work as they should, or do I send some notification (email) that one of the servers is down or about to.
Would you have a single point of failure?
If this tool is to replace all schedulers (how many are there?), including the one in SQL Server, is it just to do that? I would hope management is clear on this, since I'd be making hours on finding out exactly what I can and cannot do with this tool.|||One thing that high-end scheduler's have that SQL Agent lacks is the ease with which job dependencies can be created and monitored. Frankly, SQL Agent was an after-thought and until Microsoft gets serious about it, will remain nothing but an excellent DTS task scheduler.
Can I have a hit of what you're smoking?
DTS? What the hell is that?|||I appreciate all of the feedback and hope to have more over the next few days. I would like to ask if you haven't voted in the poll to please do so if you find the poll an accurate measure for your opinion.
Thank you again.
OSCI
Subscribe to:
Posts (Atom)