Azuredatastudio: Database Folder not Expanding

[joelvega]

We have written the needed data into your clipboard because it was too large to send. Please paste:

Issue Type: Performance Issue

With connecting to an Azure SQL Database, the Database folder will not expand. It will always timeout.

Azure Data Studio version: azuredatastudio 1.9.0 (78a42e1d112ae3231777722b51eaf44f83ddbe55, 2019-07-10T04:31:36.998Z)
OS version: Windows_NT x64 10.0.17763

System Info

|Item|Value|
|---|---|
|CPUs|Intel(R) Core(TM) i9-9900K CPU @ 3.60GHz (16 x 3600)|
|GPU Status|2d_canvas: enabled
checker_imaging: disabled_off
flash_3d: enabled
flash_stage3d: enabled
flash_stage3d_baseline: enabled
gpu_compositing: enabled
multiple_raster_threads: enabled_on
native_gpu_memory_buffers: disabled_software
rasterization: enabled
surface_synchronization: enabled_on
video_decode: enabled
webgl: enabled
webgl2: enabled|
|Load (avg)|undefined|
|Memory (System)|31.92GB (16.32GB free)|
|Process Argv||
|Screen Reader|no|
|VM|0%|

Process Info

CPU %   Mem MB     PID  Process
    1       67   67428  azuredatastudio main
    1       76    2236     window (Issue Reporter)
    1      230   29960     window (Azure Data Studio)
    0      109   68796       extensionHost
    0       69   22236         "c:\Users\JVega\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\1.5.0-alpha.105\MicrosoftSqlToolsServiceLayer.exe" --log-file C:\Users\JVega\AppData\Roaming\azuredatastudio\mssql\sqltools_68796.log --tracing-level Critical
    0        7   26060           console-window-host (Windows internal process)
    0       15   60868         "c:\Users\JVega\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\1.5.0-alpha.105\SqlToolsResourceProviderService.exe" --log-file C:\Users\JVega\AppData\Roaming\azuredatastudio\mssql\resourceprovider_68796.log --tracing-level Critical
    0        7    3852           console-window-host (Windows internal process)
    0       18   66156         "C:\Users\JVega\AppData\Local\Programs\Azure Data Studio\azuredatastudio.exe" "c:\Users\JVega\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\json-language-features\server\dist\jsonServerMain" --node-ipc --clientProcessId=68796
    0       15   69092         "c:\Users\JVega\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\1.5.0-alpha.105\MicrosoftSqlToolsCredentials.exe" --log-file C:\Users\JVega\AppData\Roaming\azuredatastudio\mssql\credentialstore_68796.log --tracing-level Critical
    0        7   35800           console-window-host (Windows internal process)
    0       47   35700     shared-process
    0      103   63632     gpu-process

Workspace Info

;

Extensions: none

[adsbot[bot]]

Thanks for submitting this issue. Please also check if it is already covered by an existing one, like:

• can't connect to server (#4320)
• Time outs connecting to servers (#2913)
  

[Charles-Gagnon]

Could you please paste the contents of the original report? Does this happen consistently and is there any error displayed when you try to expand or does it just sit there spinning forever?

[joelvega]

Could you please paste the contents of the original report? Does this happen consistently and is there any error displayed when you try to expand or does it just sit there spinning forever?

I copied and pasted the entire error message and results in the original post. This problem does happen consistently since I have applied an update from Azure Data Studio. After spinning from some time, it gives the error message "Object Explorer task didn't complete within 45 seconds."

I am able to right-click on the database and select Manage and then I instantly get a list of the table and I can do my work from there. This issue seems to be isolated to opening the database folder in the object explorer.

[juniordeveloperbootcamp]

I have a simlar issue after update to version 1.9.0. Please take a look at the screenshots provided

if I right click database folder and choose to manage, then it opens database tables and SPs in the dashboard view

[chaibloom]

It would be nice if this bug got more love. We have Azure servers hosting 5000 DBs, and they always time out when trying to open ServerName/Databases in the connections pane. While it can take a bit to populate that data in SSMS, it does eventually come up, and typically in less than 45 seconds.

[aaomidi]

@chaibloom which version of ADS are you using?

[chaibloom]

@aaomidi - 1.13.0

[fluggo]

I have this same issue in 1.13.1. Like juniordeveloperbootcamp, if I choose to "Manage" and then refresh the Object Explorer view, I am able to see the tables and views. Otherwise, it times out.

Server I'm connecting to is version 14.0.3045.24.

[nvonbenken]

I get this issue as well from time to time on 1.15.0-insider

[bew4lsh]

Experiencing this issue at version 1.14.1. Would love to use this product in place of ssms, but this basic feature's incompatibility with azure SQL strikes me as disqualifying. 'Azure' data studio, huh.

[luislozanodev]

same issue here. connecting to SQL 2014 and can't expand the database folders since I install Azure data studio a week ago. another issue, I deleted the Server Dashboard Search tables widget and i can't get it back, any ideas how to? I only have the tasks widget. see below. I appreciate any help.

[Charles-Gagnon]

@xspdr1800 - You can reset that by opening the command pallet and running the Preferences: Open Settings (JSON) command. Then find the "dashboard.server.widgets" value and delete it and everything in the square brackets after it. This will be something like this :

"dashboard.server.widgets": [
    {
        "name": "Tasks",
        "widget": {
            "tasks-widget": [
                "newQuery",
                "mssqlCluster.task.newNotebook",
                {
                    "name": "restore",
                    "when": "!mssql:iscloud && mssql:engineedition != 11"
                },
                "configureDashboard"
            ]
        },
        "gridItemConfig": {
            "sizex": 1,
            "sizey": 1
        }
    },
    {
        "widget": {
            "backup-history-server-insight": null
        }
    },
    {
        "widget": {
            "all-database-size-server-insight": null
        }
    }
]

Then re-open the dashboard and the widget should appear again.

@alanrenmsft @kisantia This is pretty annoying that there doesn't seem to be an easy way to reset the default dashboard widgets - let's make sure we have that be easy with the new Dashboard refactor.

[cairneym]

Also experiencing the expansion timeout with 500 databases on Azure SQL DB. Connection to the server is fine and the dashboard shows the full list of databases alsmost instantaneously.

However trying to expand the databases node on Object Explorer results in the 45 seconds timeout.

Is the timeout value configurable in the settings anywhere?

Version 1.15.1

[alanrenmsft]

@xspdr1800 - You can reset that by opening the command pallet and running the Preferences: Open Settings (JSON) command. Then find the "dashboard.server.widgets" value and delete it and everything in the square brackets after it. This will be something like this :

"dashboard.server.widgets": [
    {
        "name": "Tasks",
        "widget": {
            "tasks-widget": [
                "newQuery",
                "mssqlCluster.task.newNotebook",
                {
                    "name": "restore",
                    "when": "!mssql:iscloud && mssql:engineedition != 11"
                },
                "configureDashboard"
            ]
        },
        "gridItemConfig": {
            "sizex": 1,
            "sizey": 1
        }
    },
    {
        "widget": {
            "backup-history-server-insight": null
        }
    },
    {
        "widget": {
            "all-database-size-server-insight": null
        }
    }
]

Then re-open the dashboard and the widget should appear again.

@alanrenmsft @kisantia This is pretty annoying that there doesn't seem to be an easy way to reset the default dashboard widgets - let's make sure we have that be easy with the new Dashboard refactor.

@Charles-Gagnon I could find the original comment provided by @xspdr1800, but I think we can do it by adding a task 'Restore settings' to server and database dashboards, the workflow would be:

1. click the 'Restore settings' task button in the tasks widget
2. prompt the user with impact of the action and ask for confirmation (using standard vscode popup notification)
3. remove the customization done by user

I will run this by the team in our next dashboard improvement meeting this week.

[tbolon]

This error still occurs on latest version (1.17). Was already submitted in #3270.

As suggested in the other issue, the database listing is instant when using the server details, but fails when using the left pane browser. For just displaying the database nodes, the query you execute could be optimized.

It's not really fun to exclude all people using Azure SQL Database with more than a few hundreds database from using your tool.

Perhaps at least you could provide a simple way (raw setting) to increase the timeout for this query ?

[perrydyball]

Still an issue in 1.19

Changing to using full fat SQL SSMS

[tbolon]

Still occurring on latest version with less than 50 databases on an Azure Sql Database server. Same error: the server browser tries to connect to all databases one after another with a global timeout of 45 seconds. Unsurprisingly, it does not scale well when you have any serious amount of databases. And it works perfectly well when you use the Manage Server pane to list all databases (certainly only using master db to populate this list and restraining itself from doing any operation on each database while enumerating).

I am surprised that this issue is not given any attention, since it completely ruins basic Azure Data Studio usage when connecting to Azure Sql Database Server with more than any trivial amount of databases.

[alanrenmsft]

@abist could you please take a look?

[abist]

@alanrenmsft I don't think I will be able to work on this for August release, this is a perf issue and a non-trivial task

[hfleitas]

From terminal, click output, Log (Window)

authenticationType:SqlLogin|database:|server:SQLSVR2019|user:hiram|group:37244e90-6961-4ab4-b7d2-dfaa19ef5e44
[2020-10-23 15:46:08.088] [renderer1] [info] Adding connection with DB name connection:providerName:MSSQL|applicationName:azdata|authenticationType:SqlLogin|database:hiramdb|server:SQLSVR2019|user:hiram|group:37244e90-6961-4ab4-b7d2-dfaa19ef5e44
[2020-10-23 15:46:08.169] [renderer1] [error] An unknown error occurred. Please consult the log for more details.
[2020-10-23 15:46:08.170] [renderer1] [error] Cannot read property 'isCloud' of null: TypeError: Cannot read property 'isCloud' of null
    at t.MssqlIconProvider.getConnectionIconId (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\dist\main.js:411:37875)
    at d.$getConnectionIconId (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:697:248)
    at g._doInvokeHandler (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:969:707)
    at g._invokeHandler (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:969:399)
    at g._receiveRequest (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:968:59)
    at g._receiveOneMessage (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:966:886)
    at c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:965:34
    at l.fire (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:48:475)
    at v.fire (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:259:381)
    at c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:1149:963
    at l.fire (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:48:475)
    at v.fire (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:259:381)
    at t.PersistentProtocol._receiveMessage (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:264:451)
    at c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:261:489
    at l.fire (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:48:475)
    at p.acceptChunk (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:256:851)
    at c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:256:203
    at Socket.t (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:266:54)
    at Socket.emit (events.js:223:5)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:290:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at Pipe.onStreamRead (internal/stream_base_commons.js:181:23)
[2020-10-23 15:46:08.574] [renderer1] [error] Failed to create Object Explorer session

[Charles-Gagnon]

@hfleitas I think your issue is more along the lines of https://github.com/microsoft/azuredatastudio/issues/12992

Has this also recently just started happening for you? (as in with the latest release)

I'm going to leave instructions for some further logs to provide in the linked issue - the issue seems to be in a separate part of the application. So if you could get me those that would be helpful.

[hfleitas]

@Charles-Gagnon yes, this is recently occurring for me and my team. I'll send the logs in the linked issue. Thx

[hastiAu]

hi @Charles-Gagnon
I find this "dashboard.server.widgets":

but I cant delete it
how can I delete?

[Charles-Gagnon]

What do you mean you can't delete it? If you open the JSON settings you should be able to delete the entry from the JSON text editor that is opened.

[hastiAu]

I see this code in setting.json

{
"workbench.enablePreviewFeatures": true,
"datasource.connectionGroups": [
{
"name": "ROOT",
"id": "C777F06B-202E-4480-B475-FA416154D458"
}
],
"datasource.connections": [
{
"options": {
"connectionName": "Server",
"server": "185.55.224.2",
"database": "etransfer_db",
"authenticationType": "SqlLogin",
"user": "etransfer_user1",
"password": "",
"applicationName": "azdata",
"groupId": "C777F06B-202E-4480-B475-FA416154D458",
"databaseDisplayName": "etransfer_db"
},
"groupId": "C777F06B-202E-4480-B475-FA416154D458",
"providerName": "MSSQL",
"savePassword": true,
"id": "c69f6f6f-e72c-4e62-a519-5b649cceb9e1"
},
{
"options": {
"connectionName": "localhost",
"server": "Localhost",
"database": "",
"authenticationType": "SqlLogin",
"user": "sa",
"password": "",
"applicationName": "azdata",
"groupId": "C777F06B-202E-4480-B475-FA416154D458",
"databaseDisplayName": ""
},
"groupId": "C777F06B-202E-4480-B475-FA416154D458",
"providerName": "MSSQL",
"savePassword": true,
"id": "53c6503d-9eb7-44b3-934b-2c2a3db35bb8"
}
],
"workbench.startupEditor": "welcomePage"
}

---

but "dashboard.server.widgets":

is in defaultsetting.json that I cant change this file

[Charles-Gagnon]

You shouldn't be modifying the defaults.

Since you don't have a custom setting in the user settings JSON then it should be using the default one. Is this not happening?

Note that this dashboard stuff has nothing to do with the database folder not expanding. This was just a separate issue that someone else was having. If you're also having problems with the dashboard widgets then I would suggest opening another issue and we can help you there so that we can keep this one on topic.