Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

SQL Server scripts to use with sqlcmd


By:   |   Last Updated: 2018-09-14   |   Comments   |   Related Tips: More > Database Administration

Problem

Administering SQL Server comes in many forms and there are many tools that can be used to manage and administer SQL Server instances.  One tool that all DBAs should be familiar with is sqlcmd, the command line tool that allows you to run queries without the need of a graphical interface.  With the release of SQL Server on Linux, the use of command line tools becomes even greater.  In this tip we look at some things you can do to help administer SQL Server using sqlcmd and some key scripts that you can add to your toolbox.

Solution

In my career I have worked with many database engines other than SQL Server, like Oracle and MySQL that run on Linux since the release of these database systems. That forced me to work in situations where I was unable to use a GUI. Now that SQL Server has versions that run on Linux, we will see more situations when DBAs don’t have access to a graphical interface.

SQL Server has included command line tools to run queries since its beginning. First there was the deprecated isql.exe, osql.exe and now we have sqlcmd. But most of us are not used to operating at the console level. As a consequence most of us overlook sqlcmd, even though we can use SQL Srever Management Studio (SSMS) to run in sqlcmd mode. If you are one of those that didn’t know about sqlcmd mode in SSMS, I suggest you read SQL Server Management Studio SQLCMD mode option.

Why we must have SQL Server Code Templates

Among the benefits of using code templates is the obvious benefit of code reusability. But there is also a time benefit of using scripts, because we don't have to think and write a new query from scratch. This is very important if you are working in a production environment, when problems arise you need solve them as soon as possible.

SQL Server Code Templates

I don’t know a Database Administrator or even an IT professional that does not have his/her own script collection. Some DBAs use a folder with script files and others like me take advantage of SQL Server Management Studio Template Browser Window. By doing so, I have my scripts when I need them and don’t need to drag and drop a script file into SSMS. But when you don’t have a graphic utility you should rely on a folder schema to hold your code templates. 

When thinking about code templates on Linux we have two different approaches that differ just a bit by formality: its invocation. Please allow me to explain. Both approaches rely on the sqlcmd tool; the difference is that we can invoke sqlcmd directly or by a bash script. By using a bash script you will need to pass the connection data (server name, login and password) on the script invocation; otherwise, you can opt to use a .sql script and then you should pass the connection data directly to sqlcmd. For simplicity, I will only consider the case that we are only using sqlcmd and not a bash script.

In order to use sqlcmd to run our script templates, we must specify the server on which we want to run it and the user and password if you are using SQL authentication. Then we have to pass the script as an argument and I suggest you use the –W switch to remove trailing spaces and of course, include the script to be executed with the –i switch. Additionally, when the script template needs to be executed on a specific database you should add the –d switch followed by the database name.

Take a look at this example.

sqlcmd -S ORIOM -E -i HighVLFs.sql -d AdventureWorks2012 -W

Let’s break down the command:

Parameter Description
-S ORIOM Tells sqlcmd to connect to server ORIOM.
-E Uses Windows authentication for logging into the instance.
-i HighVLFs.sql Loads and executes the script file HighVLFs.sql.
-d AdventureWorks2012 The database where to run the script.
--W To remove trailing spaces from the column output.

As an alternative, you can use the –v switch to specify the database on which you want to execute the code within the script. That switch is used for sending parameters to the script. In case you decide to use the –v switch you must modify your script so it uses the sqlcmd format for variable replacement. Sqlcmd parses the scripts after executing and searches for expressions like “$(something)” where “something” is the name of a variable that will be replaced with the value provided by using the –v switch.

Take a look at the next example:

USE $(database)
GO
SELECT  name ,
        physical_name ,
        CAST(size / 128. AS NUMERIC(10, 2)) AS [size MB] ,
        state_desc
FROM    sys.database_files

The previous script returns the name, location, size and status of the files in a specific database. The database is passed as an argument with the –v switch as follows:

sqlcmd -S ORIOM -E -W -i dbFiles.sql –v database = AdventureWorks2012

Installing the SQL Server Code Templates on Linux Servers

To use the scripts on multiple servers, I suggest compressing the files and then copying the compressed file as shown below.

We are all familiar with using Windows and compressing files into a compressed file and copy files around the network.  Below I will cover how this can be done when using Linux.

Compressing Files on Linux

Assuming your script collection is in a folder, the best way to move it across servers is by compressing the scripts in a compressed file. In this case I named the compressed file Templates.tar.xz. If you are working on Windows you can use any compression program like Winzip or Winrar.

If you are using Linux you can use the tar console command as follows:

tar -cJf <archive.tar.xz> <files>

Let’s break down the command:

Parameter Description
-c Creates a new archive
-J Uses oz. tool for compression.
-f Tells tar to use an archive for output instead of a tape.
archive.tar.xz The database on which the script will run.
files List of files to be compressed.

In the case the previous command, assuming you will execute it on the same directory that contains your templates will be like the command below:

tar -cJf Templates.tar.xz *

For those of you who are new to Linux, tar is a utility for collecting many files into one archive file. The name is derived from Tape Archive because its origins was mostly used with magnetic tapes for backup.

Copy Compressed File to Another Linux Server

Now we must copy the file with our templates to other servers. For this purpose I will use PSCP, the PuTTY Secure Copy client which is a tool for transferring files securely between computers using an SSH connection. You can download this tool and the entire PuTTY suite. The syntax of PSCP to transfer files to another computer is as follows:

pscp <File> <UserName>@<Host or IP Address>:<Destination Folder>

Take a look at the next example:

pscp C:\Users\Administrator\Documents\Templates.tar.xz [email protected]:/home/daniel/Documents/

In this case we are copying file “C:\Users\Administrator\Documents\Templates.tar.xz” to folder “/home/daniel/Documents/” on the remote server “10.10.10.60” by using the credentials of local user “Daniel”.

Copying the templates to the target server.

Decompress Linux Files

After copying the file to the target server, we must decompress its contents with the following command, but first I will create a new folder named Templates to hold the files.

mkdir Templates
tar –xf Templates.tar.xz –C ./Templates

Let’s break down the command:

Parameter Description
-x Extracts files from the source which in this case is a file.
-f Tells tar to use an archive instead of a tape.
-C Sets destination folder.


Uncompressing the templates.

Valuable SQL Server Scripts for your sqlcmd Templates

In order to help you to build your own script templates, I collected some queries on Dynamic Management Views.

Top 5 SQL Server Queries by CPU

Save the following code in a file named Top5CPU.sql.

SELECT TOP 5 query_stats.query_hash AS "Query Hash",  
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",  
    MIN(query_stats.statement_text) AS "Statement Text"  
FROM   
    (SELECT QS.*,   
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(ST.text)  
        ELSE QS.statement_end_offset END   
            - QS.statement_start_offset)/2) + 1) AS statement_text  
     FROM sys.dm_exec_query_stats AS QS  
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats  
GROUP BY query_stats.query_hash  
ORDER BY 2 DESC;  

Top 5 SQL Server triggers identified by average elapsed time

Save the following code in a file named Top5TriggersByCPU.sql.

SELECT TOP 5 d.object_id, d.database_id, DB_NAME(database_id) AS 'database_name',   
    OBJECT_NAME(object_id, database_id) AS 'trigger_name', d.cached_time,  
    d.last_execution_time, d.total_elapsed_time,   
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],   
    d.last_elapsed_time, d.execution_count  
FROM sys.dm_exec_trigger_stats AS d  
ORDER BY [total_worker_time] DESC;  

SQL Server row count aggregate information

The following will show total rows, minimum rows, maximum rows and last rows for queries.

Save the following code in a file named RowCountAggByQuery.sql.

