Receiving a Communication Link Failure ODBC

Hi All,
I have a client using SQL Server 7.0 Sp3, and they have
clients connecting to it via ODBC (32) bit driver, some of
them are receiving a error message of communication Link
failure. They have MDAC 2.7.1 installed, they tried using
TCP/IP, and Name pipes, the error keeps happening.
If you have any ideas it would greatly be appreciated.
Thanks
ScottCan you ping the sql server when they receive the error?

Rebuild Master Error

I am performing a Rebiuld of the Master database using the REBUILDM utility on a SQL 2000 SP3 database which is clustered.
The utility starts off correclty, copying the correct MDF and LDF files to the hard drive, and the configuration bar goes across the screen 4 times, before giving the following error "Rebuild master failed with error -1".

I can't find anything on microsoft. I followed KB-298568 to perform this process, but the SQL Service will now not start as it cannot find the master, as it did not get rebuilt properly.

I have included screen shots of the error, in the zipped up word doc.It seems to be strange that it does it 4 times. Did you check whether the .mdf and .ldf were in the right directory?|||Originally posted by joejcheng
It seems to be strange that it does it 4 times. Did you check whether the .mdf and .ldf were in the right directory?

Yes, they appear in the correct directory, and are the correct size.|||When you run any sql utility or upgrade, a log file will be generated. In there, you can find detailed information regarding the error that you encountered. Check the directory \program files\microsoft sql server\mssql\install\.|||Also, try to copy the source to a local drive and run it from there rather than from a UNC location.|||I copied the files locally, and still got error -1|||Originally posted by joejcheng
When you run any sql utility or upgrade, a log file will be generated. In there, you can find detailed information regarding the error that you encountered. Check the directory \program files\microsoft sql server\mssql\install\.

There are not log files in this folder - only *.txt files that correspond to the *.gif files used for the Northwing and Pubs databases.|||I did some more investigation and found the cause of the problem.

When I initially installed SQL Server, I then used KB224071 to move the log files for the system databases to another drive, specifically for log files.

The location of the Master database files are written to the registry under HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\Pa rameters.

When Rebuildm runs it places all the files in the initial install location, and then tries to start the MSSQL Service, and fails as the path the the master database in the registry no longer matches the location of the log files where Rebuildm placed them - Hence error -1.

I manually moved the log file flat files to the correct drive, started the MSSQL Service and it all started up OK.|||Well, it would have been nice to know that you did move log files of systems databases to a different location :) Looks like we did all this brain-storming for nothing.|||Hi you fellow database teasers,

I think he did that in order to seperate data and log -- Performance.

But as I think it is not recommended to separate them for system databases.

Rebuild index issue - - strange

Hi Folks,

SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
the command :

dbcc dbreindex ('tablename')
go

for all tables in the database. Then I compared the dbcc showcontig
with all_index output from before and after the reindex and on the
largest table in the database I found this. First output is prior to
reindex:

Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
7
TABLE level scan performed.
- Pages Scanned........................: 184867
- Extents Scanned.......................: 23203
- Extent Switches.......................: 23324
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
- Logical Scan Fragmentation ..............: 11.13%
- Extent Scan Fragmentation ...............: 35.46%
- Avg. Bytes Free per Page................: 60.0
- Avg. Page Density (full)................: 99.26%

Second output is from after the reindex:

DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
8
TABLE level scan performed.
- Pages Scanned........................: 303177
- Extents Scanned.......................: 37964
- Extent Switches.......................: 42579
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
- Logical Scan Fragmentation ..............: 43.19%
- Extent Scan Fragmentation ...............: 24.78%
- Avg. Bytes Free per Page................: 75.1
- Avg. Page Density (full)................: 99.07%

Following are my concerns:

The following numbers are all higher after reindex than before reindex:

pages scanned, extent switches, logical scan fragmentation, avg bytes
free per page, avg page density.

scan density is lower after reindex than before reindex

Seems to me that the numbers that are higher after reindex should be
lower and numbers that are lower after reindex should be higher? I
didn't specify the fill factor in the dbcc reindex command so it should
have used the default fill factor. The fill factor has never been
changed on this machine.

Am I missing something?

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***if you look at your database ID's, they are different. did you run
this on 2 different databases?

Raziq Shekha wrote:
> Hi Folks,
> SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
> the command :
> dbcc dbreindex ('tablename')
> go
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned........................: 184867
> - Extents Scanned.......................: 23203
> - Extent Switches.......................: 23324
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..............: 11.13%
> - Extent Scan Fragmentation ...............: 35.46%
> - Avg. Bytes Free per Page................: 60.0
> - Avg. Page Density (full)................: 99.26%
>
> Second output is from after the reindex:
>
> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned........................: 303177
> - Extents Scanned.......................: 37964
> - Extent Switches.......................: 42579
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
> - Logical Scan Fragmentation ..............: 43.19%
> - Extent Scan Fragmentation ...............: 24.78%
> - Avg. Bytes Free per Page................: 75.1
> - Avg. Page Density (full)................: 99.07%
>
> Following are my concerns:
> The following numbers are all higher after reindex than before reindex:
> pages scanned, extent switches, logical scan fragmentation, avg bytes
> free per page, avg page density.
> scan density is lower after reindex than before reindex
> Seems to me that the numbers that are higher after reindex should be
> lower and numbers that are lower after reindex should be higher? I
> didn't specify the fill factor in the dbcc reindex command so it should
> have used the default fill factor. The fill factor has never been
> changed on this machine.
> Am I missing something?
> Thanks,
> Raziq.
> *** Sent via Developersdex http://www.developersdex.com ***

Rebuilbm not working

Hello
I have:
Windows 2000 server with SP3, SQL standard SP2
After a NT backup restore I want to use REBUILDM.EXE to create the system databases in order to restore the databases.
I get the error “Rebuild Master failed with error -1:”
I am aware of KB 273572 and I have a copy of the original files into a folder on the server. The files are not “Read-only”
But I get the error. Did some research…no luck.
If you experienced this please let me know.
Thanks a lot
Look at the SQL Server errolog and verify that Shared Memory is loaded. If
not, reboot the machine. Rebuildm makes a shared memory connection so if
shared memory is not loaded it will fail.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Rebuilbm not working

Hello
I have:
Windows 2000 server with SP3, SQL standard SP2
After a NT backup restore I want to use REBUILDM.EXE to create the system da
tabases in order to restore the databases.
I get the error “Rebuild Master failed with error -1:”
I am aware of KB 273572 and I have a copy of the original files into a folde
r on the server. The files are not “Read-only”
But I get the error. Did some research…no luck.
If you experienced this please let me know.
Thanks a lotLook at the SQL Server errolog and verify that Shared Memory is loaded. If
not, reboot the machine. Rebuildm makes a shared memory connection so if
shared memory is not loaded it will fail.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Reasonable query never finishes

Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a certain
query works against some datasets within a couple tables, but mysteriously
never finishes against other datasets, perhaps some recently loaded ones.
It's mainly just one big table with some smaller auxiliary tables (used in
this query).
I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC INDEXDEFRAG
without effect. However, if I do a DTS "Copy objects and data between SQL
Server databases" of the offending database to a fresh database, the problem
goes away (without touching indexes or anything).
As a further test, I restored a backup of the offending database onto a
different server, tried the query and it never returned, did the DTS copy
objects, queried again and it's fine. Past experience has shown that this
fresh, fixed database works fine for a while but can re-offend after a good
bit more data are loaded. It won't always be feasible to try this silly DTS
trick. The query plans for the query in the two databases are quite different
but I don't know enough to discern what the problem might be from comparing
them.
I've searched around but cannot see a similar issue, but maybe I haven't hit
upon the right search keywords.
Has anyone heard of or encountered such a problem? or other suggestions?
thanks,
Eric
Eric
Run sp_updatestatistics stored procedure on destination server.
(For more details please refer to the BOL)
"Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
certain
> query works against some datasets within a couple tables, but mysteriously
> never finishes against other datasets, perhaps some recently loaded ones.
> It's mainly just one big table with some smaller auxiliary tables (used in
> this query).
> I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
INDEXDEFRAG
> without effect. However, if I do a DTS "Copy objects and data between SQL
> Server databases" of the offending database to a fresh database, the
problem
> goes away (without touching indexes or anything).
> As a further test, I restored a backup of the offending database onto a
> different server, tried the query and it never returned, did the DTS copy
> objects, queried again and it's fine. Past experience has shown that this
> fresh, fixed database works fine for a while but can re-offend after a
good
> bit more data are loaded. It won't always be feasible to try this silly
DTS
> trick. The query plans for the query in the two databases are quite
different
> but I don't know enough to discern what the problem might be from
comparing
> them.
> I've searched around but cannot see a similar issue, but maybe I haven't
hit
> upon the right search keywords.
> Has anyone heard of or encountered such a problem? or other suggestions?
> thanks,
> Eric
>
|||Hi Uri, many thanks for the reply. This indeed fixes the problem! Why is it
that I needed to do this when I have "auto update statistics" set to TRUE for
this database?
Is it a good idea to run sp_updatestats periodically regardless of the "auto
update statistics"?
Many thanks!
Eric
"Uri Dimant" wrote:

> Eric
> Run sp_updatestatistics stored procedure on destination server.
> (For more details please refer to the BOL)
>
>
> "Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
> news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> certain
> INDEXDEFRAG
> problem
> good
> DTS
> different
> comparing
> hit
>
>

Reasonable query never finishes

Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a certai
n
query works against some datasets within a couple tables, but mysteriously
never finishes against other datasets, perhaps some recently loaded ones.
It's mainly just one big table with some smaller auxiliary tables (used in
this query).
I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC INDEXDEFRAG
without effect. However, if I do a DTS "Copy objects and data between SQL
Server databases" of the offending database to a fresh database, the problem
goes away (without touching indexes or anything).
As a further test, I restored a backup of the offending database onto a
different server, tried the query and it never returned, did the DTS copy
objects, queried again and it's fine. Past experience has shown that this
fresh, fixed database works fine for a while but can re-offend after a good
bit more data are loaded. It won't always be feasible to try this silly DTS
trick. The query plans for the query in the two databases are quite differen
t
but I don't know enough to discern what the problem might be from comparing
them.
I've searched around but cannot see a similar issue, but maybe I haven't hit
upon the right search keywords.
Has anyone heard of or encountered such a problem? or other suggestions?
thanks,
EricEric
Run sp_updatestatistics stored procedure on destination server.
(For more details please refer to the BOL)
"Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
certain
> query works against some datasets within a couple tables, but mysteriously
> never finishes against other datasets, perhaps some recently loaded ones.
> It's mainly just one big table with some smaller auxiliary tables (used in
> this query).
> I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
INDEXDEFRAG
> without effect. However, if I do a DTS "Copy objects and data between SQL
> Server databases" of the offending database to a fresh database, the
problem
> goes away (without touching indexes or anything).
> As a further test, I restored a backup of the offending database onto a
> different server, tried the query and it never returned, did the DTS copy
> objects, queried again and it's fine. Past experience has shown that this
> fresh, fixed database works fine for a while but can re-offend after a
good
> bit more data are loaded. It won't always be feasible to try this silly
DTS
> trick. The query plans for the query in the two databases are quite
different
> but I don't know enough to discern what the problem might be from
comparing
> them.
> I've searched around but cannot see a similar issue, but maybe I haven't
hit
> upon the right search keywords.
> Has anyone heard of or encountered such a problem? or other suggestions?
> thanks,
> Eric
>|||Hi Uri, many thanks for the reply. This indeed fixes the problem! Why is it
that I needed to do this when I have "auto update statistics" set to TRUE fo
r
this database?
Is it a good idea to run sp_updatestats periodically regardless of the "auto
update statistics"?
Many thanks!
Eric
"Uri Dimant" wrote:

> Eric
> Run sp_updatestatistics stored procedure on destination server.
> (For more details please refer to the BOL)
>
>
> "Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
> news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> certain
> INDEXDEFRAG
> problem
> good
> DTS
> different
> comparing
> hit
>
>

Reasonable query never finishes

Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a certain
query works against some datasets within a couple tables, but mysteriously
never finishes against other datasets, perhaps some recently loaded ones.
It's mainly just one big table with some smaller auxiliary tables (used in
this query).
I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC INDEXDEFRAG
without effect. However, if I do a DTS "Copy objects and data between SQL
Server databases" of the offending database to a fresh database, the problem
goes away (without touching indexes or anything).
As a further test, I restored a backup of the offending database onto a
different server, tried the query and it never returned, did the DTS copy
objects, queried again and it's fine. Past experience has shown that this
fresh, fixed database works fine for a while but can re-offend after a good
bit more data are loaded. It won't always be feasible to try this silly DTS
trick. The query plans for the query in the two databases are quite different
but I don't know enough to discern what the problem might be from comparing
them.
I've searched around but cannot see a similar issue, but maybe I haven't hit
upon the right search keywords.
Has anyone heard of or encountered such a problem? or other suggestions?
thanks,
EricEric
Run sp_updatestatistics stored procedure on destination server.
(For more details please refer to the BOL)
"Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
certain
> query works against some datasets within a couple tables, but mysteriously
> never finishes against other datasets, perhaps some recently loaded ones.
> It's mainly just one big table with some smaller auxiliary tables (used in
> this query).
> I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
INDEXDEFRAG
> without effect. However, if I do a DTS "Copy objects and data between SQL
> Server databases" of the offending database to a fresh database, the
problem
> goes away (without touching indexes or anything).
> As a further test, I restored a backup of the offending database onto a
> different server, tried the query and it never returned, did the DTS copy
> objects, queried again and it's fine. Past experience has shown that this
> fresh, fixed database works fine for a while but can re-offend after a
good
> bit more data are loaded. It won't always be feasible to try this silly
DTS
> trick. The query plans for the query in the two databases are quite
different
> but I don't know enough to discern what the problem might be from
comparing
> them.
> I've searched around but cannot see a similar issue, but maybe I haven't
hit
> upon the right search keywords.
> Has anyone heard of or encountered such a problem? or other suggestions?
> thanks,
> Eric
>|||Hi Uri, many thanks for the reply. This indeed fixes the problem! Why is it
that I needed to do this when I have "auto update statistics" set to TRUE for
this database?
Is it a good idea to run sp_updatestats periodically regardless of the "auto
update statistics"?
Many thanks!
Eric
"Uri Dimant" wrote:
> Eric
> Run sp_updatestatistics stored procedure on destination server.
> (For more details please refer to the BOL)
>
>
> "Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
> news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> > Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
> certain
> > query works against some datasets within a couple tables, but mysteriously
> > never finishes against other datasets, perhaps some recently loaded ones.
> > It's mainly just one big table with some smaller auxiliary tables (used in
> > this query).
> >
> > I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
> INDEXDEFRAG
> > without effect. However, if I do a DTS "Copy objects and data between SQL
> > Server databases" of the offending database to a fresh database, the
> problem
> > goes away (without touching indexes or anything).
> >
> > As a further test, I restored a backup of the offending database onto a
> > different server, tried the query and it never returned, did the DTS copy
> > objects, queried again and it's fine. Past experience has shown that this
> > fresh, fixed database works fine for a while but can re-offend after a
> good
> > bit more data are loaded. It won't always be feasible to try this silly
> DTS
> > trick. The query plans for the query in the two databases are quite
> different
> > but I don't know enough to discern what the problem might be from
> comparing
> > them.
> >
> > I've searched around but cannot see a similar issue, but maybe I haven't
> hit
> > upon the right search keywords.
> >
> > Has anyone heard of or encountered such a problem? or other suggestions?
> >
> > thanks,
> > Eric
> >
>
>

really strange performance problem

hey
I have a sql2000 server SP3 and I mgrate a databse from SQL7. I run teo basically equal select: he first one 2 seconds the second one 58 minutes.....

select count(*) from uu_resume_ses_dummy_dummy
where substring(dominio,1,20)
not in (select substring(col018_dominio,1,20)
from iis_uu_diario_resume where substring(col018_dominio,1,20)
= substring(uu_resume_ses_dummy_dummy.dominio,1,20))
option (maxdop 1)

select count(*) from uu_resume_ses_dummy_dummy
where substring(dominio,1,30)
not in (select substring(col018_dominio,1,30)
from iis_uu_diario_resume where substring(col018_dominio,1,30)
= substring(uu_resume_ses_dummy_dummy.dominio,1,30))
option (maxdop 1)

the only differencei s that the substring range: 20 to 30. Notice that the limit is not fixed. SOmetimes the jump in execution time happende when I change from 90 top 91......
really I dont' know. (Fields ara varchar(90) but it was the same with varchar(255). the PLAN are exactly the same. in the second case the CPU was 50% fror 58 minutes fixed.
thanks for all the help (really needed)essentialy, for every record in the "uu_resume_ses_dummy_dummy" table you are looking at every record in the "iis_uu_diario_resume" table Using only one processor.

Since you will be looking at every record you have the potential of being delayed by locks, index leaf splits and other traffic. What happens if you run these selects on a quiet system. I suspect the time diffrence is small.|||I was runnng these queries both in a "busy" server (4 cpu, 4Gb RAM) and on a really quiet server (2 CPU, 4GB RAM) with same timing. Quiet server means that basically % of CPU without that select was netween 0 and 5%|||forgot things.

1) same times without the option of processing in one CPU only
2) both table are index on the specific fields.

what you say is ok. problem is:why almost identical queries have such a big big big difference in execution time?