Click here to monitor SSC
  • Av rating:
  • Total votes: 25
  • Total comments: 4
Robert Sheldon

Seven SQL Server Under-Used Utilities

25 February 2014

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:

dta -S localhost\sqlsrv2012 -D dbtest -E -if c:\datafiles\utilities\tsql_script.sql -of c:\datafiles\utilities\dta_out.sql -F -A 0 -B 300 -s session101

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:

osql -S localhost\sqlsrv2012 -d dbtest -E -i c:\datafiles\utilities\tsql_script.sql -u -o c:\datafiles\utilities\osql_out.txt

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:

sqldiag /O c:\datafiles\utilities /G /X

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:

sqldumper 1684 0x0000

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:

sqldumper /?

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:

sqllocaldb create express12 11.0 -s

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:

sqllogship -server localhost\sqlsrv2008r2 -backup 1810E0B3-3A0C-4F22-8F3B-1FE187D257DD

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:

install_drive\Program Files\Microsoft SQL Server\MSSQL11[.instance_name]\MSSQL\Binn

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:

cd c:\program files\microsoft sql server\mssql10_50.sqlsrv2008r2\mssql\binn\

Next comes the sqlservr command that starts the instance:

sqlservr -s sqlsrv2008R2

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.

Robert Sheldon

Author profile:

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 novel 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

Search for other articles by Robert Sheldon

Rate this article:   Avg rating: from a total of 25 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: For whatever reason, the way you write - and the topics you cover...
Posted by: Anonymous (not signed in)
Posted on: Monday, March 03, 2014 at 1:11 AM
Message: ...really work for me.

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

Kurt

Subject: Great information
Posted by: Michael (not signed in)
Posted on: Monday, March 03, 2014 at 9:33 AM
Message: Thanks for sharing this information with us..Very useful

Subject: Very nice article.
Posted by: Ian Stirk (not signed in)
Posted on: Monday, March 03, 2014 at 10:19 AM
Message:
Always a pleasure to read your thoughts Robert.

Ian

Subject: worth of knowing these ...........
Posted by: Anonymous (not signed in)
Posted on: Wednesday, March 12, 2014 at 11:36 PM
Message: It's good to recollect all these utilities. Many Thanks Robert for bringing it us.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.