SELECT  qs.execution_count ,
        SUBSTRING(qt.text, qs.statement_start_offset / 2 + 1,
                  ( CASE WHEN qs.statement_end_offset = -1
                         THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                         ELSE qs.statement_end_offset
                    END - qs.statement_start_offset ) / 2) AS query_text ,
        qt.dbid ,
        dbname = DB_NAME(qt.dbid) ,
        qt.objectid ,
        qs.total_rows ,
        qs.last_rows ,
        qs.min_rows ,
        qs.max_rows
FROM    sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE   qt.text LIKE '%SELECT%'
ORDER BY qs.execution_count DESC;  

Linking SQL Server session information to operating system threads

Save the following code in a file named SessionThread.sql.

SELECT STasks.session_id, SThreads.os_thread_id  
FROM   sys.dm_os_tasks AS STasks  
       INNER JOIN sys.dm_os_threads AS SThreads ON STasks.worker_address = SThreads.worker_address  
WHERE  STasks.session_id IS NOT NULL  
ORDER BY STasks.session_id;    

Last SQL Server Log Backup Time by Database

Save the following code in a file named LastLogBckupTime.sql.

SELECT name AS 'Database Name' ,
       log_backup_time AS 'last log backup time'
FROM   sys.databases AS s
       CROSS APPLY sys.dm_db_log_stats(s.database_id);

SQL Server Databases with more than 100 VLFs in log files

Save the following code in a file named LastLogBckupTime.sql.

SELECT  [name] ,
        COUNT(l.database_id) AS 'vlf_count'
FROM    sys.databases s
        CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name]
HAVING  COUNT(l.database_id) > 100

SQL Server Lock Information by Database ID

Save the following code in a file named lock_info_by_dbid.sql.

SELECT resource_type, resource_associated_entity_id,  
    request_status, request_mode,request_session_id,  
    resource_description ,
    DB_NAME(resource_database_id) [Database]  
    FROM sys.dm_tran_locks      

SQL Server cursors opened longer than 36 hours

Save the following code in a file named OldCursors.sql.

SELECT  creation_time ,
        cursor_id ,
        name ,
        c.session_id ,
        login_name
FROM    sys.dm_exec_cursors (0) AS c
        JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE   DATEDIFF(hh, c.creation_time, GETDATE()) > 36;  
GO

Percentage of failed SQL Server background jobs

Save the following code in a file named FailedBGJobs.sql.

SELECT  CASE ended_count 
           WHEN 0  THEN 'No jobs ended'   
           ELSE CAST((failed_lock_count + failed_giveup_count + failed_other_count) / CAST(ended_count AS float) * 100 AS varchar(20))   
        END AS [Percent Failed]  
FROM sys.dm_exec_background_job_queue_stats;  
GO  

SQL Server Asynchronous jobs in the background queue

Number of active asynchronous jobs in the background queue for each database in the instance of SQL Server.

Save the following code in a file named AsyncJobsInBGQueue.sql.

SELECT  DB_NAME(database_id) AS [Database] ,
        COUNT(*) AS [Active Async Jobs]
FROM    sys.dm_exec_background_job_queue
WHERE   in_progress = 1
GROUP BY database_id;  

Percentage of SQL Server failed job enqueue attempts

Save the following code in a file named FailedEnqueuedAttempts.sql.

SELECT CASE enqueued_count 
          WHEN 0 THEN 'No jobs posted'   
          ELSE CAST((enqueue_failed_full_count + enqueue_failed_duplicate_count) / CAST(enqueued_count AS float) * 100 AS varchar(20))   
        END AS [Percent Enqueue Failed]  
FROM sys.dm_exec_background_job_queue_stats;  
GO 

SQL Server Blocking Sessions

Save the following code in a file named blocking_sessions.sql.

SELECT  A.Session_id, A.blocking_session_id,
        DB_NAME(B.database_id) [Database] ,
        C.text
FROM    sys.dm_exec_requests A
        LEFT JOIN sys.dm_exec_requests B ON A.blocking_session_id = B.session_id
        OUTER APPLY sys.dm_exec_sql_text(B.sql_handle) C
WHERE   A.blocking_session_id <> 0
GO

SQL Server Wait Types

Save the following code in a file named Wait_Types.sql.

SELECT wait_type, wait_time_ms  
FROM sys.dm_os_wait_stats;  

SQL Server Pending IO Requests

Save the following code in a file named dm_io_pending_io_requests.sql.

SELECT * 
FROM sys.dm_io_pending_io_requests

SQL Server Space Consumed in TempDB

This query can be used to determine space consumed in tempdb, by version store of each database in a SQL Server instance.

Save the following code in a file named tempdb_space_by_versionstore.sql.

SELECT DB_NAME(database_id) as 'Database Name',
       reserved_page_count,
       reserved_space_kb 
FROM   sys.dm_tran_version_store_space_usage;  

SQL Server Last Active VLF Position

Save the following code in a file named dLastActiveVLFPos.sql. Notice the “d” in the file name. This is because the script must be executed in the context of the database you want to know the last active VLF position.

;
WITH cte_vlf
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY vlf_begin_offset ) AS vlfid ,
                        DB_NAME(database_id) AS [Database Name] ,
                        vlf_sequence_number ,
                        vlf_active ,
                        vlf_begin_offset ,
                        vlf_size_mb
               FROM     sys.dm_db_log_info(DEFAULT)
             ),
        cte_vlf_cnt
          AS ( SELECT   [Database Name] ,
                        COUNT(vlf_sequence_number) AS vlf_count ,
                        ( SELECT    COUNT(vlf_sequence_number)
                          FROM      cte_vlf
                          WHERE     vlf_active = 0
                        ) AS vlf_count_inactive ,
                        ( SELECT    COUNT(vlf_sequence_number)
                          FROM      cte_vlf
                          WHERE     vlf_active = 1
                        ) AS vlf_count_active ,
                        ( SELECT    MIN(vlfid)
                          FROM      cte_vlf
                          WHERE     vlf_active = 1
                        ) AS ordinal_min_vlf_active ,
                        ( SELECT    MIN(vlf_sequence_number)
                          FROM      cte_vlf
                          WHERE     vlf_active = 1
                        ) AS min_vlf_active ,
                        ( SELECT    MAX(vlfid)
                          FROM      cte_vlf
                          WHERE     vlf_active = 1
                        ) AS ordinal_max_vlf_active ,
                        ( SELECT    MAX(vlf_sequence_number)
                          FROM      cte_vlf
                          WHERE     vlf_active = 1
                        ) AS max_vlf_active
               FROM     cte_vlf
               GROUP BY [Database Name]
             )
    SELECT  [Database Name] ,
            vlf_count ,
            min_vlf_active ,
            ordinal_min_vlf_active ,
            max_vlf_active ,
            ordinal_max_vlf_active ,
            ( ( ordinal_min_vlf_active - 1 ) * 100.00 / vlf_count ) AS free_log_pct_before_active_log ,
            ( ( ordinal_max_vlf_active - ( ordinal_min_vlf_active - 1 ) )
              * 100.00 / vlf_count ) AS active_log_pct ,
            ( ( vlf_count - ordinal_max_vlf_active ) * 100.00 / vlf_count ) AS free_log_pct_after_active_log
    FROM    cte_vlf_cnt
GO

Last SQL Server Log Backup Time

Save the following code in a file named LastLogBckupTime.sql.

SELECT  name AS 'Database Name' ,       
        log_backup_time AS 'last log backup time'
FROM    sys.databases AS s
        CROSS APPLY sys.dm_db_log_stats(s.database_id);

Viewing the number of SQL Server R scripts running on server

Save the following code in a file named RScriptsRunning.sql.

SELECT  counter_name ,
        counter_value
FROM    sys.dm_external_script_execution_stats
WHERE   language = 'R';
Next Steps


Last Updated: 2018-09-14


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools