0

I have a sql server 2017 Enterprise Edition installed on one of my servers. i am getting following error in my SQL Server logs.

DESCRIPTION: Autogrow of file 'xxxx_data' in database 'xxxxxx' was cancelled by user or timed out after 29627 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

My database datafile size is around 2 TB and auto grow is set to 64 MB unlimited. There is plenty of disk space available on disk. My trace file shows auto grow events enter image description here

I have looked in Event viewer and have found lot of enteries for: "Windows installer installed the product. Product Name SQL Server 2017 full text search etc. "

Please guide me to find out why auto grow event got cancelled. This is happening frequently on this server.

angadai
  • 1
  • 1
  • 1
  • 2
  • For a 2 TB data file why are you limiting data file growth to 64 MB? I set this to a size in _GB_, not _MB_. Also is instant file initialization enabled (check in `sys.dm_server_services`? There's no way that growth should take 30 seconds unless you are writing to a thumb drive that is two decades old. Did anything change recently (e.g. did you move the data file to different storage, or change the service account SQL Server is running as, or alter group policy, or make any Active Directory changes for that login)? – Aaron Bertrand May 06 '23 at 14:37
  • 1
    Even without IFI no 64MB growth should take 30 seconds, so I'd potentially look into problems with the underlying disk. – Aaron Bertrand May 06 '23 at 14:39
  • Topping out at 2 TB? The maximum file size of a file on an NTFS volume is (2^32 – 1) * cluster size... any chance the data file resides on an NTFS volume with a 512 byte cluster size? – AlwaysLearning May 06 '23 at 14:47
  • Given the frequency of autogrow events perhaps consider a larger autogrow size also? – Stu May 06 '23 at 14:48
  • @AaronBertrand, instant file initilization is enable. This server is hosted on cloud environment. how can i check if underlying disk system is causing the problem. – angadai May 06 '23 at 15:11
  • Well I don't know what "cloud environment" means (there are many), did you check in with the provider's support team? Also is your disk SSD/premium or did you pick the lowest possible (I learned last week that Azure still offers spinny HDDs by default - upgrading to something acceptable should be possible online). – Aaron Bertrand May 06 '23 at 15:24
  • Sorry my bad it is hosted on Azure the disk is premium ssd with 7500 provisioned IOPS, 250 Provisioned MS/s. we can upgrade the disk if i could somehow find out that the problem is with the disk. – angadai May 06 '23 at 15:34
  • Unfortunately the error message is ridiculously ambiguous (was it canceled or did it time out? or both?), and I don't think anyone _here_ is going to be able to help you get to the bottom of it. So I would contact support and say, look, I have premium SSD and do not expect these file growths to time out, can you help? – Aaron Bertrand May 06 '23 at 15:38
  • You might also consider right-sizing your data file and avoiding autogrow in general. Why are you letting so many autogrowth events happen? You have a big disk with enough room to grow, cut out the middleman and just make the files a lot bigger manually. – Aaron Bertrand May 06 '23 at 15:44
  • Are you responsible for the underlying virtualised hardware? To echo what some of the other comments have said, you should check what your disk allocation unit size is, [a question that has been asked many times](https://stackoverflow.com/questions/81236/how-can-i-view-the-allocation-unit-size-of-a-ntfs-partition-in-vista). And to reiterate, a 64MB autogrowth on a 2TB file is insane. Grow the file to account for the next X months of growth (and make that a periodic activity) or set it to a more appropriate increase. – Anthony Norwood May 07 '23 at 08:00

0 Answers0