Microsoft does relatively a good job in documenting what doesn't work in Microsoft Fabric's SQL Databases. There's a good overview available already in the documentation, though beyond this the current post lists my finding while testing the previously written code on this blog,
USE Database
The standard syntax allows to change via USE the database context to the
specified database or database snapshot. Unfortunately, this syntax doesn't
seem to be supported currently and unfortunately many scripts seem to abuse of
it. Thus, the following line of code throws an error:
-- changing the context USE master; GO USE tempdb;
"Msg 40508, Level 16, State 1, Line 1, USE statement is not supported to switch between databases. Use a new connection to connect to a different database"
However, one can use the 3-part naming convention to reference the various
objects:
-- sys metadata - retrieving the database files
SELECT *
FROM tempdb.sys.database_files dbf
ORDER BY name;
Even if the tempdb is not listed in the sys.databases table, it's still available for querying, which can prove helpful for troubleshooting.
DBCC commands
The documentation warns that some DBCC commands won't work, though in some
cases there are also alternatives. For example:
-- clearing the procedure cache via DBCC DBCC FREEPROCCACHE;
"Msg 2571, Level 14, State 9, Line 1, User '<user>' does not have permission to run DBCC freeproccache."
Alternatively, one can use the following command, which seems to work:
-- clearing the procedure cash via ALTER ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
CHECKDB, which checks the logical and physical integrity of all the objects in the specified database, can't be used as well:
-- Checking the logical and physical integrity of a database DBCC CHECKDB();
"Msg 916, Level 14, State 2, Line 1, The server principal "..." is not able to access the database "..." under the current security context."
-- checking a table's integrity DBCC CHECKTABLE ('SalesLT.Address');
"Msg 916, Level 14, State 2, Line 2, The server principal "..." is not able to access the database "..." under the current security context."
-- retrieving the LOGSPACE information for all databases DBCC SQLPERF (LOGSPACE);
"Msg 297, Level 16, State 10, Line 1, The user does not have permission to perform this action."
-- current query optimization statistics DBCC SHOW_STATISTICS('SalesLT.Address','PK_Address_AddressID');
Name | Updated | Rows | Rows Sampled | Steps | Density | Average key length | String Index | Filter Expression | Unfiltered Rows | Persisted Sample Percent |
PK_Address_AddressID | Dec 21 2024 3:02AM | 450 | 450 | 197 | 1 | 4 | NO | 450 | 0 |
-- shrinking database DBCC SHRINKDATABASE([AdventureWorks01-...]) WITH NO_INFOMSGS;
Update 29-Jan-2025: According to an answer from Ask the Expert session on Fabric Database [3], Microsoft seems to be working in bringing more DBCC features to SQL databases.
Happy coding!
Previous Post <<||>> Next Post
References:
[1] Microsoft Learn (2024) SQL Server: USE <database> [link]
[2] Microsoft Learn (2024) Database console commands [link]
[3] Microsoft Reactor (2025) Ask The Expert - Fabric Edition - Fabric Databases [link]