Hi Here Nasir

IN MY SYSTEM MICROSOFT SQL SERVER 2005 IS INSTALL ... WHEN I START MICROSOFT SQL SERVER 2005
ONE OPTION IS COME package Microsoft SQL Management Studio package' failed to load ..

SO I CANT ACESS MICROSOFT SQL SERVER 2005 ..
SO CAN U TELL ME WHATS A BROBLEM FROM THIS
--

Quote:

Originally Posted by nasir ghani

IN MY SYSTEM MICROSOFT SQL SERVER 2005 IS INSTALL ... WHEN I START MICROSOFT SQL SERVER 2005
ONE OPTION IS COME package Microsoft SQL Management Studio package' failed to load ..



SO I CANT ACESS MICROSOFT SQL SERVER 2005 ..
SO CAN U TELL ME WHATS A BROBLEM FROM THIS
--

Moved to SQL server forum.|||Does this link give you any hints?

hi friends i have one problem on crystal reports

when i execute crystal report in the other system through vb it is executing there and when i try to execute it is showing "0" bytes. may i know what would be the problem. thank u in advance.How did you execute it?
Did you call the reports that reside in other system?

Here's a weird one for all of you...

Hi,
Let me outline what I have and maybe someone will know how to react to what
is happening to my system.
Server: Windows2003 Standard
SQL: SQL 2005 Demo
and
SQL: SQL 2005 Processor Licensed (connecting via port 14330)
Additional Major S/W on that server: Sophos
We have run out the demo time frame and installed a 2nd instance of SQL 2005
on the same server only connecting on port 14330. I can connect to either
instance without any issues.
Problem: Sporadically (it seems to be very random) connections that are
"live" to the demo sql 2005 begin to "time out" when accessing the db for
just simple recordsets. It appears to start with one then two and then like
a wave suddenly nearly every pc hits a "time out" on a db access.
We shut down and reboot the server and all works fine again. Sometimes, if
we wait long enough, the "time outs" stop and everyone slowly gets back
connected and the issue goes away. But we obviously cannot rely on that.
This has happened 2 times in 1.5 days and we're fearful that it will just
keep happening. We installed SP1 on the new SQL 2005 instance.
Can anyone venture or hazard a guess as to what might be killing the
connection? We have looked to see if there was something happening at the
same time the time out's are occurring but find nothing.
HELP!
Thanks very much for any help,
Rich"RM" <rm@.rd.com> wrote in message
news:u9lPoWg0GHA.3476@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Let me outline what I have and maybe someone will know how to react to
> what is happening to my system.
> Server: Windows2003 Standard
> SQL: SQL 2005 Demo
> and
> SQL: SQL 2005 Processor Licensed (connecting via port 14330)
> Additional Major S/W on that server: Sophos
> We have run out the demo time frame and installed a 2nd instance of SQL
> 2005 on the same server only connecting on port 14330. I can connect to
> either instance without any issues.
> Problem: Sporadically (it seems to be very random) connections that are
> "live" to the demo sql 2005 begin to "time out" when accessing the db for
> just simple recordsets. It appears to start with one then two and then
> like a wave suddenly nearly every pc hits a "time out" on a db access.
> We shut down and reboot the server and all works fine again. Sometimes, if
> we wait long enough, the "time outs" stop and everyone slowly gets back
> connected and the issue goes away. But we obviously cannot rely on that.
> This has happened 2 times in 1.5 days and we're fearful that it will just
> keep happening. We installed SP1 on the new SQL 2005 instance.
> Can anyone venture or hazard a guess as to what might be killing the
> connection? We have looked to see if there was something happening at the
> same time the time out's are occurring but find nothing.
> HELP!
> Thanks very much for any help,
> Rich
>
I had this problem the other day (see post in
microsoft.public.sqlserver.msde). Although it was a post to the wrong group
as I was using 2005, not MSDE (2000). All of my clients timed out at 6am.
I put it down to the Virus Checker perhaps kicking in at that time and
slowing the server down to a crawl, but I find it hard to understand why it
would affect all clients like that, given that my default timeout is around
30 seconds. I didn't have this problem last night and my clients have been
happily doing their stuff for nearly 24 hours now. I asked our system admin
if there had been any network events kicking in around then and there were
none. SQL server showed no events occurred on the server and all my client
logs showed was an operation timeout error, for all of them. My system now
handles the timeout gracefully, as I don't maintain "always open"
connections - I just open a connection, execute an operation and then close
it again - so it's easier to recover from this kind of thing and "retry" the
operation.
Still I would be interested to know what might be causing this.|||Thanks for your contribution.
While we haven't definitively "found" the problem, I believe that we are on
the path to resolving it.
The issue, as I see it, is how we are managing our ado connection string and
all of the recordsets
used with that connection string. I suspect that what we are doing wrong is
not closing and then
destroying each recordset as it is no longer needed. We came from dao to ado
and access as a db
to sql 2005. So our source reflects the sloppy (easier) methods under dao to
the more strict ado
processes.
Not closing (and destroying) each recordset when it's no longer used and
letting the program exit
is, I believe, what is causing our timeout errors. We are going through
several hundred programs
to make those changes (thanks ado) and so far we've not seen a problem in
nearly 2 days, which
suggests that we're on the right path.
Anyway, thanks again for your comments and if I've suggested anything above
that might help you
I'm glad.
See ya,
R
"Robinson" <itoldyounottospamme@.nowmyinboxisfull.com> wrote in message
news:edosml$t1i$1$8300dec7@.news.demon.co.uk...
> "RM" <rm@.rd.com> wrote in message
> news:u9lPoWg0GHA.3476@.TK2MSFTNGP04.phx.gbl...
> I had this problem the other day (see post in
> microsoft.public.sqlserver.msde). Although it was a post to the wrong
> group as I was using 2005, not MSDE (2000). All of my clients timed out
> at 6am. I put it down to the Virus Checker perhaps kicking in at that time
> and slowing the server down to a crawl, but I find it hard to understand
> why it would affect all clients like that, given that my default timeout
> is around 30 seconds. I didn't have this problem last night and my
> clients have been happily doing their stuff for nearly 24 hours now. I
> asked our system admin if there had been any network events kicking in
> around then and there were none. SQL server showed no events occurred on
> the server and all my client logs showed was an operation timeout error,
> for all of them. My system now handles the timeout gracefully, as I don't
> maintain "always open" connections - I just open a connection, execute an
> operation and then close it again - so it's easier to recover from this
> kind of thing and "retry" the operation.
> Still I would be interested to know what might be causing this.
>
>
>
>
>

Here is the answer FOLKS and it WORKS!!!

I am trying to deploy a Database Project with Visual Studio 2005 and SQL Server 2005 Standard.

I import “System.IO” and have therefore set the permission levels to EXTERNAL_ACCESS.

I am receiving the same error message that many folks have received.

CREATE ASSEMBLY for assembly 'Images' failed because assembly 'Images' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.

The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem. Images.

My CLR access is “on”

I have tried

1) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to [Builtin\Administrators].

2) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to “My Windows Authentication ID”.

