Delay with XP

Hi all,
Desperately hoping you can help, we run pentagon2000 on an
SQL Server, which runs fine on W2K PC's, but on XP
Workstations, when the Pentagon 2000 Software data is
being added or modified from a station, and it sends the
data to the SQL Server the status states in Task manager
as "Not Responding" while sending the data and waits for
the response from the SQL Server to continue its
processes. The issue I have is that although a delay of a
few seconds is acceptable and expected, on the XP
WStations it can be 20-30 seconds, in extreme cases even
longer. this is making the system unusable. Is there
anything obvious, or even obscure anyone knows of that
might be causing this.
Thanks
Ian
Ian Broome wrote:
> Hi all,
> Desperately hoping you can help, we run pentagon2000 on an
> SQL Server, which runs fine on W2K PC's, but on XP
> Workstations, when the Pentagon 2000 Software data is
> being added or modified from a station, and it sends the
> data to the SQL Server the status states in Task manager
> as "Not Responding" while sending the data and waits for
> the response from the SQL Server to continue its
> processes. The issue I have is that although a delay of a
> few seconds is acceptable and expected, on the XP
> WStations it can be 20-30 seconds, in extreme cases even
> longer. this is making the system unusable. Is there
> anything obvious, or even obscure anyone knows of that
> might be causing this.
> Thanks
> Ian
What network library are you using to connect to SQL Server? If TCP/IP
try using the IP address instead of the server name. Or try setting up a
Connection Alias on the client for both named pipes and TCP/IP and see
if that makes a difference.
Also, make sure you have the correct MDAC installed on the WinXP
clients.
The "Not Responding" does not mean the application has necessarily
stopped responding. It could either be waiting for a response from SQL
Server as you mentioned or it could be so busy processing, it won't let
the OS know that that it is alive.
Have you tried running some queries from Query Analyzer from an XP
client to see if the response you get (Select the Show Client Statistics
option) is the same as a Win 2K client.
David G.
|||[vbcol=seagreen]
>--Original Message--
>Ian Broome wrote:
an[vbcol=seagreen]
a
>What network library are you using to connect to SQL
Server? If TCP/IP
>try using the IP address instead of the server name. Or
try setting up a
>Connection Alias on the client for both named pipes and
TCP/IP and see
>if that makes a difference.
>Also, make sure you have the correct MDAC installed on
the WinXP
>clients.
>The "Not Responding" does not mean the application has
necessarily
>stopped responding. It could either be waiting for a
response from SQL
>Server as you mentioned or it could be so busy
processing, it won't let
>the OS know that that it is alive.
>Have you tried running some queries from Query Analyzer
from an XP
>client to see if the response you get (Select the Show
Client Statistics
>option) is the same as a Win 2K client.
>
>--
>David G.
>.
>
Thanks David,
Ok first off, im afraid im not SQL friendly at all, we are
a network company brought in to resolve multiple issues
which we have but this one remains. In response to your
queries:
We are using TCP/IP, with regard to using the IP rather
than the server name, im assuming you mean within the CFG
file in BDE administrator? after that you lost me im
afriad regarding connection alias.
re: the correct MDAC installed for XP, do you know what it
should be? Ive applied SP3 for SQL and SP1 for XP.
re: Query Analyzer, ive looked this up but it seems to be
a tool I have to purchase and then run queries from,
unfortunately above my head, unless you know of an
alternative way of testing this.
Thankyou for your reply, much appreciated.
Ian
|||Ian wrote:
> Thanks David,
> Ok first off, im afraid im not SQL friendly at all, we are
> a network company brought in to resolve multiple issues
> which we have but this one remains. In response to your
> queries:
> We are using TCP/IP, with regard to using the IP rather
> than the server name, im assuming you mean within the CFG
> file in BDE administrator? after that you lost me im
> afriad regarding connection alias.
> re: the correct MDAC installed for XP, do you know what it
> should be? Ive applied SP3 for SQL and SP1 for XP.
> re: Query Analyzer, ive looked this up but it seems to be
> a tool I have to purchase and then run queries from,
> unfortunately above my head, unless you know of an
> alternative way of testing this.
> Thankyou for your reply, much appreciated.
> Ian
Query Analyzer is a client tool that comes with SQL Server. There is no
charge. If you have WinXP SP1 installed, you should be fine.
It sounds like you need a DBA to do some performance testing to see
where the problem is.
David G.

delay when running with DTexec.exe

Hi,
when I try to run my package with dtexec.exe, it starts fine but in the process it package calles another subpackage and at the time there is big delay before start processing the subpackage.
the subpackage has been setup so executionoutofprocess pramater has been set to true.
any idea what migth be the problem.
I have to metion even when I run this with in the visual studio still I have a big delay.

cheersWhat is a "big" delay? SSIS has to validate the package and its connections/metadata before executing.|||it takes normally between 30 sec to one min. for it to start the sub-package. is there any way to reduce this delay by pre-validation
Thanks|||Try setting DelayValidation = True on your connection managers, sources and destinations.|||

From another thread on this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=739674&SiteID=1

Usually, executing using DTEXEC /F File.DTSX is the fastest way, but gives the mininum logging. Varios logging options for DTEXEC add functionality but may affect performance, although I've never seen 10x times slowdown because of it.

I have tested myself and I can tell the speed improves but just too litle.

|||Is there any way to do the validation once and then cache it. instead of SSIS vaidating it each time I run as it has got a big overhead.
cheers|||

Kolf wrote:

Is there any way to do the validation once and then cache it. instead of SSIS vaidating it each time I run as it has got a big overhead.
cheers

Validation HAS to happen. And unless you set DelayValidation=TRUE a task will get validated when the package spins up and immediately pror to the task executing. There isn't alot of point in caching the results of the first Validation because that would negate the point of doing the second validation.

Does that answer the question?

-Jamie

Delay Insert

I am parsing a file where along the flow I use a conditional split. One path of the split is the primary table (with IDENTITY) values. The rest of the paths have a FOREIGN KEY to the primary table.

It seems that SSIS is trying to insert the rows at the same time (which makes sense) but this is causing a problem with the secondary tables and their FK constraint since the primary table is not yet written.

Is there a way to delay the secondary tables until the primary table is done?

(I guess one way is to run through the file twice... once for the primary table and another for the rest but that seems wasteful to me...)

Thanks.

There is no way to delay paths inside a data flow, or set any precedence. One option I like is to stage the "secondary" data in a raw file. This is very efficient compared to most source and destination combinations. In your current Data Flow write the secondary data to a raw file then add another Data Flow task, with a raw file source and the your final destination.

Delay in SQLAgent Service startup

Hi,
On a number of servers we are facing issues regarding the SQL Server Agent
service. The service is experiencing a delay during startup, more than the
wait hint period. THe service should ideally be starting in 30 seconds (Or it
seems to say so in the Wait Hint). But it is taking around 1min. Our software
thinks it is a timeout and raises an error.
Following is the SQLAgent log file of when the service is starting:
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID 1956
2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTANCE] version
8.00.760 (0 connection limit)
2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
Local host server is [MACHINE_NAME]\[INSTANCE]
2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] running
Windows NT 5.0 (2195) Service Pack 3
2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded (maximum
concurrency: 20)
2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
(maximum concurrency: 10)
2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded (maximum
concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
loaded (maximum concurrency: 10)
2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loaded
(maximum concurrency: 25)
2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under Windows NT
service control
2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No mail
profile defined)
2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum worker
threads: 0)
2006-11-08 03:31:10 - ? [146] Request servicer engine started
2006-11-08 03:31:10 - ? [167] Populating job cache...
2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defined -
OnIdle job schedules will have no effect
2006-11-08 03:31:10 - ? [133] Support engine started
2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Events)
2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in the job cache
2006-11-08 03:31:10 - ? [170] Populating alert cache...
2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
The machine is a very high spec machine and there's a lot of cpu/memory
available.
Thanks
It looks like it is hanging on ActiveScripting which is part of DTS. Does
this help you to resolve it?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kunal" <Kunal@.discussions.microsoft.com> wrote in message
news:C42254BF-88A3-49E5-A461-27B98EF8687D@.microsoft.com...
> Hi,
> On a number of servers we are facing issues regarding the SQL Server Agent
> service. The service is experiencing a delay during startup, more than the
> wait hint period. THe service should ideally be starting in 30 seconds (Or
> it
> seems to say so in the Wait Hint). But it is taking around 1min. Our
> software
> thinks it is a timeout and raises an error.
> Following is the SQLAgent log file of when the service is starting:
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
> (x86
> unicode retail build) : Process ID
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
> (x86
> unicode retail build) : Process ID 1956
> 2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTANCE] version
> 8.00.760 (0 connection limit)
> 2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
> 2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
> Local host server is [MACHINE_NAME]\[INSTANCE]
> 2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
> 2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] running
> Windows NT 5.0 (2195) Service Pack 3
> 2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded
> (maximum
> concurrency: 20)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
> (maximum concurrency: 10)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded
> (maximum
> concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
> loaded (maximum concurrency: 10)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loaded
> (maximum concurrency: 25)
> 2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under Windows
> NT
> service control
> 2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No
> mail
> profile defined)
> 2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum worker
> threads: 0)
> 2006-11-08 03:31:10 - ? [146] Request servicer engine started
> 2006-11-08 03:31:10 - ? [167] Populating job cache...
> 2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defined -
> OnIdle job schedules will have no effect
> 2006-11-08 03:31:10 - ? [133] Support engine started
> 2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Events)
> 2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in the job
> cache
> 2006-11-08 03:31:10 - ? [170] Populating alert cache...
> 2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
>
> The machine is a very high spec machine and there's a lot of cpu/memory
> available.
> Thanks
sql

Delay in SQLAgent Service startup

Hi,
On a number of servers we are facing issues regarding the SQL Server Agent
service. The service is experiencing a delay during startup, more than the
wait hint period. THe service should ideally be starting in 30 seconds (Or i
t
seems to say so in the Wait Hint). But it is taking around 1min. Our softwar
e
thinks it is a timeout and raises an error.
Following is the SQLAgent log file of when the service is starting:
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
(x86
unicode retail build) : Process ID
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
(x86
unicode retail build) : Process ID 1956
2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTANC
E] version
8.00.760 (0 connection limit)
2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.DL
L;
Local host server is [MACHINE_NAME]\[INSTANCE]
2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] runni
ng
Windows NT 5.0 (2195) Service Pack 3
2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded (maxi
mum
concurrency: 20)
2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
(maximum concurrency: 10)
2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully load
ed
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded (max
imum
concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
loaded (maximum concurrency: 10)
2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loade
d
(maximum concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loaded
(maximum concurrency: 25)
2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under Win
dows NT
service control
2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No m
ail
profile defined)
2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum work
er
threads: 0)
2006-11-08 03:31:10 - ? [146] Request servicer engine started
2006-11-08 03:31:10 - ? [167] Populating job cache...
2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defined
-
OnIdle job schedules will have no effect
2006-11-08 03:31:10 - ? [133] Support engine started
2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Event
s)
2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in the
job cache
2006-11-08 03:31:10 - ? [170] Populating alert cache...
2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
---
The machine is a very high spec machine and there's a lot of cpu/memory
available.
ThanksIt looks like it is hanging on ActiveScripting which is part of DTS. Does
this help you to resolve it?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kunal" <Kunal@.discussions.microsoft.com> wrote in message
news:C42254BF-88A3-49E5-A461-27B98EF8687D@.microsoft.com...
> Hi,
> On a number of servers we are facing issues regarding the SQL Server Agent
> service. The service is experiencing a delay during startup, more than the
> wait hint period. THe service should ideally be starting in 30 seconds (Or
> it
> seems to say so in the Wait Hint). But it is taking around 1min. Our
> software
> thinks it is a timeout and raises an error.
> Following is the SQLAgent log file of when the service is starting:
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.76
0
> (x86
> unicode retail build) : Process ID
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.76
0
> (x86
> unicode retail build) : Process ID 1956
> 2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTA
NCE] version
> 8.00.760 (0 connection limit)
> 2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
> 2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.
DLL;
> Local host server is [MACHINE_NAME]\[INSTANCE]
> 2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
> 2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] run
ning
> Windows NT 5.0 (2195) Service Pack 3
> 2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded
> (maximum
> concurrency: 20)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
> (maximum concurrency: 10)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully lo
aded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded
> (maximum
> concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
> loaded (maximum concurrency: 10)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loa
ded
> (maximum concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loade
d
> (maximum concurrency: 25)
> 2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under W
indows
> NT
> service control
> 2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No
> mail
> profile defined)
> 2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum wo
rker
> threads: 0)
> 2006-11-08 03:31:10 - ? [146] Request servicer engine started
> 2006-11-08 03:31:10 - ? [167] Populating job cache...
> 2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defin
ed -
> OnIdle job schedules will have no effect
> 2006-11-08 03:31:10 - ? [133] Support engine started
> 2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Eve
nts)
> 2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in t
he job
> cache
> 2006-11-08 03:31:10 - ? [170] Populating alert cache...
> 2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
> ---
> The machine is a very high spec machine and there's a lot of cpu/memory
> available.
> Thanks

Delay in SQLAgent Service startup

Hi,
On a number of servers we are facing issues regarding the SQL Server Agent
service. The service is experiencing a delay during startup, more than the
wait hint period. THe service should ideally be starting in 30 seconds (Or it
seems to say so in the Wait Hint). But it is taking around 1min. Our software
thinks it is a timeout and raises an error.
Following is the SQLAgent log file of when the service is starting:
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID 1956
2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTANCE] version
8.00.760 (0 connection limit)
2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
Local host server is [MACHINE_NAME]\[INSTANCE]
2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] running
Windows NT 5.0 (2195) Service Pack 3
2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded (maximum
concurrency: 20)
2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
(maximum concurrency: 10)
2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded (maximum
concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
loaded (maximum concurrency: 10)
2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loaded
(maximum concurrency: 25)
2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under Windows NT
service control
2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No mail
profile defined)
2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum worker
threads: 0)
2006-11-08 03:31:10 - ? [146] Request servicer engine started
2006-11-08 03:31:10 - ? [167] Populating job cache...
2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defined -
OnIdle job schedules will have no effect
2006-11-08 03:31:10 - ? [133] Support engine started
2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Events)
2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in the job cache
2006-11-08 03:31:10 - ? [170] Populating alert cache...
2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
---
The machine is a very high spec machine and there's a lot of cpu/memory
available.
ThanksIt looks like it is hanging on ActiveScripting which is part of DTS. Does
this help you to resolve it?
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kunal" <Kunal@.discussions.microsoft.com> wrote in message
news:C42254BF-88A3-49E5-A461-27B98EF8687D@.microsoft.com...
> Hi,
> On a number of servers we are facing issues regarding the SQL Server Agent
> service. The service is experiencing a delay during startup, more than the
> wait hint period. THe service should ideally be starting in 30 seconds (Or
> it
> seems to say so in the Wait Hint). But it is taking around 1min. Our
> software
> thinks it is a timeout and raises an error.
> Following is the SQLAgent log file of when the service is starting:
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
> (x86
> unicode retail build) : Process ID
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
> (x86
> unicode retail build) : Process ID 1956
> 2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTANCE] version
> 8.00.760 (0 connection limit)
> 2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
> 2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
> Local host server is [MACHINE_NAME]\[INSTANCE]
> 2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
> 2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] running
> Windows NT 5.0 (2195) Service Pack 3
> 2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded
> (maximum
> concurrency: 20)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
> (maximum concurrency: 10)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded
> (maximum
> concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
> loaded (maximum concurrency: 10)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loaded
> (maximum concurrency: 25)
> 2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under Windows
> NT
> service control
> 2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No
> mail
> profile defined)
> 2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum worker
> threads: 0)
> 2006-11-08 03:31:10 - ? [146] Request servicer engine started
> 2006-11-08 03:31:10 - ? [167] Populating job cache...
> 2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defined -
> OnIdle job schedules will have no effect
> 2006-11-08 03:31:10 - ? [133] Support engine started
> 2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Events)
> 2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in the job
> cache
> 2006-11-08 03:31:10 - ? [170] Populating alert cache...
> 2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
> ---
> The machine is a very high spec machine and there's a lot of cpu/memory
> available.
> Thanks

delay in running the sub-packages

Hi,
when call a sub-package from the parent package with the executeoutofprocess=true I have about 5-10 sec delay before sub-package starts running. ( this is a big delay for me as the sub_package is in a foreach loop)
the sub_package itself it's very simple and contains just one script task with a small script in in it.
even if this delay is for validation I still can't understand why it is taking 5-10 ses.
cheers

Kolf wrote:

Hi,
when call a sub-package from the parent package with the executeoutofprocess=true I have about 5-10 sec delay before sub-package starts running. ( this is a big delay for me as the sub_package is in a foreach loop)

the sub_package itself it's very simple and contains just one script task with a small script in in it.
even if this delay is for validation I still can't understand why it is taking 5-10 ses.
cheers

I have to add that the subpackage has got some variables which is specified in package configurations
|||

Have you tried executeoutofprocess = false? Do you have the same delay?

|||And delayvalidation = true?

Delay in running queries

I have a problem in running queries.

I developed an application uses sqlserver 2005 express edition

I create all queries in storedprocedures.every things work perfect but some times I get long delay in running queries. but after some minutes it comes regular . I coudlnt find any relation between delay and time of work.it comes by chance . also I set timeout for running query for 30 sec. but some times it took more than minutes.

what should I do?

It sounds like you are experience 'blocking' and 'locking' behavior. If you post the complete stored procedure code, we 'may' be able to help you.

And you could also use Profiler to determine what procedures and/or statements in the procedures are causing the blocks.

|||

my data base has so many procudures and this problem may happend in running each one . one simple of them is :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[STPListStock]

AS

SELECT GoodSyscode, Title, Code, BarCode, Type, FstUnit, SecUnit, UnitRate, OrderPoint, SalePrice1, SalePrice2, SalePrice3, SalePrice4, SalePrice5,

VisitorPer, Comment, DiscontPer, UserPrice, GroupName1, GroupName2, Term, Weight, SerialNo, TypeId, GroupId1, GroupId2

FROM VWCompleteGood

|||

try this

EXEC sp_dboption '<db_name>', 'autoclose', 'false';

Delay in package starting when running from SQL Agent

Hi,

I wonder if anybody can shed any light on this problem. I have a SQL Agent job which has three steps, each step runs an SSIS package.

The job is scheduled to start at 11.00 pm, which is does successfully. However, it has been taking between 2 and 3 hours to run, which is way longer than it should.

When I've looked at the logging, I've found that the although the job starts at 11.00 pm, the first package (in job step 1) does not start executing until about 11.30. It finishes in about 5 minutes, there is then about an hour delay before the second package (in job step 2) starts. This finishes in about 10 minutes, then there is another hour delay before the third package (in job step 3) starts.

I've tried configuring the steps as SSIS jobs, and also as cmd jobs using dtexec, both exhibit the same behaviour.

Any ideas about what could be causing this delay? The packages are stored in msdb on the same server as the SQL Agent job, if that makes any difference.

Thanks,

Sam

That sounds very strange. Although I'd guess its a SQL Server Agent problem rather than SSIS.

Can you replace the steps with something else - some simple command-line calls for example, and see if the same thing happens?

Do the log fiels for SQL Server Agent and SSIS tie up? i.e. The package may start 30 minutes late but did the job step start 30 minutes late (there's an important distinction here)?

-Jamie

|||

hi sam, I can think over that problem is that your sql agent is very busy attending other jobs ?

|||

Jamie,

Thanks for the reply, I will try the job with a couple of simple calls.

The log fields do not tie up - each job step is starting well before it's package starts.

Sam

|||

Enric,

Thanks for the reply, but this is the only job on the server at the moment, so that shouldn't be causing a problem.

Sam

|||

sam2005 wrote:

Jamie,

Thanks for the reply, I will try the job with a couple of simple calls.

The log fields do not tie up - each job step is starting well before it's package starts.

Sam

If that is the case then I would suggest that the delay is caused by the package going through validation. Set DelayValidation=TRUE on the package to see if this removes the delay. If it doesn't, set DelayValidation=TRUE on all your containers and tasks and see if this removes the delay.

If this solves the problem then you know that it is the validation step that is causing the delay. Try doing what i suggested above and then reply here and we'll take it from there!

-Jamie

|||

this is probably a longshot...

do you see this problem when you run package in bi studio?

is it possible that the service startup is slow?

there is a kb article that talks about problem in sp1

http://support.microsoft.com/?kbid=918644

|||

The DelayValidation at the package level, as suggested by Jamie, seems to have done the trick. I also found that there was a msmsgs.exe process running which was constantly using half the processor - killing this has sped things up even more.

Would the DelayValidation setting have any other impact on the package?

Sam

sql

Delay in inserting records continuously into the Database

Dear All,
I am having a process which continuously inserts a record into a single
table with the following table schema:
CREATE TABLE [MessageLog] (
[MessageIndex] [int] IDENTITY (1, 1) NOT NULL ,
[SegmentCode] [smallint] NULL ,
[SetID] [smallint] NULL ,
[ScripCode] [int] NULL ,
[ClientCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserID] [int] NULL ,
[OrderNumber] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TimeStamp1] [int] NULL ,
[MessageType] [int] NULL ,
[ValidityTime] [int] NULL ,
[Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I have written an SP to update this table from my code. Most of the time to
execute this SP it is taking around 1 to 2 ms, but some time (10 out of 5000
inserts) it takes around 75, 100 or even 200 milliseconds also.
Database Properites:
General:
Maintanence Plan -> None
Collation Name -> SQL_Latin1_General_CP1_C1_AS
Both for Data file and Log File:
Automatic Grow File -> Set to Yes
File Growth -> By 10 Percent
Maximum File Size -> Unrestricted File Growth
Recovery Model -> Simple
Auto Update Statistics -> Set to True
Auto Create Statistics -> Set to True
All the other settings are set to false
Compatibility Level -> Database Compatibility Level 80
Can any one help me out to get a consistent of 1 to 2 ms every time?
Thanks and Regards,
PeriSome possible causes may be:
1)The logic organising data and then passes it to SQL Server to insert
2)triggers on the table
3)Complex foreign keys
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Peri" <Peri@.newsgroups.nospam> wrote in message
news:eIuYzCcaGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Dear All,
> I am having a process which continuously inserts a record into a single
> table with the following table schema:
> CREATE TABLE [MessageLog] (
> [MessageIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [SegmentCode] [smallint] NULL ,
> [SetID] [smallint] NULL ,
> [ScripCode] [int] NULL ,
> [ClientCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserID] [int] NULL ,
> [OrderNumber] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TimeStamp1] [int] NULL ,
> [MessageType] [int] NULL ,
> [ValidityTime] [int] NULL ,
> [Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> I have written an SP to update this table from my code. Most of the time
to
> execute this SP it is taking around 1 to 2 ms, but some time (10 out of
5000
> inserts) it takes around 75, 100 or even 200 milliseconds also.
> Database Properites:
> General:
> Maintanence Plan -> None
> Collation Name -> SQL_Latin1_General_CP1_C1_AS
> Both for Data file and Log File:
> Automatic Grow File -> Set to Yes
> File Growth -> By 10 Percent
> Maximum File Size -> Unrestricted File Growth
> Recovery Model -> Simple
> Auto Update Statistics -> Set to True
> Auto Create Statistics -> Set to True
> All the other settings are set to false
> Compatibility Level -> Database Compatibility Level 80
> Can any one help me out to get a consistent of 1 to 2 ms every time?
> Thanks and Regards,
> Peri
>|||Hi Peri,
Thank you for using MSDN Managed Newsgroup Support.
You may need to use the SQL Profiler to trace your SP running and check why
your SQL execution will take more time than usual. Basically, you may check
the CPU usage, disk reads and writes event.
Please note that:
Performance issues can be difficult to troubleshoot and resolve in a
newsgroup setting due to the number of variables and the amount of time
required to narrow down possible causes and observe the effects. We will
assist as best as we can, but you may wish to consider contacting CSS for a
more timely resolution for these type issues.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com
for regional support phone numbers.
Thank you for your patience and understanding.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Peri" <Peri@.newsgroups.nospam>
>Subject: Delay in inserting records continuously into the Database
>Date: Thu, 27 Apr 2006 11:36:14 +0530
>Lines: 49
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
>Message-ID: <eIuYzCcaGHA.3740@.TK2MSFTNGP03.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: btnl-tn-dsl-dynamic-177.10.144.59.touchtelindia.net
59.144.10.177
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:595672
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>Dear All,
>I am having a process which continuously inserts a record into a single
>table with the following table schema:
>CREATE TABLE [MessageLog] (
> [MessageIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [SegmentCode] [smallint] NULL ,
> [SetID] [smallint] NULL ,
> [ScripCode] [int] NULL ,
> [ClientCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserID] [int] NULL ,
> [OrderNumber] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TimeStamp1] [int] NULL ,
> [MessageType] [int] NULL ,
> [ValidityTime] [int] NULL ,
> [Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
>I have written an SP to update this table from my code. Most of the time
to
>execute this SP it is taking around 1 to 2 ms, but some time (10 out of
5000
>inserts) it takes around 75, 100 or even 200 milliseconds also.
>Database Properites:
>General:
>Maintanence Plan -> None
>Collation Name -> SQL_Latin1_General_CP1_C1_AS
>Both for Data file and Log File:
>Automatic Grow File -> Set to Yes
>File Growth -> By 10 Percent
>Maximum File Size -> Unrestricted File Growth
>Recovery Model -> Simple
>Auto Update Statistics -> Set to True
>Auto Create Statistics -> Set to True
>All the other settings are set to false
>Compatibility Level -> Database Compatibility Level 80
>Can any one help me out to get a consistent of 1 to 2 ms every time?
>Thanks and Regards,
>Peri
>
>|||On Thu, 27 Apr 2006 11:36:14 +0530, "Peri" <Peri@.newsgroups.nospam>
wrote:

>Most of the time to
>execute this SP it is taking around 1 to 2 ms, but some time (10 out of 500
0
>inserts) it takes around 75, 100 or even 200 milliseconds also.
Perhaps the delay is waiting for more disl file space to be allocated,
either data or log. Log in particular can be a problem as the process
must wait for completion, and the log is always growing (and growing,
and growing...)
Roy Harvey
Beacon Falls, CT

Delay in connection

I'm working with Sql 2005 developer edition

It works well but some times I get long delay in connection and read data.is it any way to solve the problem?

for more information whene is working well I can connect to database

and get all information I need in .1 sec. when is going to be late this

action may takes 20 sec

What client provider/driver and what version are you using - MDAC, SQL Native Client, .Net SqlCLient 1.1, 2.0, etc.?

Is the client ans SQL Server on the same machine or not?

Is there are a firewall protecting the SQL Server machine, specifically its SQL Server TCP port (by default 1433)?

Most of the clients attempt TCP connection first. If the SQL Server is protected by a firewall without exception for SQL Server port this usually fails in about 21 seconds. Then they try other protocols, usuallu Named Pipes. This would succeed fast if File and Printer Sharing is enabled on the server. The protocol information gets cached for certain time, which could be an explanation why it sometimes takes short time and other times ~20 seconds.

|||Hi

Glad to hear u for my problem

I run my program on the same machin as sql server is on

and I use Sql server developer edition|||

Do you know what step takes the long time - connection etsablishment, a query, etc.?

Also, are you using C# (SqlCLient), C++ (OLEDB?, ODBC? - MDAC or SQL Native Client)?

Delay in connection

I'm working with Sql 2005 developer edition
It works well but some times I get long delay in connection and read data.is it any way to solve the problem?
for more information whene is working well I can connect to database and get all information I need in .1 sec. when is going to be late this action may takes 20 sec

What client provider/driver and what version are you using - MDAC, SQL Native Client, .Net SqlCLient 1.1, 2.0, etc.?

Is the client ans SQL Server on the same machine or not?

Is there are a firewall protecting the SQL Server machine, specifically its SQL Server TCP port (by default 1433)?

Most of the clients attempt TCP connection first. If the SQL Server is protected by a firewall without exception for SQL Server port this usually fails in about 21 seconds. Then they try other protocols, usuallu Named Pipes. This would succeed fast if File and Printer Sharing is enabled on the server. The protocol information gets cached for certain time, which could be an explanation why it sometimes takes short time and other times ~20 seconds.

|||Hi
Glad to hear u for my problem
I run my program on the same machin as sql server is on
and I use Sql server developer edition
|||

Do you know what step takes the long time - connection etsablishment, a query, etc.?

Also, are you using C# (SqlCLient), C++ (OLEDB?, ODBC? - MDAC or SQL Native Client)?

Delay for Conflict Resolution in SS2K5

I have a couple of tables set up with column level tracking and using the "Subscriber Always Wins" conflict resolver.

I am still trying to work out why there is a conflict as the only updates to these rows happen at the subscriber.

What I am finding is that a conflict occurs and there appears to be a delay between the conflict being recognised and then resolved.

The reason I know there is a delay is because this is playing havoc with a data extract that relies on the updates from the subscriber.

For instance, I have a job on which the status is changed. That status change triggers an extract via BizTalk to another system. BizTalk, via an SQL adapter extracts the data from the server and I find that some of it is missing in the extract.

I go and look into the database at the raw data and find that the data is in fact there.

I have certain triggers running which set up export flags in the database to say this data should be exported. This works perfectly when there are no conflicts.

In each case where data has not been extracted, a conflict occurs.

Is there any way to guarantee that the conflict will be resolved at the same time as the synchronisation is complete - i.e. within the same transaction?

I thought replication was supposed to be a single transaction - you get it all or you get nothing.

I'm interested to hear from anyone who has experienced this behaviour.

Thanks

Are you saying that when you run merge agent, there happens a conflict and that conflict is not resolved in the same merge session?

Please explain your scenario more clearly so that we can troubleshoot it.

Also if you could simplify and narrow down the failure, it will be helpful.

|||There is another post on here that details the conflicts we are getting. It drills into some of the details

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=540159&SiteID=1

Just to clarify, the subscribers we have are all from SQL Mobile.

When the status data gets synchronised, triggers operate on the update/insert to create a record to indicate that the data should be exported from the system. BizTalk is responsible for extracting this data and looks into SQL every ten seconds for data to extract.

I am getting, on occasion, data that is incomplete. Whenever this occurs, the incomplete data is always from one of the two tables I have that are getting conflicts (see other post for this particular problem). When I go back and look at the data in the database, I see what I would have expected the extract process to get.

This to me indicates that there is a delay in the conflict resolution. (Custom - subscriber always wins).

Let me know what you need if this is not enough.

Delay command execution

Hi
Here is the setting: Clients connect to SQL Server 2000 via ADO.
Here is the problem: After a client's successful login, the SQL Server 2000
should delay the processing of the client-commands by, e.g., 1 sec. How do I
tell it to the SQL Server 2000?
Thanks in advance.
Adrianat the sql server side
WAITFOR DELAY '00:00:01'
But why would you need it. You can as well have the delay at the client side
.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Thank you.
Well, I know this command. But where precisely do I tell the SQL Server to
delay an incoming command?
A delay at the client-side would be fine as well. But now, how do I tell it
to the client (3rd-party, no source code)?
Basically, I need to delay only the first command of a client immediately
after the login, so that I can run a "login-script" for the client on the
server.
Adrian
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:669DA5C9-52D0-4AA4-B489-2E041F14F7DE@.microsoft.com...
> at the sql server side
> WAITFOR DELAY '00:00:01'
> But why would you need it. You can as well have the delay at the client
> side.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||> Well, I know this command. But where precisely do I tell the SQL Server to delay an incom
ing
> command?
There's no such setting in SQL Server.

> A delay at the client-side would be fine as well. But now, how do I tell i
t to the client
> (3rd-party, no source code)?
You would have to talk to the 3:rd party vendor about this...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <adrian@.iai.uni-bonn.de> wrote in message news:eYlmvehjGHA.4344@.TK2MSFTNGP05.phx.g
bl...
> Thank you.
> Well, I know this command. But where precisely do I tell the SQL Server to
delay an incoming
> command?
> A delay at the client-side would be fine as well. But now, how do I tell i
t to the client
> (3rd-party, no source code)?
> Basically, I need to delay only the first command of a client immediately
after the login, so that
> I can run a "login-script" for the client on the server.
> Adrian
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:669DA5C9-52D0-4AA4-B489-2E041F14F7DE@.microsoft.com...
>|||That's bad news.
But...
I monitor login-events with a trace and process the events in a trigger,
which is attached to the trace-table. I need to keep the client waiting
until the trigger finishes. Is there any other way to do this?
Adrian
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ez$y2shjGHA.4884@.TK2MSFTNGP03.phx.gbl...
> There's no such setting in SQL Server.
>
> You would have to talk to the 3:rd party vendor about this...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Adrian" <adrian@.iai.uni-bonn.de> wrote in message
> news:eYlmvehjGHA.4344@.TK2MSFTNGP05.phx.gbl...
>|||Adrian (adrian@.iai.uni-bonn.de) writes:
> That's bad news.
> But...
> I monitor login-events with a trace and process the events in a trigger,
> which is attached to the trace-table. I need to keep the client waiting
> until the trigger finishes. Is there any other way to do this?
Wait, this sounds dangerous. OK, I don't know the architecture of
this particular 3rd party tool. But most modern applications these
days opens a connection, submits a query or two and then close the
connection. Or rather, that is how the application code looks like.
Under the hood, the client API maintains a connection pool, so that
if the application reconnects soon enough, a connection will be
reused.
Nevertheless, a one-second delay on each login sounds like a bad idea
to me.
Maybe if you explain in more detail what you are trying on achieve and
why, we may come with suggestions.
Don't forget to tell which version of SQL Server you are using.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Delay between CREATE DATABASE and ability to connect to that database

I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/
This is probably for the simple reason that it takes a while to create the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegr oups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>
|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason
|||I think I understand. You execute the CREATE command, and are blocked. As soon as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a little while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
column) to see what state the database is in and based on that connect. Still a polling approach,
though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegr oups.com...
> Tibor Karaszi wrote:
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>
|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
> info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
> column) to see what state the database is in and based on that connect. Still a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason

Delay between CREATE DATABASE and ability to connect to that database

I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/This is probably for the simple reason that it takes a while to create the d
atabase.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegroups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the
database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason|||I think I understand. You execute the CREATE command, and are blocked. As so
on as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a l
ittle while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google a
nd you should find some
info, possibly also in Books Online. So it is possible that you can query sy
s.databases (state_desc
column) to see what state the database is in and based on that connect. Stil
l a polling approach,
though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegroups.com...
> Tibor Karaszi wrote:
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google
and you should find some
> info, possibly also in Books Online. So it is possible that you can query
sys.databases (state_desc
> column) to see what state the database is in and based on that connect. St
ill a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason

Delay between CREATE DATABASE and ability to connect to that database

I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/This is probably for the simple reason that it takes a while to create the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegroups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason|||I think I understand. You execute the CREATE command, and are blocked. As soon as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a little while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
column) to see what state the database is in and based on that connect. Still a polling approach,
though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegroups.com...
> Tibor Karaszi wrote:
>> This is probably for the simple reason that it takes a while to create the database.
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
> info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
> column) to see what state the database is in and based on that connect. Still a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason

Delay between CREATE DATABASE and ability to connect to that database

I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.

I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:

open new connection
create database
create tables
populate tables
close connection

// open new connection /* can't do this yet, as it would break */

for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}

Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?

Thanks,

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/On 6 Oct 2006 17:36:01 -0700, Jason Kester wrote:

(snip)

Hi Jason,

You posted the same question and youy already received some answers
there.

In the future, please post to a single group only. It prevents people
wasting time on a problm that has already been solved.

--
Hugo Kornelis, SQL Server MVP

Delay before uncomplete transaction removed

When a workstation losts connection to server,
it can leave an uncomplete transaction. Then
SQL Server removes the transaction.
Could anyone guide me how to set the delay
before SQL Server do it ?

Thanks in advance
John S.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!john smile <nospam> wrote in message news:<40fda2d1$0$16449$c397aba@.news.newsgroups.ws>...
> When a workstation losts connection to server,
> it can leave an uncomplete transaction. Then
> SQL Server removes the transaction.
> Could anyone guide me how to set the delay
> before SQL Server do it ?
> Thanks in advance
> John S.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

I'm not sure I understand your question - as far as I know, MSSQL will
rollback the transaction immediately, but why would you want to delay
the rollback? Perhaps if you can explain what problem you are trying
to solve, someone will be able to make a suggestion.

Simon|||Simon, thanks for the response.

>>I'm not sure I understand your question -
as far as I know, MSSQL will
rollback the transaction immediately, >
It means : I want to know for sure how many seconds
is it from connection failure to the beginning
of rollback.

>>but why would you want to delay
the rollback? >
No, I do not want to delay it.
If possible, I want to make the delay short,
as short as possible by adjusting any parameters
(like in Novell Netware : "number of watchdog -
packets", "delay between watchdog packets").

>>Perhaps if you can explain what problem you are trying
to solve, someone will be able to make a suggestion. >
Currently, I have no problem. It is only a question.
I also want to ask :
If I found that SQL Server took long to rollback
an uncommitted small transaction caused by a network
failure, what should I do ?

Regards
John S.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"john smile" <nospam> wrote in message
news:40fe5998$0$16470$c397aba@.news.newsgroups.ws.. .
> Currently, I have no problem. It is only a question.
> I also want to ask :
> If I found that SQL Server took long to rollback
> an uncommitted small transaction caused by a network
> failure, what should I do ?

Design your transactions better.

Seriously, this is one of the issues we've looked at as we're moving to
clustering. Clustering basically involves shutting down and restarting SQL
Server, so any long transactions affect your failover time. And if for
example you had transactions that take 15 minutes to rollforward or back,
your failover will take at least that long to occur. Which sorta negates
the usefullness in most cases.

Generally small transactions won't take long at all to rollback. Even lots
of them.

> Regards
> John S.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

Delay before publishing

Hello
Is it possible that the Publisher could wait some time ( about 15 seconds)
before it starts publishing ?
I want it to start publishing when it gets new data, but there can be about
1000 new records in few seconds and I think it would be better to
wait for few seconds then to create new publication after single record is
inserted.
Best Regards
Wojciech Znaniecki
Wojciech ,
there is a difference between creating a publication and synchronizing data
for an existing publication. The publication can be created and as long as
the logreader and distribution agents don't run (no synchronization) there
is no effect on the publisher. Typically the log reader runs continuously,
but you can schedule the distribution agent to run whenever you want. If
this also runs continuously and you want to enforce a delay, you could
increase the POLLINGINTERVAL parameter's value. Other parameters you might
be interested in are -CommitBatchThreshold, CommitBatchSize,
MaxDeliveredTransactions.
HTH,
Paul Ibison
|||Thanks for fast anwser
I've tried to do it but I cant create a publication with stopped
Distribution Agent and Log Reader Agent.
Can I do it with sql script?
for example: my script that create Publication looks like that :
"(...)
exec sp_addpublication @.publication = N'RozkazyReplikacja', @.restricted =
N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
= N'Transactional publication of replikacyjna database from Publisher
WOJTEK-Z\W1.', @.status = N'active', @.allow_push = N'true', @.allow_pull =
N'true', @.allow_anonymous = N'false', @.enabled_for_internet = N'false',
@.independent_agent = N'false', @.immediate_sync = N'false', @.allow_sync_tran
= N'false', @.autogen_sync_procs = N'false', @.retention = 336,
@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.logreader_job_name =
N'WOJTEK-Z\W1-replikacyjna-5'
exec sp_addpublication_snapshot @.publication = N'RozkazyReplikacja',
@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval =
0, @.frequency_recurrence_factor = 1, @.frequency_subday = 4,
@.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date =
0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959,
@.snapshot_job_name = N'WOJTEK-Z\W1-replikacyjna-RozkazyReplikacja-7'
GO
(...) "
What should i change to create publication without starting Distribution and
Log Reader Agent ?
Best Regards
Wojciech Znaniecki
|||Wojtek,
using sp_addsubscriber will allow you to set the frequency for the
distribution agent. However, the easeist way to do what you require is to
create the publication without starting the snapshot agent. Then edit the
logreader and distribution agent jobs to run on a schedule.
HTH,
Paul Ibison
|||Can I set logreader and distribution agent to run more then once per minute ?
Or could I create a trigger to start, let logreader and distribution reader
to their job and stop them ?
Best Regards
Wojciech Znaniecki
"Paul Ibison" wrote:

> Wojtek,
> using sp_addsubscriber will allow you to set the frequency for the
> distribution agent. However, the easeist way to do what you require is to
> create the publication without starting the snapshot agent. Then edit the
> logreader and distribution agent jobs to run on a schedule.
> HTH,
> Paul Ibison
>
>
|||Wojciech,
you can use sp_start_job to start the agents if you want to do it
programatically. If you want to schedule it, AFAIR the maximum frequency is
1 minute or you can run continuously. If the latter, the pollinginterval
will potantially increase it in a more granular way. However, the log reader
agent normally runs continuously and the distribution agent may be
controlled in such a way. The log reader agent marks the log as having been
read, and if it is not run very frequently, the log will increase in size
and not be fully truncated during a backup.
HTH,
Paul Ibison