June 11, 2018 at 3:01 am
Hi Gurus,
I'm new to SQL Server. We got an issue on our Production Server at this moment and i'm having a hard time fixing the'error.
The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'
I tried to do dbcc shrinkfile (logfile,1) but it doens't allow me.
I decided to restart the DB as follows:
1. alter database set single_user with rollback immediate
2. alter database set offline with rollback immediate
3. alter database set online with rollback immediate
When it came up, it's still in single user mode. I tried to do alter database set multi_user with rollback immediate but i'm getting, deadlock issues.
i am planning to force the DB to go into multi_user mode but i''m afraid it'll take sometime to go online and affects other databases.
set deadlock pdqpriority high
alter database set multi_user with rollback immediate.
my questions are:
1. if i run the set deadlock pdqpriority high + alter database set multi_user with rollback immediate, will it affect or block or deadlock other DB processes? on the same instance?
2. what is the best solution to fix to the 'full due to XTP_CHECKPOINT' error? Microsoft says it's in SQL2014 SP1 CPU4... but we are running Microsoft SQL2014 SP2 already...
NOTE: restarting the DB instance is not allowed as it is running different applications.
appreciate your help in advance. Kindly email me at fedora13020@gmail.com
June 11, 2018 at 3:46 am
fedora132010 - Monday, June 11, 2018 3:01 AMHi Gurus,I'm new to SQL Server. We got an issue on our Production Server at this moment and i'm having a hard time fixing the'error.
The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'
I tried to do dbcc shrinkfile (logfile,1) but it doens't allow me.
I decided to restart the DB as follows:
1. alter database set single_user with rollback immediate
2. alter database set offline with rollback immediate
3. alter database set online with rollback immediate
When it came up, it's still in single user mode. I tried to do alter database set multi_user with rollback immediate but i'm getting, deadlock issues.
i am planning to force the DB to go into multi_user mode but i''m afraid it'll take sometime to go online and affects other databases.set deadlock pdqpriority high
alter database set multi_user with rollback immediate.my questions are:
1. if i run the set deadlock pdqpriority high + alter database set multi_user with rollback immediate, will it affect or block or deadlock other DB processes? on the same instance?
2. what is the best solution to fix to the 'full due to XTP_CHECKPOINT' error? Microsoft says it's in SQL2014 SP1 CPU4... but we are running Microsoft SQL2014 SP2 already...
NOTE: restarting the DB instance is not allowed as it is running different applications.appreciate your help in advance. Kindly email me at fedora13020@gmail.com
What is the recovery model of the database?
😎
My suggestion for resolving such a problem is NOT to shrink the file but to add another file immediately and then look into the problem. Restarting and altering the database may cause data loss and inconsistency.
June 11, 2018 at 4:20 am
Eirikur Eiriksson - Monday, June 11, 2018 3:46 AMfedora132010 - Monday, June 11, 2018 3:01 AMHi Gurus,I'm new to SQL Server. We got an issue on our Production Server at this moment and i'm having a hard time fixing the'error.
The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'
I tried to do dbcc shrinkfile (logfile,1) but it doens't allow me.
I decided to restart the DB as follows:
1. alter database set single_user with rollback immediate
2. alter database set offline with rollback immediate
3. alter database set online with rollback immediate
When it came up, it's still in single user mode. I tried to do alter database set multi_user with rollback immediate but i'm getting, deadlock issues.
i am planning to force the DB to go into multi_user mode but i''m afraid it'll take sometime to go online and affects other databases.set deadlock pdqpriority high
alter database set multi_user with rollback immediate.my questions are:
1. if i run the set deadlock pdqpriority high + alter database set multi_user with rollback immediate, will it affect or block or deadlock other DB processes? on the same instance?
2. what is the best solution to fix to the 'full due to XTP_CHECKPOINT' error? Microsoft says it's in SQL2014 SP1 CPU4... but we are running Microsoft SQL2014 SP2 already...
NOTE: restarting the DB instance is not allowed as it is running different applications.appreciate your help in advance. Kindly email me at fedora13020@gmail.com
What is the recovery model of the database?
😎My suggestion for resolving such a problem is NOT to shrink the file but to add another file immediately and then look into the problem. Restarting and altering the database may cause data loss and inconsistency.
The database is in FULL recovery mode. Even if i add a new log file, it will be consumed again as the transaction log is not being backed up.
June 11, 2018 at 4:36 am
fedora132010 - Monday, June 11, 2018 4:20 AMEirikur Eiriksson - Monday, June 11, 2018 3:46 AMfedora132010 - Monday, June 11, 2018 3:01 AMHi Gurus,I'm new to SQL Server. We got an issue on our Production Server at this moment and i'm having a hard time fixing the'error.
The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'
I tried to do dbcc shrinkfile (logfile,1) but it doens't allow me.
I decided to restart the DB as follows:
1. alter database set single_user with rollback immediate
2. alter database set offline with rollback immediate
3. alter database set online with rollback immediate
When it came up, it's still in single user mode. I tried to do alter database set multi_user with rollback immediate but i'm getting, deadlock issues.
i am planning to force the DB to go into multi_user mode but i''m afraid it'll take sometime to go online and affects other databases.set deadlock pdqpriority high
alter database set multi_user with rollback immediate.my questions are:
1. if i run the set deadlock pdqpriority high + alter database set multi_user with rollback immediate, will it affect or block or deadlock other DB processes? on the same instance?
2. what is the best solution to fix to the 'full due to XTP_CHECKPOINT' error? Microsoft says it's in SQL2014 SP1 CPU4... but we are running Microsoft SQL2014 SP2 already...
NOTE: restarting the DB instance is not allowed as it is running different applications.appreciate your help in advance. Kindly email me at fedora13020@gmail.com
What is the recovery model of the database?
😎My suggestion for resolving such a problem is NOT to shrink the file but to add another file immediately and then look into the problem. Restarting and altering the database may cause data loss and inconsistency.
The database is in FULL recovery mode. Even if i add a new log file, it will be consumed again as the transaction log is not being backed up.
What do you mean by "he transaction log is not being backed up"? If the database is in full recovery mode, you MUST back up the transaction log!
😎
June 11, 2018 at 6:53 am
What Eirikur said.
June 11, 2018 at 2:37 pm
Beatrix Kiddo - Monday, June 11, 2018 6:53 AMWhat Eirikur said.
This is the error message that shows on the sql server log.
I have a scheduled transaction log backup that runs every 10 minutes but it's throwing error as 'The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'.
June 12, 2018 at 2:54 am
fedora132010 - Monday, June 11, 2018 2:37 PMBeatrix Kiddo - Monday, June 11, 2018 6:53 AMWhat Eirikur said.This is the error message that shows on the sql server log.
I have a scheduled transaction log backup that runs every 10 minutes but it's throwing error as 'The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'.
IIRC, this is an issue which was fixed in SQL Server 2014 SP1 CU4 (build number 12.0.4436.0), is your instance fully patched and updated?
😎
Question, are you using Memory Optimized tables?
June 12, 2018 at 5:26 am
Eirikur Eiriksson - Tuesday, June 12, 2018 2:54 AMfedora132010 - Monday, June 11, 2018 2:37 PMBeatrix Kiddo - Monday, June 11, 2018 6:53 AMWhat Eirikur said.This is the error message that shows on the sql server log.
I have a scheduled transaction log backup that runs every 10 minutes but it's throwing error as 'The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'.
IIRC, this is an issue which was fixed in SQL Server 2014 SP1 CU4 (build number 12.0.4436.0), is your instance fully patched and updated?
😎Question, are you using Memory Optimized tables?
Yes. they are using Memory Optimized tables.... but it's SQL2014 SP2.
June 12, 2018 at 9:49 am
fedora132010 - Tuesday, June 12, 2018 5:26 AMEirikur Eiriksson - Tuesday, June 12, 2018 2:54 AMfedora132010 - Monday, June 11, 2018 2:37 PMBeatrix Kiddo - Monday, June 11, 2018 6:53 AMWhat Eirikur said.This is the error message that shows on the sql server log.
I have a scheduled transaction log backup that runs every 10 minutes but it's throwing error as 'The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'.
IIRC, this is an issue which was fixed in SQL Server 2014 SP1 CU4 (build number 12.0.4436.0), is your instance fully patched and updated?
😎Question, are you using Memory Optimized tables?
Yes. they are using Memory Optimized tables.... but it's SQL2014 SP2.
What CU are you on?
😎
l
June 16, 2018 at 5:45 am
Eirikur Eiriksson - Tuesday, June 12, 2018 9:49 AMfedora132010 - Tuesday, June 12, 2018 5:26 AMEirikur Eiriksson - Tuesday, June 12, 2018 2:54 AMfedora132010 - Monday, June 11, 2018 2:37 PMBeatrix Kiddo - Monday, June 11, 2018 6:53 AMWhat Eirikur said.This is the error message that shows on the sql server log.
I have a scheduled transaction log backup that runs every 10 minutes but it's throwing error as 'The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'.
IIRC, this is an issue which was fixed in SQL Server 2014 SP1 CU4 (build number 12.0.4436.0), is your instance fully patched and updated?
😎Question, are you using Memory Optimized tables?
Yes. they are using Memory Optimized tables.... but it's SQL2014 SP2.
What CU are you on?
😎
l
SP2 CU5
June 16, 2018 at 9:55 am
Beatrix Kiddo - Monday, June 11, 2018 6:53 AMWhat Eirikur said.
+1
...
June 18, 2018 at 7:07 am
fedora132010 - Saturday, June 16, 2018 5:45 AMEirikur Eiriksson - Tuesday, June 12, 2018 9:49 AMfedora132010 - Tuesday, June 12, 2018 5:26 AMEirikur Eiriksson - Tuesday, June 12, 2018 2:54 AMfedora132010 - Monday, June 11, 2018 2:37 PMBeatrix Kiddo - Monday, June 11, 2018 6:53 AMWhat Eirikur said.This is the error message that shows on the sql server log.
I have a scheduled transaction log backup that runs every 10 minutes but it's throwing error as 'The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'.
IIRC, this is an issue which was fixed in SQL Server 2014 SP1 CU4 (build number 12.0.4436.0), is your instance fully patched and updated?
😎Question, are you using Memory Optimized tables?
Yes. they are using Memory Optimized tables.... but it's SQL2014 SP2.
What CU are you on?
😎
lSP2 CU5
so what now? why still happens to SP2 CU5 Enterprise edition..
June 18, 2018 at 7:57 am
Are you doing replication using this database / has this database ever been involved in any replication?
What does `DBCC OPENTRAN` say when you run it in that database?
Are you backing up your transaction log?
What are your transaction log growth settings? How big is it? Is there space on the drive for it to grow further?
Thomas Rushton
blog: https://thelonedba.wordpress.com
June 20, 2018 at 4:47 am
ThomasRushton - Monday, June 18, 2018 7:57 AMAre you doing replication using this database / has this database ever been involved in any replication?What does `DBCC OPENTRAN` say when you run it in that database?
Are you backing up your transaction log?
What are your transaction log growth settings? How big is it? Is there space on the drive for it to grow further?
Are you doing replication using this database / has this database ever been involved in any replication?
Answer: Yes, it has always on configured but was disconnected after the tx log backup failed.
What does `DBCC OPENTRAN` say when you run it in that database?
Answer: No open transcation
Are you backing up your transaction log?
Answer: Yes, it runs every 10 minutes.. but failed for few days because of the 'XPT_CHECKPOINT' error
What are your transaction log growth settings? How big is it? Is there space on the drive for it to grow further?
Answer: 1GB growth and the drive for tx logs is 400GB. All used because the db can't do backup of transcation log because of XPT_CHECKPOINT error.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply