Auto Groo of Data file messsage in Sql server 2017

Question

Hello,
I have Sql Server 2017 and currently set up in SIMPLE Recovery model but we will setting in FULL recovery Model with TLog backup set up.
I see the message in Sql serve error log:

"Autogrow of file 'HZ_Data' in database 'HZProd' took 80576 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file."strong text

My current settings asre as below:

Total Disk Space    Available Free space  
600 GB  68 GB  

File name   Current Size  
HZdata.mdf  291 GB  
HZlog.ndf   99.6 GB  
tempdev 23 GB  
templog 1 GB  
temp2   23 GB  
temp3   23 GB  
temp4   23 GB  


Logical File Name   Initial Size MB Auto Growth  
HZ_Data 298144  By 12500 MB, Unrestricted  
HZ_Log  102001  By 8500 MB, Unlimited  

Please note that this is a VM and we have everything on one drive

I have following question:

1. What should be the ideal settings/configuration I should follow If my configuration is not correct for file size?
2. I am trying to set up "instant file initialization" but when going into "Perform Volume Maintenance Task" to add Sql Service Account which we have "NT Service\MSSQLSERVER" but couldn't find it, what I should add or search as this is a MS virtual account?
   3)What should be ideal Data file and Log file size should show for any database?
   Like I have currently, Is it right?
   :
   

4) I am refreshing this database from another database and It's taking more space as my source server having only 500 GB disk space and it shows free space 180 GB but for my new server after refreshing and some minimum deployment (not much space should take), it hsoes only 70 GB FEREE space even thought this new server has total disk space 600 GB.

Thank You!

Answer 1

You have your auto-growth set to 1.25GBs. It is going to take a while to grow your file.

Is there a reason you have your auto-growth that large?

Answer 2

Thanks Tom.
Auto Growth I think it's carried over from Source server when I refreshed?
Could you please advice me what I should change it or when I refresh again how to handle it?

Answer 3

2) I am trying to set up "instant file initialization" but when going into "Perform Volume Maintenance Task" to add Sql Service Account which we have "NT Service\MSSQLSERVER" but couldn't find it, what I should add or search as this is a MS virtual account?

We don't see what you are doing, but make sure that you have scope set to the local computer. It may default to the domain.

What should be ideal Data file and Log file size should show for any database?

How long is a string? I'm not sure that there ever is a thing like an "ideal size" for a data file, but if there is depends on the application, business requirements. 291 GB can be hopelessly oversized for one database which is need of cleansing, and it can be ridiculously small for something aims to be a data warehouse.

4) I am refreshing this database from another database and It's taking more space as my source server having only 500 GB disk space and it shows free space 180 GB but for my new server after refreshing and some minimum deployment (not much space should take), it hsoes only 70 GB FEREE space even thought this new server has total disk space 600 GB.

I read this couple of times, but I was not able to understand what you are trying to say.

Answer 4

Hi @pdsqsql ,

> IFI is not yet set up as I am having issue with Ms virtual account which we are using as Service account "NT Service\MSSQLSERVER" that needs to be added into "Perform Volume Maintenance Task".

Your SQL server 2017 is default instance? Default instance: NT SERVICE\MSSQLSERVER. Named instance: NT Service\MSSQLServer$InstanceName.

Please make sure your domain isn't selected in the From this location text box, but rather your computer name as below screenshot.

---

If the response is helpful, please click "Accept Answer", thank you.

Answer 5

Current DB Size is: 390 GB and space available in DB property window 154 GB for that Database but when I checked my D:\ drive where all the files stored and found Free disk space is 65 GB on the server.

That means that the database files take up 390 GB in space, and inside of these 390 GB, 154 GB is currently not in use.

Free space in the file system is 65 GB is space not taken up by any file.

SQL Server does not show any information about what is free in the file system when it shows allocation information about the database.

And Windows has no clue about what is inside those SQL Server files. All it knows is that there is a 291 GB file.