#13886: Storage Mngr : Shrink Database feature gives Warning:"CommonSchemaUtil.executeDDL() was not successful"

tariqrahiman reported 2015-11-20T23:40:51Z · narendravatsaraj(*) last modified 2016-02-01T07:45:26Z

Storage Mngr : Shrink Database feature gives Warning:"CommonSchemaUtil.executeDDL() was not successful"

Dev	funfun
QA	tariqrahiman

Priority	Low
Complexity	Unknown

Component	DBA-T - SQL Server - Storage Mngr
Version	17.0

Version: 17.0.0-rc-39

Build #: 47130

Build Date: 2015-Nov-19 03:07:33 PM

Storage Mngr : Shrink Database feature gives Warning:"CommonSchemaUtil.executeDDL() was not successful"

1. Connect to AWS SQL Server 2014.

2. Open SQL Server -> Storage Manager

3. Navigate to Databases tab

4. Select any database (from Tree Tab under Name column)

5. Click on toolbar menu "Shrink Database"

6.Warning displayed "CommonSchemaUtil.executeDDL() was not successful".

If we execute SQL query for the same in Query analyzer

USE [AdventureWorks2014]

DBCC SHRINKFILE(N'AdventureWorks2014_Data', 0, TRUNCATEONLY)

It executes successfully.(see attached screenshot)

8 attachments

Screen Shot 2015-11-20 at 3.32.08 PM.png

2015-11-20T23:40:51Z

101 KB
Screen Shot 2015-11-20 at 3.31.28 PM.png

2015-11-20T23:40:51Z

121 KB
DialogWithMessagesTab

2015-11-24T06:35:14Z

23 KB
DailogwithMessageTAB_RC-43.png

2015-11-26T08:44:28Z

51 KB
ShrinkDatabase-RC-43.png

2015-11-26T08:44:28Z

144 KB
Check_DBCC_RC-43.png

2015-11-26T10:16:16Z

87 KB
13886_ADS_18_Dev_38.png

2016-02-01T07:44:55Z

227 KB
13886_DBCC_ADS_18_Dev_38.png

2016-02-01T07:44:55Z

219 KB

All Comments (12) Change History

SachinPrakash 2015-11-21T00:41:44Z

@funfun: pls see this comment from Ivan: https://www.aquaclusters.com/app/home/project/public/aquadatastudio/issue/9543?tab=comment#89556DCF-7F18-97F5-E858-D3F3D1ACAE8B

NielsGron 2015-11-22T14:13:57Z

Also check the DBCC action.

funfun 2015-11-24T04:31:45Z

The root cause is execution of "DBCC SHRINKDATABASE(N'database_name')" via CommonSchemaUtil.executeDDL() generates the following warnings:

java.sql.SQLWarning: Changed database context to 'database_name'.
java.sql.SQLWarning: DBCC SHRINKDATABASE: File ID 1 of database ID 13 was skipped because the file does not have enough free space to reclaim.
java.sql.SQLWarning: DBCC SHRINKDATABASE: File ID 2 of database ID 13 was skipped because the file does not have enough free space to reclaim.
java.sql.SQLWarning: DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Hence, CommonSchemaUtil.executeDDL() returns 'false' to caller (CommonSchemaDialog) which cannot distinguish 'warning' from 'error' and thus simply displays a message dialog and reports "CommonSchemaUtil.executeDDL() was not successful.".

The root cause is execution of "DBCC SHRINKDATABASE(N'database_name')" via CommonSchemaUtil.executeDDL() generates the following warnings:

java.sql.SQLWarning: Changed database context to 'database_name'.
java.sql.SQLWarning: DBCC SHRINKDATABASE: File ID 1 of database ID 13 was skipped because the file does not have enough free space to reclaim.
java.sql.SQLWarning: DBCC SHRINKDATABASE: File ID 2 of database ID 13 was skipped because the file does not have enough free space to reclaim.
java.sql.SQLWarning: DBCC execution completed. If DBCC printed error messages, contact your system administrator.

funfun 2015-11-24T04:34:32Z

Also check the DBCC action.

MssqlDBCCDialog will not display an error dialog and reports "CommonSchemaUtil.executeDDL() was not successful."; instead, the generated warning messages is displayed in Messages tab owned by MssqlDBCCDialog. This behavior is the same as the fix made for issue #7059

funfun 2015-11-24T04:43:29Z

@Sachin,

MssqlDBCCDialog and AlertDialog (issue #7059) are derived from CommonSchemaTab, MssqlShrinkDatabaseDialog is derived from CommonSchemaDialog. I will modify MssqlShrinkDatabaseDialog and CommonSchemaDialog to let them behave similar to MssqlDBCCDialog/AlertDialog and CommonSchemaTab. This means:
(1) A Messages tab will be added to MssqlShrinkDatabaseDialog to display warning messages.
(2) OK button will be disabled after warning messages are displayed in Messages tab.
(3) MssqlShrinkDatabaseDialog will remain open after warning messages are displayed, user needs to close it manually.

If you have different thoughts, please let me know.

funfun 2015-11-24T04:46:51Z

@funfun: pls see this comment from Ivan: https://www.aquaclusters.com/app/home/project/public/aquadatastudio/issue/9543?tab=comment#89556DCF-7F18-97F5-E858-D3F3D1ACAE8B

This is irrelevant because SQL Server does not have problem to retrieve warning messages.

funfun 2015-11-24T16:44:52Z · Edited by

SachinPrakash

17.0 - SVN r47215/17.0.0-rc-43

Fixed problem based on approach described here.

@Sachin,
Maybe we should change Cancel button's label from "Cancel" to "Close" after action is performed and OK button is disabled, see this screenshot. I will wait for your input before merge changes back to trunk.

[SP] Agreed - pls proceed

funfun 2015-11-24T21:34:51Z

17.0 - SVN r47230/17.0.0-rc-43
Changed Cancel button's label from Cancel to Close after shrink database action is successfully performed.

funfun 2015-11-25T00:23:06Z

18.0 - SVN r47242/18.0.0-dev-19
Merged changes made in 17.0 branch to trunk.

raghavsingh 2015-11-26T10:16:07Z · (edited)

Product: Aqua Data Studio

Version: 17.0.0-rc-43

Build #: 47239

Build Date: 2015-Nov-24 03:50:43 PM

Scenario1:

1.Connect to SQL Server 2014.

2.Go to Menu -> DBA Tools -> SQL Server -> Storage Manager

3.Select SQL Server 2014 DB. Click on OK

4.Storage Manager window opens.

5.Select any database (from Tree Tab under Name column)

6. Click on toolbar menu "Shrink Database"

7. Shrink Database pop up displayed,Click on OK button.

Current Result: Pass No Warning message displayed when we click on OK button.Refer 'Screenshot ShrinkDatabase-RC-43.png'

Previous Result: Displayed Warning message"Warning displayed "CommonSchemaUtil.executeDDL() was not successful". when we click on OK button.

-------------------------------------------------------------------------------------------------------------------------------------------------------

Scenario2:

1.Connect to SQL Server 2014.

2.Go to Menu -> DBA Tools -> SQL Server -> Storage Manager

3.Select SQL Server 2014 DB. Click on OK

4.Storage Manager window opens.

5.Select any database (from Tree Tab under Name column)

6. Click on toolbar menu "Shrink Database"

7. Shrink Database pop up displayed,Click on OK button.

Current Result:Pass: Changed Successfully Cancel button's label from "Cancel" to "Close" after action is performed and OK button is disabled. Refer screenshot 'DailogwithMessageTAB_RC-43'

Previous Result: Its displayed Cancel button after action performed for OK button.

------------------------------------------------------------------------------------------------------------------------------------------------------

Scenario3:

1.Connect to SQL Server 2014.

2.Go to Menu -> DBA Tools -> SQL Server -> Storage Manager

3.Select SQL Server 2014 DB. Click on OK

4.Storage Manager window opens.

5.Select any database (from Tree Tab under Name column)

6. Click on toolbar menu "DBCC" option.

7. Alter DBCC window opened successfully, Select any DBCC operation then click on Alter button.

Current Result:

Under Message Tab displayed changed database messages successfully. Refer updated screenshot 'Check_DBCC_RC-43.png'

Product: Aqua Data Studio

Version: 17.0.0-rc-43

Build #: 47239

Build Date: 2015-Nov-24 03:50:43 PM

Scenario1:

1.Connect to SQL Server 2014.

2.Go to Menu -> DBA Tools -> SQL Server -> Storage Manager

3.Select SQL Server 2014 DB. Click on OK

4.Storage Manager window opens.

5.Select any database (from Tree Tab under Name column)

6. Click on toolbar menu "Shrink Database"

7. Shrink Database pop up displayed,Click on OK button.

Current Result: Pass No Warning message displayed when we click on OK button.Refer 'Screenshot ShrinkDatabase-RC-43.png'

Previous Result: Displayed Warning message"Warning displayed "CommonSchemaUtil.executeDDL() was not successful". when we click on OK button.

-------------------------------------------------------------------------------------------------------------------------------------------------------

Scenario2:

1.Connect to SQL Server 2014.

2.Go to Menu -> DBA Tools -> SQL Server -> Storage Manager

3.Select SQL Server 2014 DB. Click on OK

4.Storage Manager window opens.

5.Select any database (from Tree Tab under Name column)

6. Click on toolbar menu "Shrink Database"

7. Shrink Database pop up displayed,Click on OK button.

Current Result:Pass: Changed Successfully Cancel button's label from "Cancel" to "Close" after action is performed and OK button is disabled. Refer screenshot 'DailogwithMessageTAB_RC-43'

Previous Result: Its displayed Cancel button after action performed for OK button.

------------------------------------------------------------------------------------------------------------------------------------------------------

Scenario3:

1.Connect to SQL Server 2014.

2.Go to Menu -> DBA Tools -> SQL Server -> Storage Manager

3.Select SQL Server 2014 DB. Click on OK

4.Storage Manager window opens.

5.Select any database (from Tree Tab under Name column)

6. Click on toolbar menu "DBCC" option.

7. Alter DBCC window opened successfully, Select any DBCC operation then click on Alter button.

Current Result:

Under Message Tab displayed changed database messages successfully. Refer updated screenshot 'Check_DBCC_RC-43.png'

tariqrahiman 2015-12-02T18:55:34Z

Verified in ADS v17.0.0-rc-48

narendravatsaraj(*) 2016-02-01T07:45:26Z

Verified in ADS 18 Dev -38

Search Tips

Aqua Data Studio / nhilam

Storage Mngr : Shrink Database feature gives Warning:"CommonSchemaUtil.executeDDL() was not successful"

8 attachments

Issue #13886

Completion

2 issue links

Issue #7059

Issue #12747

Search Tips

Aqua Data Studio / nhilam

Title

Storage Mngr : Shrink Database feature gives Warning:"CommonSchemaUtil.executeDDL() was not successful"

8 attachments

Issue #13886

Completion

2 issue links

Issue #7059

Issue #12747