3) Run ALTER DATABASE MYDATABASE SET TRUSTWORTHY ON

4) In Visual Studio .NET 2005 Set the permission levels to ‘external’

5) TriedBuiltin\Administrators and my SQL Server Windows Authenticated Login ID for the ASSEMBLY OWNER.

I can compile BUT NOT DEPLOY

Any help would be greatly appreciated.

Regards Steve

Who is the owner of the database ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Thanks for the reply.

I am running with Windows Authentication at the present moment , under the ID Administrator1 (running under windows Vista.(still would like to get reporting services to run ) . I did read the article on "Opening up the Administrator ID in Vista and installing SQL Server 2005 under that ID."

Re my current problem....

I managed to get it to deploy. I assigned Administrator1 as an "owner" of the database and gave the ID security administrator rights. I did the same with Builtin/Administrator.

I found something very interesting. I am able to deploy CLR stored precedures from the Visual Studio environment but not CLR functions. (EXTERNAL ACCESS) In order to deply functions I must compile the Dll and create SQL to CREATE the ASSEMBLYfrom within the SQL Server Environment. Any ideas why.?

Once again thanks for the help? Very much appreciated.

Steve

|||

OK Folks this took me about 3 hours to do BUT I have resolved the problem and it WORKS!!!

BTW I am using SQL Server 2005 Standard and Visual Studio.NET Enterprise

First create a Strong key as described in“The secret of strong naming”

DO NOTDODELAY SIGNING !!!!!!

http://www.ondotnet.com/pub/a/dotnet/2003/04/28/strongnaming.html

Sn – k MyKeyFile.snk

FIND OUT WHERE THE SNK FILE WAS COMPILED.

Go into VS2005 .NET and under project / properties in the signing page click the Sign the Assembly check box and browse to find MyKeyFile.snk

The database page

Mark the assembly for external access and leave the owner blank

Now we must go to the article

http://sqljunkies.com/WebLog/ktegels/archive/2006/08/14/safetydancelite.aspx

The text below comes from this article....

A kinder, lighter Safety Dance for the SQLCLR

A few months ago, I published a step-by-step procedure for using certificate to sign an assembly so an external access or unsafe assembly could be run without having to set the hosting database to trustworthy. The major problem with that process was its weight: a lot of steps and some still requirements in terms of certificate management. Turns out that yes, there is an easier way to make it work.

Part one -- preparing the master database

1. As needed, start a series of queries against the MASTER database.

2. Create the target database (meaning execute a CREATE DATABASE query).

3. Code and compile the assembly you want to deploy. Make sure you sign the assembly with a strong name key file. This can be done with a PFX file generated by Visual Studio.

4. Create an asymmetric key from from the compiled assembly using a statement like this: create asymmetric key <key_name> from executable file = '<path_to_dll_file>'

USE Master

GO

create asymmetric key imageskeyFile from executable file = 'C:\YEAR 2007\Images\Images\bin\Debug\Images.dll'

5. Create a login based on that asymmetric key using a statement like this: create login <login_name> from asymmetric key <key_name>

Use Master

Go

create login ImageMaker from asymmetric key imageskeyFile

6. Grant that login the right to create either or both an unsafe or external access assembly (as needed) using: grant unsafe assembly to <login_name>

Use Master

Go

grant EXTERNAL ACCESS assembly to ImageMaker

Part two -- preparing the hosting database

1. As needed, start a series of queries against the desired user database.

2. Create a user in that database mapped to the login created in part, step 5. (e.g. create user <db_user> from login <login_name>)

3. Give that user the right to catalog an assembly, e.g.: grant create assembly to <db_user>

4. Catalog the desired assembly using the now trusted asymmetric key with a statement like: create assembly <assembly_name> authorization <db_user> from '<path_to_dll_file>' with permission_set = unsafe or external access as shown below

SET QUOTED_IDENTIFIER OFF

USE YEAR2007

GO

CREATE ASSEMBLY Images

AUTHORIZATION ImageMaker

FROM "C:\YEAR 2007\Images\Images\bin\Debug\Images.dll"

WITH PERMISSION_SET = EXTERNAL_ACCESS

GO

HELP: Transaction Log Backup Files

Hello world,
I am having some diffulty finding the method to truncate my MSSQL
transaction logs files.
I am successful backing up the system and user tables and the logs ( using
a maintianace wizard, But there isnt an option for logfile truncate'
1) Do Maintance Wizards truncate the log files automatically?
2) WHERE can i find this option?
RussTruncating the transaction log from Query Analyzer:
BACKUP LOG database_name WITH NO_LOG or TRUNCATE_ONLY
This statement will never backup log actualy but truncate inactive portion of log file.
It's better idea to let the SQL Server do all that through maintenance plans and regular log backup bacause after every log backup SQL will truncate inactive portion of log file.
Also you should check db recovery options!
More info BOL - "Truncating the Transaction Log"
hope it helps|||Why would you want to truncate the log file regularly? It only cost performance etc to truncate it
and then having it autogrow back...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Stephen Driscoll" <info@.fnc.co.uk> wrote in message news:%23bjBigKwDHA.2460@.TK2MSFTNGP10.phx.gbl...
> Hello world,
> I am having some diffulty finding the method to truncate my MSSQL
> transaction logs files.
> I am successful backing up the system and user tables and the logs ( using
> a maintianace wizard, But there isnt an option for logfile truncate'
> 1) Do Maintance Wizards truncate the log files automatically?
> 2) WHERE can i find this option?
> Russ
>

Help: SQL Server 2K draginng system down?

Hello, apologies if this is the wrong place to post this.
We've got a dev server which is supporting multiple services for
testing. It is primarily doing AD, DNS, and recently SQL Server 2000.
Although I cannot be certain, I believe since SQL Server 2000 was
installed these symptoms begain happening. The server it resides on
after a period of several days to a week becomes unresponsive. File
sharing throughput becomes slowed down, terminal services ceases
accepting connections, the server itself when logged into at the console
is very sluggish with responding to input. The mouse(USB) pointer is
lagged even. OS is Win2K Server.
The server is not under high load, as it is a development box but we are
concerned it may happen elsewhere if deployed.
Has anyone else had this happen and have they found a solution to it?
Thanks
JasonFirst question is do you have service pack 3 on the box? If not you may be
the recipient of the slammer.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Jason Coleman" <jcoleman2004@.comcast.net> wrote in message
news:MPG.19e8c1841bd78037989683@.news.microsoft.com...
> Hello, apologies if this is the wrong place to post this.
> We've got a dev server which is supporting multiple services for
> testing. It is primarily doing AD, DNS, and recently SQL Server 2000.
> Although I cannot be certain, I believe since SQL Server 2000 was
> installed these symptoms begain happening. The server it resides on
> after a period of several days to a week becomes unresponsive. File
> sharing throughput becomes slowed down, terminal services ceases
> accepting connections, the server itself when logged into at the console
> is very sluggish with responding to input. The mouse(USB) pointer is
> lagged even. OS is Win2K Server.
> The server is not under high load, as it is a development box but we are
> concerned it may happen elsewhere if deployed.
> Has anyone else had this happen and have they found a solution to it?
> Thanks
> Jason|||Yeah make sure you have SP3... I've got a small server at home, Active
Directory, Domain Controller, DNS, DHCP, Internet Portal, SQL Server, you
name it... - all fine for a limited number of users.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jason Coleman" <jcoleman2004@.comcast.net> wrote in message
news:MPG.19e8c1841bd78037989683@.news.microsoft.com...
> Hello, apologies if this is the wrong place to post this.
> We've got a dev server which is supporting multiple services for
> testing. It is primarily doing AD, DNS, and recently SQL Server 2000.
> Although I cannot be certain, I believe since SQL Server 2000 was
> installed these symptoms begain happening. The server it resides on
> after a period of several days to a week becomes unresponsive. File
> sharing throughput becomes slowed down, terminal services ceases
> accepting connections, the server itself when logged into at the console
> is very sluggish with responding to input. The mouse(USB) pointer is
> lagged even. OS is Win2K Server.
> The server is not under high load, as it is a development box but we are
> concerned it may happen elsewhere if deployed.
> Has anyone else had this happen and have they found a solution to it?
> Thanks
> Jason|||In article <#8QuHZriDHA.1940@.TK2MSFTNGP09.phx.gbl>,
rayhigdon@.higdonconsulting.com says...
> First question is do you have service pack 3 on the box? If not you may be
> the recipient of the slammer.
>
Sorry.
Yep, Windows is patched to SP4, SQL Server is patched to 3. Of course I
don't have any solid evidence that SQL Server is actually what caused
the problem either. I just suspect it is.
Thanks,
Jason

