July 12, 2011 at 2:40 pm
Thank you James.
But my cursor depends on different table suppose (main_table). and I want to find dependency of table read from main_table. And I am storing it into another table. I will try to do UPDATE in one statement and making cursor READ_ONLY. But i do not think its because of the lock on cursor.
September 4, 2011 at 9:18 pm
Hi there,
I have seen the same issue on my production box.
The problems looks like SQL Server works incorrectly with several sub-queries using <> on NOT IN expressions.
There is a workaround. You can use JOINs instead of sub-queries in order to overcome this issue.
October 6, 2011 at 8:46 pm
I have the same error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
When I execute:
select top 10 * from sysindexes
I have SQL SERVER 2005 service pack 4
Any idea???
October 7, 2011 at 12:53 am
Please run the following and post the full and complete results
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
October 10, 2011 at 9:48 am
The result of
DBCC CHECKDB ('DatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS
is:
Command(s) completed successfully.
October 10, 2011 at 10:01 am
The server was reinitialized and the next time I ran the same command it worked!!!
Thanks, I will be monitoring if this problem happens again.
April 19, 2012 at 1:27 pm
I had the same errors on one of my development servers.
As requested, I ran DBCC CHECKDB (<DATABASENAME>) WITH NO_INFOMSGS, ALL_ERRORMSGS.
The errors generated indicated either a replication errror or that the server ran out of space. Upon checking the server for space, I found I had none. I had to attach, then delete some old (useless) databases. I then reran the DBCC CHECKDB. It came back without errors.
September 20, 2012 at 3:04 pm
One of my team members hit this today will updating statistics. He got around it by creating a new table and loading the data in it (SELECT/INTO).
A DBCC checktable of the original table suggests no issues.
Still working on this one...
February 14, 2013 at 4:06 pm
GilaMonster (10/7/2011)
Please run the following and post the full and complete results
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Hi Gila, please can you help me? I have test server, and I inserted lot of rows for test with NOLOCK hint and then i got first error
Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'MCS_BIDB' is full due to 'LOG_BACKUP'.
Then i found this topic and ran this query and I got this errors
Msg 5901, Level 16, State 1, Line 1
One or more recovery units belonging to database 'MCS_BIDB' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 8, Line 1
A database snapshot cannot be created because it failed to start.
Could not write a checkpoint record in database MCS_BIDB because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'MCS_BIDB' is full due to 'LOG_BACKUP'.
February 15, 2013 at 12:55 am
tony28 (2/14/2013)
Hi Gila, please can you help me? I have test server, and I inserted lot of rows for test with NOLOCK hint and then i got first errorMsg 9002, Level 17, State 2, Line 1
The transaction log for database 'MCS_BIDB' is full due to 'LOG_BACKUP'.
Switch the DB to simple recovery or take a log backup. That's not database corruption, that's a full transaction log
Please read through this - Managing Transaction Logs[/url]
Also, ditch that nolock hint. It has no effect on inserts.
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
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
February 15, 2013 at 8:15 am
February 15, 2013 at 9:45 am
tony28 (2/15/2013)
I solved it yesterday yet, i deattached DB and then attached and recovery..
That is NOT how you solve a full transaction log.
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
February 15, 2013 at 10:11 am
February 15, 2013 at 10:16 am
tony28 (2/15/2013)
hmm, but after this I can do query and etc...
Maybe, but what you did is NOT the way you fix that 'error' (which had nothing to do with corruption anyway). You don't need to detach, restart or in any other way take down a database to fix a log full error.
I hope you're not a DBA...
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
February 15, 2013 at 10:55 am
it was just on test DB, if i did this test http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx and after appeared error with FULL
BACKUP LOG and when I wrote command dbcc shrinkdatabase ('Your-Data-Base-Name', TRUNCATEONLY) it showed error like this topic
after I found this site, where I first tried very inelegant way, how someone writes and it worked, then i could do this test and it worked.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply