25 February 2014

Seven SQL Server Under-Used Utilities

There are more than ten useful command-line applications that are either associated with, or are distributed with, SQL Server. Some, like BCP are used often, whereas others like LogDumper, almost never. However, they
all have their uses and several become important as part of script-based automation of tasks. It is definitely worth knowing what is lurking in your tools\binn directory.

Over the past few months, I’ve put out a series of articles about some of the utilities available to SQL Server 2012, including sqlcmd, logparser, tablediff, sqliosim, and sqlio. To wrap up the series, I’m offering up one last article that provides an overview of seven other utilities that might prove useful in your quest for painless SQL Server development and administration. Not surprisingly, each utility could justify its own article, so know that there’s a lot more to these tools than what I’m showing you here. Be sure to check out SQL Server Books Online and other Microsoft documentation for additional information.

The dta utility

The dta utility is essentially a command-line version of the Database Engine Tuning Advisor. From a command prompt, PowerShell, or within a script or application, you get much of the same functionality as found in the GUI tool. In fact, the session names you provide when running dta commands are synced with Tuning Advisor in the target SQL Server instance, as are the session IDs that are automatically generated.

Like Tuning Advisor, the dta utility analyzes a specified workload and recommends ways to modify the physical database design in order to improve performance. Your workload can be a T-SQL script, a plan cache, or a SQL Server Profiler trace file or table.

As you might expect, the utility supports numerous options for how to define your analysis. For example, the following dta command targets the dbtest database on a local named instance of SQL Server 2012 and includes a number of arguments:

First off, note that the dta command, as with all the utilities discussed in this article, is restricted to a single line of input. You do not press Enter until you’ve typed (or pasted) the entire command. In this case, the command wraps across multiple lines because of margin constraints, just as the lines might wrap at a command prompt window.

The -S argument identifies the SQL Server instance (sqlsrv2012), and the -D argument specifies the target database (dbtest). Next comes the -E argument, which indicates that a trusted connection should be used to connect to the server.

Once we get the connectivity information out of the way, we can define the analysis itself. This starts with the -if argument, which identifies the workload file that the analysis is based on. In this case, I’m using a T-SQL script that consists of a SELECT statement with numerous joins.

Next, the -of argument identifies the output file that will hold the results of the analysis, and the -F option specifies that the file should be overwritten if it already exists. After this comes the -A argument, which defines the maximum amount of time available for tuning the workload. Microsoft recommends using a value of 0, which gives the utility unlimited time to perform its analysis.

The -B argument comes next and determines the maximum number of megabytes that the recommended index and partition can consume. Finally, the -s argument provides a name for the session, in this case, session101. A session ID is generated automatically when you run the command.

There are other options, of course, but this example should give you an idea of what you can do. After you run the command, you’ll have a file that contains the recommendations and, in some cases, T-SQL script to carry out those recommendations. However, if the utility determines that the database structure doesn’t need to be modified, it will return a simple message to the command prompt telling you so, without generating an output file.

The osql utility

Yes, the osql utility is going away. It is  hard to say when that will happen, but it’s definitely listed as deprecated since it is now been succeeded by SQLCMD. Still, you might find yourself having to work with scripts or applications that still use osql, so it might be useful to have an overview of how it works, if you’ve never played with it before.

The osql utility lets you run T-SQL statements, stored procedures, and script files against a SQL Server instance. The syntax is straightforward and supports relatively few options. For example, the following osql command runs a T-SQL script against the dbtest database:

As you saw in the previous example, the -S argument identifies the target instance, and the -E argument species that a trusted connection be used. This time around, however, the -d option that identifies the database is lowercase, but the meaning is the same.

Next, the -i argument points to the full path and file name of the tsql_script.sql file, which contains a T-SQL statement that will run against the database when you execute the command. Following the file name is the -u argument, which indicates that the results should be outputted in Unicode, and then the -o option identifies the output file that will contain the results returned by the T-SQL statement.

That’s all there is to running the osql utility. It uses the ODBC API to connect to the SQL Server instance and from there works with the instance interactively. The query results are then returned to the command prompt or, as shown in the example, sent to a file. It’s a great little tool, but as I said, it’s going away. We now have a much more powerful utility, sqlcmd.

Note: Another utility that is rumored to have been deprecated in SQL Server 2012 is sqlmaint, which lets you perform maintenance operations against a SQL Server instance. The error messages I received when trying to run the utility suggest that it relies on SQL Server Distributed Management Objects (SQLDMO), another deprecated feature slated for removal in SQL Server 2014. However, Microsoft documentation is not clear about the future of the utility itself or about how to make the utility work. At this point, you’ll be lucky if you can get it to run at all.

The sqldiag utility

SQL Server includes the sqldiag utility to simplify the process of gathering diagnostic information from logs and data files associated with SQL Server and other systems. For example, you can collect data from SQL Server Profiler traces, Windows event logs, and Windows performance logs. You can also capture SQL Server blocking and configuration information.

The sqldiag utility lets you monitor your system and troubleshoot specific problems. The tool also provides an effective mechanism for gathering diagnostic information that can be passed on to Microsoft Customer Support Services. Keep in mind, however, that the utility relies, at least in part, on Windows Management Instrumentation (WMI) being installed and properly running on the target machine. If it’s not, you can’t use sqldiag.

As with any utility, you create a sqldiag command by specifying the utility name and then the necessary options, as shown in the following example:

The /O argument specifies the output folder where the diagnostic information is redirected. The /G option indicates that the utility should be run in generic mode, which means that the utility lets Windows determine whether you have the necessary rights to access the diagnostic information, as opposed to performing its own checks.

The final argument, /X, tells the utility to simply take a snapshot of the diagnostic information and shut down, rather than running for a specified period of time and gathering ongoing information. As a result, when we run the command, the diagnostic information is saved to the targeted folder for that specific point in time.

By default, the sqldiag utility uses a configuration file (SQLDiag.xml) to control the types of diagnostic information to collect. You can modify that file or create a different one and reference it when you run the utility. One issue to note, however, is that Microsoft documentation states that the utility “may be changed,” but provides few specifics, other than to say that your current scripts and applications could be impacted.

The sqldumper utility

First off, the sqldumper utility is not limited to SQL Server. You can use it to create a dump file for any running Windows application. All you need is the process ID for the app’s execution file and a flag that indicates the type of dump to perform. For example, the following command creates a mini-dump file for a local instance of SQL Server 2012:

The first argument, 1684, is the current process ID of the SQL Server executable that’s driving the instance. I pulled the ID from Task Manager. The second argument, 0x0000, is the dump flag, which in this case indicates that a normal mini-dump file should be generated. You can find a complete list of flags by running the following command:

The dump files you create can be used for Watson error reporting or for specific debugging, such as determining why a SQL Server computer is not responding to user requests. However, the sqldumper utility is not intended for general purpose debugging. For that, you’ll want to turn to Visual Studio or a standalone debugging tool.

When you run the sqldumper utility, it creates the dump file in whatever folder you’re working in at the command prompt. For example, if you’re running the utility from C:\, that’s where the dump file will be created. Some documentation suggests that you can specify a target folder, but nothing in the command syntax indicates that this is possible, and attempts on my part resulted only in error messages. Perhaps with a little more tenacity than I showed, you might be able to make it work. Microsoft documentation is fairly inadequate on the sqldumper utility, so finding answers might not be easy.

The sqllocaldb utility

Here’s a fun utility for you: sqllocaldb. It lets you create, delete, start, stop, and perform a number of other related tasks on an instance of SQL Server 2012 Express LocalDB, an execution mode of SQL Server Express that’s targeted at developers. A LocalDB instance uses a minimal set of files to start the database engine.

The sqllocaldb utility is simple enough to use. For example, the following command creates a LocalDB instance named express12 and then starts that instance:

After specifying the utility name, I added the create argument, followed by the instance name. I then included 11.0 to specify the instance version. The -s argument then starts the instance after it has been created. I could then have used a similar command to stop or delete the instance, which I did.

The sqllogship utility

Not surprisingly, the sqllogship utility is specific to log shipping, which must be enabled and working in order to use the tool. The utility lets you perform copy, restore, and backup operations for an existing log shipping configuration.

For example, I set up log shipping on a test database (the primary database) on a local instance of SQL Server 2008 R2. I then specified a local instance of SQL Server 2012 for my secondary database. After configuring log shipping on the primary database, I retrieved the database’s primary ID by running the sp_help_log_shipping_primary_database system stored procedure. Finally, I ran the following sqllogship command to perform a backup:

Notice that I use the -server argument to specify the SQL Server instance (sqlsrv2008r2), which contains the primary database. I next used the -backup argument and specified the database’s primary ID. When I ran the command, the utility backed up the database to the folder identified in my log shipping configuration.

Microsoft recommends that, when possible, you schedule SQL Server Agent jobs to perform copy, restore, and backup operations, rather than relying on the sqllogship utility. The reason, it seems, is that the log shipping history created by sqllogship is interspersed with job histories, implying perhaps that the utility is best used for the occasional one-offer, unless you plan to replace the corresponding jobs altogether.

The sqlservr application

It might seem odd to include sqlservr here, it being more of an application than your typical command-line utility. But you might find it useful when you need to start, stop, pause, or continue a SQL Server instance from a command prompt when you’re troubleshooting an instance or performing maintenance.

When running a sqlservr command, you must do so from the appropriate Binn folder for that instance, which can be found in a path structure similar to the following:

Once you’re in that folder, you can run the sqlservr application for that instance. For example, the following command first changes the command prompt to the Binn folder for a local instance of SQL Server 2008 R2:

Next comes the sqlservr command that starts the instance:

Notice I include only the -s argument, which specifies the instance name. This is all that’s needed to launch the instance. Once started, the application will continue to run until you explicitly stop it. One way to do that is to press Control+C, which will prompt you to confirm that you do indeed want to stop the application.

Starting the application in this way means that it doesn’t run as a service, so you cannot stop or control it by using net commands. However a number of options are available to the sqlservr application when starting it at a command prompt (in addition to the -s option shown above). For example, you can start the instance in single-user mode or with minimal configuration.

Plenty more where those came from

That should sum up these seven utilities, for the most part. Of course, SQL Server includes many more tools than what we’ve covered here. You’ll find utilities for working with Integration Services, Reporting Services and Analysis Services. Then there’s the ubiquitous bcp utility, which I covered in a separate article light years ago.

It can be a little tricky figuring out exactly how to use some of these utilities-Microsoft documentation being what it is at times-but if you play around with them a bit, usually you can put together the pieces necessary to make everything work the way you want it. You’ll then have the ability to perform a variety of tasks at the command prompt or to embed commands into your scripts and applications to automate these tasks. Regardless of how you use the utilities, you’ll find an extensive arsenal of tools at your disposal. The trick, for the most part, is knowing what’s out there.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 23129 times – thanks for reading.

Tags: , , , ,

  • Rate
    [Total: 26    Average: 4.3/5]
  • Share

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novels 'Last Stand' and 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

View all articles by Robert Sheldon

  • Anonymous

    For whatever reason, the way you write – and the topics you cover…
    …really work for me.

    Thanks for all the hard work that must go into making your articles read so easily.


  • Michael

    Great information
    Thanks for sharing this information with us..Very useful

  • Ian Stirk

    Very nice article.

    Always a pleasure to read your thoughts Robert.


  • Anonymous

    worth of knowing these ………..
    It’s good to recollect all these utilities. Many Thanks Robert for bringing it us.