HELP: SQL Server 2000 will not start after full system restore

Hi,
I just did a full system backup, memory and hard disk reconfiguration,
and a full system restore of my SQL Server 2000 machine.
SQL Server refuses to start as a service giving the error message:
"could not start the MSSQLSERVER service on the local computer"
"Error 3: the system cannot find the path specified"
It however starts from the command prompt by simply executing:
"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
Any assistance welcome!Hi
Check what flags you have specified when you start it as a service.
John
"kim@.kasnet.com" wrote:

> Hi,
> I just did a full system backup, memory and hard disk reconfiguration,
> and a full system restore of my SQL Server 2000 machine.
> SQL Server refuses to start as a service giving the error message:
> "could not start the MSSQLSERVER service on the local computer"
> "Error 3: the system cannot find the path specified"
> It however starts from the command prompt by simply executing:
> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
> Any assistance welcome!
>|||Hi John,
There are no flags specified as startup parameters... is there
anything else I should check?
Kim
On Tue, 25 Apr 2006 09:48:01 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
[vbcol=seagreen]
>Hi
>Check what flags you have specified when you start it as a service.
>John
>"kim@.kasnet.com" wrote:
>|||Hi
Check the registry
key& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSSQLServer\MSSQLServer\Parame
ters]
You will find entries like:
"SQLArg0"="-dC:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\master.md
f"
"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL\\log\\ERRORLOG"
"SQLArg2"="-lC:\\Program Files\\Microsoft SQL
Server\\MSSQL\\data\\mastlog.ldf"
Check that these files/directories exists. Other possible parameters are
detailed in Books Online topic "sqlservr Application"
John
"velliott@.hdhopwood.com" wrote:

> Hi John,
> There are no flags specified as startup parameters... is there
> anything else I should check?
> Kim
> On Tue, 25 Apr 2006 09:48:01 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
>
>|||I checked the registry entries and the only difference is that there
are single backslashes in the directory path. All other path entries
in the registry are represented the same way.
If this is the problem, my logic tells me that the SQL Server should
not start from the command prompt anyway!
Is there a way to get the service startup process to indicate which
specific path it cannot find?
Kim
On Thu, 27 Apr 2006 00:04:01 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
[vbcol=seagreen]
>Hi
>Check the registry
>key& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSSQLServer\MSSQLServer\Param
eters]
>You will find entries like:
>"SQLArg0"="-dC:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\master.m
df"
>"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL\\log\\ERRORLOG"
>"SQLArg2"="-lC:\\Program Files\\Microsoft SQL
>Server\\MSSQL\\data\\mastlog.ldf"
>Check that these files/directories exists. Other possible parameters are
>detailed in Books Online topic "sqlservr Application"
>John
>
>"velliott@.hdhopwood.com" wrote:
>|||Hi
The double backslashes are from an export of the registry check other
registry values such as
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSSQLServer\Setup]
John
"velliott@.hdhopwood.com" wrote:

> I checked the registry entries and the only difference is that there
> are single backslashes in the directory path. All other path entries
> in the registry are represented the same way.
> If this is the problem, my logic tells me that the SQL Server should
> not start from the command prompt anyway!
> Is there a way to get the service startup process to indicate which
> specific path it cannot find?
> Kim
> On Thu, 27 Apr 2006 00:04:01 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
>
>|||John,
The registry keys in error were:
& #91;HKEY_LOCAL_MACHINE\SYSTEM\CurrentCon
trolSet\Services\MSSQLServer\ImageP
ath]
and
& #91;HKEY_LOCAL_MACHINE\SYSTEM\CurrentCon
trolSet\Services\MSSQLServer\Perfor
mance\Library]
Both contained the DOS version of the path (eg.
C:\Program~1\Micro~2\...) to the relevant binaries.
I expanded the paths and the service started without error!
Many thanks for your patience and invaluable assistance!!!
Kim
On Thu, 27 Apr 2006 09:50:02 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
[vbcol=seagreen]
>Hi
>The double backslashes are from an export of the registry check other
>registry values such as
>& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSSQLServer\Setup]
>John
>"velliott@.hdhopwood.com" wrote:
>

HELP: SQL Server 2000 will not start after full system restore

Hi,
I just did a full system backup, memory and hard disk reconfiguration,
and a full system restore of my SQL Server 2000 machine.
SQL Server refuses to start as a service giving the error message:
"could not start the MSSQLSERVER service on the local computer"
"Error 3: the system cannot find the path specified"
It however starts from the command prompt by simply executing:
"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
Any assistance welcome!Hi
Check what flags you have specified when you start it as a service.
John
"kim@.kasnet.com" wrote:
> Hi,
> I just did a full system backup, memory and hard disk reconfiguration,
> and a full system restore of my SQL Server 2000 machine.
> SQL Server refuses to start as a service giving the error message:
> "could not start the MSSQLSERVER service on the local computer"
> "Error 3: the system cannot find the path specified"
> It however starts from the command prompt by simply executing:
> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
> Any assistance welcome!
>|||Hi John,
There are no flags specified as startup parameters... is there
anything else I should check?
Kim
On Tue, 25 Apr 2006 09:48:01 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>Check what flags you have specified when you start it as a service.
>John
>"kim@.kasnet.com" wrote:
>> Hi,
>> I just did a full system backup, memory and hard disk reconfiguration,
>> and a full system restore of my SQL Server 2000 machine.
>> SQL Server refuses to start as a service giving the error message:
>> "could not start the MSSQLSERVER service on the local computer"
>> "Error 3: the system cannot find the path specified"
>> It however starts from the command prompt by simply executing:
>> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
>> Any assistance welcome!|||Hi
Check the registry
key[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters]
You will find entries like:
"SQLArg0"="-dC:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\master.mdf"
"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL\\log\\ERRORLOG"
"SQLArg2"="-lC:\\Program Files\\Microsoft SQL
Server\\MSSQL\\data\\mastlog.ldf"
Check that these files/directories exists. Other possible parameters are
detailed in Books Online topic "sqlservr Application"
John
"velliott@.hdhopwood.com" wrote:
> Hi John,
> There are no flags specified as startup parameters... is there
> anything else I should check?
> Kim
> On Tue, 25 Apr 2006 09:48:01 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> >Hi
> >
> >Check what flags you have specified when you start it as a service.
> >
> >John
> >
> >"kim@.kasnet.com" wrote:
> >
> >> Hi,
> >>
> >> I just did a full system backup, memory and hard disk reconfiguration,
> >> and a full system restore of my SQL Server 2000 machine.
> >>
> >> SQL Server refuses to start as a service giving the error message:
> >>
> >> "could not start the MSSQLSERVER service on the local computer"
> >> "Error 3: the system cannot find the path specified"
> >>
> >> It however starts from the command prompt by simply executing:
> >> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
> >>
> >> Any assistance welcome!
> >>
>|||I checked the registry entries and the only difference is that there
are single backslashes in the directory path. All other path entries
in the registry are represented the same way.
If this is the problem, my logic tells me that the SQL Server should
not start from the command prompt anyway!
Is there a way to get the service startup process to indicate which
specific path it cannot find?
Kim
On Thu, 27 Apr 2006 00:04:01 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>Check the registry
>key[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters]
>You will find entries like:
>"SQLArg0"="-dC:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\master.mdf"
>"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL\\log\\ERRORLOG"
>"SQLArg2"="-lC:\\Program Files\\Microsoft SQL
>Server\\MSSQL\\data\\mastlog.ldf"
>Check that these files/directories exists. Other possible parameters are
>detailed in Books Online topic "sqlservr Application"
>John
>
>"velliott@.hdhopwood.com" wrote:
>> Hi John,
>> There are no flags specified as startup parameters... is there
>> anything else I should check?
>> Kim
>> On Tue, 25 Apr 2006 09:48:01 -0700, John Bell
>> <jbellnewsposts@.hotmail.com> wrote:
>> >Hi
>> >
>> >Check what flags you have specified when you start it as a service.
>> >
>> >John
>> >
>> >"kim@.kasnet.com" wrote:
>> >
>> >> Hi,
>> >>
>> >> I just did a full system backup, memory and hard disk reconfiguration,
>> >> and a full system restore of my SQL Server 2000 machine.
>> >>
>> >> SQL Server refuses to start as a service giving the error message:
>> >>
>> >> "could not start the MSSQLSERVER service on the local computer"
>> >> "Error 3: the system cannot find the path specified"
>> >>
>> >> It however starts from the command prompt by simply executing:
>> >> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
>> >>
>> >> Any assistance welcome!
>> >>
>>|||Hi
The double backslashes are from an export of the registry check other
registry values such as
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup]
John
"velliott@.hdhopwood.com" wrote:
> I checked the registry entries and the only difference is that there
> are single backslashes in the directory path. All other path entries
> in the registry are represented the same way.
> If this is the problem, my logic tells me that the SQL Server should
> not start from the command prompt anyway!
> Is there a way to get the service startup process to indicate which
> specific path it cannot find?
> Kim
> On Thu, 27 Apr 2006 00:04:01 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> >Hi
> >
> >Check the registry
> >key[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters]
> >
> >You will find entries like:
> >"SQLArg0"="-dC:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\master.mdf"
> >"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL\\log\\ERRORLOG"
> >"SQLArg2"="-lC:\\Program Files\\Microsoft SQL
> >Server\\MSSQL\\data\\mastlog.ldf"
> >
> >Check that these files/directories exists. Other possible parameters are
> >detailed in Books Online topic "sqlservr Application"
> >
> >John
> >
> >
> >"velliott@.hdhopwood.com" wrote:
> >
> >> Hi John,
> >>
> >> There are no flags specified as startup parameters... is there
> >> anything else I should check?
> >>
> >> Kim
> >>
> >> On Tue, 25 Apr 2006 09:48:01 -0700, John Bell
> >> <jbellnewsposts@.hotmail.com> wrote:
> >>
> >> >Hi
> >> >
> >> >Check what flags you have specified when you start it as a service.
> >> >
> >> >John
> >> >
> >> >"kim@.kasnet.com" wrote:
> >> >
> >> >> Hi,
> >> >>
> >> >> I just did a full system backup, memory and hard disk reconfiguration,
> >> >> and a full system restore of my SQL Server 2000 machine.
> >> >>
> >> >> SQL Server refuses to start as a service giving the error message:
> >> >>
> >> >> "could not start the MSSQLSERVER service on the local computer"
> >> >> "Error 3: the system cannot find the path specified"
> >> >>
> >> >> It however starts from the command prompt by simply executing:
> >> >> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
> >> >>
> >> >> Any assistance welcome!
> >> >>
> >>
> >>
>|||John,
The registry keys in error were:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\ImagePath]
and
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\Performance\Library]
Both contained the DOS version of the path (eg.
C:\Program~1\Micro~2\...) to the relevant binaries.
I expanded the paths and the service started without error!
Many thanks for your patience and invaluable assistance!!!
Kim
On Thu, 27 Apr 2006 09:50:02 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>The double backslashes are from an export of the registry check other
>registry values such as
>[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup]
>John
>"velliott@.hdhopwood.com" wrote:
>> I checked the registry entries and the only difference is that there
>> are single backslashes in the directory path. All other path entries
>> in the registry are represented the same way.
>> If this is the problem, my logic tells me that the SQL Server should
>> not start from the command prompt anyway!
>> Is there a way to get the service startup process to indicate which
>> specific path it cannot find?
>> Kim
>> On Thu, 27 Apr 2006 00:04:01 -0700, John Bell
>> <jbellnewsposts@.hotmail.com> wrote:
>> >Hi
>> >
>> >Check the registry
>> >key[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters]
>> >
>> >You will find entries like:
>> >"SQLArg0"="-dC:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\master.mdf"
>> >"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL\\log\\ERRORLOG"
>> >"SQLArg2"="-lC:\\Program Files\\Microsoft SQL
>> >Server\\MSSQL\\data\\mastlog.ldf"
>> >
>> >Check that these files/directories exists. Other possible parameters are
>> >detailed in Books Online topic "sqlservr Application"
>> >
>> >John
>> >
>> >
>> >"velliott@.hdhopwood.com" wrote:
>> >
>> >> Hi John,
>> >>
>> >> There are no flags specified as startup parameters... is there
>> >> anything else I should check?
>> >>
>> >> Kim
>> >>
>> >> On Tue, 25 Apr 2006 09:48:01 -0700, John Bell
>> >> <jbellnewsposts@.hotmail.com> wrote:
>> >>
>> >> >Hi
>> >> >
>> >> >Check what flags you have specified when you start it as a service.
>> >> >
>> >> >John
>> >> >
>> >> >"kim@.kasnet.com" wrote:
>> >> >
>> >> >> Hi,
>> >> >>
>> >> >> I just did a full system backup, memory and hard disk reconfiguration,
>> >> >> and a full system restore of my SQL Server 2000 machine.
>> >> >>
>> >> >> SQL Server refuses to start as a service giving the error message:
>> >> >>
>> >> >> "could not start the MSSQLSERVER service on the local computer"
>> >> >> "Error 3: the system cannot find the path specified"
>> >> >>
>> >> >> It however starts from the command prompt by simply executing:
>> >> >> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
>> >> >>
>> >> >> Any assistance welcome!
>> >> >>
>> >>
>> >>
>>|||<kim@.kasnet.com> wrote in message
news:l0fs421805aj0doegq6e26ft224ll3mj99@.4ax.com...
> Hi,
> I just did a full system backup, memory and hard disk reconfiguration,
> and a full system restore of my SQL Server 2000 machine.
> SQL Server refuses to start as a service giving the error message:
> "could not start the MSSQLSERVER service on the local computer"
> "Error 3: the system cannot find the path specified"
> It however starts from the command prompt by simply executing:
> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
> Any assistance welcome!
How did you do your system restore?
We had a situation once where files were robocopied from old hardware to
new. The problem with that was that some files took longer to copy than
others, and this messed up the 8.3 names of those blasted c:\Program
Files\Microsoft ... directories. For example, the SQL Server directory was
MICROS~4 on the old box, but MICROS~2 on the new box. We didn't notice this
until we started getting weird errors like you describe. When we checked
the registry, we found that many keys referenced those directories by 8.3
name. Fixing it was a headache.|||Hi Karen
The OP has found that the registry enties
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\ImagePath]
and
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\Performance\Library]
had 8.3 files names in them, change them to the correct values solved her
problem.
John
"Karen Collins" wrote:
> <kim@.kasnet.com> wrote in message
> news:l0fs421805aj0doegq6e26ft224ll3mj99@.4ax.com...
> > Hi,
> >
> > I just did a full system backup, memory and hard disk reconfiguration,
> > and a full system restore of my SQL Server 2000 machine.
> >
> > SQL Server refuses to start as a service giving the error message:
> >
> > "could not start the MSSQLSERVER service on the local computer"
> > "Error 3: the system cannot find the path specified"
> >
> > It however starts from the command prompt by simply executing:
> > "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
> >
> > Any assistance welcome!
> How did you do your system restore?
> We had a situation once where files were robocopied from old hardware to
> new. The problem with that was that some files took longer to copy than
> others, and this messed up the 8.3 names of those blasted c:\Program
> Files\Microsoft ... directories. For example, the SQL Server directory was
> MICROS~4 on the old box, but MICROS~2 on the new box. We didn't notice this
> until we started getting weird errors like you describe. When we checked
> the registry, we found that many keys referenced those directories by 8.3
> name. Fixing it was a headache.
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86E6F2AF-BD89-46C2-88BB-1329784382E3@.microsoft.com...
> Hi Karen
> The OP has found that the registry enties
> [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\ImagePath]
> and
> [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\Performance\Library]
> had 8.3 files names in them, change them to the correct values solved her
> problem.
Thanks for the update. Glad to hear she was able to fix the problem.

