February 4, 2011 at 5:00 pm
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
February 5, 2011 at 1:55 am
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
February 5, 2011 at 9:47 am
Can you extend the log file, or allocate an extra log file?
Tom
February 7, 2011 at 9:36 am
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.
February 7, 2011 at 2:41 pm
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
February 7, 2011 at 2:45 pm
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?
February 7, 2011 at 2:53 pm
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?
February 7, 2011 at 2:53 pm
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
February 7, 2011 at 3:03 pm
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.
February 7, 2011 at 3:24 pm
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?
February 7, 2011 at 3:37 pm
Ok.. I don't know which one is worse? There is another option..
I can stop SQL server and rename the log file for MSDB and let it generate a new one and then delete the old log file.
February 7, 2011 at 6:02 pm
Here is something I don't understand,
In the MSDB Log was defined as restricted growth to 2097152 MB but the actual size of the log file is just 2304KB. Why it is complainning the log is full?
thanks in advance for your reply!
February 7, 2011 at 10:20 pm
clare.xia (2/7/2011)
I can stop SQL server and rename the log file for MSDB and let it generate a new one and then delete the old log file.
There's a very good chance it won't generate a new one (database not cleanly shut down), and you'll be left with a system database in recovery_pending. Not fun.
Got a recent backup of MSDB?
Is the drive full? If so, do you have another drive with space?
Are you sure about the kB/MB measurements? It catches a lot of people.
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 8, 2011 at 10:46 am
Good to know that. Thank you!
1. There are over 100G free space in the drive
2. I and the other person checked the log file and the max setting of the log file. Again, The log file is just 2 MB but allow to grow to 2,097,152 MB
3. There is no any backups for MSDB.
Now I really want to figure out what make the log file dose not grow.
February 8, 2011 at 5:09 pm
More information..
When I tried to reduce the max allowed size for MSDB, I got the following error
SQL Server detected a logical consistency-based I/O error: incorrect checksm....
and there is one rows in msdb..suspect_pages that points to MSDB.
Is that something that stops MSDB to grow and stuck in 2 MB? I am very confused.
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply