question

gnira avatar image
gnira asked

SQL Server 2016 SP1 Error:9002 Severity:17 state:1

My database is stuck in CHECKPOINT. I cannot write. I can read. I try to make the log file bigger manually or with a command but I get the 9002 error. The log file size is limited to 2097152 MB and there is enough space on disk. The recovery model is Simple.
sql-server-2016
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@gnira It looks like you log file is configured with 2 terabytes limit. What is the actual size of the log file now? In any case, it is possible that 2 TB limit is somewhat excessive. Do you really need it to be this big? There must be a lot going on to have the transaction log of this size, moreover because the recovery model is simple. In any case, usually this error points to the lack of disk space, so it is strange that you claim that there is enough space on disk. It is possible that the problem is with the transaction log file of the other databases (model, for example). These could be located on the drive with insufficient space. Check the **log\_reuse\_wait\_desc** values of the **sys.databases**: select [name], log_reuse_wait_desc, state_desc from sys.databases where log_reuse_wait_desc 'NOTHING' This will show all databases with something happening with the log files.
0 Likes 0 ·
gnira avatar image gnira commented ·
Thank you Oleg. 1. The problem is with the transaction log of the X db which is stuck with log_reuse_wait_desc = 'CHECKPOINT'. 3. All the other databases have log_reuse_wait_desc = 'NOTHING' including master, model, msdb, tempdb. 4. The actual size of the log file of X is 20 gb. 5. The limit of 2 tb is not a must. But I cannot change it now. 6. The X db has 22 filegroups.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@gnira Does activity monitor show any process worth killing? There might be some entries in the current SQL Server logs as well which might shed some light on this problem. From what I understand, restarting the service would be a bad idea for now because upon starting up it will most probably put the database in question in recovery, and you will have to wait until all roll forwards are completed. If the log file is only 20 GB (~ 1% of the limit) at this time then it means that the problem is not related to disk space, it is something else which may or may not be related to the hardware issues. Sorry I could not help.
0 Likes 0 ·
jason_clark03 avatar image
jason_clark03 answered
check if there is any job running. If required stop it else configure your logfile setting to autogrow to unrestricted growth, Take a look over here to know how to respond SQL Server Error 9002: [ https://msdn.microsoft.com/en-us/library/ms175495(v=sql.105).aspx][1] [ http://www.sqlserverlogexplorer.com/error-9002-transaction-full/][2] [1]: https://msdn.microsoft.com/en-us/library/ms175495(v=sql.105).aspx [2]: http://www.sqlserverlogexplorer.com/error-9002-transaction-full/
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

gnira avatar image gnira commented ·
Thank you Oleg. 1. I killed all processes connecting to X db and restart the server. But nothing changed, still error 9002. Thank you Jason_clark03. 2. Alter DATABASE fails with 9002 so I cannot enlarge the log file or add log file or change size/maxsize.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@jason_clark03 Generally speaking, configuring the log file to unrestricted growth is not a good idea. I believe it would be difficult to find a production environment where DBA is ignorant enough to actually do it. @gnira What is the value of the **Recovery interval (minutes)** in the **Database Settings** of the **Server Properties**? The default is 0 meaning that the recovery interval is managed by the instance. If it is ***not zero*** in your environment, maybe it causes a problem when the time it takes to complete a checkpoint is longer than this interval and so the next checkpoint starts before the previous one had a chance to complete.
0 Likes 0 ·
gnira avatar image gnira commented ·
X db options: Target Recovery Time (Seconds) = 0 Server properties : Database Settings : Recovery interval (minutes) = 0
0 Likes 0 ·
gnira avatar image
gnira answered
Thank you Oleg and jason_clark03, It looks as if the server does not work properly.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.