MODIFY FILE failed. Specified size is less than or equal to current size

I recently ran into the following error when trying to install Polybase on SQL Server 2017 Enterprise:

MODIFY FILE failed. Specified size is less than or equal to current size

Polybase creates three new databases during the installation:

  • DWConfiguration
  • DWDiagnostics
  • DWQueue

The installation seems to have a problem creating these new databases when using the file settings on the model database.

Solution:

To get around this, toggle over to SQL Server Management Studio and change the file settings on the model database. In my case, they were set to 1024 MB, 512 MB (Logs), and 512 MB autogrowth.

I changed it to 512 MB, 71 MB (Logs, and 10 MB (autogrowth)

I then toggled back to the Polybase installation, clicked Retry and it worked! After the installation, I then went back and changed my model file size settings back to what they were.

SQL Saturday # 891 – Los Angeles 2019

SQL Saturday Los Angeles, 2019 is coming up quick! This is a FREE one-day training event for Microsoft Data Platform platform professionals. The event will be held on Saturday, June 15th at Layola Marymount University (“LMU”), 1 LMU Drive, Los Angeles, California, 90045. Click on the link below for more details and to reserve your spot today. I will be presenting a session on Master Data Services and hope to see you there!

https://www.sqlsaturday.com/891/eventhome.aspx

SQL Saturday # 802 – San Diego 2018

SQL Saturday San Diego is just over a week away! It will be held September 22nd, 2018 at UCSD Extension – University City Center, 6256 Greenwich Dr., San Diego, California, 92122.

I will be presenting a session on performance tuning jobs. Learn performance tuning strategies on how to cut hours (not minutes) from your jobs.

I hope to see you there!

http://www.sqlsaturday.com/802/EventHome.aspx

SQL Saturday # 773 – Los Angeles 2018

SQL Saturday L.A. is just around the corner! It will be held on June 9th, 2018 at Loyola Marymount University (“LMU”), 1 LMU Drive, Los Angeles, California, Los Angeles, California, 90045.

I will be presenting a session on performance tuning jobs. Learn performance tuning strategies on how to cut hours (not minutes) from your jobs.

There is still time to register so be sure to signup today!

http://www.sqlsaturday.com/773/EventHome.aspx

 

SQL Saturday # 740 – Orange County 2018

SQL Saturday Orange County, 2018 is quickly approaching! The event will be held on Saturday, April 14th at Golden West College, 15744 Goldenwest St, Orange County, California, United States, 92647. There is still time to register.

Also, I will be presenting a new session on performance tuning jobs and strategies. Hope to see you there!

http://www.sqlsaturday.com/740/eventhome.aspx

 

Can’t Start SQL Server Due to Insufficient Memory

SQL Server loves memory and will take as much as you give it. Sometimes you may want to limit the amount of memory it can have. To do this, go into management studio, right click on the server, select properties, and select the memory tab. To limit the amount of memory it can have, set a value for Maximum Server Memory (in MB).

If you aren’t careful, you can set a value that is too low and SQL Server won’t be able to run. I recently did this on my local copy and had a hard time changing the setting again to a higher value as everything expects that SQL Server is running, and that you can simply change the configuration.

In my case, I had set the value to 128 MB and SQL Server kept showing an “insufficient memory” error in the windows event log and wouldn’t start.

If you find yourself in a similar predicament, here is how to get it working again.

Open a command prompt as an administrator. Find the install path of your version of the SQL Server engine. In my case it was “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn”.

Change to the directory:

Cd “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn”

Next, we want to start SQL Server with minimal configuration. This will start SQL Server in a minimal state and allow you to connect in management studio. To do this use the following:

Sqlservr.exe -f -s <instancename>

Open the server properties window and bump up the max memory limit.

Close the command prompt window to shut down SQL Server. Finally, go to SQL Server Configuration Manager and start the service like normal.

You should now be able to run the service as usual and connect.