SQL Server - SQLCMD's Exit and Clearances

THEY have created a beautiful article for sqlcmd.
THEY have provided easy steps to follow.
THEY have made DBA's life easy for those who prefer to use DOS or those who comes from any other OS.

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

SQLCMD

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files through a variety of available modes:

[:] QUIT
Causes sqlcmd to exit.

[:] EXIT[ (statement) ]
Lets you use the result of a SELECT statement as the return value from sqlcmd. If numeric, the first column of the last result row is converted to a 4-byte integer (long). MS-DOS, Linux, and Mac pass the low byte to the parent process or operating system error level. Windows 200x passes the whole 4-byte integer. The syntax is:

:EXIT(query)

For example:

:EXIT(SELECT @@ROWCOUNT)

You can also include the EXIT parameter as part of a batch file. For example, at the command prompt, type:

sqlcmd -Q "EXIT(SELECT COUNT(*) FROM '%1')"

The sqlcmd utility sends everything between the parentheses () to the server. If a system stored procedure selects a set and returns a value, only the selection is returned. The EXIT**()** statement with nothing between the parentheses executes everything before it in the batch and then exits without a return value.

When an incorrect query is specified, sqlcmd will exit without a return value.

Here is a list of EXIT formats:

:EXIT
Does not execute the batch, and then quits immediately and returns no value.

:EXIT( )
Executes the batch, and then quits and returns no value.

:EXIT(query)
Executes the batch that includes the query, and then quits after it returns the results of the query.

If RAISERROR is used within a sqlcmd script and a state of 127 is raised, sqlcmd will quit and return the message ID back to the client. For example:

Related Article
https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver16


-----------------------------------------------CLEARANCE------------------------------------------------


In Addition to this, Please also read

SQL Server timely Clearance/s

1. Need to clear Waittype Stats timely to get fresh recent stats.

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)

2. Need to Cycle Error Logs timely.

EXEC sp_cycle_errorlog
EXEC msdb.dbo.sp_cycle_agent_errorlog

3. Though, BACKUP LOG <db_name> WITH truncate_only command, used for clearing the log file, is deprecated now.
We can still clear the log files by taking log backup
- DBCC sqlperf(logspace)
- Backup the logs and
- resize through DBCC shrinkfile

4. Clear TempDB

CREATE the necessary space by dropping objects in the filegroup, adding additional files to the filegroup,or setting autogrowth on for existing files in the filegroup.

5. To clear SQL Server's cache, run DBCC DROPCLEANBUFFERS, which clears all data from the cache.
Then run DBCC FREEPROCCACHE, which clears the stored procedure cache.
DBCC FREESYSTEMCACHE ('userdatabase')
DBCC FREESESSIONCACHE [ WITH NO_INFOMSGS ]

Beware, this is similar to restart of SQL services.


All the Best


Amit Vaid
amitvaid81@gmail.com