Error 9002: The Transaction Log for MSDB database is full.

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  November 9, 2011 at 1:22 am

  #1405012

  Gail I want to discuss this:

  These are the initial errors which I was getting are below:

  1.

  Shrink failed for LogFile 'MSDBLog'. (Microsoft.SqlServer.Smo)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+LogFile&LinkId=20476

  ------------------------------

  ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  ------------------------------

  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

  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

  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.

  Cannot shrink file '2' in database 'msdb' to 768 pages as it only contains 712 pages.

  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.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator. (Microsoft SQL Server, Error: 9002)

  2.

  FCB::Open failed: Could not open file e:\sql10_main_t\sql\mkmastr\databases\objfre\i386\MSDBData.mdf for file number 1. OS error: 3(The system cannot find the path specified.).

  3.

  SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x0; actual signature: 0x9dc8cfec). It occurred during a read of page (2:0) in database ID 4 at offset 0x00000000130000 in file 'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Mastlog.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

  ============

  May I know what should I do initially?I have the backup of all the databases.

  Edit:

  Anyone knows how to check that either Team foundation service is working fine or not.

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 9, 2011 at 1:31 am

  #1405017

  What is the current situation? Not the initial errors, those are pretty irrelevant since you rebuilt the system databases. What is the exact current situation?

  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

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  November 9, 2011 at 1:36 am

  #1405018

  Thanks.a.lot

  I am able to login in Sharepoint service but I am not confirm about the Team foundation service as TFS service is running but in browsing it is giving error 401,403...

  No error is now in eventviewer

  What should I check for TFS?

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 9, 2011 at 1:47 am

  #1405021

  No idea, never used TFS. Maybe ask that on a TFS forum or with TFS support?

  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

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  November 9, 2011 at 2:21 am

  #1405032

  Thanks, I have opened it in MSDN forum..

  http://social.msdn.microsoft.com/Forums/en-US/tfssetup/thread/fc5469e5-5245-468e-bb54-bc4f0012b53b

  Thanks

• Devendrakumar

  SSC-Forever

  Points: 42493

  More actions

  November 9, 2011 at 2:24 am

  #1405035

  Snippet from your post... Errors & respective suggestions by SQL Server itself.

  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

  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.

  Could not open file e:\sql10_main_t\sql\mkmastr\databases\objfre\i386\MSDBData.mdf for file number 1. OS error: 3(The system cannot find the path specified.).

  This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB).

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 9, 2011 at 2:32 am

  #1405037

  Dev @ +91 973 913 6683 (11/9/2011)

  ---

  Snippet from your post... Errors & respective suggestions by SQL Server itself.

  Except that so much has been done here (rebuild of system DBs and reinstall of SQL) that those are mostly no longer relevant.

  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

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  November 9, 2011 at 2:40 am

  #1405043

  Initially Should I added new log in msdb and master databases in place of rebulid?

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 9, 2011 at 7:08 am

  #1405145

  forsqlserver (11/9/2011)

  ---

  Initially Should I added new log in msdb and master databases in place of rebulid?

  Rebuild is for when the databases are damaged and unusable (or similar disasters) a full log can be resolved by fixing whatever's preventing truncation. If the log is so full that a checkpoint can't be done, then a new log file can be temporarily added so that the checkpoint can be run and the log reused.

  Let's put it this way, how much downtime resulted from your decision to rebuild the system databases?

  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

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  November 9, 2011 at 10:02 pm

  #1405505

  Thanks Gail....I thinks it requires half day for rebuild the system DBs...

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 10, 2011 at 2:57 am

  #1405582

  forsqlserver (11/9/2011)

  ---

  Thanks Gail....I thinks it requires half day for rebuild the system DBs...

  I make it 5 days from the time the thread started...

  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

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  November 10, 2011 at 2:58 am

  #1405583

  Yes dear...Hmmm

  Thanks

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  November 10, 2011 at 6:28 am

  #1405697

  forsqlserver (11/10/2011)

  ---

  Yes dear...Hmmm

  Nice way to say you don't agree but don't know why and are out of arguments.

  For some people that might not be received too well.

  :Whistling: