November 3, 2011 at 11:47 pm
Error 9002 The Transaction Log for MSDB database is full.
log_reuse_wait =1
log_reuse_wait_desc= CHECKPOINT
What Should I do?
Thanks
November 3, 2011 at 11:55 pm
check if there is any job running. If required stop it else configure your logfile setting to autogrow to unrestricted growth.
----------
Ashish
November 3, 2011 at 11:58 pm
Thanks,
Yes jobs are runned at 9:00 PM Daily.
Is Unrestricted growth a permanent solution?
Right now What should I do.
Thanks
November 4, 2011 at 12:02 am
When change setting to Unrestricted Growth I am getting error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
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)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=9002&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Thanks
November 4, 2011 at 12:06 am
need to investigate what activity this job is doing which is filling log. If you can then stop the job or if the server is not crucial stop the agent services which will automatically kill all the jobs.
I am assuming your msdb is in simple recovery. If not change it to simple recovery.
----------
Ashish
November 4, 2011 at 12:33 am
MSDB is in simple recovery mode,If I will stop the agent still in 2-3 days this problem will come.
1. Should I take the backup of MSDB right now while daily backup using maintenance plan is scheduled?
2. Should I shrink the log file of MSDB?
Help is neeeded..
Thanks
November 4, 2011 at 12:47 am
I think the best option is to disable the job which is filling the log untill the job properly investigated.
run the below steps in your database only after the job is stopped, else it will keep filling the log:-
1) select name,log_reuse_wait_desc from sys.databases where name like 'msdb%'
2) run checkpoint
3) how many status = 2 listed in dbcc loginfo() after running it in msdb.
4) Keep doing the steps from 1 to 3 untill in step1 you do not get result as "nothing" for log_reuse_wait_desc
----------
Ashish
November 4, 2011 at 12:48 am
You are correct
There is Job 'TfsversionControlhourly' run in each hour.
tfs means team foundation server and this job is used for update stale statistics.
Thanks
November 4, 2011 at 12:53 am
how many status = 2 listed in dbcc loginfo() after running it in msdb.
There are 20 status = 2 listed in dbcc loginfo().
On running checkpoint I am getting below in red color:
Msg 9002, Level 17, State 1, Line 1
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
Thanks
November 4, 2011 at 12:56 am
Is the job stopped/disabled? If not stop it and disable it untill further investigation.
----------
Ashish
November 4, 2011 at 12:59 am
It is neither stopping nor disabling and giving error 22022
Thanks
November 4, 2011 at 2:56 am
Stop the Agent Service of sql which will kill all running job. Then restart the service again and disable the job.
----------
Ashish
November 4, 2011 at 3:03 am
I am getting error when I am trying to shrink the log file:
TITLE: Microsoft SQL Server Management Studio
------------------------------
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
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x57bb4154; actual: 0xe2eab22a). It occurred during a read of page (2:0) in database ID 4 at offset 0000000000000000 in file 'F:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf'. 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.
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. (Microsoft SQL Server, Error: 9002)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=9002&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Thanks
November 6, 2011 at 11:29 pm
Ashish I have rebuild my system databases using
1. I have copy paste new master,msdb,model data and log files.(Old files I have)
2. Now I have started my server using -t 3608 but I am not able to login as logins would be destroyed.
3. without -t 3608 Sql service agent is starting but sql service is giving error.
Thanks
November 7, 2011 at 3:53 am
My error log file is:
2011-11-07 14:33:05.15 Server Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1) (VM)
2011-11-07 14:33:05.15 Server (c) 2005 Microsoft Corporation.
2011-11-07 14:33:05.15 Server All rights reserved.
2011-11-07 14:33:05.15 Server Server process ID is 4572.
2011-11-07 14:33:05.15 Server System Manufacturer: 'Microsoft Corporation', System Model: 'Virtual Machine'.
2011-11-07 14:33:05.15 Server Authentication mode is MIXED.
2011-11-07 14:33:05.15 Server Logging SQL Server messages in file 'F:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2011-11-07 14:33:05.15 Server This instance of SQL Server last reported using a process ID of 3180 at 11/7/2011 2:13:20 PM (local) 11/7/2011 10:13:20 PM (UTC). This is an informational message only; no user action is required.
2011-11-07 14:33:05.15 Server Registry startup parameters:
-d F:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
-e F:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l F:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2011-11-07 14:33:05.15 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2011-11-07 14:33:05.15 Server Detected 1 CPUs. This is an informational message; no user action is required.
2011-11-07 14:33:05.21 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2011-11-07 14:33:05.30 Server Node configuration: node 0: CPU mask: 0x00000001 Active CPU mask: 0x00000001. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2011-11-07 14:33:05.35 spid7s Starting up database 'master'.
2011-11-07 14:33:05.41 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2011-11-07 14:33:05.46 spid7s SQL Trace ID 1 was started by login "sa".
2011-11-07 14:33:05.46 spid7s Starting up database 'mssqlsystemresource'.
2011-11-07 14:33:05.46 spid7s The resource database build version is 10.00.2531. This is an informational message only. No user action is required.
2011-11-07 14:33:05.49 spid7s Server name is 'ECGC-DC-TFSSH-D'. This is an informational message only. No user action is required.
2011-11-07 14:33:05.66 spid10s Starting up database 'model'.
2011-11-07 14:33:05.76 Server A self-generated certificate was successfully loaded for encryption.
2011-11-07 14:33:05.84 Server Server is listening on [ 'any' <ipv6> 1433].
2011-11-07 14:33:05.84 Server Server is listening on [ 'any' <ipv4> 1433].
2011-11-07 14:33:05.84 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2011-11-07 14:33:05.84 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2011-11-07 14:33:05.86 Server Server is listening on [ ::1 <ipv6> 1434].
2011-11-07 14:33:05.86 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2011-11-07 14:33:05.86 spid10s Error: 17204, Severity: 16, State: 1.
2011-11-07 14:33:05.86 spid10s FCB::Open failed: Could not open file e:\sql10_main_t\sql\mkmastr\databases\objfre\i386\model.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
2011-11-07 14:33:05.87 Server Dedicated admin connection support was established for listening locally on port 1434.
2011-11-07 14:33:05.87 spid10s Error: 5120, Severity: 16, State: 101.
2011-11-07 14:33:05.87 spid10s Unable to open the physical file "e:\sql10_main_t\sql\mkmastr\databases\objfre\i386\model.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2011-11-07 14:33:05.89 spid10s Error: 17207, Severity: 16, State: 1.
2011-11-07 14:33:05.89 spid10s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'e:\sql10_main_t\sql\mkmastr\databases\objfre\i386\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.
2011-11-07 14:33:05.89 spid10s File activation failure. The physical file name "e:\sql10_main_t\sql\mkmastr\databases\objfre\i386\modellog.ldf" may be incorrect.
2011-11-07 14:33:05.89 spid10s Error: 945, Severity: 14, State: 2.
2011-11-07 14:33:05.89 spid10s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2011-11-07 14:33:05.89 spid10s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2011-11-07 14:33:05.89 spid10s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2011-11-07 14:33:05.95 spid13s A new instance of the full-text filter daemon host process has been successfully started.
Thanks
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply