May 31, 2022 at 8:14 pm
Long story short. The transaction log is getting bigger even though there's daily full backup and hourly log backup running. I decided to change the recovery mode to SIMPLE and shrink the transaction log file. However, I'm getting the following error every time I try to shrink the log file using DBCC SHRINFILE(LOGICAL FILE NAME, Target Size). Can you help please? It's a production issue.
The log scan number (202388:69632:1) passed to log scan in database is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
May 31, 2022 at 8:37 pm
Do you have replication on that database?
Have you executed DBCC CHECKDB to check for errors? If it indicates errors, see this: https://qa.sqlservercentral.com/articles/help-my-database-is-corrupt-now-what. Do not reflexively run repair w/o fully understanding the possible consequences! You may do more harm.
May 31, 2022 at 8:53 pm
It was in HA. I removed it from HA to make the recovery mode SIMPLE and shrink the transaction log file. I just ran DBCC CHECKDB and below is the error I got.
One or more recovery units belonging to database failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
A database snapshot cannot be created because it failed to start.
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
The database could not be exclusively locked to perform the operation.
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
The log scan number (202388:69632:1) passed to log scan in database is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
May 31, 2022 at 10:18 pm
Do you know/can you determine if a big transaction occurred just prior to this problem?
Are you out of space? How much disk space is used and how much is available on the drive(s) where the log & data file are located? If so can you add space (e.g. if SAN)?
When you say HA, do you mean availability groups? (I'm not experience w/ AG , but the detail may help our AG experts to guide you further).
May 31, 2022 at 11:22 pm
I don't think it is related to HA since all these errors occurs after database is off from HA. I don't think there was a big transaction. However, I will verify that separately and let you know. Below is the detail how I got there.
I got email alert saying that there's not much space left in a local drive (1 TB). We are using that local drive for the full backup for the database we are talking about now. I found out the full backup file size was three times bigger than the primary db size (db size 350 GB). I think it was because of the log file size was 600 GB at that time. Another thing I noticed was that the log file kept growing even thought we do have hourly transaction log backup running. When I tried to shrink the log file, it showed no available free space which was odd. That's when I decided to change the recovery mode to "SIMPLE" and shrink the log file forcibly. That's when I got the error message saying "The log scan number (202388:69632:1) passed to log scan in database is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf).... "/
June 1, 2022 at 4:34 am
Here's more update. The full backup failed with the similar error message like below. Now, my focus is switched to get the full backup working instead of getting the transaction log file shrunk.
One or more recovery units belonging to database failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure. [SQLSTATE 42000] (Error 5901) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) The log scan number (202388:69632:1) passed to log scan in database is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. [SQLSTATE HY000] (Error 9003). The step failed.
June 7, 2022 at 12:24 am
Sounds like your transaction log being full is putting a stop to replication. There is no need to "shrink" the log file if it is just going to grow again. It is not uncommon to have these grow larger than the mdf file at times depending. Make sure autogrow is on and you have enough space.
----------------------------------------------------
June 7, 2022 at 6:19 am
Sounds like your transaction log being full is putting a stop to replication. There is no need to "shrink" the log file if it is just going to grow again. It is not uncommon to have these grow larger than the mdf file at times depending. Make sure autogrow is on and you have enough space.
Heh... I have a 2TB and 3TB database... if what you say is true, I'm VERY happy that we don't use replication. 😀
--Jeff Moden
June 7, 2022 at 8:22 pm
Once transactions are moved over to the replica log get cleared. Problems arise if there is ever a connection issue.
----------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply