Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Transaction Log Full in Simple Recovery mode Expand / Collapse
Author
Message
Posted Friday, February 4, 2011 5:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
The transaction log in MSDB is 106% full and Log_Reuse_wait_Desc is 'Check Point'. The server is restarted every night and still has the same error. I cann't do backup or shrink log file because the Log is full. Any idea to solve the problem?

Thanks
Post #1059090
Posted Saturday, February 5, 2011 1:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
Run a manual checkpoint, see if it helps

CHECKPOINT




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1059119
Posted Saturday, February 5, 2011 9:47 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 8,571, Visits: 9,076
Can you extend the log file, or allocate an extra log file?

Tom
Post #1059146
Posted Monday, February 7, 2011 9:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
I just got some detailed error message from SQL logs. There is no way to manually issue a check point as the transaction log is 106% full. I can't do any backup as backup need some space in transaction log too.


2011-02-04 04:57:29.77 spid5s Starting up database 'msdb'.
2011-02-04 04:57:30.17 spid5s 5 transactions rolled forward in database 'msdb' (4). This is an informational message only. No user action is required.
2011-02-04 04:57:30.20 spid5s 0 transactions rolled back in database 'msdb' (4). This is an informational message only. No user action is required.
2011-02-04 04:57:30.20 spid5s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
2011-02-04 04:57:34.32 spid5s Error: 9002, Severity: 17, State: 1.
2011-02-04 04:57:34.32 spid5s 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
2011-02-04 04:57:34.32 spid5s 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.
2011-02-04 04:57:34.37 spid5s Recovery is complete. This is an informational message only. No user action is required.

Post #1059684
Posted Monday, February 7, 2011 2:40 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
Tom.Thomson (2/5/2011)
Can you extend the log file, or allocate an extra log file?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1059919
Posted Monday, February 7, 2011 2:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
I can't add file or add more space to the exsiting logfile in MSDB

I am thinking replace the .mdf and .ldf with the corresponding files from a working MSDB. Thoughts?
Post #1059922
Posted Monday, February 7, 2011 2:53 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
Why can't you add another log?

If you replace MSDB, you'll lose all jobs, job history, backup history, DTS packages, SSIS packages, agent alerts, etc.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1059930
Posted Monday, February 7, 2011 2:53 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 3,122, Visits: 11,408
clare.xia (2/7/2011)
..I am thinking replace the .mdf and .ldf with the corresponding files from a working MSDB. Thoughts?...


That sounds like a very bad idea. You would lose all your SQL Agent jobs and job history, backup and restore history, maintenance plans, email setup, email operators, and any DTS packages or SSIS packages stored in the MSDB database.

Do you have MSDB set to full recovery mode?

Post #1059931
Posted Monday, February 7, 2011 3:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
The MSDB is in Simple Recovery mode. I can't add file to the log because the transaction log is full and I can't increase the file size as it dose not allow me.
Post #1059935
Posted Monday, February 7, 2011 3:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
I tried to two different ways to add log size in MSDB

1. Add another log file - failed due to transaction log file is full

2. Increase the size of the file - did it from GUI and it dose not allow me.


Any other way, you can think of or did I do anything wrong?
Post #1059945
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse