Hi,
Can someone explain the following info came from sql server error log? This
is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS is
Windows 2003 (64 bit, attached to SAN) named instance database server.
11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
specified network name is no longer available. [SQLSTATE 08S01]
Is above error means the net work not available for database instance while
application batch job running from other server connect to database server?
11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handle is
0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\market_place\data\mp_orders_dat.ndf] in database [market_place](13). The
OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is:
0x000000307e4c000
Is above info means the SAN drive is very busy and took long time to
complete the database processes such as query, insert, update and delete, etc?
Thanks!
Chen
Check this out;
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
Also review this KB article:
http://support.microsoft.com/default.aspx/kb/897284
Linchi
"Chen" wrote:
> Hi,
> Can someone explain the following info came from sql server error log? This
> is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS is
> Windows 2003 (64 bit, attached to SAN) named instance database server.
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication link
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
> specified network name is no longer available. [SQLSTATE 08S01]
> Is above error means the net work not available for database instance while
> application batch job running from other server connect to database server?
> 11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handle is
> 0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
> 11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\market_place\data\mp_orders_dat.ndf] in database [market_place](13). The
> OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is:
> 0x000000307e4c000
> Is above info means the SAN drive is very busy and took long time to
> complete the database processes such as query, insert, update and delete, etc?
> Thanks!
> Chen
>
Showing posts with label sp1. Show all posts
Showing posts with label sp1. Show all posts
Wednesday, March 28, 2012
Information came from sql error log.
Hi,
Can someone explain the following info came from sql server error log? This
is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS i
s
Windows 2003 (64 bit, attached to SAN) named instance database server.
11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
specified network name is no longer available. [SQLSTATE 08S01]
Is above error means the net work not available for database instance while
application batch job running from other server connect to database server?
11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handle
is
0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\market_place\data\mp_orders_dat.ndf] in database [market_place](
13). The
OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is:
0x000000307e4c000
Is above info means the SAN drive is very busy and took long time to
complete the database processes such as query, insert, update and delete, et
c?
Thanks!
ChenCheck this out;
http://blogs.msdn.com/sqlserverstor.../21/642314.aspx
Also review this KB article:
http://support.microsoft.com/default.aspx/kb/897284
Linchi
"Chen" wrote:
> Hi,
> Can someone explain the following info came from sql server error log? Thi
s
> is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS
is
> Windows 2003 (64 bit, attached to SAN) named instance database server.
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication li
nk
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
> specified network name is no longer available. [SQLSTATE 08S01]
> Is above error means the net work not available for database instance whil
e
> application batch job running from other server connect to database server
?
> 11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handl
e is
> 0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
> 11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\market_place\data\mp_orders_dat.ndf] in database [market_place
](13). The
> OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is
:
> 0x000000307e4c000
> Is above info means the SAN drive is very busy and took long time to
> complete the database processes such as query, insert, update and delete,
etc?
> Thanks!
> Chen
>sql
Can someone explain the following info came from sql server error log? This
is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS i
s
Windows 2003 (64 bit, attached to SAN) named instance database server.
11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
specified network name is no longer available. [SQLSTATE 08S01]
Is above error means the net work not available for database instance while
application batch job running from other server connect to database server?
11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handle
is
0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\market_place\data\mp_orders_dat.ndf] in database [market_place](
13). The
OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is:
0x000000307e4c000
Is above info means the SAN drive is very busy and took long time to
complete the database processes such as query, insert, update and delete, et
c?
Thanks!
ChenCheck this out;
http://blogs.msdn.com/sqlserverstor.../21/642314.aspx
Also review this KB article:
http://support.microsoft.com/default.aspx/kb/897284
Linchi
"Chen" wrote:
> Hi,
> Can someone explain the following info came from sql server error log? Thi
s
> is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS
is
> Windows 2003 (64 bit, attached to SAN) named instance database server.
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication li
nk
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
> specified network name is no longer available. [SQLSTATE 08S01]
> Is above error means the net work not available for database instance whil
e
> application batch job running from other server connect to database server
?
> 11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handl
e is
> 0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
> 11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\market_place\data\mp_orders_dat.ndf] in database [market_place
](13). The
> OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is
:
> 0x000000307e4c000
> Is above info means the SAN drive is very busy and took long time to
> complete the database processes such as query, insert, update and delete,
etc?
> Thanks!
> Chen
>sql
Friday, March 9, 2012
Indexes stop working
We have about 10 sites using SQL Server 2000 SP1 or 3 or 3a, across two servers. The publisher/distributor performs transactional replication on three databases to the 'secondary' server.
Nearly all of the sites, at one time or another have experienced the indices on one of the tables simply stop working. Its always the same indices on a table that can have between 70,000 and a 300, 000 rows.
Before I start posting schema or index details anyone come across this before?Only once. Actually, now that I think about it, not even once. Do you have Auto-Stats set on for these databases? Or does UPDATE STATISTICS tablename resolve the problem?|||Yeah, that's happened to me a couple of times. Well two or three times, anyway. Or once. Maybe once. No, not even once. What the heck do you mean "stopped working", anyway? Are they not showing up in the mornings, or are your indexes just sitting in their cubes staring at the screensaver?|||auto update statistics was on so switched this off and ran update statistics again manually to no avail. The table in question is as follows:
CREATE TABLE [dbo].[rawdata] (
[evtime] [datetime] NOT NULL ,
[type] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[srcid] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[rawinfo] [varchar] (80) COLLATE Latin1_General_CI_AS NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL ,
[seqno_yr] [int] NOT NULL ,
[seqno] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata] ON [dbo].[rawdata]([type], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_1] ON [dbo].[rawdata]([srcid], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_2] ON [dbo].[rawdata]([evtime]) ON [PRIMARY]
GO
The problem, when it starts happening, means the following query stops returning data. Using different criteria by matching exactly rather than on a range in evtime works proving the data is there.
select * from rawdata where evtime between '2004-11-01 00:00:00.000' and '2004-11-02 00:00:00.000'
I am not sure whether transactional replication has anything to do with the cause. Because this data is only needed occasionally, we can get away with dropping the subscription, dropping the publication then dropping and recreating the indexes. We then publish and create a subscription to the data. It lasts for about 3 or 4 days then it goes again.
There are no maintenance plans set for this database which is possibly the problem. Running DBCC SHOWCONTIG never completes - it just sits there.
Help!|||You will want to leave Auto-stats on.
How many rows in the table? And how long has DBCC Showcontig gone for, before you give up on it? Have you run DBCC CHECKTABLE on this table?
Nearly all of the sites, at one time or another have experienced the indices on one of the tables simply stop working. Its always the same indices on a table that can have between 70,000 and a 300, 000 rows.
Before I start posting schema or index details anyone come across this before?Only once. Actually, now that I think about it, not even once. Do you have Auto-Stats set on for these databases? Or does UPDATE STATISTICS tablename resolve the problem?|||Yeah, that's happened to me a couple of times. Well two or three times, anyway. Or once. Maybe once. No, not even once. What the heck do you mean "stopped working", anyway? Are they not showing up in the mornings, or are your indexes just sitting in their cubes staring at the screensaver?|||auto update statistics was on so switched this off and ran update statistics again manually to no avail. The table in question is as follows:
CREATE TABLE [dbo].[rawdata] (
[evtime] [datetime] NOT NULL ,
[type] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[srcid] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[rawinfo] [varchar] (80) COLLATE Latin1_General_CI_AS NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL ,
[seqno_yr] [int] NOT NULL ,
[seqno] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata] ON [dbo].[rawdata]([type], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_1] ON [dbo].[rawdata]([srcid], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_2] ON [dbo].[rawdata]([evtime]) ON [PRIMARY]
GO
The problem, when it starts happening, means the following query stops returning data. Using different criteria by matching exactly rather than on a range in evtime works proving the data is there.
select * from rawdata where evtime between '2004-11-01 00:00:00.000' and '2004-11-02 00:00:00.000'
I am not sure whether transactional replication has anything to do with the cause. Because this data is only needed occasionally, we can get away with dropping the subscription, dropping the publication then dropping and recreating the indexes. We then publish and create a subscription to the data. It lasts for about 3 or 4 days then it goes again.
There are no maintenance plans set for this database which is possibly the problem. Running DBCC SHOWCONTIG never completes - it just sits there.
Help!|||You will want to leave Auto-stats on.
How many rows in the table? And how long has DBCC Showcontig gone for, before you give up on it? Have you run DBCC CHECKTABLE on this table?
Subscribe to:
Posts (Atom)