4

I created a test database with one table

CREATE TABLE [dbo].[TestTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TimeStamp] [datetime] NOT NULL
) ON [PRIMARY]

I have a sql script that consist of one line. 

INSERT INTO TestTable ([TimeStamp]) VALUES(CURRENT_TIMESTAMP);

When I run the script in SSMS it works. But when I use it as a workload in DTA the the Analysis is Aborted with this error

The minimum storage space required for the selected physical design structures exceeds the default storage space selected by Database Engine Tuning Advisor. Either keep fewer physical design structures, or increase the default storage space to be larger than at least 3 MB.Use one of the following methods to increase storage space: (1) If you are using the graphical user interface, enter the required value for Define max. space for recommendations (MB) in the Advanced Options of the Tuning Options tabbed page; (2) If you are using dta.exe, specify the maximum space value for the -B argument; (3) If you are using an XML input file, specify the maximum space value for the element under

Using Windows 10 (latest update), SQL Server 2016 SP1.

Why do I get this error?

Improve this question
2
  • Did you try "one of the following methods to increase storage space" the error suggests?
    – Tom V
    Commented Nov 19, 2016 at 10:11
  • Yes, end then the error disappeared, but why would an workload that is so small need more than 3MB of memory?
    – Andis59
    Commented Nov 19, 2016 at 11:01

3 Answers 3

Reset to default
1

The small workload may not have used more than 3MB of memory. The error isn't regarding memory, it is regarding storage space.

From BOL: The maximum disk space limit is specified in megabytes. When multiple databases are tuned, all of the recommendations for them together (collectively) cannot exceed the maximum space limit that you specify.

Perhaps the recommendations exceeded 3MB.

Again from BOL: If unspecified, Database Engine Tuning Advisor assumes the smaller of the following: Three times the current raw data size, which includes the total size of heaps and clustered indexes on tables in the database, or The free space on the all attached disk drives plus the raw data size. The default space limit does not include nonclustered indexes and indexed views.

What is the current raw data size of your database?

The total size of all databases selected for tuning (data plus indexes and indexed views) should not exceed the defined max. space for recommendations

Improve this answer
5
  • The test.mdf file is 8MB. Free space is 8TB. If I set max space to 4MB it worked. Don't understand how the calculation works. 3*8 is 24 not 4
    – Andis59
    Commented Nov 20, 2016 at 9:09
  • the mdf is not the raw data size. What is the size of your .bak?
    – stacylaray
    Commented Nov 20, 2016 at 9:25
  • the test.bak is 2,52 MB
    – Andis59
    Commented Nov 20, 2016 at 9:31
  • Okay, so 2.52MB + recommendations is probably > 3MB.
    – stacylaray
    Commented Nov 22, 2016 at 22:48
  • I can get that it's more than 3MB, but how can 4MB be enough? 3*2.52=7.56; Seems like there is more to how to calculate this. But I guess trial and error is the way to go... Thanks for your answers!
    – Andis59
    Commented Nov 23, 2016 at 8:25
3

You have to go to Tuning Options and then Click Advanced Options. On the popup, define the max. space for recommendations.

https://www.sqlservercentral.com/Forums/Topic1383773-391-1.aspx

Improve this answer
1
  • This worked. Thanks!
    – tinonetic
    Commented May 4, 2022 at 14:38
-3

The fix for this issue was first released in Cumulative Update 10. For more information about how to obtain this cumulative update package for SQL Server 2016, click the following article number to view the article in the Microsoft Knowledge Base:

2783135 Cumulative update package 10 for SQL Server 2016

Improve this answer
2
  • Did you find any evidence concerning SQL Server 2016 (mentioned in the question)? I couldn't.
    – Tom V
    Commented Nov 19, 2016 at 18:20
  • Your link does not point to the correct KB article for the fix in CU10, this would be the right one. However, it is for running loads against lower versions of SQL Server where the OP is running it against 2016.
    – user507
    Commented Nov 19, 2016 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.