January 20, 2025 at 12:54 pm
Hi Experts,
I have database about 300GB in size with ADR enabled but after every failover\restart it takes about 10-20 minutes to come online. The database have multiple files and the tables are all partitioned.
Can anyone shed some light on this ?
CREATE DATABASE [LogsDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'RK_LogsDB', FILENAME = N'H:\Data\LogsDB\RK_LogsDB.mdf' , SIZE = 27212800KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [April]
( NAME = N'April', FILENAME = N'H:\Data\LogsDB\DataFileApril.ndf' , SIZE = 512KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [August]
( NAME = N'August', FILENAME = N'H:\Data\LogsDB\DataFileAugust.ndf' , SIZE = 21888000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [December]
( NAME = N'December', FILENAME = N'H:\Data\LogsDB\DataFileDecember.ndf' , SIZE = 20589056KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [February]
( NAME = N'February', FILENAME = N'H:\Data\LogsDB\DataFileFebruary.ndf' , SIZE = 512KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [January]
( NAME = N'January', FILENAME = N'H:\Data\LogsDB\DataFileJanuary.ndf' , SIZE = 14995456KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [July]
( NAME = N'July', FILENAME = N'H:\Data\LogsDB\DataFileJuly.ndf' , SIZE = 14460928KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [June]
( NAME = N'June', FILENAME = N'H:\Data\LogsDB\DataFileJune.ndf' , SIZE = 512KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [March]
( NAME = N'March', FILENAME = N'H:\Data\LogsDB\DataFileMarch.ndf' , SIZE = 512KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [May]
( NAME = N'May', FILENAME = N'H:\Data\LogsDB\DataFileMay.ndf' , SIZE = 512KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [November]
( NAME = N'November', FILENAME = N'H:\Data\LogsDB\DataFileNovember.ndf' , SIZE = 26024448KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [October]
( NAME = N'October', FILENAME = N'H:\Data\LogsDB\DataFileOctober.ndf' , SIZE = 22183424KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [September]
( NAME = N'September', FILENAME = N'H:\Data\LogsDB\DataFileSeptember.ndf' , SIZE = 20912128KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB )
LOG ON
( NAME = N'RK_LogsDB_log', FILENAME = N'I:\LOG\LogsDB\RK_LogsDB_log.ldf' , SIZE = 194467840KB , MAXSIZE = 2048GB , FILEGROWTH = 512KB )
WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO
January 20, 2025 at 1:17 pm
Is this a clustered sql server instance ?
Is this a db involved in an Availability group ?
Is query_store active ? ( did you implement trace flags 7745 / 7752 )
What's the actiual version number of this instance ? ( are both nodes up to the same CU ? )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 21, 2025 at 8:08 am
Thank you Johan for the response. Please find my inline comment
Is this a clustered sql server instance ?Yes, its a 3 node windows cluster in which 2 nodes are part of SQL Server cluster and the third node is Always On
Is this a db involved in an Availability group ?Yes
Is query_store active ? ( did you implement trace flags 7745 / 7752 ) No
What's the actiual version number of this instance ? ( are both nodes up to the same CU ? )Microsoft SQL Server 2022 (RTM-CU16) (KB5048033) - 16.0.4165.4 (X64), Yes both are on same CU
January 21, 2025 at 8:17 am
Are there indications for the slow operation in the sql server errorlog files?
Is the always on node involved with the clustered sqlserver instance ?
if yes, are you using a listener to connect to the database(s) ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 21, 2025 at 10:18 am
Are there indications for the slow operation in the sql server errorlog files?:I couldnt find anything specific in sql error log during the recovery phases
Is the always on node involved with the clustered sqlserver instance ?: Always On Node is the 3rd node , as mentioned in my previous post out of the 3 nodes in WSFC 2 are part of sql server and 3rd is Always On Node.
if yes, are you using a listener to connect to the database(s) ?Application are connected using Listener, whereas the DBA connect most using instance names.
January 21, 2025 at 12:40 pm
What's the TARGET_RECOVERY_TIME and recovery interval that has been configured on your databases ?
added: How many VLFs are to be handled for your databases ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 22, 2025 at 5:17 am
What's the TARGET_RECOVERY_TIME and recovery interval that has been configured on your databases ? Both values are set to '0'
added: How many VLFs are to be handled for your databases ?379821
January 22, 2025 at 7:10 am
The number of VLFs are to be seen per database and will be handled at startup/recovery time ( of the database, as a step in the startup of the instance )
Have a look at this great reference: "Transaction Log VLFs – too many or too few?"
and the - in the blog post mentioned - "Important change to VLF creation algorithm in SQL Server 2014"
and of course "8 Steps to better Transaction Log throughput"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 22, 2025 at 7:51 am
The number of VLFs are to be seen per database and will be handled at startup/recovery time ( of the database, as a step in the startup of the instance )
Have a look at this great reference: "Transaction Log VLFs – too many or too few?" and the - in the blog post mentioned - "Important change to VLF creation algorithm in SQL Server 2014"
and of course "8 Steps to better Transaction Log throughput"
Thank you, Johan. Your response to the post was not only positive but also exceptionally thoughtful. You truly come across as a genuine mentor
January 22, 2025 at 8:13 am
HTH
Please provide feedback on your progress and solution.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 28, 2025 at 10:25 am
I am also facing similar issue, Will go through the steps.
January 28, 2025 at 10:26 am
Repeated post
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply