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 Monday, February 7, 2011 3:37 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
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.
Post #1059952
Posted Monday, February 7, 2011 6:02 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
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!
Post #1059985
Posted Monday, February 7, 2011 10:20 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 @ 8:31 AM
Points: 40,456, Visits: 36,912
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 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 #1060021
Posted Tuesday, February 8, 2011 10:46 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
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.
Post #1060441
Posted Tuesday, February 8, 2011 5:09 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
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.
Post #1060738
Posted Tuesday, February 8, 2011 5:51 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
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!
Post #1060746
Posted Tuesday, February 8, 2011 6:26 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
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?
Post #1060752
Posted Tuesday, February 8, 2011 10:18 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 @ 8:31 AM
Points: 40,456, Visits: 36,912
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 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 #1060825
Posted Tuesday, February 8, 2011 10:22 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 11:01 AM
Points: 790, 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.
Post #1060827
Posted Tuesday, February 8, 2011 10:39 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 @ 8:31 AM
Points: 40,456, Visits: 36,912
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 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 #1060832
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse