May 14, 2009 at 11:16 am
Is it possible that a single table is corrupted and not the entire database?
My problem :
I have a table where employees enter their sales (product, volume, etc.) that table is the only table that gets massive insertions all day long. The table is on a SQL server and the employees use a MS Access interface to insert them into the table. For an unknown reason, something went wrong. I can query the table and get results both from MS Access and SQL Management Studio but employees cannot insert their sales in the table.
Any idea?
May 14, 2009 at 11:25 am
Could you post the error raised when trying to insert data??
May 14, 2009 at 11:45 am
TcW_1978 (5/14/2009)
Is it possible that a single table is corrupted and not the entire database?
Yes, absolutely. That's the more common form of corruption, just a single page.
What's the error that you get? To see if there is corruption, run the following and post the full output.
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2009 at 11:48 am
FelixG (5/14/2009)
Could you post the error raised when trying to insert data??
Just the pop-up : "ODBC Error on tblSales"
I'm running the DBCC, I'll post the results as soon as possible.
May 14, 2009 at 12:05 pm
It gives me "Command(s) completed successfully.", nothing else.
Does that mean the database is not corrupted? Or that there is something I don't see right now on my screen?
May 14, 2009 at 12:44 pm
DBCC CHECKTABLE ('TableName') will check your specific table... however if DBCC CHECKDB is not returning errors then the table seems to be OK... check your datasource params, because it looks like you have an ODBC error and not a SQLServer error
May 14, 2009 at 12:50 pm
DBCC results for 'TBL72VTESINPUT'.
There are 1276304 rows in 50618 pages for object "TBL72VTESINPUT".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So it seems the table is not corrupted, back to square one...
May 14, 2009 at 1:06 pm
You should try to insert a record using a query directly in the management studio:
insert sales (code,amount) values ('A001',1000)
and see what happens....
May 14, 2009 at 2:31 pm
TcW_1978 (5/14/2009)
So it seems the table is not corrupted, back to square one...
Nope. No corruption.
What's the error messages that you're getting?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2009 at 11:31 am
The situation is this : the problem persisted for several days, one week to be +/- exact... and now everything's back to normal, why? Didn't find out yet...
When I go to the Error Logs, I got an entry for 2009-05-06 morning, the next one is for 2009-05-14 3h00AM.
The only thing that seemed to work is to reboot the server, the server is rebooted each day at 3h00AM, since the problems occured. I don't think it's the best idea, but it's not my decision, that's an whole another ballgame.
What I wanted to have your opinion on is, since the server is rebooted the Error Logs of SQL Studio has entries for each day, 3h00AM that read :
1:
[165] ODBC Error: 0, Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
2:
[382] Logon to server 'NC0275CON01' failed (ConnAttemptCachableOp)
3:
[382] Logon to server '(local)' failed (ConnUpdateJobActivity_NextScheduledRunDate)
4:
[382] Logon to server '(local)' failed (SaveAllSchedules)
5:
[382] Logon to server '(local)' failed (DisableAgentXPs)
Any idea or suggestion?
May 20, 2009 at 11:41 am
Found the link : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22542188.html
Which seems to be very similar :
SQL Server is cutting off client connections intermittently and I'm having a hard time figuring out why. Restarting the SQL server service fixes the issue for a few days but then it happens again. It's SQL Server 2005 on Windows SBS 2003 with XP and Win2000 clients connecting. Clients connect via a DSN file--they run an Access database. I run daily backups and all maintenance tasks on the databases and these happen without any errors.
Here are some of the error messages I get from the SQL Log:
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [CLIENT: 192.168.1.16]
Error: 18056, Severity: 20, State: 23.
The client was unable to reuse a session with SPID 72, which had been reset for conection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x862. Administrator should deregister this SPN manually to avoid client authentication errors.
And a recurring error in the SQL Agent Log:
[165] ODBC Error: 0, Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
any clues into how i can further troubleshoot would be appreciated. I can't reproduce the error on command but it is bound to happen "randomly" during business hours and i really need it to stop. any insight appreciated.
But no answers yet on how to solve the problem...
May 21, 2009 at 7:53 am
It seems problem with spn. When sql server starts, it creates spn in Active Directory. The SPN is an object in Active Directory. You can set the permissions with adsiedit.msc. Check the permissions, who can drop or change this object - you can set only admins for testing for example. Usefull is also setspn utility.
May 21, 2009 at 9:09 am
nekonecny (5/21/2009)
It seems problem with spn. When sql server starts, it creates spn in Active Directory. The SPN is an object in Active Directory. You can set the permissions with adsiedit.msc. Check the permissions, who can drop or change this object - you can set only admins for testing for example. Usefull is also setspn utility.
Thanks nekonecny, I've sent this to the department that manages the AD, we'll see what happens next.
I'll post something as soon as I get an answer... to be continued, lol.
May 21, 2009 at 12:11 pm
I've had problems with Access front end to SQL Server back end like the one you describe.
Access is a funny beast when dealing with SQL Server when the forms are bound to the database. The situation I had seemed to be caused by bound forms being left open over night. This interfered with the database maintenance tasks and caused one of them to hang in a deadlock. In the morning the users couldn't insert or update the Customers table, which was where the deadlock occurred. After either a re-boot or just stopping and re-starting SQL Server the problem went away.
Having the users make sure they closed the Access program before going home also handled the problem.
Another client had a similar problem and couldn't get the users to close the Access program. I wrote a stored procedure that killed all connections opened by Microsoft Office before the nightly maintenance and this seemed to handle the problem.
Todd Fifield
May 21, 2009 at 1:52 pm
Thanks, I'll try it tonight. I don't know much about the SQL system tables, is there a way I can find deadlocks and kill them?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply