Skip to content

CLI Server

radix29 edited this page Oct 31, 2026 · 1 revision

← goSSMS Home · ← CLI

CLI — Server

The server group: the instance's facts, its memory and disks, its error log, its sp_configure settings and its trace flags. Reads are plain connects; the four writers (config set/reset, traceflag on/off) are admin commands and follow the safety model — gate before connecting, --dry-run prints the exact batch, the real run answers a change set read back from the server.

server info — the instance's facts

gossms server info -p dev
gossms server info -p dev -o json

A record: serverName, productVersion, majorVersion, edition, productLevel, engineEdition (which is what says Azure MI vs. Linux vs. Windows), platform (null when @@VERSION names none), collation, isClustered, isHadrEnabled, startTime (the server's local wall clock, no zone), uptimeSeconds, logicalCpus, numaNodes, physicalMemoryMb, defaultDataPath, defaultLogPath, defaultBackupPath.

startTime through physicalMemoryMb need VIEW SERVER STATE (2022+: VIEW SERVER PERFORMANCE STATE): without it they are null with one warning, exit 0 — the rest of the record stands.

server memory — pressure at a glance

gossms server memory -p dev
gossms server memory -p dev -o json

Two sets:

  • memory (one row): physical_mb, available_os_mb, max_server_memory_mb, min_server_memory_mb (running values), committed_mb, target_mb, page_life_expectancy_s, memory_grants_pending.
  • clerks: the 10 memory clerk types holding the most, largest first.

Warnings, facts only: max server memory at its default (unlimited); available OS memory under 5 % of physical; memory grants pending. Needs VIEW SERVER STATE, exit 9 without.

server volumes — the disks under the files

gossms server volumes -p dev
gossms server volumes -p dev --unit gb -o json

One row per volume holding at least one database file of any database, in any state: volume (mount point, else OS label, else the lowest file path on it — some container hosts report neither), total_<u>, free_<u>, free_pct, db_files. Implausible figures (total 0, free over total, as some Azure MI reports) are left out with a warning. Warnings: a volume under 10 % free. Needs VIEW SERVER STATE, exit 9 without.

server log — the error log, filtered on the server

gossms server log --lines 50 -p prod-ro
gossms server log --search Login --search failed --since 2026-10-27 -p prod-ro
gossms server log --agent --archive 1 --lines 0 -p dev -o json

One row per entry, oldest first: date (the server's local time, no zone — the log records none), process (with --agent: level — 1 error, 2 warning, 3 information), text. Read by xp_readerrorlog, so the filters run on the server, not in the client:

Option
--lines <n> Keep the last n matching entries (default 100, 0 all)
--search <text> Entries whose text holds the string, case-insensitive; up to twice, both must match (AND)
--since <time> YYYY-MM-DD[THH:MM[:SS]] in the server's local time; a Z or an offset is exit 2 — the log has no zone to convert from
--archive <n> Which file: 0 the current one, 1 the one before… one file per run: a --since older than the file's start needs a higher --archive; a missing archive is exit 8 with the valid range in the hint
--agent The SQL Server Agent log instead

A sysadmin reads through xp_readerrorlog; any other login falls back to sp_readerrorlog (needs securityadmin, VIEW SERVER STATE, or 2022's VIEW ANY ERROR LOG — else exit 9), which reads the whole file before --since applies.

A login-flood triage: gossms server log --search Login --search failed --lines 0 -p prod -o jsonl streams every failed attempt in the current log; pipe the text fields to jq/sort | uniq -c to find the source.

server config — sp_configure

config list

gossms server config list -p dev --non-default
gossms server config list -p dev --name backup -o json

One row per sys.configurations option, advanced ones included whatever show advanced options says: name, alias (the short name get takes), configured (value), running (value_in_use), default, min, max, dynamic, advanced.

default is the engine's own default on the server's major version (gosmo's table, built from Microsoft's sp_configure reference and checked against live 2016/2017/2025 instances) — not what Setup or mssql-conf wrote, and not what the instance was installed with. NULL where gosmo has no entry (an option a CU added, Azure), one warning naming them; such options are left out of --non-default, and reset refuses them (exit 11: use set). --name contains (case-insensitive, no wildcards); --non-default compares the configured value to the default (min server memory configured 0 runs as 16 and is not listed).

config get

gossms server config get maxdop -p dev
gossms server config get max-memory min-memory -p dev -o json
gossms server config get "cost threshold for parallelism" -p dev

The same rows in the order given, by alias or full name, alias tried first; unknown is exit 8 naming up to 3 names/aliases containing it. A full name with spaces is one argument, so the shell needs quotes. Beside the set, a context record: logicalCpus, numaNodes, logicalCpusPerNumaNode for MAXDOP and cost threshold; physicalMemoryMb, committedMemoryMb for max/min server memory — the figures each setting is judged by. (Null with one warning without VIEW SERVER STATE, exit 0.)

Alias Setting Alias Setting
maxdop max degree of parallelism backup-checksum backup checksum default
cost-threshold cost threshold for parallelism remote-dac remote admin connections
max-memory max server memory (MB) clr clr enabled
min-memory min server memory (MB) xp-cmdshell xp_cmdshell
adhoc optimize for ad hoc workloads contained-auth contained database authentication
backup-compression backup compression default blocked-threshold blocked process threshold (s)

config set — one batch, read back

An admin command: refused before connecting without --allow admin, --dry-run included. Takes <name> <value> pairs:

gossms server config set maxdop 8 -p dev --allow admin --dry-run
gossms server config set max-memory 56GB min-memory 8GB -p dev --allow admin
  • Values: a whole number in the option's unit. An option ending (MB) also takes MB/GB/TB, one ending (KB) KB/MB — case-insensitive, whole after conversion (1.5GB = 1536; 1.3MB is exit 2). A negative value follows -- (-1 would read as an option). Exit 2 before anything runs: odd argument count, a setting named twice, a unit on the wrong option, a value outside min–max (the option's own default aside — a self-configuring option's 0 lies below its reported minimum).
  • Blocks (exit 7 unless --accept-risk): max or min server memory above physical memory; max below min (values set in the same call count); MAXDOP above the logical CPUs; and any of these when the figure cannot be read — it fails closed. A value equal to the default is never blocked, so reset max-memory goes through.
  • One batch: show advanced options turned on only when needed and put back after, each sp_configure, one RECONFIGURE (WITH OVERRIDE under --override, for values the plain form refuses — e.g. recovery interval (min) above 60). A refused RECONFIGURE leaves every change configured but not running, pending for anyone's next RECONFIGURE; the change set shows it and the hint says so.
  • The change set is read back from sys.configurations: name, before, requested, after_configured, after_running, restart_required (running ≠ configured). Warnings: a new max server memory below committed memory; a value waiting for a restart.

config reset

gossms server config reset maxdop cost-threshold -p dev --allow admin --dry-run

config set to each setting's engine default. A setting with no known default is UNSUPPORTED (exit 11): use set. Same gate, same blocks, same change set.

server traceflag — DBCC TRACEON/OFF, global

gossms server traceflag list -p dev

gossms server traceflag on 7745 -p dev --allow admin --dry-run
gossms server traceflag on 1222 3226 -p dev --allow admin
gossms server traceflag off 7745 -p dev --allow admin
  • list shows every flag on now (DBCC TRACESTATUS(-1), which any login may run): flag, global, session. A startup flag (-T) shows as global — the DBCC does not tell them apart; session is this command's own session, so 0 for another session's flag. None on is an empty set.
  • on/off are admin, always global (TRACEON(flag, -1)): a session flag would die with the command's own connection. Needs sysadmin (Msg 2571 → exit 9). Flags apply in order; the first the server refuses stops the run (exit 5) with the change set still read back.
  • A flag is lost at the next service restart; a lasting one is a startup parameter (-T, or mssql-conf traceflag on Linux) — warned every time you turn one on. A startup-only flag (834) is skipped by the server with no error; the second read not showing it is a warning.
  • A flag that is not a positive whole number is exit 2 before connecting; the upper bound is the server's (Msg 2560, exit 5).

Recipes

# "the server is slow, is it memory?"
gossms server memory -p prod -o json | jq '.resultSets[0].rows[0] |
  {committed_mb, target_mb, page_life_expectancy_s, memory_grants_pending}'

# what did Setup really leave non-default, and by how much
gossms server config list -p prod --non-default -o json

# raise MAXDOP with the paperwork: preview, show the human, then run
gossms server config set maxdop 4 -p prod --allow admin --dry-run
gossms server config set maxdop 4 -p prod --allow admin
gossms server config get maxdop -p prod          # restart_required in the change set

# deadlocks in the error log, and the trace flag for the deadlock graph
gossms server log --search deadlock -p prod --lines 0
gossms server traceflag on 1222 -p prod --allow admin

# disk space under the files, in GB
gossms server volumes -p prod --unit gb -o json

Permissions quick reference

Command Needs
server info VIEW SERVER STATE for uptime/CPU/RAM (else null + warning, exit 0)
server memory, server volumes VIEW SERVER STATE or exit 9
server log sysadmin (xp_readerrorlog), or securityadmin / VIEW SERVER STATE / VIEW ANY ERROR LOG (sp_readerrorlog)
server config list/get nothing special; context figures need VIEW SERVER STATE
server config set/reset --allow admin + ALTER SETTINGS (Msg 15247 → exit 9)
server traceflag on/off --allow admin + sysadmin
server traceflag list any login

gossms agent context -p prod lists the ones the login is known to lack, with the commands each breaks.

Clone this wiki locally