Hidden Rectangle causing blank page.

Hello all,

I'm running into an issue with a report we have, I was hoping you may be able to help...

We have a report that contains a rectangle which has conditional visiblity. When the expression evaluates to true, the contents of the rectangle are indeed "hidden", but we are seeing a blank page.

The rectangle does NOT have page breaks selected before or after it. (and neither does the objects before or after that rectangle.)

Any ideas?

Is this when you are viewing or printing?

Make sure that the hidden rectangle is somewhere within the page bounds of your report (and include margins in your calculations). Just a guess, but give it a try.

Mike

hi pf usage

Hi all,
I have a 2-node SQL2000 SP4 cluster running on Server 2003 SP1 with 8GB RAM.
I have enabled AWE & PAE. The performance monitor shows a constant usage of
7.55GB of on the pagefile. Is this normal'
TIA!Yo have SP4. Did you apply KB 899761?
http://support.microsoft.com/kb/899761/
FIX: Not all memory is available when AWE is enabled on a computer that is
running a 32-bit version of SQL Server 2000 SP4
Use Performance Monitor to look into
SQLServer:Memory Manager - Total Server Memory (KB)
to verify SQL Server is using all the memory you are giving to it
Ben Nevarez, MCDBA, OCP
Database Administrator
"param@.community.nospam" wrote:
> Hi all,
> I have a 2-node SQL2000 SP4 cluster running on Server 2003 SP1 with 8GB RAM.
> I have enabled AWE & PAE. The performance monitor shows a constant usage of
> 7.55GB of on the pagefile. Is this normal'
> TIA!
>
>|||Yes, I have SP4 and have applied that hotfix. Perfmon shows 7534008 KB. What
could be up?
Thanks
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:81D4043B-3C8B-466C-A3E0-A5F3826E3F40@.microsoft.com...
> Yo have SP4. Did you apply KB 899761?
> http://support.microsoft.com/kb/899761/
> FIX: Not all memory is available when AWE is enabled on a computer that is
> running a 32-bit version of SQL Server 2000 SP4
> Use Performance Monitor to look into
> SQLServer:Memory Manager - Total Server Memory (KB)
> to verify SQL Server is using all the memory you are giving to it
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "param@.community.nospam" wrote:
>> Hi all,
>> I have a 2-node SQL2000 SP4 cluster running on Server 2003 SP1 with 8GB
>> RAM.
>> I have enabled AWE & PAE. The performance monitor shows a constant usage
>> of
>> 7.55GB of on the pagefile. Is this normal'
>> TIA!
>>|||OK, here is the weird part. I checked my 2nd node of the cluster and its PF
usage is only at about 400MB. What is weird is that I am running an
Active-Active mixed cluster. Server1 is running SQL2000 SP4 and has the
issue with high memory usage. Server2 is running SQL2005 SP1 and seems fine
on memory usage. Both the servers are identical machines.
TIA!
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:81D4043B-3C8B-466C-A3E0-A5F3826E3F40@.microsoft.com...
> Yo have SP4. Did you apply KB 899761?
> http://support.microsoft.com/kb/899761/
> FIX: Not all memory is available when AWE is enabled on a computer that is
> running a 32-bit version of SQL Server 2000 SP4
> Use Performance Monitor to look into
> SQLServer:Memory Manager - Total Server Memory (KB)
> to verify SQL Server is using all the memory you are giving to it
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "param@.community.nospam" wrote:
>> Hi all,
>> I have a 2-node SQL2000 SP4 cluster running on Server 2003 SP1 with 8GB
>> RAM.
>> I have enabled AWE & PAE. The performance monitor shows a constant usage
>> of
>> 7.55GB of on the pagefile. Is this normal'
>> TIA!
>>

Hey all / Running SQL 2000 and I need to re-index a db with a scri

I have the scripts but not sure how to run them... They are external scripts.
Trying to learn more about SQL Srvr.
You either open up Query Analyzer and paste the code in a window or use
oSQL. Both are documented in BooksOnLine but QA is probably easier for you.
Andrew J. Kelly SQL MVP
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:1BBC3024-B78C-436A-8A8B-424C6577AF0C@.microsoft.com...
>I have the scripts but not sure how to run them... They are external
>scripts.
> Trying to learn more about SQL Srvr.

Hey all / Running SQL 2000 and I need to re-index a db with a scri

I have the scripts but not sure how to run them... They are external scripts.
Trying to learn more about SQL Srvr.You either open up Query Analyzer and paste the code in a window or use
oSQL. Both are documented in BooksOnLine but QA is probably easier for you.
--
Andrew J. Kelly SQL MVP
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:1BBC3024-B78C-436A-8A8B-424C6577AF0C@.microsoft.com...
>I have the scripts but not sure how to run them... They are external
>scripts.
> Trying to learn more about SQL Srvr.|||Thanks Andrew
"Andrew J. Kelly" wrote:
> You either open up Query Analyzer and paste the code in a window or use
> oSQL. Both are documented in BooksOnLine but QA is probably easier for you.
> --
> Andrew J. Kelly SQL MVP
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:1BBC3024-B78C-436A-8A8B-424C6577AF0C@.microsoft.com...
> >I have the scripts but not sure how to run them... They are external
> >scripts.
> >
> > Trying to learn more about SQL Srvr.
>
>

Hey all / Running SQL 2000 and I need to re-index a db with a scri

I have the scripts but not sure how to run them... They are external scripts
.
Trying to learn more about SQL Srvr.You either open up Query Analyzer and paste the code in a window or use
oSQL. Both are documented in BooksOnLine but QA is probably easier for you.
Andrew J. Kelly SQL MVP
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:1BBC3024-B78C-436A-8A8B-424C6577AF0C@.microsoft.com...
>I have the scripts but not sure how to run them... They are external
>scripts.
> Trying to learn more about SQL Srvr.

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set

Hi,

I have a problem with linked servers.

I have an application running against a SQLServer 2005 Express. For some limitations, I had to access from the same application to another database, but I cannot change to another server.

So I have 2 created a second instances, where the first one refers the second one and I created synonyms in the first one to access to all the objects in the second one, to emulate a database in the first instances, but running on the second one. The final idea is to move to another server, but for the testing I use another instance.

But when I try to access to the aplication database, I hav the following error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

I searched solutions for this issue, but I only found to add SET ANSI_NULLS ON and SET ANSI_WARNINGS ON to my connection, before the queries, but I can't, because I cannot change the application.

If anyone can help me, I'd be veri greatfull

Best regards, ArielAriel,
I had a similar problem with SQL 2000 when I added a stored procedure to access the data on a linked server. I had created the stored proc through Query Analyzer and the work around was to create the stored proc through Enterprise Manager. Once I created within Enterprise Manager it worked.
A google search of this problem will provide results.|||The problem is the application have a lot of SPs, tables and views I have created the synonims.

The problem is only with the SPs?

If that, I can remove the synonims and create SPs that access the original ones.

Thanks, Ariel

Heterogeneous queries error

I'am attempting to modify an SMS 2003 web report to include data from a
linked SQL server. Running the query (report) in Query Analyzer works fine,
however, running the report via the web returns the error:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable these
options and then reissue your query.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 7405
Any ideas how to would fix this?
Inside your proc, add SET ANSI_WARNINGS ON to the beginning of your code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:D892A9F1-4184-479E-822F-A6F1939F85A8@.microsoft.com...
> I'am attempting to modify an SMS 2003 web report to include data from a
> linked SQL server. Running the query (report) in Query Analyzer works fine,
> however, running the report via the web returns the error:
> Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
> set for the connection. This ensures consistent query semantics. Enable these
> options and then reissue your query.
> Error Number: -2147217900
> Source: Microsoft OLE DB Provider for SQL Server
> Native Error: 7405
> Any ideas how to would fix this?
|||I'm not using a stored procedure. SMS 2003 stores the SQL query in the
database as a text field, not as a stored procedure.
"Tibor Karaszi" wrote:

> Inside your proc, add SET ANSI_WARNINGS ON to the beginning of your code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chad" <Chad@.discussions.microsoft.com> wrote in message
> news:D892A9F1-4184-479E-822F-A6F1939F85A8@.microsoft.com...
>
>
|||OK, let me re-phrase:
Preceding any other statement in your code, execute that SET command...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:00AB7DD9-148F-4602-9E46-63C34CA3F576@.microsoft.com...[vbcol=seagreen]
> I'm not using a stored procedure. SMS 2003 stores the SQL query in the
> database as a text field, not as a stored procedure.
> "Tibor Karaszi" wrote:

Heterogeneous queries error

I'am attempting to modify an SMS 2003 web report to include data from a
linked SQL server. Running the query (report) in Query Analyzer works fine,
however, running the report via the web returns the error:
---
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable these
options and then reissue your query.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 7405
---
Any ideas how to would fix this?Inside your proc, add SET ANSI_WARNINGS ON to the beginning of your code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:D892A9F1-4184-479E-822F-A6F1939F85A8@.microsoft.com...
> I'am attempting to modify an SMS 2003 web report to include data from a
> linked SQL server. Running the query (report) in Query Analyzer works fine,
> however, running the report via the web returns the error:
> ---
> Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
> set for the connection. This ensures consistent query semantics. Enable these
> options and then reissue your query.
> Error Number: -2147217900
> Source: Microsoft OLE DB Provider for SQL Server
> Native Error: 7405
> ---
> Any ideas how to would fix this?|||I'm not using a stored procedure. SMS 2003 stores the SQL query in the
database as a text field, not as a stored procedure.
"Tibor Karaszi" wrote:
> Inside your proc, add SET ANSI_WARNINGS ON to the beginning of your code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chad" <Chad@.discussions.microsoft.com> wrote in message
> news:D892A9F1-4184-479E-822F-A6F1939F85A8@.microsoft.com...
> > I'am attempting to modify an SMS 2003 web report to include data from a
> > linked SQL server. Running the query (report) in Query Analyzer works fine,
> > however, running the report via the web returns the error:
> >
> > ---
> > Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
> > set for the connection. This ensures consistent query semantics. Enable these
> > options and then reissue your query.
> >
> > Error Number: -2147217900
> > Source: Microsoft OLE DB Provider for SQL Server
> > Native Error: 7405
> > ---
> >
> > Any ideas how to would fix this?
>
>|||OK, let me re-phrase:
Preceding any other statement in your code, execute that SET command...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:00AB7DD9-148F-4602-9E46-63C34CA3F576@.microsoft.com...
> I'm not using a stored procedure. SMS 2003 stores the SQL query in the
> database as a text field, not as a stored procedure.
> "Tibor Karaszi" wrote:
> > Inside your proc, add SET ANSI_WARNINGS ON to the beginning of your code.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Chad" <Chad@.discussions.microsoft.com> wrote in message
> > news:D892A9F1-4184-479E-822F-A6F1939F85A8@.microsoft.com...
> > > I'am attempting to modify an SMS 2003 web report to include data from a
> > > linked SQL server. Running the query (report) in Query Analyzer works fine,
> > > however, running the report via the web returns the error:
> > >
> > > ---
> > > Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
> > > set for the connection. This ensures consistent query semantics. Enable these
> > > options and then reissue your query.
> > >
> > > Error Number: -2147217900
> > > Source: Microsoft OLE DB Provider for SQL Server
> > > Native Error: 7405
> > > ---
> > >
> > > Any ideas how to would fix this?
> >
> >
> >

heres a challenge for you all.

Ok, here's the problem. I have CA's Unicenter ServiceIT Enterprise Edition 5.0 running on a production box that has NT4 (SP6) and SQL server 2000 running on it.

Just before we continue, this is about backing up the database that ServiceIT connects to.

I know that you can schedule EM to make backup's of a specific database over a certain time, but this is part of the problem. What i have since discovered is that ServiceIT will not run on a database backed up and restored by EM.

It will however run on the restored backup created through the dos command pdm_backup (comes with ServiceIT).

What i am trying to figure out is to try a find a way that i can run a dos prompt command to do the following tasks at a specific time of day:

Stop the Paradigm Server Daemons (service)
Run a DBCC CHECKDB on the database AHDTEST50.
If there are no errors, create a verbose backup using the dos command pdm_backup -v -fC:\backup\[todaysdate]
If there are errors run a script to correct them, then run a backup
Restart the Paradigm Server DaemonsSchedule a batch file?|||can you set a batch file to do all of the above though?|||Only generating filenames with today's date (yyyymmdd) is usually a pain in batch (regional settings stuff like that) so I have a tendency to use a parameter instead. Next, I don't know how to check for errors the verbose backup might give; is there a log-file, would it suffice to do a find on it? I should think there's a way to cover that, so, other than that: I'd give it a go.|||Could you use something like:CREATE PROCEDURE Backup_ServiceIT
AS

DECLARE @.cBackup VARCHAR(500)
SET @.cBackup = pdm_backup -v -fC:\backup\' + Convert(CHAR(8), GetDate(), 112)

EXECUTE @.rc = master.dbo.xp_cmdshell 'NET STOP <paradigm service name>'
if 0 = @.rc DBCC CHECKDB ('AHDTEST50')
if 0 = @.@.error EXECUTE @.rc = master.dbo.xp_cmdshell @.cBackup
if 0 <> @.rc EXECUTE @.rc = master.dbo.xp_cmdshell '<fix error batch file>'
EXECUTE @.rc = master.dbo.xp_cmdshell 'NET START <paradigm service name>'

RETURN
GO-PatP|||Hey...when did you add your location?

http://www.ifb.net/webit/inveruri.htm

HELP--cannot shrink the transaction log

Hello:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980s
I have done both of those things--no success...
PLEASE HELP!!!
childofthe1980s
"Tibor Karaszi" wrote:

> You specify the *logical name of the file*. See the sysfiles table. Also, see
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>
|||DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
"Tibor Karaszi" wrote:

> Can you post the commands you tried to execute?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>
|||Where you IN the database in question? I believe that is required.
Roy Harvey
Beacon Falls, CT
On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
<childofthe1980s@.discussions.microsoft.com> wrote:

>Hello:
>I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>analyzer in SQL 2000 against the master database.
>What is the EXACT syntax? You see, the name of the database is JC and the
>log file is named GPSJClog.ldf. I have tried to place both names in the
>syntax of the statement but with no success. I keep getting a message saying
>that the file name is not in sysfiles.
>This is what I have tried:
>DBCC SHRINKFILE (GPJClog.ldf , 2)
>Please help!
>childofthe1980s
|||Nevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"Roy Harvey" wrote:

> Where you IN the database in question? I believe that is required.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> <childofthe1980s@.discussions.microsoft.com> wrote:
>
|||You needed to use the Logical name not the physical one. Drop the .ldf and
you should be golden.
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:C3E65A22-82FF-405A-8EC9-B770E5206E4E@.microsoft.com...[vbcol=seagreen]
> DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
>
> "Tibor Karaszi" wrote:
|||Not true. You can use either the ID or the LOGICAL name not the physical
name. And hopefully the db is not in FULL recovery mode or you just lost the
log chain with that truncate.
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...[vbcol=seagreen]
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
> it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
>
>
> "Roy Harvey" wrote:
|||No, Andrew, I tried using the logical file name without the ldf and it did
not work either. So, yes, the documentation needs updating.
"Andrew J. Kelly" wrote:

> Not true. You can use either the ID or the LOGICAL name not the physical
> name. And hopefully the db is not in FULL recovery mode or you just lost the
> log chain with that truncate.
> --
> Andrew J. Kelly SQL MVP
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
>
>

HELP--cannot shrink the transaction log

Hello:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sYou specify the *logical name of the file*. See the sysfiles table. Also, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> Hello:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s|||I have done both of those things--no success...
PLEASE HELP!!!
childofthe1980s
"Tibor Karaszi" wrote:
> You specify the *logical name of the file*. See the sysfiles table. Also, see
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> > Hello:
> >
> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> > analyzer in SQL 2000 against the master database.
> >
> > What is the EXACT syntax? You see, the name of the database is JC and the
> > log file is named GPSJClog.ldf. I have tried to place both names in the
> > syntax of the statement but with no success. I keep getting a message saying
> > that the file name is not in sysfiles.
> >
> > This is what I have tried:
> >
> > DBCC SHRINKFILE (GPJClog.ldf , 2)
> >
> > Please help!
> >
> > childofthe1980s
>|||Can you post the commands you tried to execute?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>I have done both of those things--no success...
> PLEASE HELP!!!
> childofthe1980s
> "Tibor Karaszi" wrote:
>> You specify the *logical name of the file*. See the sysfiles table. Also, see
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
>> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>> > Hello:
>> >
>> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>> > analyzer in SQL 2000 against the master database.
>> >
>> > What is the EXACT syntax? You see, the name of the database is JC and the
>> > log file is named GPSJClog.ldf. I have tried to place both names in the
>> > syntax of the statement but with no success. I keep getting a message saying
>> > that the file name is not in sysfiles.
>> >
>> > This is what I have tried:
>> >
>> > DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >
>> > Please help!
>> >
>> > childofthe1980s
>>|||DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
"Tibor Karaszi" wrote:
> Can you post the commands you tried to execute?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
> >I have done both of those things--no success...
> >
> > PLEASE HELP!!!
> >
> > childofthe1980s
> >
> > "Tibor Karaszi" wrote:
> >
> >> You specify the *logical name of the file*. See the sysfiles table. Also, see
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> >> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> >> > Hello:
> >> >
> >> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> >> > analyzer in SQL 2000 against the master database.
> >> >
> >> > What is the EXACT syntax? You see, the name of the database is JC and the
> >> > log file is named GPSJClog.ldf. I have tried to place both names in the
> >> > syntax of the statement but with no success. I keep getting a message saying
> >> > that the file name is not in sysfiles.
> >> >
> >> > This is what I have tried:
> >> >
> >> > DBCC SHRINKFILE (GPJClog.ldf , 2)
> >> >
> >> > Please help!
> >> >
> >> > childofthe1980s
> >>
> >>
>|||Where you IN the database in question? I believe that is required.
Roy Harvey
Beacon Falls, CT
On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
<childofthe1980s@.discussions.microsoft.com> wrote:
>Hello:
>I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>analyzer in SQL 2000 against the master database.
>What is the EXACT syntax? You see, the name of the database is JC and the
>log file is named GPSJClog.ldf. I have tried to place both names in the
>syntax of the statement but with no success. I keep getting a message saying
>that the file name is not in sysfiles.
>This is what I have tried:
>DBCC SHRINKFILE (GPJClog.ldf , 2)
>Please help!
>childofthe1980s|||Nevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"Roy Harvey" wrote:
> Where you IN the database in question? I believe that is required.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> <childofthe1980s@.discussions.microsoft.com> wrote:
> >Hello:
> >
> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> >analyzer in SQL 2000 against the master database.
> >
> >What is the EXACT syntax? You see, the name of the database is JC and the
> >log file is named GPSJClog.ldf. I have tried to place both names in the
> >syntax of the statement but with no success. I keep getting a message saying
> >that the file name is not in sysfiles.
> >
> >This is what I have tried:
> >
> >DBCC SHRINKFILE (GPJClog.ldf , 2)
> >
> >Please help!
> >
> >childofthe1980s
>|||You needed to use the Logical name not the physical one. Drop the .ldf and
you should be golden.
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:C3E65A22-82FF-405A-8EC9-B770E5206E4E@.microsoft.com...
> DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
>
> "Tibor Karaszi" wrote:
>> Can you post the commands you tried to execute?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message
>> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>> >I have done both of those things--no success...
>> >
>> > PLEASE HELP!!!
>> >
>> > childofthe1980s
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> You specify the *logical name of the file*. See the sysfiles table.
>> >> Also, see
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>> >> > Hello:
>> >> >
>> >> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE
>> >> > in query
>> >> > analyzer in SQL 2000 against the master database.
>> >> >
>> >> > What is the EXACT syntax? You see, the name of the database is JC
>> >> > and the
>> >> > log file is named GPSJClog.ldf. I have tried to place both names in
>> >> > the
>> >> > syntax of the statement but with no success. I keep getting a
>> >> > message saying
>> >> > that the file name is not in sysfiles.
>> >> >
>> >> > This is what I have tried:
>> >> >
>> >> > DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >> >
>> >> > Please help!
>> >> >
>> >> > childofthe1980s
>> >>
>> >>
>>|||Not true. You can use either the ID or the LOGICAL name not the physical
name. And hopefully the db is not in FULL recovery mode or you just lost the
log chain with that truncate.
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
> it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
>
>
> "Roy Harvey" wrote:
>> Where you IN the database in question? I believe that is required.
>> Roy Harvey
>> Beacon Falls, CT
>> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
>> <childofthe1980s@.discussions.microsoft.com> wrote:
>> >Hello:
>> >
>> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
>> >query
>> >analyzer in SQL 2000 against the master database.
>> >
>> >What is the EXACT syntax? You see, the name of the database is JC and
>> >the
>> >log file is named GPSJClog.ldf. I have tried to place both names in the
>> >syntax of the statement but with no success. I keep getting a message
>> >saying
>> >that the file name is not in sysfiles.
>> >
>> >This is what I have tried:
>> >
>> >DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >
>> >Please help!
>> >
>> >childofthe1980s|||No, Andrew, I tried using the logical file name without the ldf and it did
not work either. So, yes, the documentation needs updating.
"Andrew J. Kelly" wrote:
> Not true. You can use either the ID or the LOGICAL name not the physical
> name. And hopefully the db is not in FULL recovery mode or you just lost the
> log chain with that truncate.
> --
> Andrew J. Kelly SQL MVP
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
> > Nevermind. I figured it out.
> >
> > I had to run the following scripts against each of the databases:
> >
> > BACKUP LOG JC with TRUNCATE_ONLY
> > DBCC SHRINKFILE (2, 20)
> >
> > You see, the support documentation on the web did not say that you have to
> > take the fileID of the database--2 in the syntax above-- in order to make
> > it
> > work! You get the number 2 from running the following query against the
> > database:
> >
> > select * from sysfiles
> >
> > It would have been nice if this had been in the documentation!
> >
> > Thanks!
> >
> > childofthe1980s
> >
> >
> >
> >
> >
> > "Roy Harvey" wrote:
> >
> >> Where you IN the database in question? I believe that is required.
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
> >> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> >> <childofthe1980s@.discussions.microsoft.com> wrote:
> >>
> >> >Hello:
> >> >
> >> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
> >> >query
> >> >analyzer in SQL 2000 against the master database.
> >> >
> >> >What is the EXACT syntax? You see, the name of the database is JC and
> >> >the
> >> >log file is named GPSJClog.ldf. I have tried to place both names in the
> >> >syntax of the statement but with no success. I keep getting a message
> >> >saying
> >> >that the file name is not in sysfiles.
> >> >
> >> >This is what I have tried:
> >> >
> >> >DBCC SHRINKFILE (GPJClog.ldf , 2)
> >> >
> >> >Please help!
> >> >
> >> >childofthe1980s
> >>
>
>|||> No, Andrew, I tried using the logical file name without the ldf and it did
> not work either. So, yes, the documentation needs updating.
The documentation clearly states that you can use *either* the logical filename or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical filename. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usage of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
USE master
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:04A6393F-154E-4A1A-946B-0768F7B47F85@.microsoft.com...
> No, Andrew, I tried using the logical file name without the ldf and it did
> not work either. So, yes, the documentation needs updating.
> "Andrew J. Kelly" wrote:
>> Not true. You can use either the ID or the LOGICAL name not the physical
>> name. And hopefully the db is not in FULL recovery mode or you just lost the
>> log chain with that truncate.
>> --
>> Andrew J. Kelly SQL MVP
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
>> > Nevermind. I figured it out.
>> >
>> > I had to run the following scripts against each of the databases:
>> >
>> > BACKUP LOG JC with TRUNCATE_ONLY
>> > DBCC SHRINKFILE (2, 20)
>> >
>> > You see, the support documentation on the web did not say that you have to
>> > take the fileID of the database--2 in the syntax above-- in order to make
>> > it
>> > work! You get the number 2 from running the following query against the
>> > database:
>> >
>> > select * from sysfiles
>> >
>> > It would have been nice if this had been in the documentation!
>> >
>> > Thanks!
>> >
>> > childofthe1980s
>> >
>> >
>> >
>> >
>> >
>> > "Roy Harvey" wrote:
>> >
>> >> Where you IN the database in question? I believe that is required.
>> >>
>> >> Roy Harvey
>> >> Beacon Falls, CT
>> >>
>> >> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
>> >> <childofthe1980s@.discussions.microsoft.com> wrote:
>> >>
>> >> >Hello:
>> >> >
>> >> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
>> >> >query
>> >> >analyzer in SQL 2000 against the master database.
>> >> >
>> >> >What is the EXACT syntax? You see, the name of the database is JC and
>> >> >the
>> >> >log file is named GPSJClog.ldf. I have tried to place both names in the
>> >> >syntax of the statement but with no success. I keep getting a message
>> >> >saying
>> >> >that the file name is not in sysfiles.
>> >> >
>> >> >This is what I have tried:
>> >> >
>> >> >DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >> >
>> >> >Please help!
>> >> >
>> >> >childofthe1980s
>> >>
>>

HELP--cannot shrink the transaction log

Hello:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message sayin
g
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sYou specify the *logical name of the file*. See the sysfiles table. Also, se
e
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> Hello:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in que
ry
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message say
ing
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s|||I have done both of those things--no success...
PLEASE HELP!!!
childofthe1980s
"Tibor Karaszi" wrote:

> You specify the *logical name of the file*. See the sysfiles table. Also,
see
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in mes
sage
> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>|||Can you post the commands you tried to execute?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...[vbcol=seagreen]
>I have done both of those things--no success...
> PLEASE HELP!!!
> childofthe1980s
> "Tibor Karaszi" wrote:
>|||DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
"Tibor Karaszi" wrote:

> Can you post the commands you tried to execute?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in mes
sage
> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>|||Where you IN the database in question? I believe that is required.
Roy Harvey
Beacon Falls, CT
On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
<childofthe1980s@.discussions.microsoft.com> wrote:

>Hello:
>I'm trying to shrink the transaction log by running DBCC SHRINKFILE in quer
y
>analyzer in SQL 2000 against the master database.
>What is the EXACT syntax? You see, the name of the database is JC and the
>log file is named GPSJClog.ldf. I have tried to place both names in the
>syntax of the statement but with no success. I keep getting a message sayi
ng
>that the file name is not in sysfiles.
>This is what I have tried:
>DBCC SHRINKFILE (GPJClog.ldf , 2)
>Please help!
>childofthe1980s|||Nevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"Roy Harvey" wrote:

> Where you IN the database in question? I believe that is required.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> <childofthe1980s@.discussions.microsoft.com> wrote:
>
>|||You needed to use the Logical name not the physical one. Drop the .ldf and
you should be golden.
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:C3E65A22-82FF-405A-8EC9-B770E5206E4E@.microsoft.com...[vbcol=seagreen]
> DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
>
> "Tibor Karaszi" wrote:
>|||Not true. You can use either the ID or the LOGICAL name not the physical
name. And hopefully the db is not in FULL recovery mode or you just lost the
log chain with that truncate.
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...[vbcol=seagreen]
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
> it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
>
>
> "Roy Harvey" wrote:
>|||No, Andrew, I tried using the logical file name without the ldf and it did
not work either. So, yes, the documentation needs updating.
"Andrew J. Kelly" wrote:

