Click here to monitor SSC
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
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 250
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.
clare.xia
clare.xia
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 250
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!
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47427 Visits: 44405
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

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


clare.xia
clare.xia
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 250
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.
clare.xia
clare.xia
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 250
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.
clare.xia
clare.xia
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 250
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
clare.xia
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 250
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47427 Visits: 44405
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
David Levy
Right there with Babe
Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)

Group: General Forum Members
Points: 792 Visits: 275
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47427 Visits: 44405
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


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