Transaction Log Full in Simple Recovery mode

Question

Post reply

Transaction Log Full in Simple Recovery mode

Viewing 15 posts - 16 through 30 (of 54 total)

You must be logged in to reply to this topic. Login to reply

clare.xia SSCrazy Points: 2569 More actions · Answer 1

clare.xia

SSCrazy

Points: 2569

February 8, 2011 at 5:51 pm

#1284140

Hi GilaMonster,

Do you have more information of how to recreate the transaction log? I am worried about the situation you talked about (Error: log cannot be rebuilt because the database was not cleanly shut down)..

Thanks SQL GURU!

clare.xia SSCrazy Points: 2569 More actions · Answer 2

clare.xia

SSCrazy

Points: 2569

February 8, 2011 at 6:26 pm

#1284144

Ok.. After reading http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx. I agree it is not good idea to recreate the log file since I alreay knew there are active transations rolloing forward in MSDB after server reboot. What other choice I have?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Let me call some help in, not sure what's happening.

Will a full backup run?

Is shutting down SQL an option? (don't do it yet, just checking)

How are you calculating the % full? (DBCC SQLPERF(LogSpace)?)

How are you checking the current size? Size of file on disk?

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

David Levy SSCommitted Points: 1622 More actions · Answer 4

Try running this in a query window:

USE [master]

GO

ALTER DATABASE [msdb] MODIFY FILE ( NAME = N'MSDBLog', MAXSIZE = 1024000KB , FILEGROWTH = 25600KB )

GO

Also, I would be interested to see the results of DBCC CHECKDB against this database. I am not 100% sure it will be able to run due to the full log but it seems worth a shot.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

One other question. Is the database online? Can you query the tables?

If so, it may be an idea to start exporting data, incase a recreate is necessary.

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 6

I'd also be interested in seeing a DBCC CHECKDB() run on it as well.

This error, "SQL Server detected a logical consistency-based I/O error: incorrect checksm...." makes me wonder if it can be repaired. Do you have any backups?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

Gail Shaw

SSC Guru

Points: 1004494

February 9, 2011 at 12:49 am

#1284234

clare.xia (2/8/2011)

3. There is no any backups for MSDB.

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

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 8

RichardDouglas

SSCarpal Tunnel

Points: 4029

February 9, 2011 at 2:37 am

#1284284

Are there any open transactions in the MSDB Log?

Have you run DBCC CHECKDB? It might be that you need to create a database snapshot and run it on there.

Hope this helps,
Rich

[p]
[/p]

clare.xia SSCrazy Points: 2569 More actions · Answer 9

Here is the result of DBCC CheckDB

Could not write a checkpoint record in database ID 4 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 1823, Level 16, State 2, Line 1

A database snapshot cannot be created because it failed to start.

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.

DBCC results for 'msdb'.

DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.

DBCC results for 'sys.sysrowsetcolumns'.

There are 1444 rows in 11 pages for object "sys.sysrowsetcolumns".

DBCC results for 'sys.sysrowsets'.

There are 209 rows in 2 pages for object "sys.sysrowsets".

DBCC results for 'sysallocunits'.

There are 243 rows in 3 pages for object "sysallocunits".

DBCC results for 'sys.sysfiles1'.

There are 2 rows in 1 pages for object "sys.sysfiles1".

DBCC results for 'sys.syshobtcolumns'.

There are 1444 rows in 12 pages for object "sys.syshobtcolumns".

DBCC results for 'sys.syshobts'.

There are 209 rows in 2 pages for object "sys.syshobts".

DBCC results for 'sys.sysftinds'.

There are 0 rows in 0 pages for object "sys.sysftinds".

DBCC results for 'sys.sysserefs'.

There are 243 rows in 1 pages for object "sys.sysserefs".

DBCC results for 'sys.sysowners'.

There are 22 rows in 1 pages for object "sys.sysowners".

DBCC results for 'sys.sysprivs'.

There are 290 rows in 3 pages for object "sys.sysprivs".

DBCC results for 'sys.sysschobjs'.

There are 623 rows in 12 pages for object "sys.sysschobjs".

DBCC results for 'sys.syscolpars'.

There are 2724 rows in 55 pages for object "sys.syscolpars".

DBCC results for 'sys.sysnsobjs'.

There are 1 rows in 1 pages for object "sys.sysnsobjs".

DBCC results for 'sys.syscerts'.

There are 2 rows in 1 pages for object "sys.syscerts".

DBCC results for 'sys.sysxprops'.

There are 0 rows in 0 pages for object "sys.sysxprops".

DBCC results for 'sys.sysscalartypes'.

There are 27 rows in 1 pages for object "sys.sysscalartypes".

DBCC results for 'sys.systypedsubobjs'.

There are 0 rows in 0 pages for object "sys.systypedsubobjs".

DBCC results for 'sys.sysidxstats'.

There are 278 rows in 5 pages for object "sys.sysidxstats".

DBCC results for 'sys.sysiscols'.

There are 432 rows in 4 pages for object "sys.sysiscols".

DBCC results for 'sys.sysbinobjs'.

There are 28 rows in 1 pages for object "sys.sysbinobjs".

DBCC results for 'sys.sysobjvalues'.

There are 696 rows in 169 pages for object "sys.sysobjvalues".

DBCC results for 'sys.sysclsobjs'.

There are 22 rows in 1 pages for object "sys.sysclsobjs".

DBCC results for 'sys.sysrowsetrefs'.

There are 0 rows in 0 pages for object "sys.sysrowsetrefs".

DBCC results for 'sys.sysremsvcbinds'.

There are 0 rows in 0 pages for object "sys.sysremsvcbinds".

DBCC results for 'sys.sysxmitqueue'.

There are 0 rows in 0 pages for object "sys.sysxmitqueue".

DBCC results for 'sys.sysrts'.

There are 1 rows in 1 pages for object "sys.sysrts".

DBCC results for 'sys.sysconvgroup'.

There are 0 rows in 0 pages for object "sys.sysconvgroup".

DBCC results for 'sys.sysdesend'.

There are 0 rows in 0 pages for object "sys.sysdesend".

DBCC results for 'sys.sysdercv'.

There are 0 rows in 0 pages for object "sys.sysdercv".

DBCC results for 'sys.syssingleobjrefs'.

There are 274 rows in 2 pages for object "sys.syssingleobjrefs".

DBCC results for 'sys.sysmultiobjrefs'.

There are 2416 rows in 19 pages for object "sys.sysmultiobjrefs".

DBCC results for 'sys.sysdbfiles'.

There are 2 rows in 1 pages for object "sys.sysdbfiles".

DBCC results for 'sys.sysguidrefs'.

There are 0 rows in 0 pages for object "sys.sysguidrefs".

DBCC results for 'sys.sysqnames'.

There are 91 rows in 1 pages for object "sys.sysqnames".

DBCC results for 'sys.sysxmlcomponent'.

There are 93 rows in 1 pages for object "sys.sysxmlcomponent".

DBCC results for 'sys.sysxmlfacet'.

There are 97 rows in 1 pages for object "sys.sysxmlfacet".

DBCC results for 'sys.sysxmlplacement'.

There are 17 rows in 1 pages for object "sys.sysxmlplacement".

DBCC results for 'sys.sysobjkeycrypts'.

There are 618 rows in 23 pages for object "sys.sysobjkeycrypts".

DBCC results for 'sys.sysasymkeys'.

There are 0 rows in 0 pages for object "sys.sysasymkeys".

DBCC results for 'sys.syssqlguides'.

There are 0 rows in 0 pages for object "sys.syssqlguides".

DBCC results for 'sys.sysbinsubobjs'.

There are 0 rows in 0 pages for object "sys.sysbinsubobjs".

DBCC results for 'syssubsystems'.

There are 10 rows in 1 pages for object "syssubsystems".

DBCC results for 'sysproxysubsystem'.

There are 0 rows in 0 pages for object "sysproxysubsystem".

DBCC results for 'restorefilegroup'.

There are 3 rows in 1 pages for object "restorefilegroup".

DBCC results for 'sysproxylogin'.

There are 0 rows in 0 pages for object "sysproxylogin".

DBCC results for 'logmarkhistory'.

There are 0 rows in 0 pages for object "logmarkhistory".

DBCC results for 'sqlagent_info'.

There are 0 rows in 0 pages for object "sqlagent_info".

DBCC results for 'suspect_pages'.

There are 1 rows in 1 pages for object "suspect_pages".

DBCC results for 'sysdownloadlist'.

There are 0 rows in 0 pages for object "sysdownloadlist".

DBCC results for 'sysjobhistory'.

There are 0 rows in 0 pages for object "sysjobhistory".

DBCC results for 'log_shipping_primary_databases'.

There are 0 rows in 0 pages for object "log_shipping_primary_databases".

DBCC results for 'sysdtscategories'.

There are 3 rows in 1 pages for object "sysdtscategories".

DBCC results for 'sysoriginatingservers'.

There are 0 rows in 0 pages for object "sysoriginatingservers".

DBCC results for 'log_shipping_primary_secondaries'.

There are 0 rows in 0 pages for object "log_shipping_primary_secondaries".

DBCC results for 'sysdtspackages'.

There are 0 rows in 0 pages for object "sysdtspackages".

DBCC results for 'log_shipping_monitor_primary'.

There are 0 rows in 0 pages for object "log_shipping_monitor_primary".

DBCC results for 'log_shipping_monitor_history_detail'.

There are 0 rows in 0 pages for object "log_shipping_monitor_history_detail".

DBCC results for 'log_shipping_monitor_error_detail'.

There are 0 rows in 0 pages for object "log_shipping_monitor_error_detail".

DBCC results for 'log_shipping_secondary'.

There are 0 rows in 0 pages for object "log_shipping_secondary".

DBCC results for 'sysjobs'.

There are 0 rows in 0 pages for object "sysjobs".

DBCC results for 'log_shipping_secondary_databases'.

There are 0 rows in 0 pages for object "log_shipping_secondary_databases".

DBCC results for 'sysjobservers'.

There are 0 rows in 0 pages for object "sysjobservers".

DBCC results for 'log_shipping_monitor_secondary'.

There are 0 rows in 0 pages for object "log_shipping_monitor_secondary".

DBCC results for 'syssessions'.

There are 0 rows in 0 pages for object "syssessions".

DBCC results for 'log_shipping_monitor_alert'.

There are 0 rows in 0 pages for object "log_shipping_monitor_alert".

DBCC results for 'sysjobactivity'.

There are 0 rows in 0 pages for object "sysjobactivity".

DBCC results for 'sysjobsteps'.

There are 0 rows in 0 pages for object "sysjobsteps".

DBCC results for 'sysjobstepslogs'.

There are 0 rows in 0 pages for object "sysjobstepslogs".

DBCC results for 'sysdtspackagelog'.

There are 0 rows in 0 pages for object "sysdtspackagelog".

DBCC results for 'sysdtssteplog'.

There are 0 rows in 0 pages for object "sysdtssteplog".

DBCC results for 'sysschedules'.

There are 0 rows in 0 pages for object "sysschedules".

DBCC results for 'sysdtstasklog'.

There are 0 rows in 0 pages for object "sysdtstasklog".

DBCC results for 'sysjobschedules'.

There are 0 rows in 0 pages for object "sysjobschedules".

DBCC results for 'syscategories'.

There are 21 rows in 1 pages for object "syscategories".

DBCC results for 'systargetservers'.

There are 0 rows in 0 pages for object "systargetservers".

DBCC results for 'systargetservergroups'.

There are 0 rows in 0 pages for object "systargetservergroups".

DBCC results for 'systargetservergroupmembers'.

There are 0 rows in 0 pages for object "systargetservergroupmembers".

DBCC results for 'sysalerts'.

There are 0 rows in 0 pages for object "sysalerts".

DBCC results for 'sysmail_profile'.

There are 0 rows in 0 pages for object "sysmail_profile".

DBCC results for 'sysoperators'.

There are 0 rows in 0 pages for object "sysoperators".

DBCC results for 'sysnotifications'.

There are 0 rows in 0 pages for object "sysnotifications".

DBCC results for 'sysmaintplan_subplans'.

There are 0 rows in 0 pages for object "sysmaintplan_subplans".

DBCC results for 'sys.queue_messages_843150049'.

There are 0 rows in 0 pages for object "sys.queue_messages_843150049".

DBCC results for 'sysmail_principalprofile'.

There are 0 rows in 0 pages for object "sysmail_principalprofile".

DBCC results for 'sys.queue_messages_875150163'.

There are 0 rows in 0 pages for object "sys.queue_messages_875150163".

DBCC results for 'sysmaintplan_log'.

There are 0 rows in 0 pages for object "sysmaintplan_log".

DBCC results for 'sysmaintplan_logdetail'.

There are 0 rows in 0 pages for object "sysmaintplan_logdetail".

DBCC results for 'sysmail_account'.

There are 0 rows in 0 pages for object "sysmail_account".

DBCC results for 'systaskids'.

There are 0 rows in 0 pages for object "systaskids".

DBCC results for 'syscachedcredentials'.

There are 0 rows in 0 pages for object "syscachedcredentials".

DBCC results for 'sysdbmaintplans'.

There are 1 rows in 1 pages for object "sysdbmaintplans".

DBCC results for 'sysmail_profileaccount'.

There are 0 rows in 0 pages for object "sysmail_profileaccount".

DBCC results for 'MSdbms'.

There are 7 rows in 1 pages for object "MSdbms".

DBCC results for 'MSdbms_datatype'.

There are 141 rows in 1 pages for object "MSdbms_datatype".

DBCC results for 'sysmail_servertype'.

There are 1 rows in 1 pages for object "sysmail_servertype".

DBCC results for 'sysdbmaintplan_jobs'.

There are 0 rows in 0 pages for object "sysdbmaintplan_jobs".

DBCC results for 'MSdbms_map'.

There are 248 rows in 3 pages for object "MSdbms_map".

DBCC results for 'sysdbmaintplan_databases'.

There are 0 rows in 0 pages for object "sysdbmaintplan_databases".

DBCC results for 'sysmail_server'.

There are 0 rows in 0 pages for object "sysmail_server".

DBCC results for 'sysdbmaintplan_history'.

There are 0 rows in 0 pages for object "sysdbmaintplan_history".

DBCC results for 'MSdbms_datatype_mapping'.

There are 325 rows in 3 pages for object "MSdbms_datatype_mapping".

DBCC results for 'sysmail_configuration'.

There are 7 rows in 1 pages for object "sysmail_configuration".

DBCC results for 'sysmail_mailitems'.

There are 0 rows in 0 pages for object "sysmail_mailitems".

DBCC results for 'log_shipping_primaries'.

There are 0 rows in 0 pages for object "log_shipping_primaries".

DBCC results for 'log_shipping_secondaries'.

There are 0 rows in 0 pages for object "log_shipping_secondaries".

DBCC results for 'sysmail_attachments'.

There are 0 rows in 0 pages for object "sysmail_attachments".

DBCC results for 'sysmail_send_retries'.

There are 0 rows in 0 pages for object "sysmail_send_retries".

DBCC results for 'backupmediaset'.

There are 59 rows in 1 pages for object "backupmediaset".

DBCC results for 'sysmail_log'.

There are 0 rows in 0 pages for object "sysmail_log".

DBCC results for 'backupmediafamily'.

There are 59 rows in 2 pages for object "backupmediafamily".

DBCC results for 'sysdtspackages90'.

There are 0 rows in 0 pages for object "sysdtspackages90".

DBCC results for 'backupset'.

There are 59 rows in 4 pages for object "backupset".

DBCC results for 'sysdtspackagefolders90'.

There are 2 rows in 1 pages for object "sysdtspackagefolders90".

DBCC results for 'sys.queue_messages_1977058079'.

There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".

DBCC results for 'sysmail_query_transfer'.

There are 0 rows in 0 pages for object "sysmail_query_transfer".

DBCC results for 'backupfilegroup'.

There are 59 rows in 1 pages for object "backupfilegroup".

DBCC results for 'sys.queue_messages_2009058193'.

There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".

DBCC results for 'sysdtslog90'.

There are 0 rows in 0 pages for object "sysdtslog90".

DBCC results for 'sysmail_attachments_transfer'.

There are 0 rows in 0 pages for object "sysmail_attachments_transfer".

DBCC results for 'backupfile'.

There are 118 rows in 6 pages for object "backupfile".

DBCC results for 'sys.queue_messages_2041058307'.

There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".

DBCC results for 'restorehistory'.

There are 3 rows in 1 pages for object "restorehistory".

DBCC results for 'sysproxies'.

There are 0 rows in 0 pages for object "sysproxies".

DBCC results for 'restorefile'.

There are 6 rows in 1 pages for object "restorefile".

CHECKDB found 0 allocation errors and 0 consistency errors in database 'msdb'.

Msg 9002, Level 17, State 7, Line 1

The transaction log for database 'msdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Msg 9002, Level 17, State 1, Line 1

The transaction log for database 'msdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

clare.xia SSCrazy Points: 2569 More actions · Answer 10

clare.xia

SSCrazy

Points: 2569

February 9, 2011 at 10:37 am

#1284647

1. I can't backup the database (full) because of the transaction log is full.

2. Yes, shutting down SQL is an option.

3. Yes, I used DBCC SQLPERF(LogSpace)

4. I checked the size of the file on disk.

clare.xia SSCrazy Points: 2569 More actions · Answer 11

clare.xia

SSCrazy

Points: 2569

February 9, 2011 at 10:39 am

#1284652

Yes, the MSDB is online and status is Normal. I am trying to find out if we have critical data stored there. Yes, I should import the data somewhere now.. Thank you!!!

Jack Corbett SSC Guru Points: 184388 More actions · Answer 12

Okay, so what is the log_reuse_wait_desc in sys.databases? Post the results of this query:

SELECT



    D.name,

    D.recovery_model_desc,

    D.state_desc,

    D.log_reuse_wait_desc,

    D.page_verify_option_desc

FROM

    sys.databases AS D

WHERE

D.name = 'MSDB'

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

David Levy SSCommitted Points: 1622 More actions · Answer 13

David Levy

SSCommitted

Points: 1622

February 9, 2011 at 10:44 am

#1284663

Have you had any luck with this command?

USE [master]

GO

ALTER DATABASE [msdb] MODIFY FILE ( NAME = N'MSDBLog', MAXSIZE = 1024000KB , FILEGROWTH = 25600KB )

GO

clare.xia SSCrazy Points: 2569 More actions · Answer 14

clare.xia

SSCrazy

Points: 2569

February 9, 2011 at 10:46 am

#1284664

msdb SIMPLE ONLINE CHECKPOINT CHECKSUM

clare.xia SSCrazy Points: 2569 More actions · Answer 15

No luck.. here is the message

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x6f9014c7; actual: 0x6f903ecb). It occurred during a read of page (2:0) in database ID 4 at offset 0000000000000000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.