intermittent replication issue

Hello,

I currently run a nightly replication from a windows server 2003 machine with sql server 2000 sp3 to a windows 2003 server machine with sql server 2005 sp1. I run a snapshot replication as merge is not supported from 2000 to 2005.

2 out 5 snapshots weekly fail with the following error:

2006-08-09 17:00:06.97 Message Code: 10054
2006-08-09 17:00:06.97
2006-08-09 17:00:06.97 [0%] The replication agent had encountered an exception.
2006-08-09 17:00:06.97 Source: Replication
2006-08-09 17:00:06.97 Exception Type: Microsoft.SqlServer.Replication.ConnectionFailureException
2006-08-09 17:00:06.97 Exception Message: TCP Provider: An existing connection was forcibly closed by the remote host.

I have setup the sql server 2005 agent service to automatic with a 'log on as' domain admin account. The sql server agent on the 2000 machine uses the local system account.

Does anyone know what i can try to fix this?

thks

geoff

Is this the snapshot agent or the distribution agent that's failing?|||

regardless, it's some kind of a security or network problem you're having. Check this similar post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=187500&SiteID=1.

sql

Intermittent Login failure at boot

I have a Windows Service middleware application that depends on SQL
Server. We are migrating from an existing Windows 2000 server/
SQLServer 2000 to Windows 2003 Web Edition and SQLServer Express. At
boot time and more often than not, the middleware application fails to
start and SQLServer reports a Failure Audit saying the Login failed
for the user 'SomeUser'. This is the account the middleware uses, it
is a SQLServer account and not a Windows account.
But once the machine is booted and I login to the server, I can start
the middleware application successfully using the Services control
panel. I'm assuming that despite the dependency of my middleware
service on the SQLServer service, SQLServer is not sufficiently
initialised to accept client connections at the point that my
middleware service tries to connect. I can't say whether this is
peculiar to SQLServer Express because to see if it also occurs with
SQLServer Standard 2005 I would need to reinstall and configure the
operating system and application software.
If there is no workaround for this, the only solution I can think of
(without going to Windows 2003 Standard and SQLServer 2005 Standard)
is to write another Windows Service to check whether or not my
middleware started successfully and to start it if it failed to start.
Any help with this problem would be much appreciated.
Thanks
Go through the SQL Server log to get a better idea if it's
just a timing issue...which I would guess it probably is.
You should see the login failure with a date time as well as
when the database the middleware uses is available, when SQL
is ready to accept client connections. You would also want
to check the state of the login failure to see the cause of
the failure.
But...going through the log should give you more information
to understand what's going on in your situation.
-Sue
On Tue, 31 Jul 2007 17:46:13 -0700, Dave
<pigwin32@.gmail.com> wrote:

>I have a Windows Service middleware application that depends on SQL
>Server. We are migrating from an existing Windows 2000 server/
>SQLServer 2000 to Windows 2003 Web Edition and SQLServer Express. At
>boot time and more often than not, the middleware application fails to
>start and SQLServer reports a Failure Audit saying the Login failed
>for the user 'SomeUser'. This is the account the middleware uses, it
>is a SQLServer account and not a Windows account.
>But once the machine is booted and I login to the server, I can start
>the middleware application successfully using the Services control
>panel. I'm assuming that despite the dependency of my middleware
>service on the SQLServer service, SQLServer is not sufficiently
>initialised to accept client connections at the point that my
>middleware service tries to connect. I can't say whether this is
>peculiar to SQLServer Express because to see if it also occurs with
>SQLServer Standard 2005 I would need to reinstall and configure the
>operating system and application software.
>If there is no workaround for this, the only solution I can think of
>(without going to Windows 2003 Standard and SQLServer 2005 Standard)
>is to write another Windows Service to check whether or not my
>middleware started successfully and to start it if it failed to start.
>Any help with this problem would be much appreciated.
>Thanks

Intermittent Login failure at boot

I have a Windows Service middleware application that depends on SQL
Server. We are migrating from an existing Windows 2000 server/
SQLServer 2000 to Windows 2003 Web Edition and SQLServer Express. At
boot time and more often than not, the middleware application fails to
start and SQLServer reports a Failure Audit saying the Login failed
for the user 'SomeUser'. This is the account the middleware uses, it
is a SQLServer account and not a Windows account.
But once the machine is booted and I login to the server, I can start
the middleware application successfully using the Services control
panel. I'm assuming that despite the dependency of my middleware
service on the SQLServer service, SQLServer is not sufficiently
initialised to accept client connections at the point that my
middleware service tries to connect. I can't say whether this is
peculiar to SQLServer Express because to see if it also occurs with
SQLServer Standard 2005 I would need to reinstall and configure the
operating system and application software.
If there is no workaround for this, the only solution I can think of
(without going to Windows 2003 Standard and SQLServer 2005 Standard)
is to write another Windows Service to check whether or not my
middleware started successfully and to start it if it failed to start.
Any help with this problem would be much appreciated.
ThanksGo through the SQL Server log to get a better idea if it's
just a timing issue...which I would guess it probably is.
You should see the login failure with a date time as well as
when the database the middleware uses is available, when SQL
is ready to accept client connections. You would also want
to check the state of the login failure to see the cause of
the failure.
But...going through the log should give you more information
to understand what's going on in your situation.
-Sue
On Tue, 31 Jul 2007 17:46:13 -0700, Dave
<pigwin32@.gmail.com> wrote:

>I have a Windows Service middleware application that depends on SQL
>Server. We are migrating from an existing Windows 2000 server/
>SQLServer 2000 to Windows 2003 Web Edition and SQLServer Express. At
>boot time and more often than not, the middleware application fails to
>start and SQLServer reports a Failure Audit saying the Login failed
>for the user 'SomeUser'. This is the account the middleware uses, it
>is a SQLServer account and not a Windows account.
>But once the machine is booted and I login to the server, I can start
>the middleware application successfully using the Services control
>panel. I'm assuming that despite the dependency of my middleware
>service on the SQLServer service, SQLServer is not sufficiently
>initialised to accept client connections at the point that my
>middleware service tries to connect. I can't say whether this is
>peculiar to SQLServer Express because to see if it also occurs with
>SQLServer Standard 2005 I would need to reinstall and configure the
>operating system and application software.
>If there is no workaround for this, the only solution I can think of
>(without going to Windows 2003 Standard and SQLServer 2005 Standard)
>is to write another Windows Service to check whether or not my
>middleware started successfully and to start it if it failed to start.
>Any help with this problem would be much appreciated.
>Thankssql

Intermittent connection timeout / general network error

We have a SQL 2005 clustered server (Microsoft Cluster Services) that is queried from 4 IIS6 Windows Server 2003 frontends. Each frontend runs both classic ASP apps connection with SQL Server ODBC and .NET2 apps connecting with System.Data.SqlClient. Ocassionaly we get a string of errors/timeouts opening a connection lasting maybe 2 minutes.

One the classic ASP apps we log one of these two errors:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation

or

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver]Timeout expired

on the ADODB.Connection.Open

On the .NET2 apps we log

Message Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

StackTrace at
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)

Source .Net SqlClient Data Provider

Any ideas for further debugging?

What does your connection string look like? Please try add "Connect Timeout=120" or "Connection Timeout = 120" in it, and see wether the problems go away.

Thanks!

Ming.

|||<add name="default" connectionString="Data Source=server;Initial Catalog=db;Integrated Security=False; uid=user; password=pw" providerName="System.Data.SqlClient"/>

for .NET via TCP/IP

The regular ASP websites are having problems as well though using ODBC.

Upping the timeout seems like a band-aid at best and may lead to connections piling up and making the situation worse. There isn't a good reason for communication between the frontends and db server over a private Gigabit link to take over 30 seconds to connect.
|||You can use your HOSTS file to define the servername-IP address combination to resolve DNS names. If you have a DNS server, define this in your DNS server. Try a simple test. Try installing any SQL Server client tool(Query Analyzer, Enterprise Manager, etc) on the server running your .NET app. Connect to your database server using this client tool. If it takes more than 5 seconds to connect, there's something wrong with your network|||

Did you get an answer for this?

we are facign similar issues.

We have 4 way cluster sql server 2005 on SP2

|||No I haven't had any answers. The problem is definitely more pronounced during heavier traffic so I wasn't able to debug much during the lull during the summer.

Intermittent connection timeout / general network error

We have a SQL 2005

clustered server (Microsoft Cluster Services) that is queried from 4 IIS6

Windows Server 2003 frontends. Each frontend runs both classic ASP apps

connection with SQL Server ODBC and .NET2 apps connecting with

System.Data.SqlClient. Ocassionaly we get a string of errors/timeouts opening a

connection lasting maybe 2 minutes.

One the classic ASP

apps we log one of these two errors:

Microsoft OLE DB Provider for ODBC

Drivers (0x80004005)
[Microsoft][ODBC SQL Server

Driver][DBNETLIB]General network error. Check your network

documentation

or

Microsoft OLE DB Provider for

ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server

Driver]Timeout expired

on the

ADODB.Connection.Open

On the .NET2 apps we

log

Message Timeout expired. The timeout

period elapsed prior to completion of the operation or the server is not

responding.

StackTrace at

System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection

owningObject)
at

System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection

owningConnection)
at

System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection

outerConnection, DbConnectionFactory connectionFactory)
at

System.Data.SqlClient.SqlConnection.Open()
at

System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo

sqlPartitionInfo)

Source .Net SqlClient Data Provider

Any ideas for further debugging?

What does your connection string look like? Please try add "Connect Timeout=120" or "Connection Timeout = 120" in it, and see wether the problems go away.

Thanks!

Ming.

|||<add name="default" connectionString="Data Source=server;Initial Catalog=db;Integrated Security=False; uid=user; password=pw" providerName="System.Data.SqlClient"/>

for .NET via TCP/IP

The regular ASP websites are having problems as well though using ODBC.

Upping the timeout seems like a band-aid at best and may lead to connections piling up and making the situation worse. There isn't a good reason for communication between the frontends and db server over a private Gigabit link to take over 30 seconds to connect.|||You can use your HOSTS file to define the servername-IP address combination to resolve DNS names. If you have a DNS server, define this in your DNS server. Try a simple test. Try installing any SQL Server client tool(Query Analyzer, Enterprise Manager, etc) on the server running your .NET app. Connect to your database server using this client tool. If it takes more than 5 seconds to connect, there's something wrong with your network|||

Did you get an answer for this?

we are facign similar issues.

We have 4 way cluster sql server 2005 on SP2

|||No I haven't had any answers. The problem is definitely more pronounced during heavier traffic so I wasn't able to debug much during the lull during the summer.

|||We have a similar issue with our Clustered SQL Server 2005. We are seein it a lot in a VB 6 executable, but I have gotten the errors in Server Management Studio, too. We get one of two errors:
Timeout expired
or
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

We have run Sysinternals Process Monitor on a PC that receives these errors and have seen a NAME NOT FOUND result on queryopen operation for CLUSAPI.DLL, RESUTILS.DLL and ntdsapi.dll. The path it is looking in is the application path C:\Program Files\ROAS. These DLLs don't exist in this folder, but do exist in C:\Windows\System32.

The first two are MS Clustering DLLS and i asume with a name like ntdsapi the other is a server OS DLL.

Intermittent connection loss?

Hi all,
I have a .Net windows application that uses the micrsoft application block
to make calls to the database. I am doing a few thousand records that must
be inserted. I have a problem where on occasion I get an exception that
says "SQL Server does not exist or access denied." This seems to be an
intermittent problem, and profiler has not turned up any info. The server is
running locally, so I don't think it is a network issue.
Anybody have any ideas?
Thanks
J
Are you running the .NET application using TCP/IP or Shared Memory?
Check the sysprocesses table when your application is running & look at the
net_library column to confirm.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Kevin,
I get a timeout period elapsed error when trying to open a connection on a
machine where the server is local. It does not occur when running the same
app on another client on the network. It is a .net application using the
SQLClient. If I turn off shared memory protocol for clients on the server the
error does not occur. Is there an issue with shared memory access and .Net?
"Kevin McDonnell [MSFT]" wrote:

> Are you running the .NET application using TCP/IP or Shared Memory?
> Check the sysprocesses table when your application is running & look at the
> net_library column to confirm.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||There may be an issue. I would open a case with a VC engineer in the
Webdata group to investigate.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Intermitent problem : New transaction cannot enlist in the specified transaction coordinat

I have a Windows 2003 Application Server. When I dont use the
Application for about 15 minutes, I get the error : "New transaction
cannot enlist in the specified transaction coordinator" when trying to
do a transaction with the Database server.
When I got the error, if I keep trying to do a transaction, eventually
the transaction process restart to work until I keep it inactive for
about 15 minutes.
I did some test with the application and with DTCTester. When it dont
work with the application it dont work with DTCTester
(http://support.microsoft.com/default.aspx?scid=kb;en-us;293799).
- The application server is Windows 2003 SP1
- This server connect to a Cluster SQL Server 2000 database intalled on
Windows 2003 servers .
- The two servers are in two differents Network zone.
- All ports are opened between Database servers and application
servers.
- MSDTC is started on the Application and Database server.
- In security configuration of MSDTC of the Application server the
option "No Authentication Required is selected". This option is not
selected on the Database server because the service pack 1 is not
installed.
- Database computers names are in the host file of the application
server and application server name is in the host file of the SQL
Server.
I did some test with another Database server (SQL Server 2000 on
windows 2000) and it always works, but these two server are in the same
zone.
I would like to know if someone have an idea why the transaction stop
to works after about 15 minutes and restart to work if I keep trying.
Thank you very much.
DavidHi Dave
I am not sure what you mean by different zones? Is this a different sub-net?
It is not clear if you are seeing exactly the same symptoms with DTC tester
or if it never works?
As the problem is intermittent it sounds like a network type issue rather
than the components. You may want to try changing network cards and
hubs/switches to see if it continues to fail. Also if there are any firewalls
make sure they are configured correctly, also make sure that no antivirus or
intrusion detection software is causing a conflict.
John
"Dave960" wrote:
> I have a Windows 2003 Application Server. When I dont use the
> Application for about 15 minutes, I get the error : "New transaction
> cannot enlist in the specified transaction coordinator" when trying to
> do a transaction with the Database server.
> When I got the error, if I keep trying to do a transaction, eventually
> the transaction process restart to work until I keep it inactive for
> about 15 minutes.
> I did some test with the application and with DTCTester. When it dont
> work with the application it dont work with DTCTester
> (http://support.microsoft.com/default.aspx?scid=kb;en-us;293799).
> - The application server is Windows 2003 SP1
> - This server connect to a Cluster SQL Server 2000 database intalled on
> Windows 2003 servers .
> - The two servers are in two differents Network zone.
> - All ports are opened between Database servers and application
> servers.
> - MSDTC is started on the Application and Database server.
> - In security configuration of MSDTC of the Application server the
> option "No Authentication Required is selected". This option is not
> selected on the Database server because the service pack 1 is not
> installed.
> - Database computers names are in the host file of the application
> server and application server name is in the host file of the SQL
> Server.
>
> I did some test with another Database server (SQL Server 2000 on
> windows 2000) and it always works, but these two server are in the same
> zone.
> I would like to know if someone have an idea why the transaction stop
> to works after about 15 minutes and restart to work if I keep trying.
> Thank you very much.
> David
>|||Hi John,
Thank's for your reply :)
- By different zone I mean different sub-net.
- I'm seeing exactly the same problem with dtctester. After a couple of
try it work's and if I do nothing for about 15 minutes it stop to work
until I try dtctester for a couple of try ( 2 to 20).
If I run a transaction between the application server and the SQL
server every 5-10 minutes, transaction always work.
The transaction process always work with another Database server
(Server2) in the same zone as my application server, so I think it's
not hardware related. But there is difference with this second server,
it run on windows 2000 and is not in a cluster.
If I do the dtctester test with the Database server (Server1 - the one
that I have problem with) with another application server in the Dabase
server sub-net it always works.
Another thing, I'm always able to retrieve data from the Database
server, I just have problem with transactions.
>From what I see it look like a network problem, but I dont see why
after about 15 minutes of inactivity it strop to work ?
If you have any ideas, let me know.
Thanks a lot.
David
It look similar to this problem :
http://groups.google.ca/group/microsoft.public.platformsdk.complus_mts/browse_thread/thread/da9b915896c6d8a1/66b4518597052cfb?lnk=st&q=&rnum=1&hl=en#66b4518597052cfb
John Bell wrote:
> Hi Dave
> I am not sure what you mean by different zones? Is this a different sub-net?
> It is not clear if you are seeing exactly the same symptoms with DTC tester
> or if it never works?
> As the problem is intermittent it sounds like a network type issue rather
> than the components. You may want to try changing network cards and
> hubs/switches to see if it continues to fail. Also if there are any firewalls
> make sure they are configured correctly, also make sure that no antivirus or
> intrusion detection software is causing a conflict.
> John
>
> "Dave960" wrote:
> > I have a Windows 2003 Application Server. When I dont use the
> > Application for about 15 minutes, I get the error : "New transaction
> > cannot enlist in the specified transaction coordinator" when trying to
> > do a transaction with the Database server.
> >
> > When I got the error, if I keep trying to do a transaction, eventually
> > the transaction process restart to work until I keep it inactive for
> > about 15 minutes.
> >
> > I did some test with the application and with DTCTester. When it dont
> > work with the application it dont work with DTCTester
> > (http://support.microsoft.com/default.aspx?scid=kb;en-us;293799).
> >
> > - The application server is Windows 2003 SP1
> >
> > - This server connect to a Cluster SQL Server 2000 database intalled on
> > Windows 2003 servers .
> >
> > - The two servers are in two differents Network zone.
> >
> > - All ports are opened between Database servers and application
> > servers.
> >
> > - MSDTC is started on the Application and Database server.
> >
> > - In security configuration of MSDTC of the Application server the
> > option "No Authentication Required is selected". This option is not
> > selected on the Database server because the service pack 1 is not
> > installed.
> >
> > - Database computers names are in the host file of the application
> > server and application server name is in the host file of the SQL
> > Server.
> >
> >
> >
> > I did some test with another Database server (SQL Server 2000 on
> > windows 2000) and it always works, but these two server are in the same
> > zone.
> >
> > I would like to know if someone have an idea why the transaction stop
> > to works after about 15 minutes and restart to work if I keep trying.
> >
> > Thank you very much.
> >
> > David
> >
> >|||Hi Dave
It is not clear why you need a distributed transaction, from what you say it
is only
the one server being used at any one time?
John
"Dave960" wrote:
> Hi John,
> Thank's for your reply :)
> - By different zone I mean different sub-net.
> - I'm seeing exactly the same problem with dtctester. After a couple of
> try it work's and if I do nothing for about 15 minutes it stop to work
> until I try dtctester for a couple of try ( 2 to 20).
> If I run a transaction between the application server and the SQL
> server every 5-10 minutes, transaction always work.
> The transaction process always work with another Database server
> (Server2) in the same zone as my application server, so I think it's
> not hardware related. But there is difference with this second server,
> it run on windows 2000 and is not in a cluster.
> If I do the dtctester test with the Database server (Server1 - the one
> that I have problem with) with another application server in the Dabase
> server sub-net it always works.
> Another thing, I'm always able to retrieve data from the Database
> server, I just have problem with transactions.
> >From what I see it look like a network problem, but I dont see why
> after about 15 minutes of inactivity it strop to work ?
> If you have any ideas, let me know.
> Thanks a lot.
> David
>
> It look similar to this problem :
> http://groups.google.ca/group/microsoft.public.platformsdk.complus_mts/browse_thread/thread/da9b915896c6d8a1/66b4518597052cfb?lnk=st&q=&rnum=1&hl=en#66b4518597052cfb
> John Bell wrote:
> > Hi Dave
> >
> > I am not sure what you mean by different zones? Is this a different sub-net?
> > It is not clear if you are seeing exactly the same symptoms with DTC tester
> > or if it never works?
> >
> > As the problem is intermittent it sounds like a network type issue rather
> > than the components. You may want to try changing network cards and
> > hubs/switches to see if it continues to fail. Also if there are any firewalls
> > make sure they are configured correctly, also make sure that no antivirus or
> > intrusion detection software is causing a conflict.
> >
> > John
> >
> >
> > "Dave960" wrote:
> >
> > > I have a Windows 2003 Application Server. When I dont use the
> > > Application for about 15 minutes, I get the error : "New transaction
> > > cannot enlist in the specified transaction coordinator" when trying to
> > > do a transaction with the Database server.
> > >
> > > When I got the error, if I keep trying to do a transaction, eventually
> > > the transaction process restart to work until I keep it inactive for
> > > about 15 minutes.
> > >
> > > I did some test with the application and with DTCTester. When it dont
> > > work with the application it dont work with DTCTester
> > > (http://support.microsoft.com/default.aspx?scid=kb;en-us;293799).
> > >
> > > - The application server is Windows 2003 SP1
> > >
> > > - This server connect to a Cluster SQL Server 2000 database intalled on
> > > Windows 2003 servers .
> > >
> > > - The two servers are in two differents Network zone.
> > >
> > > - All ports are opened between Database servers and application
> > > servers.
> > >
> > > - MSDTC is started on the Application and Database server.
> > >
> > > - In security configuration of MSDTC of the Application server the
> > > option "No Authentication Required is selected". This option is not
> > > selected on the Database server because the service pack 1 is not
> > > installed.
> > >
> > > - Database computers names are in the host file of the application
> > > server and application server name is in the host file of the SQL
> > > Server.
> > >
> > >
> > >
> > > I did some test with another Database server (SQL Server 2000 on
> > > windows 2000) and it always works, but these two server are in the same
> > > zone.
> > >
> > > I would like to know if someone have an idea why the transaction stop
> > > to works after about 15 minutes and restart to work if I keep trying.
> > >
> > > Thank you very much.
> > >
> > > David
> > >
> > >
>|||Hi John,
The application server run COM+ components that use distributed
transaction for certain type of query to the database.
This application is already running on another server, we just want to
switch this application to a new server that is in a different sub-net.
If there's missing informations, dont hesitate to ask me.
Thanks !!!
David
John Bell wrote:
> Hi Dave
> It is not clear why you need a distributed transaction, from what you say it
> is only
> the one server being used at any one time?
> John
>
> "Dave960" wrote:
> > Hi John,
> >
> > Thank's for your reply :)
> >
> > - By different zone I mean different sub-net.
> > - I'm seeing exactly the same problem with dtctester. After a couple of
> > try it work's and if I do nothing for about 15 minutes it stop to work
> > until I try dtctester for a couple of try ( 2 to 20).
> >
> > If I run a transaction between the application server and the SQL
> > server every 5-10 minutes, transaction always work.
> >
> > The transaction process always work with another Database server
> > (Server2) in the same zone as my application server, so I think it's
> > not hardware related. But there is difference with this second server,
> > it run on windows 2000 and is not in a cluster.
> >
> > If I do the dtctester test with the Database server (Server1 - the one
> > that I have problem with) with another application server in the Dabase
> > server sub-net it always works.
> >
> > Another thing, I'm always able to retrieve data from the Database
> > server, I just have problem with transactions.
> >
> > >From what I see it look like a network problem, but I dont see why
> > after about 15 minutes of inactivity it strop to work ?
> >
> > If you have any ideas, let me know.
> >
> > Thanks a lot.
> >
> > David
> >
> >
> > It look similar to this problem :
> > http://groups.google.ca/group/microsoft.public.platformsdk.complus_mts/browse_thread/thread/da9b915896c6d8a1/66b4518597052cfb?lnk=st&q=&rnum=1&hl=en#66b4518597052cfb
> >
> > John Bell wrote:
> > > Hi Dave
> > >
> > > I am not sure what you mean by different zones? Is this a different sub-net?
> > > It is not clear if you are seeing exactly the same symptoms with DTC tester
> > > or if it never works?
> > >
> > > As the problem is intermittent it sounds like a network type issue rather
> > > than the components. You may want to try changing network cards and
> > > hubs/switches to see if it continues to fail. Also if there are any firewalls
> > > make sure they are configured correctly, also make sure that no antivirus or
> > > intrusion detection software is causing a conflict.
> > >
> > > John
> > >
> > >
> > > "Dave960" wrote:
> > >
> > > > I have a Windows 2003 Application Server. When I dont use the
> > > > Application for about 15 minutes, I get the error : "New transaction
> > > > cannot enlist in the specified transaction coordinator" when trying to
> > > > do a transaction with the Database server.
> > > >
> > > > When I got the error, if I keep trying to do a transaction, eventually
> > > > the transaction process restart to work until I keep it inactive for
> > > > about 15 minutes.
> > > >
> > > > I did some test with the application and with DTCTester. When it dont
> > > > work with the application it dont work with DTCTester
> > > > (http://support.microsoft.com/default.aspx?scid=kb;en-us;293799).
> > > >
> > > > - The application server is Windows 2003 SP1
> > > >
> > > > - This server connect to a Cluster SQL Server 2000 database intalled on
> > > > Windows 2003 servers .
> > > >
> > > > - The two servers are in two differents Network zone.
> > > >
> > > > - All ports are opened between Database servers and application
> > > > servers.
> > > >
> > > > - MSDTC is started on the Application and Database server.
> > > >
> > > > - In security configuration of MSDTC of the Application server the
> > > > option "No Authentication Required is selected". This option is not
> > > > selected on the Database server because the service pack 1 is not
> > > > installed.
> > > >
> > > > - Database computers names are in the host file of the application
> > > > server and application server name is in the host file of the SQL
> > > > Server.
> > > >
> > > >
> > > >
> > > > I did some test with another Database server (SQL Server 2000 on
> > > > windows 2000) and it always works, but these two server are in the same
> > > > zone.
> > > >
> > > > I would like to know if someone have an idea why the transaction stop
> > > > to works after about 15 minutes and restart to work if I keep trying.
> > > >
> > > > Thank you very much.
> > > >
> > > > David
> > > >
> > > >
> >
> >|||Hi Dave
As this is intermitten, it implies that it is set up correctly and something
other than SQL Server is causing this to fail. Have you checked for intrusion
detection or virus checkers being active at this time?
John
"Dave960" wrote:
> Hi John,
> The application server run COM+ components that use distributed
> transaction for certain type of query to the database.
> This application is already running on another server, we just want to
> switch this application to a new server that is in a different sub-net.
> If there's missing informations, dont hesitate to ask me.
> Thanks !!!
> David
>
> John Bell wrote:
> > Hi Dave
> >
> > It is not clear why you need a distributed transaction, from what you say it
> > is only
> > the one server being used at any one time?
> >
> > John
> >
> >
> >
> > "Dave960" wrote:
> >
> > > Hi John,
> > >
> > > Thank's for your reply :)
> > >
> > > - By different zone I mean different sub-net.
> > > - I'm seeing exactly the same problem with dtctester. After a couple of
> > > try it work's and if I do nothing for about 15 minutes it stop to work
> > > until I try dtctester for a couple of try ( 2 to 20).
> > >
> > > If I run a transaction between the application server and the SQL
> > > server every 5-10 minutes, transaction always work.
> > >
> > > The transaction process always work with another Database server
> > > (Server2) in the same zone as my application server, so I think it's
> > > not hardware related. But there is difference with this second server,
> > > it run on windows 2000 and is not in a cluster.
> > >
> > > If I do the dtctester test with the Database server (Server1 - the one
> > > that I have problem with) with another application server in the Dabase
> > > server sub-net it always works.
> > >
> > > Another thing, I'm always able to retrieve data from the Database
> > > server, I just have problem with transactions.
> > >
> > > >From what I see it look like a network problem, but I dont see why
> > > after about 15 minutes of inactivity it strop to work ?
> > >
> > > If you have any ideas, let me know.
> > >
> > > Thanks a lot.
> > >
> > > David
> > >
> > >
> > > It look similar to this problem :
> > > http://groups.google.ca/group/microsoft.public.platformsdk.complus_mts/browse_thread/thread/da9b915896c6d8a1/66b4518597052cfb?lnk=st&q=&rnum=1&hl=en#66b4518597052cfb
> > >
> > > John Bell wrote:
> > > > Hi Dave
> > > >
> > > > I am not sure what you mean by different zones? Is this a different sub-net?
> > > > It is not clear if you are seeing exactly the same symptoms with DTC tester
> > > > or if it never works?
> > > >
> > > > As the problem is intermittent it sounds like a network type issue rather
> > > > than the components. You may want to try changing network cards and
> > > > hubs/switches to see if it continues to fail. Also if there are any firewalls
> > > > make sure they are configured correctly, also make sure that no antivirus or
> > > > intrusion detection software is causing a conflict.
> > > >
> > > > John
> > > >
> > > >
> > > > "Dave960" wrote:
> > > >
> > > > > I have a Windows 2003 Application Server. When I dont use the
> > > > > Application for about 15 minutes, I get the error : "New transaction
> > > > > cannot enlist in the specified transaction coordinator" when trying to
> > > > > do a transaction with the Database server.
> > > > >
> > > > > When I got the error, if I keep trying to do a transaction, eventually
> > > > > the transaction process restart to work until I keep it inactive for
> > > > > about 15 minutes.
> > > > >
> > > > > I did some test with the application and with DTCTester. When it dont
> > > > > work with the application it dont work with DTCTester
> > > > > (http://support.microsoft.com/default.aspx?scid=kb;en-us;293799).
> > > > >
> > > > > - The application server is Windows 2003 SP1
> > > > >
> > > > > - This server connect to a Cluster SQL Server 2000 database intalled on
> > > > > Windows 2003 servers .
> > > > >
> > > > > - The two servers are in two differents Network zone.
> > > > >
> > > > > - All ports are opened between Database servers and application
> > > > > servers.
> > > > >
> > > > > - MSDTC is started on the Application and Database server.
> > > > >
> > > > > - In security configuration of MSDTC of the Application server the
> > > > > option "No Authentication Required is selected". This option is not
> > > > > selected on the Database server because the service pack 1 is not
> > > > > installed.
> > > > >
> > > > > - Database computers names are in the host file of the application
> > > > > server and application server name is in the host file of the SQL
> > > > > Server.
> > > > >
> > > > >
> > > > >
> > > > > I did some test with another Database server (SQL Server 2000 on
> > > > > windows 2000) and it always works, but these two server are in the same
> > > > > zone.
> > > > >
> > > > > I would like to know if someone have an idea why the transaction stop
> > > > > to works after about 15 minutes and restart to work if I keep trying.
> > > > >
> > > > > Thank you very much.
> > > > >
> > > > > David
> > > > >
> > > > >
> > >
> > >
>|||Hi Dave
You may also want to check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306843
John
"Dave960" wrote:
> Hi John,
> The application server run COM+ components that use distributed
> transaction for certain type of query to the database.
> This application is already running on another server, we just want to
> switch this application to a new server that is in a different sub-net.
> If there's missing informations, dont hesitate to ask me.
> Thanks !!!
> David
>
> John Bell wrote:
> > Hi Dave
> >
> > It is not clear why you need a distributed transaction, from what you say it
> > is only
> > the one server being used at any one time?
> >
> > John
> >
> >
> >
> > "Dave960" wrote:
> >
> > > Hi John,
> > >
> > > Thank's for your reply :)
> > >
> > > - By different zone I mean different sub-net.
> > > - I'm seeing exactly the same problem with dtctester. After a couple of
> > > try it work's and if I do nothing for about 15 minutes it stop to work
> > > until I try dtctester for a couple of try ( 2 to 20).
> > >
> > > If I run a transaction between the application server and the SQL
> > > server every 5-10 minutes, transaction always work.
> > >
> > > The transaction process always work with another Database server
> > > (Server2) in the same zone as my application server, so I think it's
> > > not hardware related. But there is difference with this second server,
> > > it run on windows 2000 and is not in a cluster.
> > >
> > > If I do the dtctester test with the Database server (Server1 - the one
> > > that I have problem with) with another application server in the Dabase
> > > server sub-net it always works.
> > >
> > > Another thing, I'm always able to retrieve data from the Database
> > > server, I just have problem with transactions.
> > >
> > > >From what I see it look like a network problem, but I dont see why
> > > after about 15 minutes of inactivity it strop to work ?
> > >
> > > If you have any ideas, let me know.
> > >
> > > Thanks a lot.
> > >
> > > David
> > >
> > >
> > > It look similar to this problem :
> > > http://groups.google.ca/group/microsoft.public.platformsdk.complus_mts/browse_thread/thread/da9b915896c6d8a1/66b4518597052cfb?lnk=st&q=&rnum=1&hl=en#66b4518597052cfb
> > >
> > > John Bell wrote:
> > > > Hi Dave
> > > >
> > > > I am not sure what you mean by different zones? Is this a different sub-net?
> > > > It is not clear if you are seeing exactly the same symptoms with DTC tester
> > > > or if it never works?
> > > >
> > > > As the problem is intermittent it sounds like a network type issue rather
> > > > than the components. You may want to try changing network cards and
> > > > hubs/switches to see if it continues to fail. Also if there are any firewalls
> > > > make sure they are configured correctly, also make sure that no antivirus or
> > > > intrusion detection software is causing a conflict.
> > > >
> > > > John
> > > >
> > > >
> > > > "Dave960" wrote:
> > > >
> > > > > I have a Windows 2003 Application Server. When I dont use the
> > > > > Application for about 15 minutes, I get the error : "New transaction
> > > > > cannot enlist in the specified transaction coordinator" when trying to
> > > > > do a transaction with the Database server.
> > > > >
> > > > > When I got the error, if I keep trying to do a transaction, eventually
> > > > > the transaction process restart to work until I keep it inactive for
> > > > > about 15 minutes.
> > > > >
> > > > > I did some test with the application and with DTCTester. When it dont
> > > > > work with the application it dont work with DTCTester
> > > > > (http://support.microsoft.com/default.aspx?scid=kb;en-us;293799).
> > > > >
> > > > > - The application server is Windows 2003 SP1
> > > > >
> > > > > - This server connect to a Cluster SQL Server 2000 database intalled on
> > > > > Windows 2003 servers .
> > > > >
> > > > > - The two servers are in two differents Network zone.
> > > > >
> > > > > - All ports are opened between Database servers and application
> > > > > servers.
> > > > >
> > > > > - MSDTC is started on the Application and Database server.
> > > > >
> > > > > - In security configuration of MSDTC of the Application server the
> > > > > option "No Authentication Required is selected". This option is not
> > > > > selected on the Database server because the service pack 1 is not
> > > > > installed.
> > > > >
> > > > > - Database computers names are in the host file of the application
> > > > > server and application server name is in the host file of the SQL
> > > > > Server.
> > > > >
> > > > >
> > > > >
> > > > > I did some test with another Database server (SQL Server 2000 on
> > > > > windows 2000) and it always works, but these two server are in the same
> > > > > zone.
> > > > >
> > > > > I would like to know if someone have an idea why the transaction stop
> > > > > to works after about 15 minutes and restart to work if I keep trying.
> > > > >
> > > > > Thank you very much.
> > > > >
> > > > > David
> > > > >
> > > > >
> > >
> > >
>|||Hi John,
- I have the same problem if I desactivate the Antivirus sofware.
- I already follow the steps list in this link and DTCPing always
works.
If you have any other ideas, let me know.
Thanks
David
John Bell wrote:
> Hi Dave
> You may also want to check out
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306843
> John
> "Dave960" wrote:
> > Hi John,
> >
> > The application server run COM+ components that use distributed
> > transaction for certain type of query to the database.
> >
> > This application is already running on another server, we just want to
> > switch this application to a new server that is in a different sub-net.
> >
> > If there's missing informations, dont hesitate to ask me.
> >
> > Thanks !!!
> >
> > David
> >
> >
> > John Bell wrote:
> > > Hi Dave
> > >
> > > It is not clear why you need a distributed transaction, from what you say it
> > > is only
> > > the one server being used at any one time?
> > >
> > > John
> > >
> > >
> > >
> > > "Dave960" wrote:
> > >
> > > > Hi John,
> > > >
> > > > Thank's for your reply :)
> > > >
> > > > - By different zone I mean different sub-net.
> > > > - I'm seeing exactly the same problem with dtctester. After a couple of
> > > > try it work's and if I do nothing for about 15 minutes it stop to work
> > > > until I try dtctester for a couple of try ( 2 to 20).
> > > >
> > > > If I run a transaction between the application server and the SQL
> > > > server every 5-10 minutes, transaction always work.
> > > >
> > > > The transaction process always work with another Database server
> > > > (Server2) in the same zone as my application server, so I think it's
> > > > not hardware related. But there is difference with this second server,
> > > > it run on windows 2000 and is not in a cluster.
> > > >
> > > > If I do the dtctester test with the Database server (Server1 - the one
> > > > that I have problem with) with another application server in the Dabase
> > > > server sub-net it always works.
> > > >
> > > > Another thing, I'm always able to retrieve data from the Database
> > > > server, I just have problem with transactions.
> > > >
> > > > >From what I see it look like a network problem, but I dont see why
> > > > after about 15 minutes of inactivity it strop to work ?
> > > >
> > > > If you have any ideas, let me know.
> > > >
> > > > Thanks a lot.
> > > >
> > > > David
> > > >
> > > >
> > > > It look similar to this problem :
> > > > http://groups.google.ca/group/microsoft.public.platformsdk.complus_mts/browse_thread/thread/da9b915896c6d8a1/66b4518597052cfb?lnk=st&q=&rnum=1&hl=en#66b4518597052cfb
> > > >
> > > > John Bell wrote:
> > > > > Hi Dave
> > > > >
> > > > > I am not sure what you mean by different zones? Is this a different sub-net?
> > > > > It is not clear if you are seeing exactly the same symptoms with DTC tester
> > > > > or if it never works?
> > > > >
> > > > > As the problem is intermittent it sounds like a network type issue rather
> > > > > than the components. You may want to try changing network cards and
> > > > > hubs/switches to see if it continues to fail. Also if there are any firewalls
> > > > > make sure they are configured correctly, also make sure that no antivirus or
> > > > > intrusion detection software is causing a conflict.
> > > > >
> > > > > John
> > > > >
> > > > >
> > > > > "Dave960" wrote:
> > > > >
> > > > > > I have a Windows 2003 Application Server. When I dont use the
> > > > > > Application for about 15 minutes, I get the error : "New transaction
> > > > > > cannot enlist in the specified transaction coordinator" when trying to
> > > > > > do a transaction with the Database server.
> > > > > >
> > > > > > When I got the error, if I keep trying to do a transaction, eventually
> > > > > > the transaction process restart to work until I keep it inactive for
> > > > > > about 15 minutes.
> > > > > >
> > > > > > I did some test with the application and with DTCTester. When it dont
> > > > > > work with the application it dont work with DTCTester
> > > > > > (http://support.microsoft.com/default.aspx?scid=kb;en-us;293799).
> > > > > >
> > > > > > - The application server is Windows 2003 SP1
> > > > > >
> > > > > > - This server connect to a Cluster SQL Server 2000 database intalled on
> > > > > > Windows 2003 servers .
> > > > > >
> > > > > > - The two servers are in two differents Network zone.
> > > > > >
> > > > > > - All ports are opened between Database servers and application
> > > > > > servers.
> > > > > >
> > > > > > - MSDTC is started on the Application and Database server.
> > > > > >
> > > > > > - In security configuration of MSDTC of the Application server the
> > > > > > option "No Authentication Required is selected". This option is not
> > > > > > selected on the Database server because the service pack 1 is not
> > > > > > installed.
> > > > > >
> > > > > > - Database computers names are in the host file of the application
> > > > > > server and application server name is in the host file of the SQL
> > > > > > Server.
> > > > > >
> > > > > >
> > > > > >
> > > > > > I did some test with another Database server (SQL Server 2000 on
> > > > > > windows 2000) and it always works, but these two server are in the same
> > > > > > zone.
> > > > > >
> > > > > > I would like to know if someone have an idea why the transaction stop
> > > > > > to works after about 15 minutes and restart to work if I keep trying.
> > > > > >
> > > > > > Thank you very much.
> > > > > >
> > > > > > David
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >

Intergrity of forms and windows Auth

i Have an ASP.NET Application with forms authentication which uses sql
reporting for report purpose ,but report server works on windows
authentication ,due to which dialog box is popping up,i need to know a way of
integratinf the two to have a single sign on Process?Rags,
You have two options:
1. Generating the report on the server side of the application by using the
Render SOAP API. The advantage of this approach is that it is more secure
since the user doesn't see the report URL (everything takes place on the
server). The tradeoff is that the interactive features (drilldown,
drillthrough, etc.) will not work with SOAP since their require direct
access to the Report Server by URL. If you decide to take this approach, you
can pass the web app identity to the Report Server and grant a minimum set
of permissions in RS to this account.
2. Replace the RS Windows security with Forms Authentication by writing a
custom security extension. This will allow you to incorporate interactive
features in your reports. In this scenario, the reports will be requested on
the client side of the application (e.g. by using the Report Viewer sample
control). If you decide to take this approach check out the sample security
extension from MS at
(http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.a
sp?frame=true#ufairs_topic3).
So, you have to carefully weight out your requirements for security,
reporting features and your application architecture to determine the best
integration scenario.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
news:0066D811-7499-4FFD-BB70-D97D8051D1F5@.microsoft.com...
> i Have an ASP.NET Application with forms authentication which uses sql
> reporting for report purpose ,but report server works on windows
> authentication ,due to which dialog box is popping up,i need to know a way
of
> integratinf the two to have a single sign on Process?

Interface not registered error

I have not ever had a problem with my windows media player burning cds...but everytime I put ina blank cd to copy...it says the interface is not registered could someone please instruct me.

This is a wrong place for this question. This forum is for SQL Server Integration Services.

Thanks.

sql

interesting sql login issue

Hi all,
I have a SQL 2005 server running on SERVERA. I have the management studio
installed on my desktop (Windows XP Pro SP2) and connect to the sql server
using my regular domain login account (windows auth). This worked fine until
I had to change my domain user account's password as it had expired (we have
to change it every 90 days). Now, ever since then, I have been unable to
connect to the sql server in management studio. I get:-
Login Failed for user ''. Error: 18452
If I remote into the server and try to connect to sql it works fine (I am
member of local admin group on the server)
Any ideas'http://support.microsoft.com/kb/555332
Is it possible that you have your Windows auth password hardcoded somewhere?
When worse comes to worse, you may have to drop and re-create your login on
a
SQL Server.
"param@.community.nospam" wrote:

> Hi all,
> I have a SQL 2005 server running on SERVERA. I have the management studio
> installed on my desktop (Windows XP Pro SP2) and connect to the sql server
> using my regular domain login account (windows auth). This worked fine unt
il
> I had to change my domain user account's password as it had expired (we ha
ve
> to change it every 90 days). Now, ever since then, I have been unable to
> connect to the sql server in management studio. I get:-
> Login Failed for user ''. Error: 18452
> If I remote into the server and try to connect to sql it works fine (I am
> member of local admin group on the server)
> Any ideas'
>
>|||Can you connect from the command line with sqlcmd?
It may also be worth a try of deleting the registration and
reregistering the server.
-Sue
On Wed, 21 Mar 2007 14:50:49 -0500, <param@.community.nospam>
wrote:

>Hi all,
>I have a SQL 2005 server running on SERVERA. I have the management studio
>installed on my desktop (Windows XP Pro SP2) and connect to the sql server
>using my regular domain login account (windows auth). This worked fine unti
l
>I had to change my domain user account's password as it had expired (we hav
e
>to change it every 90 days). Now, ever since then, I have been unable to
>connect to the sql server in management studio. I get:-
>Login Failed for user ''. Error: 18452
>If I remote into the server and try to connect to sql it works fine (I am
>member of local admin group on the server)
>Any ideas'
>|||What do you mean by hardcoded? I just login to my workstation using my
windows domain login and password?
I have tried dropping and re-creating the login in SQL Server.
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:D1639F71-646C-4286-983F-87AF7C25360A@.microsoft.com...[vbcol=seagreen]
> http://support.microsoft.com/kb/555332
> Is it possible that you have your Windows auth password hardcoded
> somewhere?
> When worse comes to worse, you may have to drop and re-create your login
> on a
> SQL Server.
> "param@.community.nospam" wrote:
>|||If you notice the username is empty in the error message below. Could it be
a bug where management studio is not passing credentials to the server?
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:D1639F71-646C-4286-983F-87AF7C25360A@.microsoft.com...[vbcol=seagreen]
> http://support.microsoft.com/kb/555332
> Is it possible that you have your Windows auth password hardcoded
> somewhere?
> When worse comes to worse, you may have to drop and re-create your login
> on a
> SQL Server.
> "param@.community.nospam" wrote:
>

Interesting question about memory usage - standard edition

Hi,
I have sql server 2000 standard edition (windows 2000
server - NOT advanced) on the server which has 2560MB of
memory. This edition has restriction of 2048MB of memory
it can use (max memory parameter set to this value).
I'm making performance analysis of whether we need to
upgrade memory on this server (and use /awe) - we're
about to add some additional load to the system.
I collected statistics using performance monitor. Among
many counters, I was monitoring Available Memory (MB).
This value varies from 325 to 660.
Given that 512MB of memory is totally invisible to the
sql server, does this mean that we're hitting the wall
regarding memory for sql server? I also noticed something
strange, not aligned with bol, that sql server doesn't
release memory at all, because even if the server is
idle, available memory doesn't grow higher than 660MB
(there is nothing other than sql server that uses this
memory - dedicated database server)... Since performance
monitor is in this case useless (or not?), how can you
determine how much memory database engine really
uses/needs?
Thanks
The main things to look at are still Pages/sec (should be near 0 most of the
time) and Buffer Cache Hit ratio (>95% but depends on what type of workload
and you really need to be on SP3 for the number to mean anything). SQL
Server won't give back memory unless there is competition for it so its
perfectly normal for the Available Memory to stay at 660MB. Page life
expectancy,Memory grants outstanding and free buffers can also help to
narrow down memory contention
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Peja" <peja@.sympatico.ca> wrote in message
news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
> Hi,
> I have sql server 2000 standard edition (windows 2000
> server - NOT advanced) on the server which has 2560MB of
> memory. This edition has restriction of 2048MB of memory
> it can use (max memory parameter set to this value).
> I'm making performance analysis of whether we need to
> upgrade memory on this server (and use /awe) - we're
> about to add some additional load to the system.
> I collected statistics using performance monitor. Among
> many counters, I was monitoring Available Memory (MB).
> This value varies from 325 to 660.
> Given that 512MB of memory is totally invisible to the
> sql server, does this mean that we're hitting the wall
> regarding memory for sql server? I also noticed something
> strange, not aligned with bol, that sql server doesn't
> release memory at all, because even if the server is
> idle, available memory doesn't grow higher than 660MB
> (there is nothing other than sql server that uses this
> memory - dedicated database server)... Since performance
> monitor is in this case useless (or not?), how can you
> determine how much memory database engine really
> uses/needs?
> Thanks
|||So Buffer Cache is not reliable if you have 2000 SP2?

>--Original Message--
>The main things to look at are still Pages/sec (should be
near 0 most of the
>time) and Buffer Cache Hit ratio (>95% but depends on
what type of workload
>and you really need to be on SP3 for the number to mean
anything). SQL
>Server won't give back memory unless there is competition
for it so its
>perfectly normal for the Available Memory to stay at
660MB. Page life
>expectancy,Memory grants outstanding and free buffers can
also help to[vbcol=seagreen]
>narrow down memory contention
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Peja" <peja@.sympatico.ca> wrote in message
>news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
something
>
>.
>
sql

Interesting question about memory usage - standard edition

Hi,
I have sql server 2000 standard edition (windows 2000
server - NOT advanced) on the server which has 2560MB of
memory. This edition has restriction of 2048MB of memory
it can use (max memory parameter set to this value).
I'm making performance analysis of whether we need to
upgrade memory on this server (and use /awe) - we're
about to add some additional load to the system.
I collected statistics using performance monitor. Among
many counters, I was monitoring Available Memory (MB).
This value varies from 325 to 660.
Given that 512MB of memory is totally invisible to the
sql server, does this mean that we're hitting the wall
regarding memory for sql server? I also noticed something
strange, not aligned with bol, that sql server doesn't
release memory at all, because even if the server is
idle, available memory doesn't grow higher than 660MB
(there is nothing other than sql server that uses this
memory - dedicated database server)... Since performance
monitor is in this case useless (or not?), how can you
determine how much memory database engine really
uses/needs?
ThanksThe main things to look at are still Pages/sec (should be near 0 most of the
time) and Buffer Cache Hit ratio (>95% but depends on what type of workload
and you really need to be on SP3 for the number to mean anything). SQL
Server won't give back memory unless there is competition for it so its
perfectly normal for the Available Memory to stay at 660MB. Page life
expectancy,Memory grants outstanding and free buffers can also help to
narrow down memory contention
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Peja" <peja@.sympatico.ca> wrote in message
news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
> Hi,
> I have sql server 2000 standard edition (windows 2000
> server - NOT advanced) on the server which has 2560MB of
> memory. This edition has restriction of 2048MB of memory
> it can use (max memory parameter set to this value).
> I'm making performance analysis of whether we need to
> upgrade memory on this server (and use /awe) - we're
> about to add some additional load to the system.
> I collected statistics using performance monitor. Among
> many counters, I was monitoring Available Memory (MB).
> This value varies from 325 to 660.
> Given that 512MB of memory is totally invisible to the
> sql server, does this mean that we're hitting the wall
> regarding memory for sql server? I also noticed something
> strange, not aligned with bol, that sql server doesn't
> release memory at all, because even if the server is
> idle, available memory doesn't grow higher than 660MB
> (there is nothing other than sql server that uses this
> memory - dedicated database server)... Since performance
> monitor is in this case useless (or not?), how can you
> determine how much memory database engine really
> uses/needs?
> Thanks|||So Buffer Cache is not reliable if you have 2000 SP2'
>--Original Message--
>The main things to look at are still Pages/sec (should be
near 0 most of the
>time) and Buffer Cache Hit ratio (>95% but depends on
what type of workload
>and you really need to be on SP3 for the number to mean
anything). SQL
>Server won't give back memory unless there is competition
for it so its
>perfectly normal for the Available Memory to stay at
660MB. Page life
>expectancy,Memory grants outstanding and free buffers can
also help to
>narrow down memory contention
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Peja" <peja@.sympatico.ca> wrote in message
>news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
>> Hi,
>> I have sql server 2000 standard edition (windows 2000
>> server - NOT advanced) on the server which has 2560MB of
>> memory. This edition has restriction of 2048MB of memory
>> it can use (max memory parameter set to this value).
>> I'm making performance analysis of whether we need to
>> upgrade memory on this server (and use /awe) - we're
>> about to add some additional load to the system.
>> I collected statistics using performance monitor. Among
>> many counters, I was monitoring Available Memory (MB).
>> This value varies from 325 to 660.
>> Given that 512MB of memory is totally invisible to the
>> sql server, does this mean that we're hitting the wall
>> regarding memory for sql server? I also noticed
something
>> strange, not aligned with bol, that sql server doesn't
>> release memory at all, because even if the server is
>> idle, available memory doesn't grow higher than 660MB
>> (there is nothing other than sql server that uses this
>> memory - dedicated database server)... Since performance
>> monitor is in this case useless (or not?), how can you
>> determine how much memory database engine really
>> uses/needs?
>> Thanks
>
>.
>

Interesting question about memory usage - standard edition

Hi,
I have sql server 2000 standard edition (windows 2000
server - NOT advanced) on the server which has 2560MB of
memory. This edition has restriction of 2048MB of memory
it can use (max memory parameter set to this value).
I'm making performance analysis of whether we need to
upgrade memory on this server (and use /awe) - we're
about to add some additional load to the system.
I collected statistics using performance monitor. Among
many counters, I was monitoring Available Memory (MB).
This value varies from 325 to 660.
Given that 512MB of memory is totally invisible to the
sql server, does this mean that we're hitting the wall
regarding memory for sql server? I also noticed something
strange, not aligned with bol, that sql server doesn't
release memory at all, because even if the server is
idle, available memory doesn't grow higher than 660MB
(there is nothing other than sql server that uses this
memory - dedicated database server)... Since performance
monitor is in this case useless (or not?), how can you
determine how much memory database engine really
uses/needs?
ThanksThe main things to look at are still Pages/sec (should be near 0 most of the
time) and Buffer Cache Hit ratio (>95% but depends on what type of workload
and you really need to be on SP3 for the number to mean anything). SQL
Server won't give back memory unless there is competition for it so its
perfectly normal for the Available Memory to stay at 660MB. Page life
expectancy,Memory grants outstanding and free buffers can also help to
narrow down memory contention
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Peja" <peja@.sympatico.ca> wrote in message
news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
> Hi,
> I have sql server 2000 standard edition (windows 2000
> server - NOT advanced) on the server which has 2560MB of
> memory. This edition has restriction of 2048MB of memory
> it can use (max memory parameter set to this value).
> I'm making performance analysis of whether we need to
> upgrade memory on this server (and use /awe) - we're
> about to add some additional load to the system.
> I collected statistics using performance monitor. Among
> many counters, I was monitoring Available Memory (MB).
> This value varies from 325 to 660.
> Given that 512MB of memory is totally invisible to the
> sql server, does this mean that we're hitting the wall
> regarding memory for sql server? I also noticed something
> strange, not aligned with bol, that sql server doesn't
> release memory at all, because even if the server is
> idle, available memory doesn't grow higher than 660MB
> (there is nothing other than sql server that uses this
> memory - dedicated database server)... Since performance
> monitor is in this case useless (or not?), how can you
> determine how much memory database engine really
> uses/needs?
> Thanks|||So Buffer Cache is not reliable if you have 2000 SP2'

>--Original Message--
>The main things to look at are still Pages/sec (should be
near 0 most of the
>time) and Buffer Cache Hit ratio (>95% but depends on
what type of workload
>and you really need to be on SP3 for the number to mean
anything). SQL
>Server won't give back memory unless there is competition
for it so its
>perfectly normal for the Available Memory to stay at
660MB. Page life
>expectancy,Memory grants outstanding and free buffers can
also help to
>narrow down memory contention
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Peja" <peja@.sympatico.ca> wrote in message
>news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
something[vbcol=seagreen]
>
>.
>

Integrity Check Errors

Hello,
I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
patches.
I use Database Maintenance Plans to backup several of my databases and I hav
e
selected the option to check integrity before doing backup. If anything fail
s,
I get a net send notification.
This has been working extremely well for a year now but only lately,
Integrity checks reveals errors on random tables in random databases.
This morning I got a notification on a database called 'Planning'.
So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
just to make sure.
The following details were revealed: -
========================================
====================================
====
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310627712, index ID 7: Page (1:3766) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'WorkTransaction' (object ID 1310627712).
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Planning'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (Planning ).
========================================
====================================
====
I have been able to repair these errors by doing the following: -
1. Restrict access to Single User on this particular database.
2. In the Query Analyzer, run the statement :
USE Planning
go
DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH NO_INFOMSGS
this seems to correct the problem.
3. Remove Single User restriction.
However, I have been doing the above procedure 2 - 3 times a day on many
other databases and tables that come up with errors.
I was wondering what could be the root cause of all these sudden errors.
I use Simple Database Backup Recovery Model on all my databases.
Thanks,
Sameer.
Message posted via http://www.droptable.comThe most likely cause of db integrity issues (especially recurring ones) is
bad hardware. I would run some intensive hardware tests to find the root
cause.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57da24b5e8956@.uwe...
> Hello,
> I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
> patches.
> I use Database Maintenance Plans to backup several of my databases and I
> have
> selected the option to check integrity before doing backup. If anything
> fails,
> I get a net send notification.
> This has been working extremely well for a year now but only lately,
> Integrity checks reveals errors on random tables in random databases.
> This morning I got a notification on a database called 'Planning'.
> So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
> just to make sure.
> The following details were revealed: -
> ========================================
==================================
======
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1310627712, index ID 7: Page (1:3766) could not be processed.
> See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'WorkTransaction' (object ID 1310627712).
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Planning'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (Planning ).
> ========================================
==================================
======
> I have been able to repair these errors by doing the following: -
> 1. Restrict access to Single User on this particular database.
> 2. In the Query Analyzer, run the statement :
> USE Planning
> go
> DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH
> NO_INFOMSGS
> this seems to correct the problem.
> 3. Remove Single User restriction.
>
> However, I have been doing the above procedure 2 - 3 times a day on many
> other databases and tables that come up with errors.
> I was wondering what could be the root cause of all these sudden errors.
> I use Simple Database Backup Recovery Model on all my databases.
> Thanks,
> Sameer.
> --
> Message posted via http://www.droptable.com|||Thanks Andrew,
I forgot to mention that I did a comprehensive hardware diagnostics 2 times
on this past Sunday and did tests especially on Memory like 4 time using the
Dell Diagnostics software and no errors were found.
Should I doubt the results and start changing the hardware components
gradually ?
Andrew J. Kelly wrote:[vbcol=seagreen]
>The most likely cause of db integrity issues (especially recurring ones) is
>bad hardware. I would run some intensive hardware tests to find the root
>cause.
>
>[quoted text clipped - 59 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200511/1|||It still sounds like hardware related. Do you have a UPS and are you
confident of it's condition? But I just re-read your original post and
missed the part in which you were using Repair Allow data loss. That does
exactly what it says. So to fix the problems it has to destroy or loose
some data. The problem is that you don't know what data you loose and your
data integrity is not lost. Your best bet for a situation such as that is
to backup from the last known good backup. Since you are using simple mode
you can never do a restore to a point in time. That limits your restores to
the last full or differential backup. If you only do that once a night you
can loose up to 24 hours worth of work. You should rethink your backup
strategy. The fact that you have been doing this for some time now means
that you probably do not have a valid Full backup anymore. They most likely
all have some form of corruption in them. Either from what is causing the
issues or from you doing the repairs and allowing data loss. You might want
to consider creating a new db and copying the data out from one and back
into another with referential integrity constraints to see where you may
have bad data.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57db2f1476231@.uwe...
> Thanks Andrew,
> I forgot to mention that I did a comprehensive hardware diagnostics 2
> times
> on this past Sunday and did tests especially on Memory like 4 time using
> the
> Dell Diagnostics software and no errors were found.
> Should I doubt the results and start changing the hardware components
> gradually ?
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200511/1|||Thanks Andrew,
1. I will check the UPS tomorrow and keep you posted. I'll also have the
batteries in the UPS replaced as soon as I can.
2. I do a backup every hour.
3. If I create a new database, should I use a different name or preferably
keep the same name as the current one with problems ? If I change to a new
name, that would add a lot of tasks for me to change the name wherever it
exists.....all codes, queries, reports, etc.
4. Given I create a new database, regardless of the issue in point 3 above,
how do I copy the data with referential integrity ? use DTS ? or queries ?
Thanks for your guidance....I highly appreciate it.
Sameer.
Andrew J. Kelly wrote:[vbcol=seagreen]
>It still sounds like hardware related. Do you have a UPS and are you
>confident of it's condition? But I just re-read your original post and
>missed the part in which you were using Repair Allow data loss. That does
>exactly what it says. So to fix the problems it has to destroy or loose
>some data. The problem is that you don't know what data you loose and your
>data integrity is not lost. Your best bet for a situation such as that is
>to backup from the last known good backup. Since you are using simple mode
>you can never do a restore to a point in time. That limits your restores t
o
>the last full or differential backup. If you only do that once a night you
>can loose up to 24 hours worth of work. You should rethink your backup
>strategy. The fact that you have been doing this for some time now means
>that you probably do not have a valid Full backup anymore. They most likel
y
>all have some form of corruption in them. Either from what is causing the
>issues or from you doing the repairs and allowing data loss. You might wan
t
>to consider creating a new db and copying the data out from one and back
>into another with referential integrity constraints to see where you may
>have bad data.
>
>[quoted text clipped - 17 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200511/1|||3. The new db is just so that you can get the data in a know condition.
Once you have it where you are comfortable with it and want to use it in
production you can rename it after dropping the original.
4. What I meant was that if you have parent child relationships you want to
add RI constraints on the tables in the new db before you add the data.
That way you will know when you go to insert the data which rows are orphans
etc. It really wouldn't matter how you imported the data as long as you do
it in the right order. Parents first then children and so on.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57dfb788bfc8e@.uwe...
> Thanks Andrew,
> 1. I will check the UPS tomorrow and keep you posted. I'll also have the
> batteries in the UPS replaced as soon as I can.
> 2. I do a backup every hour.
> 3. If I create a new database, should I use a different name or preferably
> keep the same name as the current one with problems ? If I change to a new
> name, that would add a lot of tasks for me to change the name wherever it
> exists.....all codes, queries, reports, etc.
> 4. Given I create a new database, regardless of the issue in point 3
> above,
> how do I copy the data with referential integrity ? use DTS ? or queries ?
> Thanks for your guidance....I highly appreciate it.
> Sameer.
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200511/1|||Hi Andrew,
To give you latest status ...
1. I replaced the UPS batteries and after 4 days, corruption came back on on
e
table in the production database.
2. I also defragmented the disk and after a couple of days, corruption came
back.
3. My next step is to replace RAM chips and see what happens...
The weird thing is that when I get alerted through the DB Maintenance
Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
However, when I simply stop/start the SQL Server, the error goes away WITHOU
T
me even running a repair on that particular table.
Is there a way to flush all the connected users so that I can set the
database to a single user before repairing it ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200512/1|||> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
ALTER DATABASE with the ROLLBACK option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via droptable.com" <u4996@.uwe> wrote in message news:58622451c05f7@.uwe...green">
> Hi Andrew,
> To give you latest status ...
> 1. I replaced the UPS batteries and after 4 days, corruption came back on
one
> table in the production database.
> 2. I also defragmented the disk and after a couple of days, corruption cam
e
> back.
> 3. My next step is to replace RAM chips and see what happens...
> The weird thing is that when I get alerted through the DB Maintenance
> Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
> However, when I simply stop/start the SQL Server, the error goes away WITH
OUT
> me even running a repair on that particular table.
> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200512/1|||While the database is online and being used, I would like to stop all future
users from connecting from it, kill all current users logged-in and then
change the db to a single user mode to be repaired.
Could you please elaborate it more ?
Thanks.
Message posted via http://www.droptable.com|||Wow, it worked like a charm....
How do I reset it back to multi-user after I finish repairing it ?
Thanks a million.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200512/1

Integrity Check Errors

Hello,
I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
patches.
I use Database Maintenance Plans to backup several of my databases and I have
selected the option to check integrity before doing backup. If anything fails,
I get a net send notification.
This has been working extremely well for a year now but only lately,
Integrity checks reveals errors on random tables in random databases.
This morning I got a notification on a database called 'Planning'.
So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
just to make sure.
The following details were revealed: -
================================================================================ Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310627712, index ID 7: Page (1:3766) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'WorkTransaction' (object ID 1310627712).
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Planning'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (Planning ).
================================================================================
I have been able to repair these errors by doing the following: -
1. Restrict access to Single User on this particular database.
2. In the Query Analyzer, run the statement :
USE Planning
go
DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH NO_INFOMSGS
this seems to correct the problem.
3. Remove Single User restriction.
However, I have been doing the above procedure 2 - 3 times a day on many
other databases and tables that come up with errors.
I was wondering what could be the root cause of all these sudden errors.
I use Simple Database Backup Recovery Model on all my databases.
Thanks,
Sameer.
--
Message posted via http://www.sqlmonster.comThe most likely cause of db integrity issues (especially recurring ones) is
bad hardware. I would run some intensive hardware tests to find the root
cause.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:57da24b5e8956@.uwe...
> Hello,
> I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
> patches.
> I use Database Maintenance Plans to backup several of my databases and I
> have
> selected the option to check integrity before doing backup. If anything
> fails,
> I get a net send notification.
> This has been working extremely well for a year now but only lately,
> Integrity checks reveals errors on random tables in random databases.
> This morning I got a notification on a database called 'Planning'.
> So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
> just to make sure.
> The following details were revealed: -
> ================================================================================> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1310627712, index ID 7: Page (1:3766) could not be processed.
> See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'WorkTransaction' (object ID 1310627712).
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Planning'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (Planning ).
> ================================================================================> I have been able to repair these errors by doing the following: -
> 1. Restrict access to Single User on this particular database.
> 2. In the Query Analyzer, run the statement :
> USE Planning
> go
> DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH
> NO_INFOMSGS
> this seems to correct the problem.
> 3. Remove Single User restriction.
>
> However, I have been doing the above procedure 2 - 3 times a day on many
> other databases and tables that come up with errors.
> I was wondering what could be the root cause of all these sudden errors.
> I use Simple Database Backup Recovery Model on all my databases.
> Thanks,
> Sameer.
> --
> Message posted via http://www.sqlmonster.com|||Thanks Andrew,
I forgot to mention that I did a comprehensive hardware diagnostics 2 times
on this past Sunday and did tests especially on Memory like 4 time using the
Dell Diagnostics software and no errors were found.
Should I doubt the results and start changing the hardware components
gradually ?
Andrew J. Kelly wrote:
>The most likely cause of db integrity issues (especially recurring ones) is
>bad hardware. I would run some intensive hardware tests to find the root
>cause.
>> Hello,
>[quoted text clipped - 59 lines]
>> Sameer.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||It still sounds like hardware related. Do you have a UPS and are you
confident of it's condition? But I just re-read your original post and
missed the part in which you were using Repair Allow data loss. That does
exactly what it says. So to fix the problems it has to destroy or loose
some data. The problem is that you don't know what data you loose and your
data integrity is not lost. Your best bet for a situation such as that is
to backup from the last known good backup. Since you are using simple mode
you can never do a restore to a point in time. That limits your restores to
the last full or differential backup. If you only do that once a night you
can loose up to 24 hours worth of work. You should rethink your backup
strategy. The fact that you have been doing this for some time now means
that you probably do not have a valid Full backup anymore. They most likely
all have some form of corruption in them. Either from what is causing the
issues or from you doing the repairs and allowing data loss. You might want
to consider creating a new db and copying the data out from one and back
into another with referential integrity constraints to see where you may
have bad data.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:57db2f1476231@.uwe...
> Thanks Andrew,
> I forgot to mention that I did a comprehensive hardware diagnostics 2
> times
> on this past Sunday and did tests especially on Memory like 4 time using
> the
> Dell Diagnostics software and no errors were found.
> Should I doubt the results and start changing the hardware components
> gradually ?
>
> Andrew J. Kelly wrote:
>>The most likely cause of db integrity issues (especially recurring ones)
>>is
>>bad hardware. I would run some intensive hardware tests to find the root
>>cause.
>> Hello,
>>[quoted text clipped - 59 lines]
>> Sameer.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||Thanks Andrew,
1. I will check the UPS tomorrow and keep you posted. I'll also have the
batteries in the UPS replaced as soon as I can.
2. I do a backup every hour.
3. If I create a new database, should I use a different name or preferably
keep the same name as the current one with problems ? If I change to a new
name, that would add a lot of tasks for me to change the name wherever it
exists.....all codes, queries, reports, etc.
4. Given I create a new database, regardless of the issue in point 3 above,
how do I copy the data with referential integrity ? use DTS ? or queries ?
Thanks for your guidance....I highly appreciate it.
Sameer.
Andrew J. Kelly wrote:
>It still sounds like hardware related. Do you have a UPS and are you
>confident of it's condition? But I just re-read your original post and
>missed the part in which you were using Repair Allow data loss. That does
>exactly what it says. So to fix the problems it has to destroy or loose
>some data. The problem is that you don't know what data you loose and your
>data integrity is not lost. Your best bet for a situation such as that is
>to backup from the last known good backup. Since you are using simple mode
>you can never do a restore to a point in time. That limits your restores to
>the last full or differential backup. If you only do that once a night you
>can loose up to 24 hours worth of work. You should rethink your backup
>strategy. The fact that you have been doing this for some time now means
>that you probably do not have a valid Full backup anymore. They most likely
>all have some form of corruption in them. Either from what is causing the
>issues or from you doing the repairs and allowing data loss. You might want
>to consider creating a new db and copying the data out from one and back
>into another with referential integrity constraints to see where you may
>have bad data.
>> Thanks Andrew,
>[quoted text clipped - 17 lines]
>> Sameer.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||3. The new db is just so that you can get the data in a know condition.
Once you have it where you are comfortable with it and want to use it in
production you can rename it after dropping the original.
4. What I meant was that if you have parent child relationships you want to
add RI constraints on the tables in the new db before you add the data.
That way you will know when you go to insert the data which rows are orphans
etc. It really wouldn't matter how you imported the data as long as you do
it in the right order. Parents first then children and so on.
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:57dfb788bfc8e@.uwe...
> Thanks Andrew,
> 1. I will check the UPS tomorrow and keep you posted. I'll also have the
> batteries in the UPS replaced as soon as I can.
> 2. I do a backup every hour.
> 3. If I create a new database, should I use a different name or preferably
> keep the same name as the current one with problems ? If I change to a new
> name, that would add a lot of tasks for me to change the name wherever it
> exists.....all codes, queries, reports, etc.
> 4. Given I create a new database, regardless of the issue in point 3
> above,
> how do I copy the data with referential integrity ? use DTS ? or queries ?
> Thanks for your guidance....I highly appreciate it.
> Sameer.
>
> Andrew J. Kelly wrote:
>>It still sounds like hardware related. Do you have a UPS and are you
>>confident of it's condition? But I just re-read your original post and
>>missed the part in which you were using Repair Allow data loss. That does
>>exactly what it says. So to fix the problems it has to destroy or loose
>>some data. The problem is that you don't know what data you loose and
>>your
>>data integrity is not lost. Your best bet for a situation such as that is
>>to backup from the last known good backup. Since you are using simple
>>mode
>>you can never do a restore to a point in time. That limits your restores
>>to
>>the last full or differential backup. If you only do that once a night
>>you
>>can loose up to 24 hours worth of work. You should rethink your backup
>>strategy. The fact that you have been doing this for some time now means
>>that you probably do not have a valid Full backup anymore. They most
>>likely
>>all have some form of corruption in them. Either from what is causing the
>>issues or from you doing the repairs and allowing data loss. You might
>>want
>>to consider creating a new db and copying the data out from one and back
>>into another with referential integrity constraints to see where you may
>>have bad data.
>> Thanks Andrew,
>>[quoted text clipped - 17 lines]
>> Sameer.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||Hi Andrew,
To give you latest status ...
1. I replaced the UPS batteries and after 4 days, corruption came back on one
table in the production database.
2. I also defragmented the disk and after a couple of days, corruption came
back.
3. My next step is to replace RAM chips and see what happens...
The weird thing is that when I get alerted through the DB Maintenance
Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
However, when I simply stop/start the SQL Server, the error goes away WITHOUT
me even running a repair on that particular table.
Is there a way to flush all the connected users so that I can set the
database to a single user before repairing it ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
ALTER DATABASE with the ROLLBACK option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message news:58622451c05f7@.uwe...
> Hi Andrew,
> To give you latest status ...
> 1. I replaced the UPS batteries and after 4 days, corruption came back on one
> table in the production database.
> 2. I also defragmented the disk and after a couple of days, corruption came
> back.
> 3. My next step is to replace RAM chips and see what happens...
> The weird thing is that when I get alerted through the DB Maintenance
> Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
> However, when I simply stop/start the SQL Server, the error goes away WITHOUT
> me even running a repair on that particular table.
> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||While the database is online and being used, I would like to stop all future
users from connecting from it, kill all current users logged-in and then
change the db to a single user mode to be repaired.
Could you please elaborate it more ?
Thanks.
--
Message posted via http://www.sqlmonster.com|||ALTER DATABASE dbname SET SINGLE_USER WITH ROILLBACK IMMEDIATE
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message news:5863a80856c4f@.uwe...
> While the database is online and being used, I would like to stop all future
> users from connecting from it, kill all current users logged-in and then
> change the db to a single user mode to be repaired.
> Could you please elaborate it more ?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Wow, it worked like a charm....
How do I reset it back to multi-user after I finish repairing it ?
Thanks a million.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||ALTER DATABASE dbname SET MULTI_USER
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message news:5863d7195c667@.uwe...
> Wow, it worked like a charm....
> How do I reset it back to multi-user after I finish repairing it ?
> Thanks a million.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||Thanks Mr. Karaszi ... I appreciate it !
Now only if this recurring Integrity errors would vanish away after I replace
the RAM, hopefully ....
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||You are not using a RAM disk by any chance are you?
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:58622451c05f7@.uwe...
> Hi Andrew,
> To give you latest status ...
> 1. I replaced the UPS batteries and after 4 days, corruption came back on
> one
> table in the production database.
> 2. I also defragmented the disk and after a couple of days, corruption
> came
> back.
> 3. My next step is to replace RAM chips and see what happens...
> The weird thing is that when I get alerted through the DB Maintenance
> Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
> However, when I simply stop/start the SQL Server, the error goes away
> WITHOUT
> me even running a repair on that particular table.
> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||Hi Andrew,
Thanks for coming back....what's a RAM disk ?
We're using a simple Primary Hard Disk being mirrored to a Secondary Hard
Disk using Windows Disk Mirroring functionality.
For Memory, we use 2 X 512 MB chips on 2 dimm slots.
Next plan is to buy 1 X 1GB memory chip and put it in 1 dimm slot and if need
be, buy another one and fill the last dimm slot.
--
Message posted via http://www.sqlmonster.com|||If you don't know then you are most likely not using it<g>. But you are
using software raid vs. hardware. I don't know if I would trust the
software mirroring. Maybe it's OK but that would be my guess. You might
want to look into a lug in card to do the mirroring.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:58683e30c7369@.uwe...
> Hi Andrew,
> Thanks for coming back....what's a RAM disk ?
> We're using a simple Primary Hard Disk being mirrored to a Secondary Hard
> Disk using Windows Disk Mirroring functionality.
> For Memory, we use 2 X 512 MB chips on 2 dimm slots.
> Next plan is to buy 1 X 1GB memory chip and put it in 1 dimm slot and if
> need
> be, buy another one and fill the last dimm slot.
> --
> Message posted via http://www.sqlmonster.com|||1. Unfortunately, Hardware Mirroring is out of question for our small company,
atleast for now.
But just to verify if Software Mirroring is the cause, then I will
temporarily disable it and observe what happens.
2. I usually get corruptions in 2 tables (each are over half-a-million
records). I don't have any additional indexes in those tables apart from the
Primary Key [Id] column.
......2.1 Is it possible that indexes get corrupted for heavy tables ? If
so, how do I safely rebuild indexes
3. The 2 tables I mentioned above, each table gets populated by its own
corresponding Client VB App developed by one of the consultatnts we had hired.
However, even when I do inspect the code and design of the app, I don't seem
to find any flaws.
......3.1 Could those client apps causing the trouble ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||It makes sense that the two most heavily used tables are the ones you see
the most issues with. Since they are changing more than the others there is
more opportunity for issues to arise. But the act of Inserting , Updating
and Deleting too much should not be a concern. Indexes don't get corrupted
after you reach a certain change threshold. I still think the hardware or
now maybe the mirroring software is the root cause. Hardware controllers
that do mirroring these days are extremely inexpensive. Most systems have
the capability built into the mother boards now. I think you will find it
is cheaper than you think and worth exploring.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:586f11a2109af@.uwe...
> 1. Unfortunately, Hardware Mirroring is out of question for our small
> company,
> atleast for now.
> But just to verify if Software Mirroring is the cause, then I will
> temporarily disable it and observe what happens.
>
> 2. I usually get corruptions in 2 tables (each are over half-a-million
> records). I don't have any additional indexes in those tables apart from
> the
> Primary Key [Id] column.
> ......2.1 Is it possible that indexes get corrupted for heavy tables ?
> If
> so, how do I safely rebuild indexes
>
> 3. The 2 tables I mentioned above, each table gets populated by its own
> corresponding Client VB App developed by one of the consultatnts we had
> hired.
> However, even when I do inspect the code and design of the app, I don't
> seem
> to find any flaws.
> ......3.1 Could those client apps causing the trouble ?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1