Profile Error 17055

I applied a service pack to a third party application and
as of now I keep receiving the following in the sql
server log and application log every 1 minute or so.
How can I use the profiler on this to find out what's
happening?
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 17055
Date: 12/28/2003
Time: 11:54:26 AM
User: N/A
Computer: SERVERP01
Description:
18456 :
Login failed for user 'TRACK'.
Data:
0000: 18 48 00 00 0e 00 00 00 .H.....
0008: 0f 00 00 00 4a 00 52 00 ...J.R.
0010: 41 00 59 00 44 00 55 00 A.Y.D.U.
0018: 42 00 49 00 53 00 51 00 B.I.S.Q.
0020: 4c 00 50 00 30 00 31 00 L.P.0.1.
0028: 00 00 07 00 00 00 6d 00 .....m.
0030: 61 00 73 00 74 00 65 00 a.s.t.e.
0038: 72 00 00 00 r...
Thanks,
ShonetYou can use profiler to log connection attempts...Someone is trying to log
in using the login TRACK, which is a failed attempt... Watching this in
profiler will get you the name of the workstation, and name of the program
they are using... then you can track them down..
--
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
"Shonet" <anonymous@.discussions.microsoft.com> wrote in message
news:02bc01c3cd17$897e17f0$a601280a@.phx.gbl...
> I applied a service pack to a third party application and
> as of now I keep receiving the following in the sql
> server log and application log every 1 minute or so.
> How can I use the profiler on this to find out what's
> happening?
> Event Type: Information
> Event Source: MSSQLSERVER
> Event Category: (4)
> Event ID: 17055
> Date: 12/28/2003
> Time: 11:54:26 AM
> User: N/A
> Computer: SERVERP01
> Description:
> 18456 :
> Login failed for user 'TRACK'.
> Data:
> 0000: 18 48 00 00 0e 00 00 00 .H.....
> 0008: 0f 00 00 00 4a 00 52 00 ...J.R.
> 0010: 41 00 59 00 44 00 55 00 A.Y.D.U.
> 0018: 42 00 49 00 53 00 51 00 B.I.S.Q.
> 0020: 4c 00 50 00 30 00 31 00 L.P.0.1.
> 0028: 00 00 07 00 00 00 6d 00 .....m.
> 0030: 61 00 73 00 74 00 65 00 a.s.t.e.
> 0038: 72 00 00 00 r...
>
> Thanks,
> Shonet|||I managed to work this one out, but I'm please to see
what I came up with is was the same as your solution.
Excellent.
Thanks!!
Shonet
>--Original Message--
>You can use profiler to log connection
attempts...Someone is trying to log
>in using the login TRACK, which is a failed attempt...
Watching this in
>profiler will get you the name of the workstation, and
name of the program
>they are using... then you can track them down..
>--
>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
>
>"Shonet" <anonymous@.discussions.microsoft.com> wrote in
message
>news:02bc01c3cd17$897e17f0$a601280a@.phx.gbl...
>> I applied a service pack to a third party application
and
>> as of now I keep receiving the following in the sql
>> server log and application log every 1 minute or so.
>> How can I use the profiler on this to find out what's
>> happening?
>> Event Type: Information
>> Event Source: MSSQLSERVER
>> Event Category: (4)
>> Event ID: 17055
>> Date: 12/28/2003
>> Time: 11:54:26 AM
>> User: N/A
>> Computer: SERVERP01
>> Description:
>> 18456 :
>> Login failed for user 'TRACK'.
>> Data:
>> 0000: 18 48 00 00 0e 00 00 00 .H.....
>> 0008: 0f 00 00 00 4a 00 52 00 ...J.R.
>> 0010: 41 00 59 00 44 00 55 00 A.Y.D.U.
>> 0018: 42 00 49 00 53 00 51 00 B.I.S.Q.
>> 0020: 4c 00 50 00 30 00 31 00 L.P.0.1.
>> 0028: 00 00 07 00 00 00 6d 00 .....m.
>> 0030: 61 00 73 00 74 00 65 00 a.s.t.e.
>> 0038: 72 00 00 00 r...
>>
>> Thanks,
>> Shonet
>
>.
>

Production SQL server down!

Someone attempted to change the partition layout of our SQL cluster, then we
recieved an error:
"Could not start the MSSQLSERVER service on local computer. The service did
not return an error. This could be an internal Windows error or an internal
service error. . . ."
in event log, we have two repetitive errors (application), and errors in
system log:
Application:
Event ID #17052
Error: 17826, Severity: 18, State: 1
Could not set up Net-Library 'SSNETLIB'.
and
Event ID#17055
17120 :
SQL Server could not spawn FRunCM thread.
System:
Event ID#1069
Cluster resource 'SQL Server' failed.
Any ideas? I've been "googling" all morning
If your production SQL Server is down then I would suggest opening a case with Microsoft Product Support Services (PSS).
Newsgroups doesn't work well for critical situations that needs to be resolved asap.
The error messages that you mentioned could have many causes and a thourough diagnoses is required to find root
cause. One has to review the SQL Server Errorlogs, NT Event Logs, Cluster logs for a start. One may also need to review
the Cluster hive and other cluster related registry keys.
Here are some ideas:
What error messages do you see in SQL Server Errorlog?
Along with the errors that you mentioned are you also getting the following message
Encryption requested but no valid certificate was found. SQL Server terminating
Has somebody turned on the Force Protocol Encryption option in Server Network Utility ?
BUG: Turning On the 'Force Protocol Encryption' Option Is Irreversible If There Is No Certificate
http://support.microsoft.com/?id=319349
Since SQL Server is not coming online, you will not be able to open Server Network Utility. I will check the following registry
key :
For default instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\SuperSocketNetLib
For named instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer
\SuperSocketNetLib
See if it has the 'Encrypt' key. It will be present and set to 1 if Force Protocol Encryption is enabled. If it does not exist, it will
indicate that the encryption was never set.
If encryption is not set then the reason may be something different.
One more thing that comes to miind now is to check the Parameters key in the CLUSTER hive in the registry.
-- Locate the SQL Server Resource in the Cluster Hive under the Resources Key. Then drill down to the Parameters key
under the SQL Server Resource.
We should see the following two values under the Parameters Key.
(HKLM\Cluster\Resources\{GUID for Resource}\Parameters\)
Default Instance
Name: InstanceName Type: Reg_SZ Data: MSSQLSERVER
Name: VirtualServerName Type: Reg_SZ Data: Virtual SQL Server Name
Named Instance
Name: InstanceName Type: Reg_SZ Data: Instance Name of the SQL Server
Name: VirtualServerName Type: Reg_SZ Data: Virtual SQL Server Name
If these are missing then add them and see if you can bring SQL Server resource online. If you have another (working)
cluster, you may want to see the values under Parameter Key first and then make the changes on the problem cluster.
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security
Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against
viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Another point to consider:
If it turns out that the Parameters key is empty (and based on the
description of what happened, I believe this is it) then verirfy that the
following key exists:
HKLM\Cluster\Resources\{GUID for Resource}\RegSync.
If not, then this key will have to be recreated and the checkpoint files
for SQL Server may need to be recreated. You will probably want to open a
case with Microsoft Product Support Services (PSS) to help recreate the
checkpoint files.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Processing Time

Hi,

I am verifying my reports processing time. I get the information from the Reporting Service DB - [ExecutionLogs] table. I have the following information:

[TimeEnd] – time that reports generation ends.

[TimeStart] - time that reports generation starts.

[TimeDataRetrieval] - amount of time spent running the data sources.

[TimeProcessing] - time spent processing the report.

[TimeRendering] - time spent generating the output format.

If this information is correct the following statement should be true:

([TimeEnd] - [TimeStart]) = ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering])

But it isn't, ([TimeEnd] - [TimeStart]) is always bigger then ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering]).

Why does this happen?

Regards,

Rodrigo

That's interesting. I've never looked to check that. Are you seeing a big difference? how much is it a % of total execution time?

I suspect that TimeDataRetrieval, TimeProcessing, TimeRendering are each recorded by the subsystem resposible for each of those. I would expect there to be a small amount of overhead for the engine to do it's stuff and transition between the various phases between start and end.

|||

I have the following results for different reports:

End-Start

(ms)

Sum

(ms)

%

5030 3717 73,8966203 17437 12009 68,870792 5047 3374 66,851595 4733 3142 66,3849567 23170 15348 66,2408287 4780 3134 65,5648536 15750 10129 64,3111111 13017 7849 60,2980718 11153 6548 58,7106608 13170 7660 58,1624905 8830 5119 57,9728199

The first column is ([TimeEnd] - [TimeStart])

The second column is ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering])

How can i find the cause of this difference ?

Regards,

Rodrigo

|||

The difference is consistently large, too large. From your observations i.e. looking at the screen with a stopwatch, which is the "more correct" value?

|||

Do the reports have multiple datasets?

If multiple datasets are present and transactions are not used, SSRS will execute dataset queries in parallel. However, since there is only one data retrieval value in the execution log, it will pick the time of the longest data retrieval thread.

In addition, time data retrieval + processing + rendering does not cover all the time between the start and the end time of the request. Generally however, the other operations performed should just be small fraction compared to ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering]).

-- Robert

Processing Cube

Hi,

Everyday I have a schedule job to processing the cubes, but I always receive the same error and then the Analysis Service will be stop. Here is the error msg from the Event Viewer:

Event Type: Error
Event Source: MSSQLServerOLAPService
Event Category: (256)
Event ID: 22
Date: 9/18/2007
Time: 5:03:38 AM
User: N/A
Computer: HODB04
Description:
The description for Event ID ( 22 ) in Source ( MSSQLServerOLAPService ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: File system error: The following error occurred during a file operation: The process cannot access the file because it is being used by another process. . (\\?\z:\OLAP\data\DM_POS_V2.0.db\POS DM.558.cub\Fact Sales.591.det\FACT SALES F2008 P2.7.prt\55.agg.flex.data)..

Thanks,

I would reccomend you to investigate the storage subsystem (RAID).

I have almost the same problem - the source of it was in the configurationof the RAID controller.

|||

What modifications did you made to the raid config to make it run? Whe are just running two mirrored disks on a HP so there's not much to modify.

Process name for SQL6.5 (sqlexec.exe ??)

I have noticed that the process name for the SQL Server service on some
installation is called SQLexec.exe and others called SQLServr.exe. Both
cases are for 6.5 version. Which name is correct. Or it may be that there
is an external name for the service.
Thanks for any input.
MacHi Mac.
sqlserver.exe is the main sql server process. sqlexec.exe was the "SQL
Executive", which was the old name for what is now called "SQL Agent". Both
run as services under the Windows Service Control Manager.
Regards,
Greg Linwood
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:c4cobv$27f5$1@.si05.rsvl.unisys.com...
> I have noticed that the process name for the SQL Server service on some
> installation is called SQLexec.exe and others called SQLServr.exe. Both
> cases are for 6.5 version. Which name is correct. Or it may be that
there
> is an external name for the service.
> Thanks for any input.
> Mac
>|||Thanks for your prompt reply. That is what I wanted to know.
-- Mac
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eWPtaBsFEHA.1396@.TK2MSFTNGP11.phx.gbl...
> Hi Mac.
> sqlserver.exe is the main sql server process. sqlexec.exe was the "SQL
> Executive", which was the old name for what is now called "SQL Agent".
Both
> run as services under the Windows Service Control Manager.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:c4cobv$27f5$1@.si05.rsvl.unisys.com...
Both
> there
>

Process Blocking Problem

We are running SQL Server 7.0 Service Pack #4, running on Windows 2000 Server.
We have a problem with process blocking. At some points the T-SQL statement runs restricting all our users from accessing the DB.
The statement is:

SET FMTONLY ON
exec sp_execute 1
SET FMTONLY OFF

Only when it finishes running, then users are allowed to access the db again.
It is triggered automatically. Does any know what condition on the server is causing this statements to run and what can be done to prevent it. Thanks so much for your help.Look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/sqlquerproc.asp

It explains what sp_execute does.
Do you have a lot of dynamic sql or embedded sql rather than SPs?|||Also look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag2k/html/adoapp.asp|||What I found out from the Microsoft website that sp_execute is a command that runs a prepared statement using the sp_prepare command.
So what happened is I've watched the SQL for the condition to repeat but it did not. However what I've noticed that our of nowhere at time sp_prepare runs without any parameters, causing the CPU utilization go up to high 90%.
What is causing this sp_prepate to run, as it done automatically.
Thanks for your help.

Process 1304, c000001 d EXCEPTION_ILLEGAL_INSTRUCTION

MS SQL 7.0 server service was stopped suddenly with the
error code logged in NT error log tat "17310:
SqlDumpExceptionHander: Process 1304 generated fatal
exception c000001 d EXCEPTION_ILLEGAL_INSTRUCTION. SQL
Server is terminating this process. What could be
possible reason for this problem?
thanks
balaDo a google search on EXCEPTION_ILLEGAL_INSTRUCTION SQL. I found lots of
stuff there...
--
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
"BalamuruganMR" <rblm@.chevrontexaco.com> wrote in message
news:049801c3cde2$2b3a7460$a301280a@.phx.gbl...
> MS SQL 7.0 server service was stopped suddenly with the
> error code logged in NT error log tat "17310:
> SqlDumpExceptionHander: Process 1304 generated fatal
> exception c000001 d EXCEPTION_ILLEGAL_INSTRUCTION. SQL
> Server is terminating this process. What could be
> possible reason for this problem?
> thanks
> bala

Proceesing cube using analysis services processing task

Hi,

I have a dts package stored in sql server 2005 which has a analysis service processing task to process a cube stored in sql server 2005 itself.

whenever i try to execute this package it crashes the sql server management studio and in the logfile i can find the error operation has been cancelled.

When i try to process the cube directly it processes without any problem and i can browse and see the data also.

In the package i am trying to do full process of the cube.

Also the cube and the package are migrated from 2000 to 2005.

The onemore problem is after migrating the package i am unable to see the cube setting also

Please help me to solve this

Thanks in advance

Mervin

Analysis Services 2005 uses completely new management object model: AMO. The old object model: DSO used in DTS processing packages although supported, requires from you to go through additional conifugration process.

You should try to create a new SSIS 2005 package to process your AS 2005 cube.

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

|||

Hi,

thanks you i will try this.

But if i want to keep the dts packages itself what kind of configuration process i need to add.

And if i try to create the new packages i am not clear about the processing options that are to be provided

for the refresh data and rebuild dimension structure of dts.

Regards

Mervin

|||

Full process is going to rebuild dimension stucture

Process Update will refresh data in your dimension.

Here is whitepaper talking about processing options in Analysis Services 2005: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

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

Procedure taking longer due to Service Packs applied to SQL Server 7

I have a job setup to run a procedure in a database. The user account
sa is the owner. The actual database resides on an NT Server 4.0
Terminal Services PC using SQL Server 7.
The first time I scheduled it the job took approx 6 minutes to
complete and allowing it to continue averaged at approx 6 minutes.
I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
with no Service Packs applied. So I applied the Service Packs to the
environment and then restored the database I was using.
Running the same job again which executes the procedure took approx 20
minutes and allowing it to continue averaged at approx 20 minutes.
I need to find out what difference the Service Packs make to the SQL
environment as this is the only thing that has changed. I'm not saying
this is a SQL issue as it may be that the procedure needs amending in
some way to make it run more efficiently.
Any feedback or advice would be grateful.
Try updating statistics on your tables, and see if that makes a difference.
Just a stab in the dark.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rzirpolo" <robert.zirpolo@.phoenixdb.co.uk> wrote in message
news:5320d330.0408050041.5c815b24@.posting.google.c om...
> I have a job setup to run a procedure in a database. The user account
> sa is the owner. The actual database resides on an NT Server 4.0
> Terminal Services PC using SQL Server 7.
> The first time I scheduled it the job took approx 6 minutes to
> complete and allowing it to continue averaged at approx 6 minutes.
> I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
> with no Service Packs applied. So I applied the Service Packs to the
> environment and then restored the database I was using.
> Running the same job again which executes the procedure took approx 20
> minutes and allowing it to continue averaged at approx 20 minutes.
> I need to find out what difference the Service Packs make to the SQL
> environment as this is the only thing that has changed. I'm not saying
> this is a SQL issue as it may be that the procedure needs amending in
> some way to make it run more efficiently.
> Any feedback or advice would be grateful.
|||Looks a very good stab in the dark, thank you.
Upon enabling the job again the job has now reduced back down to 6
minutes.
I'm not to clued up on the SQL Server admin side and am wondering what
exactly are these statistics and what did this procedure do i.e. what
does updating them do ?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||From SQL 200 BOL:
"The distribution statistics are used by Microsoft SQL ServerT to optimize
navigation through tables during the processing of Transact-SQL statements.
To build the distribution statistics automatically, SQL Server periodically
samples a percentage of the data in the corresponding table for each index."
From "Distribution Statistics" in SQL 2000 BOL:
"All indexes have distribution statistics that describe the selectivity and
distribution of the key values in the index. Selectivity is a property that
relates to how many rows are typically identified by a key value. A unique
key has high selectivity; a key value found in 1,000 rows has poor
selectivity. The selectivity and distribution statistics are used by
Microsoft SQL ServerT 2000 to optimize its navigation through tables and
indexed views when processing Transact-SQL statements. The distribution
statistics are used to estimate how efficient an index would be in
retrieving data associated with a key value or range specified in the query.
The statistics for each index are not limited to a single page but are
stored as a long string of bits across multiple pages in the same way image
data is stored. The column sysindexes.statblob points to this distribution
data. You can use the DBCC SHOW_STATISTICS statement to get a report on the
distribution statistics for an index.
Distribution statistics may also be maintained for unindexed columns. These
can be defined manually using the CREATE STATISTICS statement or created
automatically by the query optimizer. Statistics on unindexed columns count
against the limit of 249 nonclustered indexes allowed on a table.
To be useful to query optimizer, distribution statistics must be kept
reasonably current. The distribution statistics should be refreshed anytime
significant numbers of changes to keys occur in the index. Distribution
statistics can be updated manually using the UPDATE STATISTICS statement.
SQL Server 2000 can also detect when distribution statistics are out of date
and update the statistics automatically. This update is performed by the
task that detected that the statistics needed to be updated. The update is
performed using a complex sampling method that minimizes the effect of the
update on transaction throughput."
Appiles to SQL 7 and 2000 alike.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"microsoft.public.sqlserver.server" <robert.zirpolo@.phoenixdb.co.uk> wrote
in message news:OHmihz7eEHA.2916@.TK2MSFTNGP12.phx.gbl...
> Looks a very good stab in the dark, thank you.
> Upon enabling the job again the job has now reduced back down to 6
> minutes.
> I'm not to clued up on the SQL Server admin side and am wondering what
> exactly are these statistics and what did this procedure do i.e. what
> does updating them do ?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Procedure taking longer due to Service Packs applied to SQL Server 7

I have a job setup to run a procedure in a database. The user account
sa is the owner. The actual database resides on an NT Server 4.0
Terminal Services PC using SQL Server 7.
The first time I scheduled it the job took approx 6 minutes to
complete and allowing it to continue averaged at approx 6 minutes.
I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
with no Service Packs applied. So I applied the Service Packs to the
environment and then restored the database I was using.
Running the same job again which executes the procedure took approx 20
minutes and allowing it to continue averaged at approx 20 minutes.
I need to find out what difference the Service Packs make to the SQL
environment as this is the only thing that has changed. I'm not saying
this is a SQL issue as it may be that the procedure needs amending in
some way to make it run more efficiently.
Any feedback or advice would be grateful.Try updating statistics on your tables, and see if that makes a difference.
Just a stab in the dark.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rzirpolo" <robert.zirpolo@.phoenixdb.co.uk> wrote in message
news:5320d330.0408050041.5c815b24@.posting.google.com...
> I have a job setup to run a procedure in a database. The user account
> sa is the owner. The actual database resides on an NT Server 4.0
> Terminal Services PC using SQL Server 7.
> The first time I scheduled it the job took approx 6 minutes to
> complete and allowing it to continue averaged at approx 6 minutes.
> I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
> with no Service Packs applied. So I applied the Service Packs to the
> environment and then restored the database I was using.
> Running the same job again which executes the procedure took approx 20
> minutes and allowing it to continue averaged at approx 20 minutes.
> I need to find out what difference the Service Packs make to the SQL
> environment as this is the only thing that has changed. I'm not saying
> this is a SQL issue as it may be that the procedure needs amending in
> some way to make it run more efficiently.
> Any feedback or advice would be grateful.|||Looks a very good stab in the dark, thank you.
Upon enabling the job again the job has now reduced back down to 6
minutes.
I'm not to clued up on the SQL Server admin side and am wondering what
exactly are these statistics and what did this procedure do i.e. what
does updating them do ?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||From SQL 200 BOL:
"The distribution statistics are used by Microsoft SQL ServerT to optimize
navigation through tables during the processing of Transact-SQL statements.
To build the distribution statistics automatically, SQL Server periodically
samples a percentage of the data in the corresponding table for each index."
From "Distribution Statistics" in SQL 2000 BOL:
"All indexes have distribution statistics that describe the selectivity and
distribution of the key values in the index. Selectivity is a property that
relates to how many rows are typically identified by a key value. A unique
key has high selectivity; a key value found in 1,000 rows has poor
selectivity. The selectivity and distribution statistics are used by
Microsoft SQL ServerT 2000 to optimize its navigation through tables and
indexed views when processing Transact-SQL statements. The distribution
statistics are used to estimate how efficient an index would be in
retrieving data associated with a key value or range specified in the query.
The statistics for each index are not limited to a single page but are
stored as a long string of bits across multiple pages in the same way image
data is stored. The column sysindexes.statblob points to this distribution
data. You can use the DBCC SHOW_STATISTICS statement to get a report on the
distribution statistics for an index.
Distribution statistics may also be maintained for unindexed columns. These
can be defined manually using the CREATE STATISTICS statement or created
automatically by the query optimizer. Statistics on unindexed columns count
against the limit of 249 nonclustered indexes allowed on a table.
To be useful to query optimizer, distribution statistics must be kept
reasonably current. The distribution statistics should be refreshed anytime
significant numbers of changes to keys occur in the index. Distribution
statistics can be updated manually using the UPDATE STATISTICS statement.
SQL Server 2000 can also detect when distribution statistics are out of date
and update the statistics automatically. This update is performed by the
task that detected that the statistics needed to be updated. The update is
performed using a complex sampling method that minimizes the effect of the
update on transaction throughput."
Appiles to SQL 7 and 2000 alike.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"microsoft.public.sqlserver.server" <robert.zirpolo@.phoenixdb.co.uk> wrote
in message news:OHmihz7eEHA.2916@.TK2MSFTNGP12.phx.gbl...
> Looks a very good stab in the dark, thank you.
> Upon enabling the job again the job has now reduced back down to 6
> minutes.
> I'm not to clued up on the SQL Server admin side and am wondering what
> exactly are these statistics and what did this procedure do i.e. what
> does updating them do ?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Procedure taking longer due to Service Packs applied to SQL Server 7

I have a job setup to run a procedure in a database. The user account
sa is the owner. The actual database resides on an NT Server 4.0
Terminal Services PC using SQL Server 7.
The first time I scheduled it the job took approx 6 minutes to
complete and allowing it to continue averaged at approx 6 minutes.
I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
with no Service Packs applied. So I applied the Service Packs to the
environment and then restored the database I was using.
Running the same job again which executes the procedure took approx 20
minutes and allowing it to continue averaged at approx 20 minutes.
I need to find out what difference the Service Packs make to the SQL
environment as this is the only thing that has changed. I'm not saying
this is a SQL issue as it may be that the procedure needs amending in
some way to make it run more efficiently.
Any feedback or advice would be grateful.Try updating statistics on your tables, and see if that makes a difference.
Just a stab in the dark.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rzirpolo" <robert.zirpolo@.phoenixdb.co.uk> wrote in message
news:5320d330.0408050041.5c815b24@.posting.google.com...
> I have a job setup to run a procedure in a database. The user account
> sa is the owner. The actual database resides on an NT Server 4.0
> Terminal Services PC using SQL Server 7.
> The first time I scheduled it the job took approx 6 minutes to
> complete and allowing it to continue averaged at approx 6 minutes.
> I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
> with no Service Packs applied. So I applied the Service Packs to the
> environment and then restored the database I was using.
> Running the same job again which executes the procedure took approx 20
> minutes and allowing it to continue averaged at approx 20 minutes.
> I need to find out what difference the Service Packs make to the SQL
> environment as this is the only thing that has changed. I'm not saying
> this is a SQL issue as it may be that the procedure needs amending in
> some way to make it run more efficiently.
> Any feedback or advice would be grateful.

Procedure sp_fulltext_database

Server: Msg 7635, Level 16, State 1, Procedure sp_fulltext_database, Line 46
The Microsoft Search service cannot be administered under the present user
account
Does anyone know how to fix this problem?
The services are started under localsystem
How to enable administration of ms search?
Are you running this proc using the sa account or an account in the system
administrators role?
This problem can be caused by changing the SQL Server service account
through the services applet in control panel as opposed to through right
clicking on SQL Server in Enterprise Manager, selecting properties, and
changing it in the security tab.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dimitri" <Dimitri@.discussions.microsoft.com> wrote in message
news:DF932405-FA49-433D-B561-9E30F18DFEF5@.microsoft.com...
> Server: Msg 7635, Level 16, State 1, Procedure sp_fulltext_database, Line
> 46
> The Microsoft Search service cannot be administered under the present user
> account
> Does anyone know how to fix this problem?
> The services are started under localsystem
> How to enable administration of ms search?
|||If you have a clustered database, ensure that on the SQL Server the SQL Server FullText is online.