Weird Issue
-
It is not my first time to have the same issue when using Microsoft products.
If I run a program a batch-by-batch, it has no issue at all. If I run the whole program through, I will get stange errors. Here is one example I came across recently.
USE [A]
GO
DBCC CHECKDB('A')
GO
USE
GO
DBCC CHECKDB('B')
GO
When I ran it through, it works sometimes and fails sometimes. If I run a batch-by-batch, I never have any issue.
The error I got is below:
DBCC results for 'sysobjects'. [SQLSTATE 01000] (Message 2536) There are 214 rows in 5 pages for object 'sysobjects'. [SQLSTATE 01000] (Message 2593) DBCC results for 'sysindexes'. [SQLSTATE 01000] (Message 2536) There are 230 rows in 11 pages for object 'sysindexes'. [SQLSTATE 01000] (Message 2593) DBCC results for 'syscolumns'. [SQLSTATE 01000] (Message 2536) There are 731 rows in 15 pages for object 'syscolumns'. [SQLSTATE 01000] (Message 2593) DBCC results for 'systypes'. [SQLSTATE 01000] (Message 2536) There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000] (Message 2593) DBCC results for 'syscomments'. [SQLSTATE 01000] (Message 2536) There are 187 rows in 13 pages for object 'syscomments'. [SQLSTATE 01000] (Message 2593) DBCC results for 'sysfiles1'. [SQLSTATE 01000] (Message 2536) There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000] (Message 2593) DBCC results for 'syspermissions'. [SQLSTATE 01000] (... The step failed.
Anyone came across the same issue? Any input will be greatly appreciated.
-
Could you run the DBCC CHECKDB commands WITH NO_INFOMSGS?
-
Maybe check tempdb, when you are using CHECKDB in one transaction it might be putting pressure on your tempdb?
Mohit.
[font="Arial"]---Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font] -
First of all, thank you all for your inputs.
All of my databases are very small (less than 1 GB). I checked CPU and memory. Their usages are both in very low level when executing those scripts. Therefore, executing those scripts should not apply high pressure on tempdb.
This kind of issue does not only happen in T-Script in MS SQL Server, but also in SSIS packages, and Visual Basic. It makes people very frustrated while debugging.
But we do not have any such a case in DB2, Sybase, MySQL, and Oracle so far. We guess Microsoft must have its own way to write a result in fly. When we try to get a result calcuated in the previous scripts, it may not be in the right place as we think yet.
Any input will be greatly appreciated.
-
Hmmm please check the log file for more detailed error information. The SQL Server job history doesn't store detailed information; what you pasted above doesn't tell much. It's failing for some other reason not because of the output. What is your server configuration? If CPU/Memory is low but you are having long execution time (which eventually error out) maybe you have blocking issues?
Thanks.
Mohit.
[font="Arial"]---Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font] -
The message you have listed is not an error. It is the normal informational output from CHECKDB and that is why is suggested specifying WITH NO_INFOMSGS, so you can see the wood for the trees.
You last posts mentions SSIS and VB, so that does that mean it is not just CHECKDB that has the problem?
Some database corruption could cause other programs to have erratic success, so you should still do the above.
What error messages do you get from VB? Does anything get logged in the error log? Have a look in the windows event log.
-
The T-Script I used to check database did not only provide informative message, it also caused the whole script failed. The job was terminated and marked as failure.
In VB, we got run-time error while executing a program through. I cannot remember the run-time error number. If we ran it step-by-step, it worked perfectly.
After discussing with others and having some logical thinks, we believe this issue is caused by the way data is written. For example, we have data sync, direct IO, and Cache IO options in Sybase database server. In hardware configuration, we have data write-through.
Although we can solve this problem technically in scripts, we still feel frustrated because this kind issue is hard to predict. I greatly appreciate all of you for sharing your experiences and knowledge.
-
I would implement a trace on your server that tracks all blocking relating issues; this can help you narrow down which queries are causing the issue. You can do this using SQL Server 2005 profiler :).
From there you can performace tuning those tables to add proper indexes, cluster indexes or performace tune the queires so they are not doing wide selects.
Thanks.
Mohit.
[font="Arial"]---Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font] -
SQL ORACLE (3/24/2009)
The T-Script I used to check database did not only provide informative message, it also caused the whole script failed. The job was terminated and marked as failure.We can't help without seeing the entire error. Set the job step to log to a file and then post the full content of the file here. Whatever error it is is getting cut off.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gilamonster:
The error message is in the top post. What I changed is only the database name.
-
SQL ORACLE (3/26/2009)
Gilamonster:The error message is in the top post. What I changed is only the database name.
That's not the complete error. The job history cuts off after a certain number of characters (as shown by the ... at the end of that error). Since it's cur off, the actual error is not there
Please get the job to log to a text file so that you can get the entire error message.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply