x

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.

more ▼

asked Jan 08 at 04:54 PM in Default

avatar image

gnira
11 2

@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.

Jan 08 at 07:05 PM Oleg

Thank you Oleg.

  1. The problem is with the transaction log of the X db which is stuck with log_reuse_wait_desc = 'CHECKPOINT'.

  2. All the other databases have log_reuse_wait_desc = 'NOTHING' including master, model, msdb, tempdb.

  3. The actual size of the log file of X is 20 gb.

  4. The limit of 2 tb is not a must. But I cannot change it now.

  5. The X db has 22 filegroups.

Jan 08 at 08:06 PM gnira

@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.

Jan 08 at 09:04 PM Oleg
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

http://www.sqlserverlogexplorer.com/error-9002-transaction-full/

more ▼

answered Jan 09 at 06:06 AM

avatar image

jason_clark03
421 3 7

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.

  1. Alter DATABASE fails with 9002 so I cannot enlarge the log file or add log file or change size/maxsize.

Jan 09 at 01:29 PM gnira

@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.

Jan 09 at 02:13 PM Oleg

X db options: Target Recovery Time (Seconds) = 0

Server properties : Database Settings : Recovery interval (minutes) = 0

Jan 09 at 02:31 PM gnira
(comments are locked)
10|1200 characters needed characters left

Thank you Oleg and jason_clark03, It looks as if the server does not work properly.

more ▼

answered Jan 11 at 10:09 AM

avatar image

gnira
11 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x26

asked: Jan 08 at 04:54 PM

Seen: 63 times

Last Updated: Jan 11 at 10:09 AM

Copyright 2018 Redgate Software. Privacy Policy