> Not true. You can use either the ID or the LOGICAL name not the physical
> name. And hopefully the db is not in FULL recovery mode or you just lost t
he
> log chain with that truncate.
> --
> Andrew J. Kelly SQL MVP
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
>
>

Help: slow sp execution using job

A scheduled job to execute a stored procedure to get data out of one table and insert into another blank table using cursor.
Running this stored procedure in Query Analyzer takes 1.5 mins to finish, however, running a job with the exactly same execute command takes 1 hour to finish.
Please help !!!!!Why dont you create a DTS package to transfer the data between the tables and schedule the package|||Thanks. I never tried DTS. But I need some complicated logic to define the data partition I need to transfer before the data is transfered. I am not sure if DTS can include this logic by a programmable way in its process to set the transfer criteria.
Regarding to the slow problem, I found the answer from someone else's post weeks ago. Basically, I put "set nocount on" in front of each exec and it speeded up the execution.

Originally posted by pcmbalaji
Why dont you create a DTS package to transfer the data between the tables and schedule the package

Help: Replication stops with no error or warnings!

hi all,
I have sql server 2005 sp2 running transactional replication between 2
production servers on different domains. Every once in a while
transaction stop being delivered to the target with no warnings or
errors being generated by any of the servers involved. Using
replication monitor on the the publisher and viewing the tab "
Publisher to Distributor" shows transactions being delivered
successfully ("n transaction(s) with n command(s) were delivered
successfully") but the transactions do not show up on the target
server?
Any thoughts much appreciated as the only resolution i've found is to
wait until off hours and reinitialize the subscriptions. i'm going to
have to put a custom alert system in place to check table counts but i
really need to find the cause of this.
-Thanks! DF
Difficult to tell exactly what is getting replicated in repl monitor. Best
tool to troubleshoot is to run profiler on subscriber and see if you can find
anything. Best to do after hours if you can repeat problem w/ just you as
user.
What is your distribution agent profile? How did you initialize subscriber -
snapshot agent or backup/restore? If backup init - make sure you disable
triggers or set to NFR and drop FK's at subscriber. Perhaps your agent is
ignoring errors caused by FK constraints and not delivering rows changes...
ChrisB MCDBA
MSSQLConsulting.com
"dfateman@.yahoo.com" wrote:

> hi all,
> I have sql server 2005 sp2 running transactional replication between 2
> production servers on different domains. Every once in a while
> transaction stop being delivered to the target with no warnings or
> errors being generated by any of the servers involved. Using
> replication monitor on the the publisher and viewing the tab "
> Publisher to Distributor" shows transactions being delivered
> successfully ("n transaction(s) with n command(s) were delivered
> successfully") but the transactions do not show up on the target
> server?
> Any thoughts much appreciated as the only resolution i've found is to
> wait until off hours and reinitialize the subscriptions. i'm going to
> have to put a custom alert system in place to check table counts but i
> really need to find the cause of this.
> -Thanks! DF
>

Help: Query Timeout Expired

Hi,
I have a long running view (counting over 2,000,000 records) that regurns
"[ODBC SQL Server Driver] Timeout Expired" error after about 30 seconds.
Can somebody tell me how to increase the timeout period for running View?
Thanks in advance.
Regards
YeeYee,
were are you running the view from , EM? If you are don't. Try running it
from QA.
Timeouts are a client connection issue and can be adjusted via your ODBC
connection details, they are not a server problem.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.aspx. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Yee CN" <nospamxx_cn.yee@.attic-solutions.com> wrote in message
news:eZsXYag6DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a long running view (counting over 2,000,000 records) that regurns
> "[ODBC SQL Server Driver] Timeout Expired" error after about 30 seconds.
> Can somebody tell me how to increase the timeout period for running View?
> Thanks in advance.
> Regards
> Yee
>|||Hi Yee:
The timeout may haeepn in several place. You can change it by T-SQL for
database or in the ADO for for ODBC.
Because you do not let us know what kind of operation you are doing.
What I can do is give some suggestion.
For database, you can type "timeout" in book online then you can get
information of how to set the timeout property in diffirent situation
For ODBC you can see this link and see the timeout part.
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003JUL.1033/dnodbc/html/odbcsql.htm
Best Wishes
Wei Ci Zhou|||I was running it in EM. Just tried with QA, still the same problem.
I was managed to get it to run once earlier on though. It seems that the
timing the first row got returned is important here.
Regards,
Yee
"Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in
message news:eGGXBsg6DHA.2056@.TK2MSFTNGP10.phx.gbl...
> Yee,
> were are you running the view from , EM? If you are don't. Try running
it
> from QA.
> Timeouts are a client connection issue and can be adjusted via your ODBC
> connection details, they are not a server problem.
>
> --
> I hope this helps
> regards
> Greg O MCSD
> http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe
Documentation
> Builder, the quickest way to document your database
> http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
> Property Extended properties manager for SQL 2000
> http://www.ag-software.com/IconExtractionProgram.aspx. Free icon
extraction
> program
> http://www.ag-software.com. Free programming tools
>
> "Yee CN" <nospamxx_cn.yee@.attic-solutions.com> wrote in message
> news:eZsXYag6DHA.2656@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I have a long running view (counting over 2,000,000 records) that
regurns
> > "[ODBC SQL Server Driver] Timeout Expired" error after about 30 seconds.
> >
> > Can somebody tell me how to increase the timeout period for running
View?
> >
> > Thanks in advance.
> >
> > Regards
> > Yee
> >
> >
>|||I was opening the view from Enterprise Manager, then from Query Analyser.
Both gives the same error. I tried various things suggested in the help
file, including using sp_configure.
I also found the following from book online. The error message is what I
got - but I could not find the Tool->option screen mentioned below. Can you
shed any light?
----
--
ODBC error text
[Microsoft][ODBC SQL Server Driver]Timeout expired.
Explanation
The timeout can occur when you're updating the database with any
Transact-SQL changes.
Action
a.. Try again later to save the diagram or selected tables.
b.. Save a change script and apply it to the database at a later time.
c.. Increase the SQL Query Time-out value and try to save the diagram or
selected tables again.
To increase the SQL Query Time-out value
1.. From the Tools menu, choose Options.
2.. In the left pane, click Data Tools, and then click Data View.
3.. Type a new value in the SQL query time-out box.
----
--
Also the link that you suggested:
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003JUL.1033/dnodbc/html/odbcsql.htm
- can you tell me how to get there?
Thanks a lot for your help.
Best regards,
Yee
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:eBoSXfh6DHA.3008@.TK2MSFTNGP09.phx.gbl...
> Hi Yee:
> The timeout may haeepn in several place. You can change it by T-SQL
for
> database or in the ADO for for ODBC.
> Because you do not let us know what kind of operation you are doing.
> What I can do is give some suggestion.
> For database, you can type "timeout" in book online then you can get
> information of how to set the timeout property in diffirent situation
> For ODBC you can see this link and see the timeout part.
> Best Wishes
> Wei Ci Zhou
>

Help: Query Timeout Expired

Hi,
I have a long running view (counting over 2,000,000 records) that regurns
"[ODBC SQL Server Driver] Timeout Expired" error after about 30 seconds.
Can somebody tell me how to increase the timeout period for running View?
Thanks in advance.
Regards
YeeYee,
were are you running the view from , EM? If you are don't. Try running it
from QA.
Timeouts are a client connection issue and can be adjusted via your ODBC
connection details, they are not a server problem.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.aspx. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Yee CN" <nospamxx_cn.yee@.attic-solutions.com> wrote in message
news:eZsXYag6DHA.2656@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi,
> I have a long running view (counting over 2,000,000 records) that regurns
> "[ODBC SQL Server Driver] Timeout Expired" error after about 30 seconds.
> Can somebody tell me how to increase the timeout period for running View?
> Thanks in advance.
> Regards
> Yee
>

---

|||Hi Yee:
The timeout may haeepn in several place. You can change it by T-SQL for
database or in the ADO for for ODBC.
Because you do not let us know what kind of operation you are doing.
What I can do is give some suggestion.
For database, you can type "timeout" in book online then you can get
information of how to set the timeout property in diffirent situation
For ODBC you can see this link and see the timeout part.
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003JUL.1033/dnodbc/html/odbcsql.htm
Best Wishes
Wei Ci Zhou|||I was running it in EM. Just tried with QA, still the same problem.
I was managed to get it to run once earlier on though. It seems that the
timing the first row got returned is important here.
Regards,
Yee
"Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in
message news:eGGXBsg6DHA.2056@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Yee,
> were are you running the view from , EM? If you are don't. Try running

---

it

quote:

---

> from QA.
> Timeouts are a client connection issue and can be adjusted via your ODBC
> connection details, they are not a server problem.
>
> --
> I hope this helps
> regards
> Greg O MCSD
> http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe

---

Documentation

quote:

---

> Builder, the quickest way to document your database
> http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
> Property Extended properties manager for SQL 2000
> http://www.ag-software.com/IconExtractionProgram.aspx. Free icon

---

extraction

quote:

---

> program
> http://www.ag-software.com. Free programming tools
>
> "Yee CN" <nospamxx_cn.yee@.attic-solutions.com> wrote in message
> news:eZsXYag6DHA.2656@.TK2MSFTNGP11.phx.gbl...
regurns[QUOTE]
View?[QUOTE]
>

---

|||I was opening the view from Enterprise Manager, then from Query Analyser.
Both gives the same error. I tried various things suggested in the help
file, including using sp_configure.
I also found the following from book online. The error message is what I
got - but I could not find the Tool->option screen mentioned below. Can you
shed any light?
----
--
ODBC error text
[Microsoft][ODBC SQL Server Driver]Timeout expired.
Explanation
The timeout can occur when you're updating the database with any
Transact-SQL changes.
Action
a.. Try again later to save the diagram or selected tables.
b.. Save a change script and apply it to the database at a later time.
c.. Increase the SQL Query Time-out value and try to save the diagram or
selected tables again.
To increase the SQL Query Time-out value
1.. From the Tools menu, choose Options.
2.. In the left pane, click Data Tools, and then click Data View.
3.. Type a new value in the SQL query time-out box.
----
--
Also the link that you suggested:
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003JUL.1033/dnodbc/html/odbcsql.htm
- can you tell me how to get there?
Thanks a lot for your help.
Best regards,
Yee
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:eBoSXfh6DHA.3008@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Yee:
> The timeout may haeepn in several place. You can change it by T-SQL

---

for

quote:

---

> database or in the ADO for for ODBC.
> Because you do not let us know what kind of operation you are doing.
> What I can do is give some suggestion.
> For database, you can type "timeout" in book online then you can get
> information of how to set the timeout property in diffirent situation
> For ODBC you can see this link and see the timeout part.
> Best Wishes
> Wei Ci Zhou
>

---

Help: GetTextExtentPointIX could not be located?

I experienced the exact same problem. I was running Win2K & Sql Server sp3a.
Everything was fine. I upgraded
to Win Server 2003. I got warned about running Sql Server 2000 sp2 or lower.
I continued. Upgraded Win Server
2003 with all current critical patches. I run Sql Server 2003. Try to open u
p DTS designer and get GetTextExtentPointI
could not be located in msdart.dll. Try to open a table and I get "Unknown e
rror: 8007007F".
msdart.dll ver is 2.80.1022.0Same problem for me on 3 servers that I upgraded from Windows 2000 Server to
Windows 2003 Server. The SQL Enterprise Manager's DTS Package Designer wil
l complain about not finding GetTextExtentPointI in MSDART.DLL. I also can'
t create a .UDL file anymor
e and get the same error message.
My msdart.dll version is 2.80.1022.0.|||Hello,
Thanks for your post. What is the version of SQL Server (Service pack) on
your side? Based on my experience, MSDART.DLL is a MDAC component. Please
try to run MDAC component checker which is downloaded from
MDAC Utility: Component Checker
http://www.microsoft.com/downloads/...8DF6-4A21-4B43-
BF53-14332EF092C9&displaylang=en
Check to see if there are some different dlls were overwritten by 3rd party
software.
Also, in order to concentrate fully on your issue, it is best that you can
post it as a separate thread. On that way each issue can receive full
attention and will also make the thread more clear and consistent for
others reference. Please feel free to open a new post for this issue and we
will be very glad to work with you. Thanks for your understanding.
Thanks again for posting in community.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I've figured out a fix to this problem.
On a machine running Windows 2003 Server that was upgraded from Windows 2000
Server, look for OLEDB32.DLL in "c:\Program Files\Common Files\System\Ole
DB".
Check the version of OLEDB32.DLL. If it is the correct version for Windows
2003 Server, it should be (at least) version 2.80.1022.0 according to the
MDAC 2.8 manifest.
On my servers that were upgraded from Windows 2000 Server to Windows 2003
Server, that OLEDB32.DLL file was version 2.71.9042.0. The wrong version.
I copied the OLEDB32.DLL that was version 2.8.1022.0 to c:\Program
Files\Common Files\System\Ole DB on the upgraded machines, and it seems to
have solved the "The procedure entry point GetTextExtentPointI could not be
located in the DLL MSDART.dll" problem.
The correct OLEDB32.DLL can be found from the MDAC 2.8 redistributable, or
from a cleanly installed Windows 2003 Server.
""Yuan Shao"" <v-yshao@.online.microsoft.com> wrote in message
news:3AZnnvp%23DHA.2848@.cpmsftngxa06.phx.gbl...
> Hello,
> Thanks for your post. What is the version of SQL Server (Service pack) on
> your side? Based on my experience, MSDART.DLL is a MDAC component. Please
> try to run MDAC component checker which is downloaded from
> MDAC Utility: Component Checker
>
http://www.microsoft.com/downloads/...8DF6-4A21-4B43-
> BF53-14332EF092C9&displaylang=en
> Check to see if there are some different dlls were overwritten by 3rd
party
> software.
> Also, in order to concentrate fully on your issue, it is best that you can
> post it as a separate thread. On that way each issue can receive full
> attention and will also make the thread more clear and consistent for
> others reference. Please feel free to open a new post for this issue and
we
> will be very glad to work with you. Thanks for your understanding.
> Thanks again for posting in community.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||

quote:

---

Originally posted by Scot Kelly
I've figured out a fix to this problem.
On a machine running Windows 2003 Server that was upgraded from Windows 2000
Server, look for OLEDB32.DLL in "c:\Program Files\Common Files\System\Ole
DB".
Check the version of OLEDB32.DLL. If it is the correct version for Windows
2003 Server, it should be (at least) version 2.80.1022.0 according to the
MDAC 2.8 manifest.
On my servers that were upgraded from Windows 2000 Server to Windows 2003
Server, that OLEDB32.DLL file was version 2.71.9042.0. The wrong version.
I copied the OLEDB32.DLL that was version 2.8.1022.0 to c:\Program
Files\Common Files\System\Ole DB on the upgraded machines, and it seems to
have solved the "The procedure entry point GetTextExtentPointI could not be
located in the DLL MSDART.dll" problem.
The correct OLEDB32.DLL can be found from the MDAC 2.8 redistributable, or
from a cleanly installed Windows 2003 Server.
""Yuan Shao"" <v-yshao@.online.microsoft.com> wrote in message
news:3AZnnvp%23DHA.2848@.cpmsftngxa06.phx.gbl...
> Hello,
> Thanks for your post. What is the version of SQL Server (Service pack) on
> your side? Based on my experience, MSDART.DLL is a MDAC component. Please
> try to run MDAC component checker which is downloaded from
> MDAC Utility: Component Checker
>
http://www.microsoft.com/downloads/...8DF6-4A21-4B43-
> BF53-14332EF092C9&displaylang=en
> Check to see if there are some different dlls were overwritten by 3rd
party
> software.
> Also, in order to concentrate fully on your issue, it is best that you can
> post it as a separate thread. On that way each issue can receive full
> attention and will also make the thread more clear and consistent for
> others reference. Please feel free to open a new post for this issue and
we
> will be very glad to work with you. Thanks for your understanding.
> Thanks again for posting in community.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>

---

|||Hi,
I download and installed MDAC_TYP.EXE and mdac28sdk.msi. However, I still ha
ve older version of oledb32.dll. Is there a quick to get the latest version?
Thanks.
Chris

Help: CHECKDB gives errors I can't fix

Hi,
I've got what appears to be a corrupt database, (SQL 7 SP4) running CHECKDB on it returns lots of errors, the first being:
Msg 8966, Level 16, State 5, Server 01AW01, Procedure , Line 3
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not read and latch page
(1:177936) with latch type SH. PFS failed.
I switched the database to single user, and tried various repair flags on CHECKDB, and I always seem to get the same errors, and it returns lots of:
'The repair level on the DBCC statement caused this repair to be bypassed.'
Any ideas on where to start on this.. Its a customers database, I don't have information on what backups they have etc.. and its probably problems in the only large table in the database (some 2.5 million records, and 900Mb on disk, with 400Mb index).. Could this be a resource related problem somehow 'Hopefully you get some specific suggestions about your message. Below you
find my general recommendations. Note, however, that this *might* not
actually be a corrupt database (I can't say for sure) so opening a case with
MS might be a better option than start with the restore route (also search
Kb etc first):
Here are the general recommendations for handling a suspect or corrupt
database:
0. Ensure you have a backup strategy that you can use to recover from
hardware failures (including corruption). I recommend performing both
database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books Online and
KB for the error numbers that CHECKDB gives you. There might be specific
info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.;
search Books Online and KB for those errors. You don't want this to happen
again! If the database is suspect, the file might have been in use by for
instance an anti-virus program and restarting SQL Server might be all that
is needed - but you still want to read logs etc to find out what happened.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of
course. If the database is suspect, then the NO_TRUNCATE option for the
RESTORE command must be used. Also, you might want to do a file backup of
the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per
step 4, then you will most probably have zero dataloss. You should restore
the latest clean database backup and the subsequent log backups including
the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be a
secondary option but this will often result in loss of data. Additional
solutions, depending on the errors, may be to manually rebuild non-clustered
indexes, manually drop and reload a table if the data is static, and so on.
If the database is suspect, a secondary option can be to try to "un-suspect"
the database using sp_resetstatus. Read about it (books online, KB, google
etc). It might help but if the database is too damaged, it might just pop
back to suspect again. There's also something called "emergency mode" which
is a "panic" status you can set in order to try to get data out of a damaged
database. I think the name of that option speaks for itself. Again search
the net for info.
If you feel uncertain with above steps, I recommend letting MS hand-hold you
through the steps appropriate for your particular situation.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:BC4EEEE9-BA17-416F-8C9A-8DED0C6AED3F@.microsoft.com...
> Hi,
> I've got what appears to be a corrupt database, (SQL 7 SP4) running
CHECKDB on it returns lots of errors, the first being:
> Msg 8966, Level 16, State 5, Server 01AW01, Procedure , Line 3
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not read and latch
page
> (1:177936) with latch type SH. PFS failed.
> I switched the database to single user, and tried various repair flags on
CHECKDB, and I always seem to get the same errors, and it returns lots of:
> 'The repair level on the DBCC statement caused this repair to be
bypassed.'
> Any ideas on where to start on this.. Its a customers database, I don't
have information on what backups they have etc.. and its probably problems
in the only large table in the database (some 2.5 million records, and 900Mb
on disk, with 400Mb index).. Could this be a resource related problem
somehow '
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0058_01C3BD8A.EDA4E890
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Hi Kevin,
I think that this is probably a corruption in the database. This error =message indicates that DBCC was not able to read (or latch) page 177936 =in your customer's database. The page type in question (PFS) is a =system allocation page that tracks database free space and some other =state information. This page is actually used to drive the DBCC scan, =so an error here is pretty severe. This is made worse by the fact that =DBCC can't repair this type of page if there is a problem.
Without more error context it's hard for me to say why we can't read =this page: it could be hardware causing a bad read from disk, a bad =page header that resulted from a page corruption, etc.
I completely agree with Tibor that you should restore the database, =perhaps pairing that with a case with PSS to investigate root cause.
Thanks,
Ryan Stonecipher
SQL Server Storage Engine (DBCC)
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:einCq2YvDHA.2712@.tk2msftngp13.phx.gbl...
Hopefully you get some specific suggestions about your message. Below =you
find my general recommendations. Note, however, that this *might* not
actually be a corrupt database (I can't say for sure) so opening a =case with
MS might be a better option than start with the restore route (also =search
Kb etc first):
Here are the general recommendations for handling a suspect or corrupt
database:
0. Ensure you have a backup strategy that you can use to recover from
hardware failures (including corruption). I recommend performing both
database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books =Online and
KB for the error numbers that CHECKDB gives you. There might be =specific
info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.;
search Books Online and KB for those errors. You don't want this to =happen
again! If the database is suspect, the file might have been in use by =for
instance an anti-virus program and restarting SQL Server might be all =that
is needed - but you still want to read logs etc to find out what =happened.
3. If there is a hardware problem, ensure the faulty hardware is =replaced.
4. Backup the log. This assumes that log backup schedule is in place, =of
course. If the database is suspect, then the NO_TRUNCATE option for =the
RESTORE command must be used. Also, you might want to do a file backup =of
the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log =as per
step 4, then you will most probably have zero dataloss. You should =restore
the latest clean database backup and the subsequent log backups =including
the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be =a
secondary option but this will often result in loss of data. =Additional
solutions, depending on the errors, may be to manually rebuild =non-clustered
indexes, manually drop and reload a table if the data is static, and =so on.
If the database is suspect, a secondary option can be to try to ="un-suspect"
the database using sp_resetstatus. Read about it (books online, KB, =google
etc). It might help but if the database is too damaged, it might just =pop
back to suspect again. There's also something called "emergency mode" =which
is a "panic" status you can set in order to try to get data out of a =damaged
database. I think the name of that option speaks for itself. Again =search
the net for info.
If you feel uncertain with above steps, I recommend letting MS =hand-hold you
through the steps appropriate for your particular situation.
-- Tibor Karaszi, SQL Server MVP
Archive at:
=http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:BC4EEEE9-BA17-416F-8C9A-8DED0C6AED3F@.microsoft.com...
> Hi,
>
> I've got what appears to be a corrupt database, (SQL 7 SP4) running
CHECKDB on it returns lots of errors, the first being:
>
> Msg 8966, Level 16, State 5, Server 01AW01, Procedure , Line 3
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not read and =latch
page
> (1:177936) with latch type SH. PFS failed.
>
> I switched the database to single user, and tried various repair =flags on
CHECKDB, and I always seem to get the same errors, and it returns lots =of:
>
> 'The repair level on the DBCC statement caused this repair to be
bypassed.'
>
> Any ideas on where to start on this.. Its a customers database, I =don't
have information on what backups they have etc.. and its probably =problems
in the only large table in the database (some 2.5 million records, and =900Mb
on disk, with 400Mb index).. Could this be a resource related problem
somehow '
>
>
>
>
--=_NextPart_000_0058_01C3BD8A.EDA4E890
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi Kevin,
I think that this is probably a =corruption in the database. This error message indicates that DBCC was not =able to read (or latch) page 177936 in your customer's database. The page =type in question (PFS) is a system allocation page that tracks database free =space and some other state information. This page is actually used to drive =the DBCC scan, so an error here is pretty severe. This is made worse by the =fact that DBCC can't repair this type of page if there is a =problem.
Without more error context it's =hard for me to say why we can't read this page: it could be hardware causing a =bad read from disk, a bad page header that resulted from a page corruption, etc.
I completely agree with Tibor =that you should restore the database, perhaps pairing that with a case with PSS =to investigate root cause.
Thanks,
Ryan Stonecipher
SQL Server Storage Engine (DBCC)
"Tibor Karaszi" wrote in message news:einCq2YvDHA.2712=@.tk2msftngp13.phx.gbl...Hopefully you get some specific suggestions about your message. Below =youfind my general recommendations. Note, however, that this *might* =notactually be a corrupt database (I can't say for sure) so opening a case withMS =might be a better option than start with the restore route (also searchKb =etc first):Here are the general recommendations for handling a =suspect or corruptdatabase:0. Ensure you have a backup strategy that =you can use to recover fromhardware failures (including corruption). I =recommend performing bothdatabase and log backup in most =situations.1. If you can run DBCC CHECKDB against the database: Search Books Online =andKB for the error numbers that CHECKDB gives you. There might be =specificinfo for that type of error.2. Find out why this happened. Check =eventlog, do HW diagnostics etc.;search Books Online and KB for those =errors. You don't want this to happenagain! If the database is suspect, the =file might have been in use by forinstance an anti-virus program and =restarting SQL Server might be all thatis needed - but you still want to read =logs etc to find out what happened.3. If there is a hardware problem, =ensure the faulty hardware is replaced.4. Backup the log. This assumes =that log backup schedule is in place, ofcourse. If the database is suspect, =then the NO_TRUNCATE option for theRESTORE command must be used. Also, =you might want to do a file backup ofthe mdf and ldf files, for extra safety.5. Restore is the best thing to do now. If you managed =to backup log as perstep 4, then you will most probably have zero =dataloss. You should restorethe latest clean database backup and the =subsequent log backups includingthe one taken in above step.If the database =isn't suspect, then DBCC with a REPAIR option might be asecondary option =but this will often result in loss of data. Additionalsolutions, =depending on the errors, may be to manually rebuild non-clusteredindexes, =manually drop and reload a table if the data is static, and so on.If the =database is suspect, a secondary option can be to try to "un-suspect"the =database using sp_resetstatus. Read about it (books online, KB, googleetc). =It might help but if the database is too damaged, it might just =popback to suspect again. There's also something called "emergency mode" =whichis a "panic" status you can set in order to try to get data out of a damageddatabase. I think the name of that option speaks for =itself. Again searchthe net for info.If you feel uncertain with =above steps, I recommend letting MS hand-hold youthrough the steps appropriate =for your particular situation.-- Tibor Karaszi, SQL Server =MVPArchive at:http://groups.google.com/groups?oi=3Ddjq&as_ugrou=p=3Dmicrosoft.public.sqlserver"Kevin" wrote in messagenews:BC4=EEEE9-BA17-416F-8C9A-8DED0C6AED3F@.microsoft.com...> Hi,>> I've got what appears to be a corrupt database, =(SQL 7 SP4) runningCHECKDB on it returns lots of errors, the first being:>> Msg 8966, Level 16, State 5, Server 01AW01, =Procedure , Line 3> [Microsoft][ODBC SQL Server Driver][SQL Server]Could =not read and latchpage> (1:177936) with latch type SH. PFS failed.>> I switched the database to single user, and =tried various repair flags onCHECKDB, and I always seem to get the same =errors, and it returns lots of:>> 'The repair level on the DBCC statement caused this repair to bebypassed.'>> Any =ideas on where to start on this.. Its a customers database, I =don'thave information on what backups they have etc.. and its probably problemsin the only large table in the database (some 2.5 million =records, and 900Mbon disk, with 400Mb index).. Could this be a =resource related problemsomehow '>>>>

---

--=_NextPart_000_0058_01C3BD8A.EDA4E890--

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