HELP: How to delete a user from Reporting Services?

I have the following problem.
The system setup is as follows.
IIS and sqlserver are on the same server and I'm using windows integrated
security.
I initially created a windows login user (userA).
I then in the reporting services(RS) admin area, set the security permissions
for a directory(Dir1). The permissions were, userA is a member of the
browser role and the directory was set to the browser role.
Everything worked as it should at that point.
I then wanted to delete userA from the server. So i delete userA from the
'local users and groups' admin area in the control panel on the server.
This is when I started to have problems.
When I go back into RS to administor permissions, I tried to ammend the
'browser role' permissions for Dir1. When I try to save the changes the
following error is displayed.
'The user or group name 'userA' is not recognized. (rsUnknownUserName) '
Its seems that userA is still registered in RS.
Logically I understand why, I've only deleted userA from windows, not from RS.
My question is , How do I completely delete userA from RS.
I noticed a users table in the RS database but there are numerous other
tables which could have entries refering to userA.
Is there a way to be sure all references to userA are deleted.
The only way I could get round this was to re-generate userA as a windows
user and disable it.
This is not an ideal solution as I do not wish to have numerous disabled
windows login users.
Any suggestions would be gratefully received.
--
Message posted via http://www.sqlmonster.comSounds like you would have to delete userA from the folders on RS, then
delete the user profile. If possible, use a security group instead of
assigning individually.
"Stewart Waddell via SQLMonster.com" wrote:
> I have the following problem.
> The system setup is as follows.
> IIS and sqlserver are on the same server and I'm using windows integrated
> security.
> I initially created a windows login user (userA).
> I then in the reporting services(RS) admin area, set the security permissions
> for a directory(Dir1). The permissions were, userA is a member of the
> browser role and the directory was set to the browser role.
> Everything worked as it should at that point.
> I then wanted to delete userA from the server. So i delete userA from the
> 'local users and groups' admin area in the control panel on the server.
> This is when I started to have problems.
> When I go back into RS to administor permissions, I tried to ammend the
> 'browser role' permissions for Dir1. When I try to save the changes the
> following error is displayed.
> 'The user or group name 'userA' is not recognized. (rsUnknownUserName) '
> Its seems that userA is still registered in RS.
> Logically I understand why, I've only deleted userA from windows, not from RS.
>
> My question is , How do I completely delete userA from RS.
> I noticed a users table in the RS database but there are numerous other
> tables which could have entries refering to userA.
> Is there a way to be sure all references to userA are deleted.
> The only way I could get round this was to re-generate userA as a windows
> user and disable it.
> This is not an ideal solution as I do not wish to have numerous disabled
> windows login users.
> Any suggestions would be gratefully received.
>
> --
> Message posted via http://www.sqlmonster.com
>|||I almost never assign individual users to a role. I create a local group and
then add users (in my case domain users) to the group. I then assign a role
to that local group.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stewart Waddell via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:53C43C9C93255@.SQLMonster.com...
>I have the following problem.
> The system setup is as follows.
> IIS and sqlserver are on the same server and I'm using windows integrated
> security.
> I initially created a windows login user (userA).
> I then in the reporting services(RS) admin area, set the security
> permissions
> for a directory(Dir1). The permissions were, userA is a member of the
> browser role and the directory was set to the browser role.
> Everything worked as it should at that point.
> I then wanted to delete userA from the server. So i delete userA from the
> 'local users and groups' admin area in the control panel on the server.
> This is when I started to have problems.
> When I go back into RS to administor permissions, I tried to ammend the
> 'browser role' permissions for Dir1. When I try to save the changes the
> following error is displayed.
> 'The user or group name 'userA' is not recognized. (rsUnknownUserName) '
> Its seems that userA is still registered in RS.
> Logically I understand why, I've only deleted userA from windows, not from
> RS.
>
> My question is , How do I completely delete userA from RS.
> I noticed a users table in the RS database but there are numerous other
> tables which could have entries refering to userA.
> Is there a way to be sure all references to userA are deleted.
> The only way I could get round this was to re-generate userA as a windows
> user and disable it.
> This is not an ideal solution as I do not wish to have numerous disabled
> windows login users.
> Any suggestions would be gratefully received.
>
> --
> Message posted via http://www.sqlmonster.com

HELP: How to avoid ridiculous system resource issues when connecting to a huge Excel file

Hi all,

I have a 400MB Excel file that I consume from another automated process (don't ask). I copy this file down locally to my server, and I am attempting to create an SSIS package that points to this file via a connection manager. My computer starts gobbling up massive amounts of memory (devenv.exe gets up to about 800MB or so, then drops back down to 100MB) even when I attempt to rename the connection in the connection managers tab.

I have set all BypassPrepare to TRUE and ValidateExternalMetadata properties to FALSE, and still it can take up to 3 to 6 minutes for BI Dev Studio to respond. My specs:

Intel Centrino Duo 2.00 GHz

2GB RAM

XP Pro SP2

There MUST be a way for me to work effectively on a file of this size. Please help! Thanks much for any assistance.

Sincerely,

Brian Pulliam

Ok, so it sounds like it wants to read the entire file, a nice feature, and probably unavoidable.

Personally I would be developing and testing against a smaller file that is easier to work with, and easier to manipulate for test cases. Then when the bulk of the dev is ready run a test with the real file.

You may think this is a big issue, and whilst it would be nicer if it behaved better, what if this was a SQL load, woud you expect to dev against a 100GB database? Whilst the metadata would be OK, the simple running of a test would be impractical, so I'd still go with smaller test data during development.

|||

Just one more idea to try...

If you are using "Excel Source" try to use "OLEDB Source" instead and specify Microsoft Jet 4.0 OLE DB Provider.

If you are using Excel 2007, you should use the new ACE provider.

Regards,

Yitzhak

|||

In case if you don't have MS Office 2007 installed on your machine,

here is the link to donwload the connectivity components:

2007 Office System Driver: Data Connectivity Components.

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=ja

Regards,

Yitzhak

|||

Thanks much for the reply Darren. I agree that a small subset of data would be great to test with. Actually I would dev against a 100GB database, although I would not pull all the data in my tests.

But your analogy to running a test on a 100GB DB is not very applicable. Firstly, I am not loading an entire DB. My Excel connection is pointing at one worksheet in an Excel 2003 file. And I have not even tried executing this package yet, I am nowhere near that far. Simply renaming the connection to the Excel file takes about 5 minutes before BI Developer Studio is responsive again. Also note that when connecting to a 100GB database, BI does not attempt to load the entire data set in that scenario. Maybe it has something to do with using an Excel file, that SSIS goes all paranoid and wants to look at the whole dang file.

Do you think if I set up a linked server to the Excel file in Management Studio that it might not have this problem? Maybe that would fake it into trusting the data source enough to not to the validation load. I attempted to do this on Friday but did not have much luck. Has anyone done this for an Excel 11.0 workbook?

Thanks again,

Brian

HELP..Installed production system slows over time

We have a SQL Server 2000 transactional business production system that is
installed at 20 different locations and has been running very well in all
but 1 location that was installed over the weekend. The hardware setup
"seems" to be rock solid according to the MS SQL Server performance
guidelines.
It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
Windows Server 2003 standard with SP1. The machine is dedicated to SQL
Server, the only other database being a Veritas backup DB. There are 4 gb of
400 mhz RAM.
The Data files are located on a Raid 5 high speed disk array and the
Transaction log is on a Raid 1 mirrored pair. The server is connected to the
network by a Broadcom NetXtreme Gigab: Network controller configured for
100Mb full-duplex link.
Once again let me state that this same system is installed on much less
capable hardware at other locations with up to 30 users accessing it via a
VB Client application running on the workstations. Those other locations are
not experiencing the problem.
Now, the differences...
This location has 4 time as much data as the other locations and 40 client
workstations. One of the tables has 2.2 million records (the Account Detail
table which tracks all activity to Invoices) and the other 13 tables have
record counts that vary but mostly in the 100,000 to 600,000 range. The
system data was converted from a DOS DBF system (on a less capable server)
that held these very same records and was running slowly but did not time
out. We ran multiple reports after the data migration and everything seems
to have come over as expected. The same data migration process has been run
at all the other installations without problem.
Here is an example of what happens. When the Server has been freshly
rebooted a simple query on the largest table takes about 3 seconds. After
the 40 clients run the system for about 2 hours the system gradually slows
to a crawl. When I try to run the same test query it can take up to 3
minutes.
Here is the strange part. Looking at the Task Manager, the Processors are
barely taxed and there is 2GB of free RAM. I don't know how to monitor the
disk activity. This is where it gets stranger. After all users have logged
off, the system STILL runs dog slow using the same test query, BUT after
about 2 hours it is back up to good performance. I am puzzled. It is as if
the Transaction queue is getting backed up, but resolves itself after time.
Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
reboot seems to clear the problem almost immediately.
Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
Thanks for your help...John,
Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
sp_blockcnt.
Also, take a look at:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
HTH
Jerry
"John Kotuby" <jkotuby@.snet.net> wrote in message
news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb
> of 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to
> the network by a Broadcom NetXtreme Gigab: Network controller configured
> for 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations
> are not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account
> Detail table which tracks all activity to Invoices) and the other 13
> tables have record counts that vary but mostly in the 100,000 to 600,000
> range. The system data was converted from a DOS DBF system (on a less
> capable server) that held these very same records and was running slowly
> but did not time out. We ran multiple reports after the data migration and
> everything seems to have come over as expected. The same data migration
> process has been run at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after
> time. Or is it maybe the Disk arrays having trouble synchronizing? But, a
> Server reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh
> in.
> Thanks for your help...
>
>|||John Kotuby wrote:
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb of
> 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to the
> network by a Broadcom NetXtreme Gigab: Network controller configured for
> 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations are
> not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account Detail
> table which tracks all activity to Invoices) and the other 13 tables have
> record counts that vary but mostly in the 100,000 to 600,000 range. The
> system data was converted from a DOS DBF system (on a less capable server)
> that held these very same records and was running slowly but did not time
> out. We ran multiple reports after the data migration and everything seems
> to have come over as expected. The same data migration process has been run
> at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
> Thanks for your help...|||Your problem is an interesting one. A question I would have relating to
it has to do with the outcome of your migration data validation. You
did not elaborate on the process utilized which would lead me to
suspect it may have been quite an abbreviated validation. Can you be
quite certain that stray charactors did not populate some table space
along with valid data? I am currently doing a small bit of research
around validation methodoligy. Would be quite interested in learning
more about the process you empoloyed for this.|||We ran Invoice balance reports for every Company (Division) in the original
DOS system and again after the data migration. All companies balanced before
and after the migration to the penny. The report output was identical for 5
years of data.
<jeff.livingston@.philips.com> wrote in message
news:1129655929.952358.319620@.f14g2000cwb.googlegroups.com...
> Your problem is an interesting one. A question I would have relating to
> it has to do with the outcome of your migration data validation. You
> did not elaborate on the process utilized which would lead me to
> suspect it may have been quite an abbreviated validation. Can you be
> quite certain that stray charactors did not populate some table space
> along with valid data? I am currently doing a small bit of research
> around validation methodoligy. Would be quite interested in learning
> more about the process you empoloyed for this.
>|||Thanks Jerry,
I have been looking at the results of the SPs and finding some strange
things, like a single workstation having 30 SELECT statements running
simultaneously. We are digging deeper.. it's a start.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23EXw0Z$0FHA.1108@.TK2MSFTNGP14.phx.gbl...
> John,
> Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
> sp_blockcnt.
> Also, take a look at:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
> HTH
> Jerry
> "John Kotuby" <jkotuby@.snet.net> wrote in message
> news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
>> We have a SQL Server 2000 transactional business production system that
>> is installed at 20 different locations and has been running very well in
>> all but 1 location that was installed over the weekend. The hardware
>> setup "seems" to be rock solid according to the MS SQL Server performance
>> guidelines.
>> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
>> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
>> Server, the only other database being a Veritas backup DB. There are 4 gb
>> of 400 mhz RAM.
>> The Data files are located on a Raid 5 high speed disk array and the
>> Transaction log is on a Raid 1 mirrored pair. The server is connected to
>> the network by a Broadcom NetXtreme Gigab: Network controller configured
>> for 100Mb full-duplex link.
>> Once again let me state that this same system is installed on much less
>> capable hardware at other locations with up to 30 users accessing it via
>> a VB Client application running on the workstations. Those other
>> locations are not experiencing the problem.
>> Now, the differences...
>> This location has 4 time as much data as the other locations and 40
>> client workstations. One of the tables has 2.2 million records (the
>> Account Detail table which tracks all activity to Invoices) and the other
>> 13 tables have record counts that vary but mostly in the 100,000 to
>> 600,000 range. The system data was converted from a DOS DBF system (on a
>> less capable server) that held these very same records and was running
>> slowly but did not time out. We ran multiple reports after the data
>> migration and everything seems to have come over as expected. The same
>> data migration process has been run at all the other installations
>> without problem.
>> Here is an example of what happens. When the Server has been freshly
>> rebooted a simple query on the largest table takes about 3 seconds. After
>> the 40 clients run the system for about 2 hours the system gradually
>> slows to a crawl. When I try to run the same test query it can take up to
>> 3 minutes.
>> Here is the strange part. Looking at the Task Manager, the Processors are
>> barely taxed and there is 2GB of free RAM. I don't know how to monitor
>> the disk activity. This is where it gets stranger. After all users have
>> logged off, the system STILL runs dog slow using the same test query, BUT
>> after about 2 hours it is back up to good performance. I am puzzled. It
>> is as if the Transaction queue is getting backed up, but resolves itself
>> after time. Or is it maybe the Disk arrays having trouble synchronizing?
>> But, a Server reboot seems to clear the problem almost immediately.
>> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh
>> in.
>> Thanks for your help...
>>
>|||John Kotuby wrote:
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
> Thanks for your help...
Do you update statistics? Auto-update?
There may be contention for tempdb, especially if you have a lot of sorts (order by and group by) in you SQL statements.
Supposedly this happens on servers with multiple processors and lots of memory. Search MS knowledge base for
concurrency and tempdb to see if it fits your situation.
Ed

HELP..Installed production system slows over time

We have a SQL Server 2000 transactional business production system that is
installed at 20 different locations and has been running very well in all
but 1 location that was installed over the weekend. The hardware setup
"seems" to be rock solid according to the MS SQL Server performance
guidelines.
It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
Windows Server 2003 standard with SP1. The machine is dedicated to SQL
Server, the only other database being a Veritas backup DB. There are 4 gb of
400 mhz RAM.
The Data files are located on a Raid 5 high speed disk array and the
Transaction log is on a Raid 1 mirrored pair. The server is connected to the
network by a Broadcom NetXtreme Gigab: Network controller configured for
100Mb full-duplex link.
Once again let me state that this same system is installed on much less
capable hardware at other locations with up to 30 users accessing it via a
VB Client application running on the workstations. Those other locations are
not experiencing the problem.
Now, the differences...
This location has 4 time as much data as the other locations and 40 client
workstations. One of the tables has 2.2 million records (the Account Detail
table which tracks all activity to Invoices) and the other 13 tables have
record counts that vary but mostly in the 100,000 to 600,000 range. The
system data was converted from a DOS DBF system (on a less capable server)
that held these very same records and was running slowly but did not time
out. We ran multiple reports after the data migration and everything seems
to have come over as expected. The same data migration process has been run
at all the other installations without problem.
Here is an example of what happens. When the Server has been freshly
rebooted a simple query on the largest table takes about 3 seconds. After
the 40 clients run the system for about 2 hours the system gradually slows
to a crawl. When I try to run the same test query it can take up to 3
minutes.
Here is the strange part. Looking at the Task Manager, the Processors are
barely taxed and there is 2GB of free RAM. I don't know how to monitor the
disk activity. This is where it gets stranger. After all users have logged
off, the system STILL runs dog slow using the same test query, BUT after
about 2 hours it is back up to good performance. I am puzzled. It is as if
the Transaction queue is getting backed up, but resolves itself after time.
Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
reboot seems to clear the problem almost immediately.
Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
Thanks for your help...John,
Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
sp_blockcnt.
Also, take a look at:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/defaul...kb;en-us;271509
HTH
Jerry
"John Kotuby" <jkotuby@.snet.net> wrote in message
news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb
> of 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to
> the network by a Broadcom NetXtreme Gigab: Network controller configured
> for 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations
> are not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account
> Detail table which tracks all activity to Invoices) and the other 13
> tables have record counts that vary but mostly in the 100,000 to 600,000
> range. The system data was converted from a DOS DBF system (on a less
> capable server) that held these very same records and was running slowly
> but did not time out. We ran multiple reports after the data migration and
> everything seems to have come over as expected. The same data migration
> process has been run at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after
> time. Or is it maybe the Disk arrays having trouble synchronizing? But, a
> Server reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh
> in.
> Thanks for your help...
>
>|||John Kotuby wrote:
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb
of
> 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to t
he
> network by a Broadcom NetXtreme Gigab: Network controller configured for
> 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations a
re
> not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account Detai
l
> table which tracks all activity to Invoices) and the other 13 tables have
> record counts that vary but mostly in the 100,000 to 600,000 range. The
> system data was converted from a DOS DBF system (on a less capable server)
> that held these very same records and was running slowly but did not time
> out. We ran multiple reports after the data migration and everything seems
> to have come over as expected. The same data migration process has been ru
n
> at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time
.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in
.
> Thanks for your help...|||Your problem is an interesting one. A question I would have relating to
it has to do with the outcome of your migration data validation. You
did not elaborate on the process utilized which would lead me to
suspect it may have been quite an abbreviated validation. Can you be
quite certain that stray charactors did not populate some table space
along with valid data? I am currently doing a small bit of research
around validation methodoligy. Would be quite interested in learning
more about the process you empoloyed for this.|||We ran Invoice balance reports for every Company (Division) in the original
DOS system and again after the data migration. All companies balanced before
and after the migration to the penny. The report output was identical for 5
years of data.
<jeff.livingston@.philips.com> wrote in message
news:1129655929.952358.319620@.f14g2000cwb.googlegroups.com...
> Your problem is an interesting one. A question I would have relating to
> it has to do with the outcome of your migration data validation. You
> did not elaborate on the process utilized which would lead me to
> suspect it may have been quite an abbreviated validation. Can you be
> quite certain that stray charactors did not populate some table space
> along with valid data? I am currently doing a small bit of research
> around validation methodoligy. Would be quite interested in learning
> more about the process you empoloyed for this.
>|||Thanks Jerry,
I have been looking at the results of the SPs and finding some strange
things, like a single workstation having 30 SELECT statements running
simultaneously. We are digging deeper.. it's a start.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23EXw0Z$0FHA.1108@.TK2MSFTNGP14.phx.gbl...
> John,
> Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
> sp_blockcnt.
> Also, take a look at:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/defaul...kb;en-us;271509
> HTH
> Jerry
> "John Kotuby" <jkotuby@.snet.net> wrote in message
> news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||John Kotuby wrote:

> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time
.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in
.
> Thanks for your help...
Do you update statistics? Auto-update?
There may be contention for tempdb, especially if you have a lot of sorts (o
rder by and group by) in you SQL statements.
Supposedly this happens on servers with multiple processors and lots of memo
ry. Search MS knowledge base for
concurrency and tempdb to see if it fits your situation.
Ed

HELP..Installed production system slows over time

We have a SQL Server 2000 transactional business production system that is
installed at 20 different locations and has been running very well in all
but 1 location that was installed over the weekend. The hardware setup
"seems" to be rock solid according to the MS SQL Server performance
guidelines.
It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
Windows Server 2003 standard with SP1. The machine is dedicated to SQL
Server, the only other database being a Veritas backup DB. There are 4 gb of
400 mhz RAM.
The Data files are located on a Raid 5 high speed disk array and the
Transaction log is on a Raid 1 mirrored pair. The server is connected to the
network by a Broadcom NetXtreme Gigab: Network controller configured for
100Mb full-duplex link.
Once again let me state that this same system is installed on much less
capable hardware at other locations with up to 30 users accessing it via a
VB Client application running on the workstations. Those other locations are
not experiencing the problem.
Now, the differences...
This location has 4 time as much data as the other locations and 40 client
workstations. One of the tables has 2.2 million records (the Account Detail
table which tracks all activity to Invoices) and the other 13 tables have
record counts that vary but mostly in the 100,000 to 600,000 range. The
system data was converted from a DOS DBF system (on a less capable server)
that held these very same records and was running slowly but did not time
out. We ran multiple reports after the data migration and everything seems
to have come over as expected. The same data migration process has been run
at all the other installations without problem.
Here is an example of what happens. When the Server has been freshly
rebooted a simple query on the largest table takes about 3 seconds. After
the 40 clients run the system for about 2 hours the system gradually slows
to a crawl. When I try to run the same test query it can take up to 3
minutes.
Here is the strange part. Looking at the Task Manager, the Processors are
barely taxed and there is 2GB of free RAM. I don't know how to monitor the
disk activity. This is where it gets stranger. After all users have logged
off, the system STILL runs dog slow using the same test query, BUT after
about 2 hours it is back up to good performance. I am puzzled. It is as if
the Transaction queue is getting backed up, but resolves itself after time.
Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
reboot seems to clear the problem almost immediately.
Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
Thanks for your help...
John,
Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
sp_blockcnt.
Also, take a look at:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/default...b;en-us;271509
HTH
Jerry
"John Kotuby" <jkotuby@.snet.net> wrote in message
news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb
> of 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to
> the network by a Broadcom NetXtreme Gigab: Network controller configured
> for 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations
> are not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account
> Detail table which tracks all activity to Invoices) and the other 13
> tables have record counts that vary but mostly in the 100,000 to 600,000
> range. The system data was converted from a DOS DBF system (on a less
> capable server) that held these very same records and was running slowly
> but did not time out. We ran multiple reports after the data migration and
> everything seems to have come over as expected. The same data migration
> process has been run at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after
> time. Or is it maybe the Disk arrays having trouble synchronizing? But, a
> Server reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh
> in.
> Thanks for your help...
>
>
|||John Kotuby wrote:
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb of
> 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to the
> network by a Broadcom NetXtreme Gigab: Network controller configured for
> 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations are
> not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account Detail
> table which tracks all activity to Invoices) and the other 13 tables have
> record counts that vary but mostly in the 100,000 to 600,000 range. The
> system data was converted from a DOS DBF system (on a less capable server)
> that held these very same records and was running slowly but did not time
> out. We ran multiple reports after the data migration and everything seems
> to have come over as expected. The same data migration process has been run
> at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
> Thanks for your help...
|||Your problem is an interesting one. A question I would have relating to
it has to do with the outcome of your migration data validation. You
did not elaborate on the process utilized which would lead me to
suspect it may have been quite an abbreviated validation. Can you be
quite certain that stray charactors did not populate some table space
along with valid data? I am currently doing a small bit of research
around validation methodoligy. Would be quite interested in learning
more about the process you empoloyed for this.
|||We ran Invoice balance reports for every Company (Division) in the original
DOS system and again after the data migration. All companies balanced before
and after the migration to the penny. The report output was identical for 5
years of data.
<jeff.livingston@.philips.com> wrote in message
news:1129655929.952358.319620@.f14g2000cwb.googlegr oups.com...
> Your problem is an interesting one. A question I would have relating to
> it has to do with the outcome of your migration data validation. You
> did not elaborate on the process utilized which would lead me to
> suspect it may have been quite an abbreviated validation. Can you be
> quite certain that stray charactors did not populate some table space
> along with valid data? I am currently doing a small bit of research
> around validation methodoligy. Would be quite interested in learning
> more about the process you empoloyed for this.
>
|||Thanks Jerry,
I have been looking at the results of the SPs and finding some strange
things, like a single workstation having 30 SELECT statements running
simultaneously. We are digging deeper.. it's a start.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23EXw0Z$0FHA.1108@.TK2MSFTNGP14.phx.gbl...
> John,
> Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
> sp_blockcnt.
> Also, take a look at:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/default...b;en-us;271509
> HTH
> Jerry
> "John Kotuby" <jkotuby@.snet.net> wrote in message
> news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
>
|||John Kotuby wrote:

> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
> Thanks for your help...
Do you update statistics? Auto-update?
There may be contention for tempdb, especially if you have a lot of sorts (order by and group by) in you SQL statements.
Supposedly this happens on servers with multiple processors and lots of memory. Search MS knowledge base for
concurrency and tempdb to see if it fits your situation.
Ed