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


Transaction Log Full in Simple Recovery mode


Transaction Log Full in Simple Recovery mode

Author
Message
clare.xia
clare.xia
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 250
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231154 Visits: 46349
Run a manual checkpoint, see if it helps

CHECKPOINT



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


Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26349 Visits: 12506
Can you extend the log file, or allocate an extra log file?

Tom

clare.xia
clare.xia
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 250
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231154 Visits: 46349
Tom.Thomson (2/5/2011)
Can you extend the log file, or allocate an extra log file?


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


clare.xia
clare.xia
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 250
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231154 Visits: 46349
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, 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


Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 11848
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?
clare.xia
clare.xia
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 250
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.
clare.xia
clare.xia
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 250
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?
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