SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A severe error occurred on the current command.The results, if any, should be discarded.


A severe error occurred on the current command.The results, if any, should be discarded.

Author
Message
Maria Claudia Sanchez
Maria Claudia Sanchez
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 50
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.
Bill Pepping
Bill Pepping
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 506
I had the same errors on one of my development servers.

As requested, I ran DBCC CHECKDB (<DATABASENAME>Wink 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.
davidturpin
davidturpin
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 186
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...
tony28
tony28
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1612 Visits: 889
GilaMonster (10/7/2011)
Please run the following and post the full and complete results


DBCC CHECKDB (<Database Name>Wink 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'.




How to post data/code on a forum to get the best help: Option 1 / Option 2
GilaMonster
GilaMonster
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222161 Visits: 46285
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 error
Msg 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

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


tony28
tony28
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1612 Visits: 889
I solved it yesterday yet, i deattached DB and then attached and recovery.. thx for link, on night shift I will read.

thx for response



How to post data/code on a forum to get the best help: Option 1 / Option 2
GilaMonster
GilaMonster
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222161 Visits: 46285
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


tony28
tony28
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1612 Visits: 889
hmm, but after this I could do again query and etc... at work I will read your link



How to post data/code on a forum to get the best help: Option 1 / Option 2
GilaMonster
GilaMonster
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222161 Visits: 46285
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


tony28
tony28
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1612 Visits: 889
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.
http://stackoverflow.com/questions/56628/how-do-you-clear-the-transaction-log-in-a-sql-server-2005-database



How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search