Showing posts with label running. Show all posts
Showing posts with label running. Show all posts

Friday, March 30, 2012

Multi Server Jobs

I am running two instances of SQL Server 2005 Developer Edition (SP1) on the
same machine. Both SQL Server and Agent services use a local user account
which is member of Windows Administrators. The wizard for Enlisting TSX
fails with the following report:
Enlist TSX Progress
- Create MSXOperator (Success)
Checking for an existing MSXOperator.
Updating existing MSXOperator.
Successfully updated MSXOperator.
- Make sure the Agent service for 'NIMA\sql2005_1' is running (Success)
The service 'SQLAgent$SQL2005_1' is running.
- Enlist 'NIMA\sql2005_2' into 'NIMA\sql2005_1' (Error)
Enlisting target server 'NIMA\sql2005_2' with master server
'NIMA\sql2005_1'.
Using new enlistment method.
Messages
MSX enlist failed for JobServer 'NIMA\sql2005_2'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476
--
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
The enlist operation failed (reason: SQLServerAgent Error: Unable to connect
to server. .) (Microsoft SQL Server, Error: 22026)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476
Any help would be greatly appreciated,
leilaHello Leila,
Personally MSX is really bad in RTM and SP1. a huge number of fixes are in
SP2.
Have you tried a domain account for SQL Agent?
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> I am running two instances of SQL Server 2005 Developer Edition (SP1)
> on the same machine. Both SQL Server and Agent services use a local
> user account which is member of Windows Administrators. The wizard for
> Enlisting TSX fails with the following report:
> Enlist TSX Progress
> - Create MSXOperator (Success)
> Checking for an existing MSXOperator.
> Updating existing MSXOperator.
> Successfully updated MSXOperator.
> - Make sure the Agent service for 'NIMA\sql2005_1' is running
> (Success)
> The service 'SQLAgent$SQL2005_1' is running.
> - Enlist 'NIMA\sql2005_2' into 'NIMA\sql2005_1' (Error)
> Enlisting target server 'NIMA\sql2005_2' with master server
> 'NIMA\sql2005_1'.
> Using new enlistment method.
> Messages
> MSX enlist failed for JobServer 'NIMA\sql2005_2'.
> (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9
> .00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplat
> es.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=2047
> 6
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> The enlist operation failed (reason: SQLServerAgent Error: Unable to
> connect to server. .) (Microsoft SQL Server, Error: 22026)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=0
> 9.00.2047&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476
> Any help would be greatly appreciated,
> leil|||Actually I don't have a domain on my notebook, I have used .\username for
agents.
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a43cc68c90a27d3a103d4@.msnews.microsoft.com...
> Hello Leila,
> Personally MSX is really bad in RTM and SP1. a huge number of fixes are in
> SP2.
> Have you tried a domain account for SQL Agent?
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>> I am running two instances of SQL Server 2005 Developer Edition (SP1)
>> on the same machine. Both SQL Server and Agent services use a local
>> user account which is member of Windows Administrators. The wizard for
>> Enlisting TSX fails with the following report:
>> Enlist TSX Progress
>> - Create MSXOperator (Success)
>> Checking for an existing MSXOperator.
>> Updating existing MSXOperator.
>> Successfully updated MSXOperator.
>> - Make sure the Agent service for 'NIMA\sql2005_1' is running
>> (Success)
>> The service 'SQLAgent$SQL2005_1' is running.
>> - Enlist 'NIMA\sql2005_2' into 'NIMA\sql2005_1' (Error)
>> Enlisting target server 'NIMA\sql2005_2' with master server
>> 'NIMA\sql2005_1'.
>> Using new enlistment method.
>> Messages
>> MSX enlist failed for JobServer 'NIMA\sql2005_2'.
>> (Microsoft.SqlServer.Smo)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9
>> .00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplat
>> es.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=2047
>> 6
>> --
>> ADDITIONAL INFORMATION:
>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> The enlist operation failed (reason: SQLServerAgent Error: Unable to
>> connect to server. .) (Microsoft SQL Server, Error: 22026)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=0
>> 9.00.2047&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476
>> Any help would be greatly appreciated,
>> leila
>

Multi Server Jobs

I am running two instances of SQL Server 2005 Developer Edition (SP1) on the
same machine. Both SQL Server and Agent services use a local user account
which is member of Windows Administrators. The wizard for Enlisting TSX
fails with the following report:
Enlist TSX Progress
- Create MSXOperator (Success)
Checking for an existing MSXOperator.
Updating existing MSXOperator.
Successfully updated MSXOperator.
- Make sure the Agent service for 'NIMA\sql2005_1' is running (Success)
The service 'SQLAgent$SQL2005_1' is running.
- Enlist 'NIMA\sql2005_2' into 'NIMA\sql2005_1' (Error)
Enlisting target server 'NIMA\sql2005_2' with master server
'NIMA\sql2005_1'.
Using new enlistment method.
Messages
MSX enlist failed for JobServer 'NIMA\sql2005_2'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The enlist operation failed (reason: SQLServerAgent Error: Unable to connect
to server. .) (Microsoft SQL Server, Error: 22026)
For help, click:
http://go.microsoft.com/fwlink?Prod...26&LinkId=20476
Any help would be greatly appreciated,
leilaHello Leila,
Personally MSX is really bad in RTM and SP1. a huge number of fixes are in
SP2.
Have you tried a domain account for SQL Agent?
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> I am running two instances of SQL Server 2005 Developer Edition (SP1)
> on the same machine. Both SQL Server and Agent services use a local
> user account which is member of Windows Administrators. The wizard for
> Enlisting TSX fails with the following report:
> Enlist TSX Progress
> - Create MSXOperator (Success)
> Checking for an existing MSXOperator.
> Updating existing MSXOperator.
> Successfully updated MSXOperator.
> - Make sure the Agent service for 'NIMA\sql2005_1' is running
> (Success)
> The service 'SQLAgent$SQL2005_1' is running.
> - Enlist 'NIMA\sql2005_2' into 'NIMA\sql2005_1' (Error)
> Enlisting target server 'NIMA\sql2005_2' with master server
> 'NIMA\sql2005_1'.
> Using new enlistment method.
> Messages
> MSX enlist failed for JobServer 'NIMA\sql2005_2'.
> (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...erver&ProdVer=9
> .00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplat
> es.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=2047
> 6
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> The enlist operation failed (reason: SQLServerAgent Error: Unable to
> connect to server. .) (Microsoft SQL Server, Error: 22026)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...erver&ProdVer=0
> 9.00.2047&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476
> Any help would be greatly appreciated,
> leila|||Actually I don't have a domain on my notebook, I have used .\username for
agents.
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a43cc68c90a27d3a103d4@.msnews
.microsoft.com...
> Hello Leila,
> Personally MSX is really bad in RTM and SP1. a huge number of fixes are in
> SP2.
> Have you tried a domain account for SQL Agent?
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>sql

Wednesday, March 28, 2012

Multi Server Jobs

I am running two instances of SQL Server 2005 Developer Edition (SP1) on the
same machine. Both SQL Server and Agent services use a local user account
which is member of Windows Administrators. The wizard for Enlisting TSX
fails with the following report:
Enlist TSX Progress
- Create MSXOperator (Success)
Checking for an existing MSXOperator.
Updating existing MSXOperator.
Successfully updated MSXOperator.
- Make sure the Agent service for 'NIMA\sql2005_1' is running (Success)
The service 'SQLAgent$SQL2005_1' is running.
- Enlist 'NIMA\sql2005_2' into 'NIMA\sql2005_1' (Error)
Enlisting target server 'NIMA\sql2005_2' with master server
'NIMA\sql2005_1'.
Using new enlistment method.
Messages
MSX enlist failed for JobServer 'NIMA\sql2005_2'. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=MSX+enlist+JobServer&LinkId=20476[/url]
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The enlist operation failed (reason: SQLServerAgent Error: Unable to connect
to server. .) (Microsoft SQL Server, Error: 22026)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476[ /url]
Any help would be greatly appreciated,
leila
Hello Leila,
Personally MSX is really bad in RTM and SP1. a huge number of fixes are in
SP2.
Have you tried a domain account for SQL Agent?
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> I am running two instances of SQL Server 2005 Developer Edition (SP1)
> on the same machine. Both SQL Server and Agent services use a local
> user account which is member of Windows Administrators. The wizard for
> Enlisting TSX fails with the following report:
> Enlist TSX Progress
> - Create MSXOperator (Success)
> Checking for an existing MSXOperator.
> Updating existing MSXOperator.
> Successfully updated MSXOperator.
> - Make sure the Agent service for 'NIMA\sql2005_1' is running
> (Success)
> The service 'SQLAgent$SQL2005_1' is running.
> - Enlist 'NIMA\sql2005_2' into 'NIMA\sql2005_1' (Error)
> Enlisting target server 'NIMA\sql2005_2' with master server
> 'NIMA\sql2005_1'.
> Using new enlistment method.
> Messages
> MSX enlist failed for JobServer 'NIMA\sql2005_2'.
> (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9
> .00.2047.00&EvtSrc=Microsoft.SqlServer.Management. Smo.ExceptionTemplat
> es.FailedOperationExceptionText&EvtID=MSX+enlist+J obServer&LinkId=2047
> 6
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> The enlist operation failed (reason: SQLServerAgent Error: Unable to
> connect to server. .) (Microsoft SQL Server, Error: 22026)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=0
> 9.00.2047&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20 476
> Any help would be greatly appreciated,
> leila
|||Actually I don't have a domain on my notebook, I have used .\username for
agents.
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a43cc68c90a27d3a103d4@.msnews.microsoft .com...
> Hello Leila,
> Personally MSX is really bad in RTM and SP1. a huge number of fixes are in
> SP2.
> Have you tried a domain account for SQL Agent?
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>

multi processors

I have a quad processor machine not running hyperthreading. I am also
running sQL enterprise additions. I would like to understand why the
processing load is not distrubuted evenly. The second and third processors
seem to have most of the utilization.
I assume that you have configured SQL Server to use all processors via
sp_configure and the affinity mask. SQL Server will split and distribute
the query load as it sees fit. You might not end up with a perfect
distribution of processor utilization.
exec sp_configure 'affinity mask'
/*
to use all processors:
exec sp_configure 'affinity mask' , 0
*/
Keith
"Ruth Henderson" <Ruth Henderson@.discussions.microsoft.com> wrote in message
news:2BB0F14B-CA24-4EB4-B120-F88044A2B55F@.microsoft.com...
> I have a quad processor machine not running hyperthreading. I am also
> running sQL enterprise additions. I would like to understand why the
> processing load is not distrubuted evenly. The second and third
processors
> seem to have most of the utilization.
|||There can be several reasons. First, all threads do not require the same
amount of processor resource so there will always be some imbalance.
Second, Windows binds physical processors to service particular IO cards, so
your NIC and Disk subsystems are likely bound to the second and third
processors. On very-multi-processor systems (16-way and up) excluding
certain processors from SQL because of this OS binding can improve
performance. On a four-way box, you are best to leave things as is.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ruth Henderson" <Ruth Henderson@.discussions.microsoft.com> wrote in message
news:2BB0F14B-CA24-4EB4-B120-F88044A2B55F@.microsoft.com...
> I have a quad processor machine not running hyperthreading. I am also
> running sQL enterprise additions. I would like to understand why the
> processing load is not distrubuted evenly. The second and third
processors
> seem to have most of the utilization.

multi processors

I have a quad processor machine not running hyperthreading. I am also
running sQL enterprise additions. I would like to understand why the
processing load is not distrubuted evenly. The second and third processors
seem to have most of the utilization.I assume that you have configured SQL Server to use all processors via
sp_configure and the affinity mask. SQL Server will split and distribute
the query load as it sees fit. You might not end up with a perfect
distribution of processor utilization.
exec sp_configure 'affinity mask'
/*
to use all processors:
exec sp_configure 'affinity mask' , 0
*/
--
Keith
"Ruth Henderson" <Ruth Henderson@.discussions.microsoft.com> wrote in message
news:2BB0F14B-CA24-4EB4-B120-F88044A2B55F@.microsoft.com...
> I have a quad processor machine not running hyperthreading. I am also
> running sQL enterprise additions. I would like to understand why the
> processing load is not distrubuted evenly. The second and third
processors
> seem to have most of the utilization.|||There can be several reasons. First, all threads do not require the same
amount of processor resource so there will always be some imbalance.
Second, Windows binds physical processors to service particular IO cards, so
your NIC and Disk subsystems are likely bound to the second and third
processors. On very-multi-processor systems (16-way and up) excluding
certain processors from SQL because of this OS binding can improve
performance. On a four-way box, you are best to leave things as is.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ruth Henderson" <Ruth Henderson@.discussions.microsoft.com> wrote in message
news:2BB0F14B-CA24-4EB4-B120-F88044A2B55F@.microsoft.com...
> I have a quad processor machine not running hyperthreading. I am also
> running sQL enterprise additions. I would like to understand why the
> processing load is not distrubuted evenly. The second and third
processors
> seem to have most of the utilization.

multi processors

I have a quad processor machine not running hyperthreading. I am also
running sQL enterprise additions. I would like to understand why the
processing load is not distrubuted evenly. The second and third processors
seem to have most of the utilization.I assume that you have configured SQL Server to use all processors via
sp_configure and the affinity mask. SQL Server will split and distribute
the query load as it sees fit. You might not end up with a perfect
distribution of processor utilization.
exec sp_configure 'affinity mask'
/*
to use all processors:
exec sp_configure 'affinity mask' , 0
*/
Keith
"Ruth Henderson" <Ruth Henderson@.discussions.microsoft.com> wrote in message
news:2BB0F14B-CA24-4EB4-B120-F88044A2B55F@.microsoft.com...
> I have a quad processor machine not running hyperthreading. I am also
> running sQL enterprise additions. I would like to understand why the
> processing load is not distrubuted evenly. The second and third
processors
> seem to have most of the utilization.|||There can be several reasons. First, all threads do not require the same
amount of processor resource so there will always be some imbalance.
Second, Windows binds physical processors to service particular IO cards, so
your NIC and Disk subsystems are likely bound to the second and third
processors. On very-multi-processor systems (16-way and up) excluding
certain processors from SQL because of this OS binding can improve
performance. On a four-way box, you are best to leave things as is.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ruth Henderson" <Ruth Henderson@.discussions.microsoft.com> wrote in message
news:2BB0F14B-CA24-4EB4-B120-F88044A2B55F@.microsoft.com...
> I have a quad processor machine not running hyperthreading. I am also
> running sQL enterprise additions. I would like to understand why the
> processing load is not distrubuted evenly. The second and third
processors
> seem to have most of the utilization.sql

Multi processor OS restriction?

Hi, Ive been told this, but I hope it is NOT true. I have an sql
server2000 installation running on a server that has four processors.
It is on a active network but is not the domain controller so
essentially it is fully dedicated to servicing the needs of sql
server, (a bit of browsing, a bit of ms Office, but almost wholly
dedicated to sqlserver. Now, the big question, why, when the server
properties have been set to utilize all four processors, can any one
job never get more than 25% of cpu time? I can launch multiple
instance of QA and run the same job on each one and that will utilise
more and more cpu time, but if you launch multile QA windows from
within one insance of QA, you can NEVER get more than 25% CPU
utilisation. Now i have to run a job (FTS is a good example,
re-indexing lots of db's another, or even a huge query with multiple
ufd's on computed cols which I hoped would grab lots of CPU time that
they need, but no. So do I have to live with this or can I tell either
windows or sql server to grab more cpu when it want to ie use my spare
CPU capacity more efficiently or am i working on a misguided premise
and 25% per job is your lot?

DMAC>>Hi, Ive been told this, but I hope it is NOT true. I have an sql
>>server2000 installation running on a server that has four processors.
>>It is on a active network but is not the domain controller so
>>essentially it is fully dedicated to servicing the needs of sql
>>server, (a bit of browsing, a bit of ms Office, but almost wholly
>>dedicated to sqlserver. Now, the big question, why, when the server
>>properties have been set to utilize all four processors, can any one
>>job never get more than 25% of cpu time?
MSSQL can utilize 'parallelism' to make use of multi processors. But
it doesn't work half the time. Unless you have a single user, it is
better for MSSQL to save those other processors for other SPIDS.

>>I can launch multiple
>>instance of QA and run the same job on each one and that will utilise
>>more and more cpu time, but if you launch multile QA windows from
>>within one insance of QA, you can NEVER get more than 25% CPU
>>utilisation. Now i have to run a job (FTS is a good example,
>>re-indexing lots of db's another, or even a huge query with multiple
>>ufd's on computed cols which I hoped would grab lots of CPU time that
>>they need, but no. So do I have to live with this or can I tell
either
>>windows or sql server to grab more cpu when it want to ie use my
spare
>>CPU capacity more efficiently or am i working on a misguided premise
>>and 25% per job is your lot?
What's FTS & UFDs? User defined function?|||The SQL Server optimizer will generate a parallel plan only when it makes
sense to do so and the current server workload permits it. Many queries
will not benefit from a parallel plan. In practice, parallelism can be a
symptom of needed indexes or poorly formulated query.

> I can launch multiple
> instance of QA and run the same job on each one and that will utilise
> more and more cpu time, but if you launch multile QA windows from
> within one insance of QA, you can NEVER get more than 25% CPU
> utilisation.

This is not consistent with my experience. For example, I see both
processors fully used on my dual-cpu box by running the following query from
within the same QA instance. Do you get similar results?

USE master
SELECT COUNT(*)
FROM sysobjects a
CROSS JOIN sysobjects b
CROSS JOIN sysobjects c
CROSS JOIN sysobjects d
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"DMAC" <drmcl@.drmcl.free-online.co.uk> wrote in message
news:462ac9a0.0501180705.4f2c5be7@.posting.google.c om...
> Hi, Ive been told this, but I hope it is NOT true. I have an sql
> server2000 installation running on a server that has four processors.
> It is on a active network but is not the domain controller so
> essentially it is fully dedicated to servicing the needs of sql
> server, (a bit of browsing, a bit of ms Office, but almost wholly
> dedicated to sqlserver. Now, the big question, why, when the server
> properties have been set to utilize all four processors, can any one
> job never get more than 25% of cpu time? I can launch multiple
> instance of QA and run the same job on each one and that will utilise
> more and more cpu time, but if you launch multile QA windows from
> within one insance of QA, you can NEVER get more than 25% CPU
> utilisation. Now i have to run a job (FTS is a good example,
> re-indexing lots of db's another, or even a huge query with multiple
> ufd's on computed cols which I hoped would grab lots of CPU time that
> they need, but no. So do I have to live with this or can I tell either
> windows or sql server to grab more cpu when it want to ie use my spare
> CPU capacity more efficiently or am i working on a misguided premise
> and 25% per job is your lot?
> DMAC|||Thanks Dan,

Your query did indeed put all four processors into overdrive, (took me
a while to cancel it cos I could not get my mouse click over the wire)
so my problem probably lies with your first suggestion about poorly
formed queries. Is there any mechanism to influence the optimiser or
thread selection to have my one really bad query utilise its own
processor( or just generally so that I can keep developement to its own
cpu/thread combo), leaving everthing else to utilize the other
processors, ie why did sql server immediately grap all the cpu time from
your query?

Cheers

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||> Is there any mechanism to influence the optimiser or
> thread selection to have my one really bad query utilise its own
> processor( or just generally so that I can keep developement to its own
> cpu/thread combo), leaving everthing else to utilize the other
> processors,

You can specify a MAXDOP hint to limit parallelism to the specified number
of processors: for a particular query:

USE master
SELECT COUNT(*)
FROM sysobjects a
CROSS JOIN sysobjects b
CROSS JOIN sysobjects c
CROSS JOIN sysobjects d
OPTION (MAXDOP 1)
GO

You can also adjust the server-wide setting with the 'max degree of
parallelism' configuration option. On a server with 4 or more processors, I
usually use this option to specify fewer processors than are available (e.g.
3) so that a single query won't monopolize CPU resources.

> ie why did sql server immediately grap all the cpu time from
> your query?

When SQL Server determines a query can benefit from parallelism, it
considers the current server workload and adjusts the number of parallel
threads accordingly. The optimizer may choose to use a single thread or
fewer processors when the machine is busy and a more aggressive plan when
not currently busy.

It's a good practice to segregate development and production on different
servers when possible.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<DMAC@.devdex.com> wrote in message news:41ef7c7f$1_2@.127.0.0.1...
> Thanks Dan,
> Your query did indeed put all four processors into overdrive, (took me
> a while to cancel it cos I could not get my mouse click over the wire)
> so my problem probably lies with your first suggestion about poorly
> formed queries. Is there any mechanism to influence the optimiser or
> thread selection to have my one really bad query utilise its own
> processor( or just generally so that I can keep developement to its own
> cpu/thread combo), leaving everthing else to utilize the other
> processors, ie why did sql server immediately grap all the cpu time from
> your query?
> Cheers
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Monday, March 26, 2012

multi databases or partitions

Hi all

i have 3 million customer stored in a billing system.

currently i dived them to 40 database running sql2000.

i plan to migrate to sql 2005.

Is it better to let all customer in one partioned table based on city.?

or divide them one database for city.

taking into account there is 15 million transaction per month.

i want to get best performance and maintainance for backup.

thanks

Hi,

I would go for the partioned table. It is easier to remain within 1 database. I think there is more overhead in going to multiple databases anyway. With the partioned tables in 2005, you can span your data on multiple disks.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi Geert Verhoeven

Thank you for reply.

I use one storage Raid-5 for database storage composed of of 8 disk*73GB . In that case ,can that fullfill "span data on multiple disks".

Is there overhead for table partition compared to multi databases.

Just i want to know the side effect before starting merging data in one database using partitioned tables.

thanks

|||

Hi,

Indeed since you are using Raid, the spanning multiple disks is not an argument. Partioned tables can be processed by different processors but that is the same for multiple databases.

I still think that multiple databases give you more overhead then remaining in one database but it is hard to tell. Either way, I don't think that partitioned tables will be slower than multiple databases. Partitioned tables are easier to manage then multiple databases.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Since you have 40 different databases, I assume you want to be able to backup a portion (i.e. database in this case) and also that your data for one city does not interact with data in another city. With table partitioning, you can get the same convenience. you can partition by the city. You can possibly put each partition in its own file group there by mapped to diiferentg disk, you can backup/restore at filegroup granularity. Since partitioned table is managed as one logical unit, you won't need to worry about making schema changes in 40 databases, for example if you want to create an index.

multi databases or partitions

Hi all

i have 3 million customer stored in a billing system.

currently i dived them to 40 database running sql2000.

i plan to migrate to sql 2005.

Is it better to let all customer in one partioned table based on city.?

or divide them one database for city.

taking into account there is 15 million transaction per month.

i want to get best performance and maintainance for backup.

thanks

Hi,

I would go for the partioned table. It is easier to remain within 1 database. I think there is more overhead in going to multiple databases anyway. With the partioned tables in 2005, you can span your data on multiple disks.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi Geert Verhoeven

Thank you for reply.

I use one storage Raid-5 for database storage composed of of 8 disk*73GB . In that case ,can that fullfill "span data on multiple disks".

Is there overhead for table partition compared to multi databases.

Just i want to know the side effect before starting merging data in one database using partitioned tables.

thanks

|||

Hi,

Indeed since you are using Raid, the spanning multiple disks is not an argument. Partioned tables can be processed by different processors but that is the same for multiple databases.

I still think that multiple databases give you more overhead then remaining in one database but it is hard to tell. Either way, I don't think that partitioned tables will be slower than multiple databases. Partitioned tables are easier to manage then multiple databases.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Since you have 40 different databases, I assume you want to be able to backup a portion (i.e. database in this case) and also that your data for one city does not interact with data in another city. With table partitioning, you can get the same convenience. you can partition by the city. You can possibly put each partition in its own file group there by mapped to diiferentg disk, you can backup/restore at filegroup granularity. Since partitioned table is managed as one logical unit, you won't need to worry about making schema changes in 40 databases, for example if you want to create an index.

sql

Friday, March 23, 2012

Mulitple Create Views in Query Batch

I have a script that I am running from a Query Analyser that I want to put
into a SP eventually.
In the script, I have 5 Create Views (which I drop when the script exits).
But I have to have "GO" after each Create View or I will get an error:
'CREATE VIEW' must be the first statement in a query batch.'
The problem is I also have a beginning date and ending date that I am using
in my query after the Views are created. But I can't declare and set them
up until after the Create Views are done.
Create View...
go
Create View...
go
Create View
go
declare @.StartDate smallDateTime,@.EndDate smallDateTime
select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
select ...
drop View...
What I would like to do is put the declares at the top of the script so that
it will be easier to find for the person running the script to allow them to
change dates as they will be running this 6 or 7 times for different date
ranges.
This is just a one time project, so I don't want to set up a SP at the
moment or write a simple GUI to handle it.
Is there a way to do this (put the dates at the top somehow)?
Also, is the multiple creation of Views a problem in a SP also?
Thanks,
TomTshad,
I've never done what you're asking and I'm not totally sure why you would.
That being said, your variable is batch specific and cannot span batches.
Also, I'm not sure how you would code multiple GOs in your stored procedure.
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> Tshad,
> I've never done what you're asking and I'm not totally sure why you would.
> That being said, your variable is batch specific and cannot span batches.
> Also, I'm not sure how you would code multiple GOs in your stored
> procedure.
But if you can only create one View in a Batch, that would be a problem in a
SP where you may need to create more than one.
But I don't know how it is done either.
This is being done to do one specific script to move selected data into a
CSV file to move some data from a client site to ours. We are just going to
give them the script to run. They will run it from Query Analyser and they
can save the results to a tab delimited file and send it to us.
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>|||Tshad,
Your variables can be persisted across batches by using a temporary table or
a permanent table - table can be dropped at the end of the script. Why does
this need to be embedded in a proc? Can you just give them a .sql script to
run?
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> But if you can only create one View in a Batch, that would be a problem in
> a SP where you may need to create more than one.
> But I don't know how it is done either.
> This is being done to do one specific script to move selected data into a
> CSV file to move some data from a client site to ours. We are just going
> to give them the script to run. They will run it from Query Analyser and
> they can save the results to a tab delimited file and send it to us.
> Tom
>|||Look up CREATE SCHEMA in BOL.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
That is what I am doing. The problem is that the Declares for the dates are
halfway down the script. And I was just trying to make it easy on them. It
isn't a big problem, just that I would be nice for them to be able to change
the dates at the top of the script.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||SQL Server wants to see just one CREATE VIEW in a batch of its own.
That is just the rules. If you think about it, how could you use a
view that is created in the same batch as code that references it?
Only if this were procedural code that is executed, step by step, like
a 3GL, instead of an RDBMS.
But a better question is why would you create VIEWs and then drop them?
That is not what VIEWs are for. Use derived tables, CTE, etc. if you
want to to do this kind of thing. I'll bet you are still thinking in
3GL terms and want to fake a bunch of scratch files.|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
It doesn't. And I did give them an sql script. I was trying to see if
there was a way to move the variable declares to the top of files in the
first batch. It wasn't necessary, just curious if there was a way to do it.
As far as the procedure, I was just asking as I may want to set up an SP
using multiple views and if you can't do it as a batch, I may not be able to
do it in a procedure, either.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127446041.456812.95650@.g47g2000cwa.googlegroups.com...
> SQL Server wants to see just one CREATE VIEW in a batch of its own.
> That is just the rules. If you think about it, how could you use a
> view that is created in the same batch as code that references it?
> Only if this were procedural code that is executed, step by step, like
> a 3GL, instead of an RDBMS.
Why not?
I am creating multiple Views that I am using temporarily and I am not sure
why it is obvious that you can't create a View and then immediately
reference it. There probably is a good reason for it, but I don't know what
or why it is.

> But a better question is why would you create VIEWs and then drop them?
> That is not what VIEWs are for. Use derived tables, CTE, etc. if you
> want to to do this kind of thing. I'll bet you are still thinking in
> 3GL terms and want to fake a bunch of scratch files.
You are right. That is the way I think. So shoot me. :)
This may not be what Views are for, but they solve a huge problem for me and
worked great.
May not have been the cleanest way, but I liked it. As a matter of fact, I
did use one table that I selected into and 5 Views, which worked great (took
a little time to put together with a great deal of help from Hugo).
As far as derived tables, I used those also. But for my select statement
(which was very large - at least I thought so) I was doing quite a bit of
work to get all my data to go across one line for each record ( I know there
are no fields, records or tables). This was for a csv file import/export.
According to what Hugo explained I was, in effect, using derived tables in
the form of Views. But if I had to replace all my references to my views
with derived tables, I think it would have been a bear to debug and my files
would have been 10-20 times larger.
I look at this as using the Views as a subroutine or macro that I call
instead of placing multiple instances of the same code throughout my select
statement. Much cleaner, even if not as efficient.
Tom|||>> Why not? <<
VIEWs can be built on VIEWs; they have to be created in order. Think
about it
NO, NO, NO!! You create VIEWs because they have meaning as data
elements that "persist" over many queries. There are no "temporary
kind of things" in a good data model. Damn it, man, you are still
writing scratch files in a 1960's COBOL system!
Not a problem, but if you do not learn, we will have to kill you\
. Hey, if I could not get at least a breakdown or a suicide during
final exams, the quarter was a waste!
VIEWs are good, but they are part of a schema design and need to be
planned as much as any other tabel. In spite of the myth, size does
not matter. In RDBMS, unlike sex, speed is better [note to self: I am
going har this quote again]

Wednesday, March 21, 2012

MSSQLServerOLAPSerice will not start.

I setup a MSSQL 2005 Cluster running on top of Virtual Server 2005. Everthing was running fine until this morning, when suddenly Analysis Services failed to start on the Cluster. When I looked in the Event Log I see the following:

Source: MSSQLServerOLAPService
Category: (289)
Type: Error
Event ID: 0

Description:
The service cannot be started: The DOM parser failed to load and parse the stream. URL:'' Reason: 'A document must contain exactly one root element.

' Source:''. File position: 0. Line: 1. The DOM parser failed to load and parse the file '\\?\N:\Microsoft SQL Server\MSSQL.2\OLAP\Data\master.vmp'.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Any idea on what I can do to resolve this problem.

Thanks.

Most likely you data became corrupted. If you can reproduce the scenario you should contact product support services to troubleshoot this problem.

To get Analysis Server to start again you delete all the files in "%AS installation folder%\data" folder.

You need to restore you databases you've backed up earlier. Alternatively you can re-create and re-process all the cubes and all dimensions you had.

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

|||Thanks for the reply. I ended up simply deleting and reinstalling the service.

mssqlserverADhelper service terminated, Access Denied when trying to connect

This problem just started recently. This computer connected to the database
when it was first installed. All computer are new Compaq running Windows xp
pro.
This is a problem with MSDE. We are running MS Retail Management system.
One Point of Sale station won't connect to the server. I am running MSDE on
4 computers. One is the peer server it is named "serverxp" the one that
won't connect is "POS2". Other computers are POS1 and notebook. Below is
the case notes so far.
This one POS station is having a problem connection to the server database.
It has no problem connecting to the local database. What I see is on the SQL
Service Manager, When I select the network server "ServerXP" there is
nothing in the dropdown for services. When I select the local server then
three options are listed Dist. Tran, SQL Server and SQL Server agent.
This POS2 computer can connect to ServerXP and see shared folders etc. So I
haven't found any problem with the LAN or hardware.
Can you tell by this description what is wrong or what to try to do to fix
this?
Thanks
----
6/9/2004 1:28:00 PM CDT -- Allison Haaland
Steven,
I have a few more questions for you in order to further troubleshoot this
issue:
1. When you look at the SQL Server Service Manager and choose the "ServerXP"
option, does it say Not Connected at the bottom? yes
2. When you select the local server option in SQL Server Service Manager,
can you then connect to the database in Administrator? (You would choose
local as your server in Administrator as well.) yes
3. If you go to Start | Control | Administrative Tools | Services, do you
see your MSSQL and SQLAgent options there? If so, is the Status showing as
'Started'? If not, you should make sure that the Services are started if
you're trying to use them.
.MSSQLServer is started
MSSQLServerADhelper is set to manual and will not start "could not start
access denied"
4. Is there anything else that is different about this workstation from the
others? This might help us find out why it is not working, but all of the
others are. no
Thanks,
Allison
----
6/9/2004 3:51:00 PM CDT -- Steven
Allison:
More Info. An error in the System error log shows.
The mssqlserverADhelper service terminated with service specific error
3221225572 error 2270 refer to Knowledge base 314094. ID 7024
this is talking about the computer name. The name of this PC is POS2 it is
unique on this network.
----
6/9/2004 4:43:00 PM CDT -- Allison Haaland
Steven,
It looks like the error you are running into now is a network related issue
related to your computer name. I see that you have already referenced
Knowledge base article #314094 - have you tried the steps that it gives to
resolve this error? YES.
POS2 was working correctly at the time it was installed. No name have been
changed.
Can anyone with more experience working with SQL server give me some help
correcting this problem? Thank you.Hi Steve,
What error is generated with the connection fails?
Create an ODBC Test DSN and post the entire message here.
The ADHelper servers is not required for machines to connect to one
another. It's used to publish objects to Active Directory.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

mssqlserverADhelper service terminated, Access Denied when trying to connect

This problem just started recently. This computer connected to the database
when it was first installed. All computer are new Compaq running Windows xp
pro.
This is a problem with MSDE. We are running MS Retail Management system.
One Point of Sale station won't connect to the server. I am running MSDE on
4 computers. One is the peer server it is named "serverxp" the one that
won't connect is "POS2". Other computers are POS1 and notebook. Below is
the case notes so far.
This one POS station is having a problem connection to the server database.
It has no problem connecting to the local database. What I see is on the SQL
Service Manager, When I select the network server "ServerXP" there is
nothing in the dropdown for services. When I select the local server then
three options are listed Dist. Tran, SQL Server and SQL Server agent.
This POS2 computer can connect to ServerXP and see shared folders etc. So I
haven't found any problem with the LAN or hardware.
Can you tell by this description what is wrong or what to try to do to fix
this?
Thanks
6/9/2004 1:28:00 PM CDT -- Allison Haaland
Steven,
I have a few more questions for you in order to further troubleshoot this
issue:
1. When you look at the SQL Server Service Manager and choose the "ServerXP"
option, does it say Not Connected at the bottom? yes
2. When you select the local server option in SQL Server Service Manager,
can you then connect to the database in Administrator? (You would choose
local as your server in Administrator as well.) yes
3. If you go to Start | Control | Administrative Tools | Services, do you
see your MSSQL and SQLAgent options there? If so, is the Status showing as
'Started'? If not, you should make sure that the Services are started if
you're trying to use them.
..MSSQLServer is started
MSSQLServerADhelper is set to manual and will not start "could not start
access denied"
4. Is there anything else that is different about this workstation from the
others? This might help us find out why it is not working, but all of the
others are. no
Thanks,
Allison
6/9/2004 3:51:00 PM CDT -- Steven
Allison:
More Info. An error in the System error log shows.
The mssqlserverADhelper service terminated with service specific error
3221225572 error 2270 refer to Knowledge base 314094. ID 7024
this is talking about the computer name. The name of this PC is POS2 it is
unique on this network.
6/9/2004 4:43:00 PM CDT -- Allison Haaland
Steven,
It looks like the error you are running into now is a network related issue
related to your computer name. I see that you have already referenced
Knowledge base article #314094 - have you tried the steps that it gives to
resolve this error? YES.
POS2 was working correctly at the time it was installed. No name have been
changed.
Can anyone with more experience working with SQL server give me some help
correcting this problem? Thank you.
Hi Steve,
What error is generated with the connection fails?
Create an ODBC Test DSN and post the entire message here.
The ADHelper servers is not required for machines to connect to one
another. It's used to publish objects to Active Directory.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

MSSQLServerADHelper Service Error on Reboot

I am running SQL Server 2000 sp 4 on Win2K Server sp 4.
Recently, I started getting an MSSQLServerADHelper service error. I can't
rely pin it on anything, other than I had a big database with 2 tables (7 and
20 million records respectively). I ran out of disk space trying to index
one of the tables.
Ultimately, I detached the database, because it was too much for my system.
Since then I have completely removed then reinstalled SQL Server and I still
get the following errors. The MSSQLServerADHelper is set to automatic, which
I believe is the correct state since is supposedly launched as needed. I
have done some research and cannot track down an answer. Any ideas?
Event Type:Error
Event Source:Service Control Manager
Event Category:None
Event ID:7024
Date:6/26/2005
Time:1:54:11 AM
User:N/A
Computer:MERLOT
Description:
The MSSQLServerADHelper service terminated with service-specific error
3221225572.
Event Type:Error
Event Source:MSSQLServerADHelper
Event Category:None
Event ID:100
Date:6/26/2005
Time:1:54:11 AM
User:N/A
Computer:MERLOT
Description:
'0' is an invalid number of start up parameters. This service takes two
start up parameters.
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17055
Date:6/26/2005
Time:2:33:04 PM
User:N/A
Computer:MERLOT
Description:
19012 :
SuperSocket Info: Bind failed on TCP port 1433.
Data:
0000: 44 4a 00 00 10 00 00 00 DJ.....
0008: 07 00 00 00 4d 00 45 00 ...M.E.
0010: 52 00 4c 00 4f 00 54 00 R.L.O.T.
0018: 00 00 00 00 00 00 .....
The MSSQLServerADHelper service should be set to manual. SQL
Server will start and stop the service itself when needed.
-Sue
On Sun, 26 Jun 2005 20:06:01 -0700, Jed
<Jed@.discussions.microsoft.com> wrote:

>I am running SQL Server 2000 sp 4 on Win2K Server sp 4.
>Recently, I started getting an MSSQLServerADHelper service error. I can't
>rely pin it on anything, other than I had a big database with 2 tables (7 and
>20 million records respectively). I ran out of disk space trying to index
>one of the tables.
>Ultimately, I detached the database, because it was too much for my system.
>Since then I have completely removed then reinstalled SQL Server and I still
>get the following errors. The MSSQLServerADHelper is set to automatic, which
>I believe is the correct state since is supposedly launched as needed. I
>have done some research and cannot track down an answer. Any ideas?
>Event Type:Error
>Event Source:Service Control Manager
>Event Category:None
>Event ID:7024
>Date:6/26/2005
>Time:1:54:11 AM
>User:N/A
>Computer:MERLOT
>Description:
>The MSSQLServerADHelper service terminated with service-specific error
>3221225572.
>Event Type:Error
>Event Source:MSSQLServerADHelper
>Event Category:None
>Event ID:100
>Date:6/26/2005
>Time:1:54:11 AM
>User:N/A
>Computer:MERLOT
>Description:
>'0' is an invalid number of start up parameters. This service takes two
>start up parameters.
>Event Type:Error
>Event Source:MSSQLSERVER
>Event Category:(2)
>Event ID:17055
>Date:6/26/2005
>Time:2:33:04 PM
>User:N/A
>Computer:MERLOT
>Description:
>19012 :
>SuperSocket Info: Bind failed on TCP port 1433.
>Data:
>0000: 44 4a 00 00 10 00 00 00 DJ.....
>0008: 07 00 00 00 4d 00 45 00 ...M.E.
>0010: 52 00 4c 00 4f 00 54 00 R.L.O.T.
>0018: 00 00 00 00 00 00 .....

Monday, March 19, 2012

MSSQLServerADHelper Service Error on Reboot

I am running SQL Server 2000 sp 4 on Win2K Server sp 4.
Recently, I started getting an MSSQLServerADHelper service error. I can't
rely pin it on anything, other than I had a big database with 2 tables (7 an
d
20 million records respectively). I ran out of disk space trying to index
one of the tables.
Ultimately, I detached the database, because it was too much for my system.
Since then I have completely removed then reinstalled SQL Server and I still
get the following errors. The MSSQLServerADHelper is set to automatic, whic
h
I believe is the correct state since is supposedly launched as needed. I
have done some research and cannot track down an answer. Any ideas?
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 6/26/2005
Time: 1:54:11 AM
User: N/A
Computer: MERLOT
Description:
The MSSQLServerADHelper service terminated with service-specific error
3221225572.
Event Type: Error
Event Source: MSSQLServerADHelper
Event Category: None
Event ID: 100
Date: 6/26/2005
Time: 1:54:11 AM
User: N/A
Computer: MERLOT
Description:
'0' is an invalid number of start up parameters. This service takes two
start up parameters.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 6/26/2005
Time: 2:33:04 PM
User: N/A
Computer: MERLOT
Description:
19012 :
SuperSocket Info: Bind failed on TCP port 1433.
Data:
0000: 44 4a 00 00 10 00 00 00 DJ.....
0008: 07 00 00 00 4d 00 45 00 ...M.E.
0010: 52 00 4c 00 4f 00 54 00 R.L.O.T.
0018: 00 00 00 00 00 00 .....The MSSQLServerADHelper service should be set to manual. SQL
Server will start and stop the service itself when needed.
-Sue
On Sun, 26 Jun 2005 20:06:01 -0700, Jed
<Jed@.discussions.microsoft.com> wrote:

>I am running SQL Server 2000 sp 4 on Win2K Server sp 4.
>Recently, I started getting an MSSQLServerADHelper service error. I can't
>rely pin it on anything, other than I had a big database with 2 tables (7 a
nd
>20 million records respectively). I ran out of disk space trying to index
>one of the tables.
>Ultimately, I detached the database, because it was too much for my system.
>Since then I have completely removed then reinstalled SQL Server and I stil
l
>get the following errors. The MSSQLServerADHelper is set to automatic, whi
ch
>I believe is the correct state since is supposedly launched as needed. I
>have done some research and cannot track down an answer. Any ideas?
>Event Type: Error
>Event Source: Service Control Manager
>Event Category: None
>Event ID: 7024
>Date: 6/26/2005
>Time: 1:54:11 AM
>User: N/A
>Computer: MERLOT
>Description:
>The MSSQLServerADHelper service terminated with service-specific error
>3221225572.
>Event Type: Error
>Event Source: MSSQLServerADHelper
>Event Category: None
>Event ID: 100
>Date: 6/26/2005
>Time: 1:54:11 AM
>User: N/A
>Computer: MERLOT
>Description:
>'0' is an invalid number of start up parameters. This service takes two
>start up parameters.
>Event Type: Error
>Event Source: MSSQLSERVER
>Event Category: (2)
>Event ID: 17055
>Date: 6/26/2005
>Time: 2:33:04 PM
>User: N/A
>Computer: MERLOT
>Description:
>19012 :
>SuperSocket Info: Bind failed on TCP port 1433.
>Data:
>0000: 44 4a 00 00 10 00 00 00 DJ.....
>0008: 07 00 00 00 4d 00 45 00 ...M.E.
>0010: 52 00 4c 00 4f 00 54 00 R.L.O.T.
>0018: 00 00 00 00 00 00 .....

MSSQLServerADHelper Service Error on Reboot

I am running SQL Server 2000 sp 4 on Win2K Server sp 4.
Recently, I started getting an MSSQLServerADHelper service error. I can't
rely pin it on anything, other than I had a big database with 2 tables (7 and
20 million records respectively). I ran out of disk space trying to index
one of the tables.
Ultimately, I detached the database, because it was too much for my system.
Since then I have completely removed then reinstalled SQL Server and I still
get the following errors. The MSSQLServerADHelper is set to automatic, which
I believe is the correct state since is supposedly launched as needed. I
have done some research and cannot track down an answer. Any ideas?
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 6/26/2005
Time: 1:54:11 AM
User: N/A
Computer: MERLOT
Description:
The MSSQLServerADHelper service terminated with service-specific error
3221225572.
Event Type: Error
Event Source: MSSQLServerADHelper
Event Category: None
Event ID: 100
Date: 6/26/2005
Time: 1:54:11 AM
User: N/A
Computer: MERLOT
Description:
'0' is an invalid number of start up parameters. This service takes two
start up parameters.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 6/26/2005
Time: 2:33:04 PM
User: N/A
Computer: MERLOT
Description:
19012 :
SuperSocket Info: Bind failed on TCP port 1433.
Data:
0000: 44 4a 00 00 10 00 00 00 DJ.....
0008: 07 00 00 00 4d 00 45 00 ...M.E.
0010: 52 00 4c 00 4f 00 54 00 R.L.O.T.
0018: 00 00 00 00 00 00 .....The MSSQLServerADHelper service should be set to manual. SQL
Server will start and stop the service itself when needed.
-Sue
On Sun, 26 Jun 2005 20:06:01 -0700, Jed
<Jed@.discussions.microsoft.com> wrote:
>I am running SQL Server 2000 sp 4 on Win2K Server sp 4.
>Recently, I started getting an MSSQLServerADHelper service error. I can't
>rely pin it on anything, other than I had a big database with 2 tables (7 and
>20 million records respectively). I ran out of disk space trying to index
>one of the tables.
>Ultimately, I detached the database, because it was too much for my system.
>Since then I have completely removed then reinstalled SQL Server and I still
>get the following errors. The MSSQLServerADHelper is set to automatic, which
>I believe is the correct state since is supposedly launched as needed. I
>have done some research and cannot track down an answer. Any ideas?
>Event Type: Error
>Event Source: Service Control Manager
>Event Category: None
>Event ID: 7024
>Date: 6/26/2005
>Time: 1:54:11 AM
>User: N/A
>Computer: MERLOT
>Description:
>The MSSQLServerADHelper service terminated with service-specific error
>3221225572.
>Event Type: Error
>Event Source: MSSQLServerADHelper
>Event Category: None
>Event ID: 100
>Date: 6/26/2005
>Time: 1:54:11 AM
>User: N/A
>Computer: MERLOT
>Description:
>'0' is an invalid number of start up parameters. This service takes two
>start up parameters.
>Event Type: Error
>Event Source: MSSQLSERVER
>Event Category: (2)
>Event ID: 17055
>Date: 6/26/2005
>Time: 2:33:04 PM
>User: N/A
>Computer: MERLOT
>Description:
>19012 :
>SuperSocket Info: Bind failed on TCP port 1433.
>Data:
>0000: 44 4a 00 00 10 00 00 00 DJ.....
>0008: 07 00 00 00 4d 00 45 00 ...M.E.
>0010: 52 00 4c 00 4f 00 54 00 R.L.O.T.
>0018: 00 00 00 00 00 00 .....

MSSQLServerADHelper is problem?

MSSQLServerADHelper isn't running and we can't get it to
start. System Event Log says "The MSSQLServerADHelper
service terminated with service-specific error 3221225572."
Any clues at all how to fix?

>--Original Message--
>That's SQL Server 2000 (sorry!) and Windows (Server)
>2000... it's almost like SQL isn't reading from or
>synchronizing with Active Directory properly, but we can
>find no other indicator that there is a problem.
>Again, any help is greatly appreciated!
>
>login
I
>a
the
>.
>That's normal behavior. You can't start the service
manually. It runs when requested to by SQL Server service or
Analysis Server service and then stops running when it's
completed it's work.
-Sue
On Thu, 25 Mar 2004 12:27:49 -0800, "Sandy Mach"
<anonymous@.discussions.microsoft.com> wrote:
>MSSQLServerADHelper isn't running and we can't get it to
>start. System Event Log says "The MSSQLServerADHelper
>service terminated with service-specific error 3221225572."
>Any clues at all how to fix?
>
>I
>the|||Well I was just trying to find something... is there any
way to see if it's working properly when it's supposed to
be working? My gut tells me this problem has to do with
AD somehow...
Can you think of any other options or ways to fix?

>--Original Message--
>That's normal behavior. You can't start the service
>manually. It runs when requested to by SQL Server service
or
>Analysis Server service and then stops running when it's
>completed it's work.
>-Sue
>On Thu, 25 Mar 2004 12:27:49 -0800, "Sandy Mach"
><anonymous@.discussions.microsoft.com> wrote:
>
3221225572."
can
if
in
adding
and
won't
then
>.
>|||Try revoking the login and then re-add it and see if you can
add the login as a database user.
Is the a problem for all logins or just one or a few? Is it
a problem with trying to add them to any database or just
one specific database?
-Sue
On Thu, 25 Mar 2004 13:31:55 -0800, "Sandy Mach"
<anonymous@.discussions.microsoft.com> wrote:
>Well I was just trying to find something... is there any
>way to see if it's working properly when it's supposed to
>be working? My gut tells me this problem has to do with
>AD somehow...
>Can you think of any other options or ways to fix?
>
>or
>3221225572."
>can
>if
>in
>adding
>and
>won't
>then|||Well, that did the job, but now I have to re-set the
group's permissions on each db, some of which contain over
200 tables... yikes. I guess I could look at the bright
side--I should only have to do this once!
Thanks for your help Sue.
Sandy

>--Original Message--
>Try revoking the login and then re-add it and see if you
can
>add the login as a database user.
>Is the a problem for all logins or just one or a few? Is
it
>a problem with trying to add them to any database or just
>one specific database?
>-Sue
>On Thu, 25 Mar 2004 13:31:55 -0800, "Sandy Mach"
><anonymous@.discussions.microsoft.com> wrote:
>
to
service
to
but
error
looked
use|||Your welcome...glad it's working now and thanks for posting
back.
-Sue
On Thu, 25 Mar 2004 14:22:19 -0800, "Sandy Mach"
<anonymous@.discussions.microsoft.com> wrote:
>Well, that did the job, but now I have to re-set the
>group's permissions on each db, some of which contain over
>200 tables... yikes. I guess I could look at the bright
>side--I should only have to do this once!
>Thanks for your help Sue.
>Sandy
>
>can
>it
>to
>service
>to
>but
>error
>looked
>use

MSSQLServer services

I'm running SQL 7.0 SP4 on NT 4.0. Each time i restart server, the sql
services just showing starting status for 2 minutes & gives error message "1
or more services failed to start". I have start the services manually. I've
tried to reinstall sql server but still the same. Kindly advice wht could be
the possible causes for this problem.
ThanksHi
What does the NT event log say? Any startup errors are logged there.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"chittana" <chittana@.discussions.microsoft.com> wrote in message
news:638D188B-8D35-4667-BFE3-0225BA31CB4B@.microsoft.com...
> I'm running SQL 7.0 SP4 on NT 4.0. Each time i restart server, the sql
> services just showing starting status for 2 minutes & gives error message
"1
> or more services failed to start". I have start the services manually.
I've
> tried to reinstall sql server but still the same. Kindly advice wht could
be
> the possible causes for this problem.
> Thanks|||Nope Mike. No startup error been logged in NT event log.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> What does the NT event log say? Any startup errors are logged there.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "chittana" <chittana@.discussions.microsoft.com> wrote in message
> news:638D188B-8D35-4667-BFE3-0225BA31CB4B@.microsoft.com...
> > I'm running SQL 7.0 SP4 on NT 4.0. Each time i restart server, the sql
> > services just showing starting status for 2 minutes & gives error message
> "1
> > or more services failed to start". I have start the services manually.
> I've
> > tried to reinstall sql server but still the same. Kindly advice wht could
> be
> > the possible causes for this problem.
> >
> > Thanks
>
>

MSSQLServer services

I'm running SQL 7.0 SP4 on NT 4.0. Each time i restart server, the sql
services just showing starting status for 2 minutes & gives error message "1
or more services failed to start". I have start the services manually. I've
tried to reinstall sql server but still the same. Kindly advice wht could be
the possible causes for this problem.
Thanks
Hi
What does the NT event log say? Any startup errors are logged there.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"chittana" <chittana@.discussions.microsoft.com> wrote in message
news:638D188B-8D35-4667-BFE3-0225BA31CB4B@.microsoft.com...
> I'm running SQL 7.0 SP4 on NT 4.0. Each time i restart server, the sql
> services just showing starting status for 2 minutes & gives error message
"1
> or more services failed to start". I have start the services manually.
I've
> tried to reinstall sql server but still the same. Kindly advice wht could
be
> the possible causes for this problem.
> Thanks
|||Nope Mike. No startup error been logged in NT event log.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> What does the NT event log say? Any startup errors are logged there.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "chittana" <chittana@.discussions.microsoft.com> wrote in message
> news:638D188B-8D35-4667-BFE3-0225BA31CB4B@.microsoft.com...
> "1
> I've
> be
>
>

MSSQLServer services

I'm running SQL 7.0 SP4 on NT 4.0. Each time i restart server, the sql
services just showing starting status for 2 minutes & gives error message "1
or more services failed to start". I have start the services manually. I've
tried to reinstall sql server but still the same. Kindly advice wht could be
the possible causes for this problem.
ThanksHi
What does the NT event log say? Any startup errors are logged there.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"chittana" <chittana@.discussions.microsoft.com> wrote in message
news:638D188B-8D35-4667-BFE3-0225BA31CB4B@.microsoft.com...
> I'm running SQL 7.0 SP4 on NT 4.0. Each time i restart server, the sql
> services just showing starting status for 2 minutes & gives error message
"1
> or more services failed to start". I have start the services manually.
I've
> tried to reinstall sql server but still the same. Kindly advice wht could
be
> the possible causes for this problem.
> Thanks|||Nope Mike. No startup error been logged in NT event log.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> What does the NT event log say? Any startup errors are logged there.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "chittana" <chittana@.discussions.microsoft.com> wrote in message
> news:638D188B-8D35-4667-BFE3-0225BA31CB4B@.microsoft.com...
> "1
> I've
> be
>
>