May 21, 2004 at 7:14 am
I have a database maintenance plan which only checks database integrity with indexes everyday at the same hour. Sometimes it works fine, sometimes I get this error:
ConnectionCheckForData (CheckforData()). [SQLSTATE 01000] (Message 10054) General network error. Check your network documentation. [SQLSTATE 08S01] (Error 11). The step failed.
If I check the log when it fials I find errors like:
<dbname> check integrity failed: This server has been disconnected. You must reconnect to perform this operation.
Did anybody have the same problem? aby possible solution will be highly appreciated.
May 21, 2004 at 7:45 am
I haven't seen this, but the general network error means the server didn't repond to a request. From what you have listed, it's possible you have some structural problem that forcibly disconnected the connection. It should have raised an error in the SQL Server error log. Can you check there.?
Can you run a DBCC CHECKDB?
May 21, 2004 at 7:54 am
Yes, I can run the DBCC CHECKDB and gives no error.
In the SQL Server log I only see that the job failed. The"General network..." is shown by the Job History.
And the same job sometimes works fine and sometimes not. I suspect some momentary activity on the server or on the netwerk but I don't know how to catch it!
May 21, 2004 at 9:14 am
I've seen this many times. It happens to me when it tries to correct indexes and someone is still logged in or they a login has stayed active to the database.
I don't correct indexes on the maintance plan. I use a stored proc. I can't explain why but this doesn't have the same issue scheduled once a week. Probably cause I chose the weekend to do it. It will keep your indexes from fragmenting and stop that error from killing your maintenance plan. If you backup transaction logs after the check like I do, the backup will not run.
So just uncheck indexes in your mantenance plan and create this stored proc to run once a week.
Create Procedure dbo.IndexDefrag
as
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 20.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
May 21, 2004 at 9:36 am
Thank you, Edward, I will try to do it this way.
However, I am not sure I have the same problem. I have only checked the box for "Include indexes", and not the "attempt to repair..." one. But you might be right, something is still connected to some of the databases but I don't know what (process) or who. In SQL Server log I see a lot of "Login successfull" for SQL Server account, but nothing about what was going on. And there is no regular job that I know running at that particular moment...
Anyways, thank you.
May 21, 2004 at 10:19 am
I read it wrong then. I get that problem when attempting to repair.
Did you try spacing things in the plan further apart? It could be stepping on itself. But if you are repairing indexes with my script then you wont have to even check the indexes. The problem may clear up.
One way I debug maintenance plans is to go to the actual sql code generated in jobs and run the code. Thats how I found the cause of the repair problem. It may help you to run the code in query during the day. Errors in query analyzer are sometimes more robust than the ones given by sql agent.
May 24, 2004 at 8:06 pm
I had the exact same error returned in Query Analyzer when performing some intensive data loads.
In my case, the database files reside on a SAN. When SQL Server got to the most intensive part of my process CPU would sky rocket to 100% and the Server would lock up and was unable to maintain a connection to it's database files on the SAN. After a period of time it appeared to time out and that's when I'd get the error message.
I resolved my problem by unchecking 'Boost SQL Server Priority on Windows' Under the Processes tab of the SQL Server Properties.
I would try and monitor your performance and see if you are maxing out on CPU or memory.
Good luck
Angela
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply