Skip to content

CLI Databases

radix29 edited this page Oct 31, 2026 · 1 revision

← goSSMS Home · ← CLI

CLI — Databases

The db group: the server's database inventory, its space in every sense (files, total space, log usage, per-table space), its options, and database lifecycle (create, drop, scoped configurations). All commands connect through a profile or ad hoc options — see Getting started.

Ten commands: list, info, files, space, log-usage, table-sizes, set, create, drop, scoped-config list/get/set. The four space commands report the server's own figures: db space and db table-sizes use sp_spaceused's arithmetic and the log figures are sys.dm_db_log_space_usage's, so under --unit kb each equals what the procedure or DMV prints. (The TUI's panes split space by allocation-unit type instead, so their data/index figures can differ — these commands match the server.)

Units, warnings, one-or-all — shared rules

  • --unit kb|mb|gb (default mb) names the size columns — size_mb becomes size_gb — and sets their text: kb integers, mb/gb two decimals (a JSON string, like a decimal). *_pct columns always have two decimals. Filters and sorting compare the exact KB, never the rounded text.
  • One database or all: db files, db space and db log-usage take exactly one of <name> or --all (neither or both is exit 2). --all walks every database, system ones included, catalog order, database column first. db table-sizes takes <name> only.
  • A database that cannot be read inside (not ONLINE, or no user for the login): named, db space/log-usage/table-sizes fail — exit 8 for the state, 9 for the login; db files still lists the file paths from sys.master_files with used/free NULL and a warning (paths are what you need when a database is in trouble). Under --all each such database is one warning and the rest are reported.
  • Warnings have fixed thresholds, no options, and fire only for the rows shown: a data or log file under 10 % free inside it, at ≥ 90 % of a capped max_size, or growing by a percentage; a file or volume under 10 % free; a log whose log_reuse_wait_desc is not NOTHING (transient waits such as CHECKPOINT included). Facts to look at, not judgements.

db list — the inventory

gossms db list -p dev
gossms db list -p dev --include-system -o json
gossms db list -p dev --state offline

One row per database, by name: name, database_id, state, recovery_model, compatibility_level, collation (NULL for a database not ONLINE), read_only, size_mb, data_mb, log_mb, created, is_system, snapshot_of (the source's name for a snapshot).

  • System databases — the four built-ins and a replication distribution database — only with --include-system. Snapshots are listed (their snapshot_of names the source).
  • Sizes are allocated file sizes from sys.master_files, so they answer in any state (tempdb's are its startup sizes); NULL where the login cannot see the files (needs VIEW ANY DEFINITION), and a failed size read is a warning, not an error.
  • --state is state_desc lower-cased: online, offline, restoring, recovering, recovery_pending, suspect, emergency, copying, offline_secondary.

db info — one database's properties

gossms db info -p dev AdventureWorks
gossms db info -p dev tempdb -o json

The Database Properties dialog as a record: identity and state (name, databaseId, state, recoveryModel, compatibilityLevel, collation, catalogCollation, readOnly, created, isSystem, isDistributor, snapshotOf), space (sizeMb, dataMb, logMb, dataUnallocatedMb, logAvailableMb), then the ALTER DATABASE SET options — owner, userAccess, pageVerify, containment, snapshotIsolation, readCommittedSnapshot, autoClose, autoShrink, autoCreateStats, autoUpdateStats, autoUpdateStatsAsync, parameterization, isTrustworthy, dbChaining, isBrokerEnabled, isEncrypted, delayedDurability, targetRecoveryTimeSeconds, acceleratedDatabaseRecovery, isLedger.

A part that could not be read is empty with a warning saying why: the space-used pair needs the database ONLINE and enterable; the rest answers from the server catalog in any state. An unknown database is NOT_FOUND, exit 8.

db files — files, growth and volume space

gossms db files -p dev AdventureWorks
gossms db files -p dev tempdb --unit kb
gossms db files -p dev --all --max-free-pct 10 --sort-by free-pct
gossms db files -p dev --all --type log --unit gb -o json

One row per file, database then file_id order: database (with --all), logical_name, type, filegroup, physical_path, size_<u>, used_<u>, free_<u>, free_pct, growth (10 %, none, or a size), max_size_<u> (unlimited or a size), volume_free_pct.

  • Read inside the database (sys.database_files, FILEPROPERTY), so tempdb's sizes are current. A FILESTREAM or full-text container's used, free, growth and max size are NULL. A snapshot lists its own sparse files, whose size is a ceiling.
  • --type data|log|filestream|fulltext, --filegroup <fg>, --sort-by name|size|used|free-pct, --max-free-pct <n> (keep the files whose free % is known and ≤ n — the disk-running-out early warning).
  • volume_free_pct needs VIEW SERVER STATE (NULL with a warning without).

--all --max-free-pct 10 --sort-by free-pct is the "what is filling up" one-liner: the fullest files on the whole instance, first.

db space — sp_spaceused, per database

gossms db space -p dev AdventureWorks
gossms db space -p dev --all -o json
gossms db space -p dev --all --unit gb

One row per database: database, total_<u>, data_size_<u>, data_used_<u>, unallocated_<u>, reserved_<u>, data_<u>, index_<u>, unused_<u> — sp_spaceused's figures and arithmetic, equal to its output in KB — then log_size_<u>, log_used_<u>, log_used_pct from the DMV (NULL with one warning without VIEW SERVER STATE). A snapshot shows its source's figures as of creation, log NULL, with a warning.

db log-usage — the log, and why it cannot shrink

gossms db log-usage -p dev AdventureWorks
gossms db log-usage -p dev --all
gossms db log-usage -p dev --all --unit gb -o json

One row per database: log_size_<u>, log_used_<u>, log_used_pct (sys.dm_db_log_space_usage), log_reuse_wait_desc (NOTHING is the good one; LOG_BACKUP means the log waits for a log backup; ACTIVE_TRANSACTION means an open transaction pins it — find it with monitor sessions), vlf_count, vlf_active (sys.dm_db_log_info, from 2016 SP2; NULL with a warning before).

  • Needs VIEW SERVER STATE, exit 9 without (the log DMVs are server-scoped despite their names).
  • --all skips snapshots (no log); a named snapshot is exit 8.

db table-sizes — the largest tables

gossms db table-sizes -p dev AdventureWorks
gossms db table-sizes -p dev AdventureWorks --top 10 --sort-by rows
gossms db table-sizes -p dev AdventureWorks --schema Sales --top 0 --unit kb -o json

One row per user table, largest first (default reserved; --sort-by reserved|rows|data|index|unused; schema then name break ties): schema, table, rows, reserved_<u>, data_<u>, index_<u>, unused_<u> — each row equal to EXEC sp_spaceused 'schema.table' in KB, the table's XML, spatial and full-text internal tables included in the right bucket.

  • --top default 20, 0 for every table (a cut, not a truncation — totalRowCount stays honest in JSON).
  • Needs no VIEW DATABASE STATE — one round trip over sys.partitions and sys.allocation_units; the login sees the tables it has metadata visibility of.
  • A memory-optimized table's sizes are NULL with a warning (as sp_spaceused prints them) and it sorts last on a size.

db set — ALTER DATABASE SET options

An admin command: --allow admin (capped by the profile's ceiling) and ALTER on the database (Msg 5011 → exit 9). One ALTER DATABASE … SET per --option KEY=VALUE, in the order given:

# preview: prints the statements and a planned_change set, changes nothing
gossms db set AdventureWorks --option COMPATIBILITY_LEVEL=160 -p dev --allow admin --dry-run

gossms db set AdventureWorks --option RECOVERY=FULL -p dev --allow admin
gossms db set AdventureWorks --option QUERY_STORE=ON -p dev --allow admin
gossms db set AdventureWorks --option AUTO_UPDATE_STATISTICS_ASYNC=ON \
                             --option PAGE_VERIFY=CHECKSUM -p dev --allow admin

# an option that needs the database to itself, in the face of live sessions
gossms db set AdventureWorks --option READ_COMMITTED_SNAPSHOT=ON \
  --rollback-immediate --confirm AdventureWorks -p dev --allow admin
Key Values
RECOVERY FULL, SIMPLE, BULK_LOGGED
COMPATIBILITY_LEVEL 100 up to the server's own, steps of 10
READ_ONLY ON, OFF
QUERY_STORE ON (= READ_WRITE), READ_WRITE, READ_ONLY, OFF — the other Query Store options are re-sent, so nothing else changes
PAGE_VERIFY CHECKSUM, TORN_PAGE_DETECTION, NONE
CURSOR_DEFAULT LOCAL, GLOBAL
CONTAINMENT NONE, PARTIAL
21 more switches ON/OFF (an unknown key is exit 2 with the keys in the hint)
  • No transaction (ALTER DATABASE cannot run in one): the first failure stops the run, earlier changes stay applied, the change set shows every key with its after, a warning names those not attempted, exit 5.
  • READ_COMMITTED_SNAPSHOT, CONTAINMENT and READ_ONLY need the database to themselves and wait for its other sessions, bounded by --timeout (default 60, 0 none; exit 6, hint naming --rollback-immediate). --rollback-immediate disconnects those sessions, rolling back their open transactions — it needs --confirm equal to the database's name as given (exit 7 without; --confirm alone is also exit 2).
  • The real run's change set is read back from sys.databases (+ Query Store's view) before and after: option, before, requested, after; an after that is not the request is a warning.
  • USER_ACCESS, OFFLINE, ONLINE are deliberately not offered.

db create / db drop — lifecycle

gossms db create Scratch -p dev --allow admin --dry-run
gossms db create Scratch --recovery simple --compat 150 -p dev --allow admin

gossms db drop Scratch --confirm Scratch -p dev --allow admin --dry-run
gossms db drop Scratch --confirm Scratch --close-connections -p dev --allow admin

db create (admin): CREATE DATABASE with the model's files at the instance's default paths, then the SETs for --collation, --recovery simple|full|bulk-logged, --compat <n> (100 to the server's major × 10, steps of 10). An existing name is exit 2 before anything runs; without the right (Msg 262) exit 9. Not one transaction: if a SET fails after the CREATE, the database stays, the record shows it, a warning says so, exit 5. The record of a real run is the database read back from sys.databases; on --dry-run it is what the database would be, plus tsql.

db drop (admin) deletes the files. Refused before anything is sent: a system or distribution database (exit 7); the connection's own database (exit 2 — connect with -d master); a database in an availability group (7, hint names the group's REMOVE DATABASE); a database with snapshots (7, naming them). --confirm <name> must equal the name exactly as given — even for a dry run — else exit 7 before connecting. The record is the database as it stands before the drop: state, recovery model, size, lastFullBackup (msdb; null with a warning when there is none — look at that before dropping). A database in use without --close-connections is Msg 3702 → exit 7, hint naming it; --close-connections runs SET SINGLE_USER WITH ROLLBACK IMMEDIATE and the DROP in one batch. SQL Server deletes the files of an ONLINE database only — an OFFLINE one leaves them on disk, warned. dropped says sys.databases no longer holds the name.

db scoped-config — database-scoped configurations

gossms db scoped-config list AdventureWorks -p dev
gossms db scoped-config get AdventureWorks MAXDOP -p dev
gossms db scoped-config get AdventureWorks LEGACY_CARDINALITY_ESTIMATION PARAMETER_SNIFFING -p dev -o json
gossms db scoped-config set AdventureWorks MAXDOP 4 -p dev --allow admin --dry-run
gossms db scoped-config set AdventureWorks MAXDOP 2 --for-secondary -p dev --allow admin
gossms db scoped-config set AdventureWorks LEGACY_CARDINALITY_ESTIMATION ON -p dev --allow admin

list and get are reads: name, value, value_for_secondary, is_default, type from sys.database_scoped_configurations, values as text (a switch reads 1/0, a keyword the keyword). value_for_secondary is NULL where secondaries follow the primary; is_default is NULL on 2016. The database must be ONLINE (else exit 8) and the login need a user in it (else exit 9). An unknown setting is exit 8, hint naming up to 3 containing it.

set (admin; needs ALTER ANY DATABASE SCOPED CONFIGURATION, Msg 15247 → exit 9) checks the value against the setting's type before anything runs (exit 2): a switch takes ON/OFF (1/0 are converted — the statement refuses a number there), a number a whole number ≥ 0, a keyword letters, digits and _ (a quoted URL is not supported here). --for-secondary sets the value readable secondaries use (PRIMARY only there, to make them follow the primary again). On a readable secondary the database is read-only: Msg 3906, exit 5, hint sending you to the primary. Dry run and change sets work as for db set; values are spelled as the statement takes them (ON/OFF, PRIMARY).

Recipes

# nightly capacity check — exit non-zero is not part of it; grep the warnings
gossms db files -p prod --all --max-free-pct 10 --sort-by free-pct
gossms db log-usage -p prod --all
gossms server volumes -p prod            # see the Server page
gossms db table-sizes -p prod AdventureWorks --top 10

# a database whose log will not reuse: why, and who holds it
gossms db log-usage -p prod AdventureWorks --unit gb    # log_reuse_wait_desc
gossms monitor sessions -p prod --session-database AdventureWorks

# pre-flight a version upgrade of one database
gossms db info -p dev AdventureWorks -o json | jq '.data.compatibilityLevel'
gossms db set AdventureWorks --option COMPATIBILITY_LEVEL=160 -p dev --allow admin --dry-run

Permissions quick reference

Command Needs beyond the object itself
db list / info Answers from sys.databases in any state; sizes NULL without VIEW ANY DEFINITION
db files volume_free_pct NULL without VIEW SERVER STATE
db space Log columns NULL without VIEW SERVER STATE
db log-usage VIEW SERVER STATE or exit 9
db table-sizes Nothing special (no VIEW DATABASE STATE)
db set --allow admin + ALTER on the database
db create --allow admin + CREATE (ANY) DATABASE
db drop --allow admin + --confirm + CONTROL/ALTER ANY DATABASE
db scoped-config set --allow admin + ALTER ANY DATABASE SCOPED CONFIGURATION

Predict them all before running anything with agent context.

Clone this wiki locally