February 8, 2019 at 11:55 am
Hi,
My databse size aroung 1tb.
Instead of selecting the file to shrink by mistake i did for whole db..Now it is running. I am doubting to stop the process. On this db transactional replication is goingon. In the replication monitor everything running excellent. no latency.
How much time it will take shrink 1tb database?
actually i was getting error as below.
The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'.
So thought of shrinking the log file. But did it for full db. It is a production db. Now i am scaring how much time it will take. Will it currupt if i kill shrinking process..
What i need to take care?Please guide me.:(
February 8, 2019 at 12:14 pm
pols - Friday, February 8, 2019 11:55 AMHi,
My databse size aroung 1tb.
Instead of selecting the file to shrink by mistake i did for whole db..Now it is running. I am doubting to stop the process. On this db transactional replication is goingon. In the replication monitor everything running excellent. no latency.
How much time it will take shrink 1tb database?
actually i was getting error as below.
The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'.
So thought of shrinking the log file. But did it for full db. It is a production db. Now i am scaring how much time it will take. Will it currupt if i kill shrinking process..
What i need to take care?Please guide me.:(
I don't know how much time it will take but you safely kill a shrink process.
After that backup the log. clean up the fragmentation from shrinking and check your patch level for your SQL Server. There was a fix in one or two of the updates for SQL Server 2014 related to issues with waits on xtp_checkpoint. So you may want to get current on your patch level if you aren't already.
Sue
February 8, 2019 at 12:25 pm
Thank you very much for your reply. Fragmentation cleanup means index rebuild right..
I have taken log backup of that db.. So what do u want me to do with log backup?
February 8, 2019 at 12:34 pm
pols - Friday, February 8, 2019 12:25 PMThank you very much for your reply. Fragmentation cleanup means index rebuild right..
I have taken log backup of that db.. So what do u want me to do with log backup?
Yup...clean up the indexes since shrinking will cause a lot of fragmentation.
Take the log backup to see if you get some space available in the log since you had errors it was full.
Sue
February 8, 2019 at 12:36 pm
sure i wil do that now. thanks.
February 8, 2019 at 12:38 pm
pols - Friday, February 8, 2019 12:36 PMsure i wil do that now. thanks.
And check your versions of SQL Server 2014 - there were fixes in some CUs for the issue with reporting log not truncating due to XTP_CHECKPOINT so you will want to get current on those since it could be causing the issues.
Sue
February 8, 2019 at 12:44 pm
Yes. This is sql 2014 and 12.0.5207.0 version.
February 8, 2019 at 2:34 pm
pols - Friday, February 8, 2019 12:44 PMYes. This is sql 2014 and 12.0.5207.0 version.
That version should be okay for the issues I was thinking about but there are other issues on 2014, this error and the replication you have that I just noticed. I have no idea what happened with the replication related issues as it was all in Connect which was replaced and who knows what MS did. There were also people who hit the issue when when they were on CU3/CU4 for SP1. They all had memory optimized filegroups - some didn't even have the tables, just the presence of the filegroup.
The only workaround I remember for the replication and the error was to set the autogrowth property of the file to a fixed size (not a percent, something you wouldn't want anyway).
The issues may be fixed on later service packs - I would try to test SP3 and see about moving to that patch level if possible.
Sue
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply