{"id":1767,"date":"2014-02-25T00:00:00","date_gmt":"2014-02-25T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/seven-sql-server-under-used-utilities\/"},"modified":"2021-08-24T13:39:47","modified_gmt":"2021-08-24T13:39:47","slug":"seven-sql-server-under-used-utilities","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/seven-sql-server-under-used-utilities\/","title":{"rendered":"Seven SQL Server Under-Used Utilities"},"content":{"rendered":"
\n

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<\/code><\/a>, logparser<\/code><\/a>, tablediff<\/code><\/a>, sqliosim<\/code><\/a>, and sqlio<\/code><\/a>. 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.<\/p>\n

The dta utility<\/h1>\n

The dta<\/code> utility is essentially a command-line version of the Database Engine Tuning Advisor. From a command prompt, \t\tPowerShell, 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<\/code> commands are synced with Tuning Advisor in the target SQL Server instance, as are the session IDs that are automatically generated.<\/p>\n

Like Tuning Advisor, the dta<\/code> 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.<\/p>\n

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

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<\/pre>\n
First off, note that the dta<\/code> command, as with all the utilities discussed in this article, is restricted to a single line of input. You do not press Enter<\/code> 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.<\/p>\n
The -S<\/code> argument identifies the SQL Server instance (sqlsrv2012<\/code>), and the -D<\/code> argument specifies the target database (dbtest<\/code>). Next comes the -E<\/code> argument, which indicates that a trusted connection should be used to connect to the server. <\/p>\n
Once we get the connectivity information out of the way, we can define the analysis itself. This starts with the -if<\/code> 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<\/code> statement with numerous joins. <\/p>\n
Next, the -of<\/code> argument identifies the output file that will hold the results of the analysis, and the -F<\/code> option specifies that the file should be overwritten if it already exists. After this comes the -A<\/code> argument, which defines the maximum amount of time available for tuning the workload. Microsoft recommends using a value of 0<\/code>, which gives the utility unlimited time to perform its analysis.<\/p>\n
The -B<\/code> argument comes next and determines the maximum number of megabytes that the recommended index and partition can consume. Finally, the -s<\/code> argument provides a name for the session, in this case, session101<\/code>. A session ID is generated automatically when you run the command.<\/p>\n
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.<\/p>\n
The osql utility<\/h1>\n
Yes, the osql<\/code> utility is going away.  \t\tIt is  hard to say when that will happen, but it’s definitely listed as deprecated since it is now been  \t\tsucceeded by SQLCMD. Still, you might find yourself having to work with scripts or applications that still use osql<\/code>, so it might be useful to have an overview of how it works, if you’ve never played with it before.<\/p>\n
The osql<\/code> 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<\/code> command runs a T-SQL script against the dbtest<\/code> database:<\/p>\n
osql -S localhost\\sqlsrv2012 -d dbtest -E -i c:\\datafiles\\utilities\\tsql_script.sql -u -o c:\\datafiles\\utilities\\osql_out.txt<\/pre>\n
As you saw in the previous example, the -S<\/code> argument identifies the target instance, and the -E<\/code> argument species that a trusted connection be used. This time around, however, the -d<\/code> option that identifies the database is lowercase, but the meaning is the same. <\/p>\n
Next, the -i<\/code> argument points to the full path and file name of the tsql_script.sql<\/code> 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<\/code> argument, which indicates that the results should be outputted in Unicode, and then the -o<\/code> option identifies the output file that will contain the results returned by the T-SQL statement.<\/p>\n
That’s all there is to running the osql<\/code> 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<\/code>.<\/p>\n
\n
Note:<\/code> Another utility that is rumored to have been deprecated in SQL Server 2012 is sqlmaint<\/code>, 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.<\/p>\n<\/div>\n
The sqldiag utility<\/h1>\n
SQL Server includes the sqldiag<\/code> 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. <\/p>\n
The sqldiag<\/code> 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<\/code>.<\/p>\n
As with any utility, you create a sqldiag<\/code> command by specifying the utility name and then the necessary options, as shown in the following example:<\/p>\n
sqldiag \/O c:\\datafiles\\utilities \/G \/X<\/pre>\n
The \/O<\/code> argument specifies the output folder where the diagnostic information is redirected. The \/G<\/code> 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.<\/p>\n
The final argument, \/X<\/code>, 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.<\/p>\n
By default, the sqldiag<\/code> utility uses a configuration file (SQLDiag<\/code>.xml<\/code>) 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.<\/p>\n
The sqldumper utility<\/h1>\n
First off, the sqldumper<\/code> 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:<\/p>\n
sqldumper 1684 0x0000<\/pre>\n
The first argument, 1684<\/code>, 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, 0<\/code>x<\/code>0000<\/code>, 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:<\/p>\n
sqldumper \/?<\/pre>\n
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<\/code> utility is not intended for general purpose debugging. For that, you’ll want to turn to Visual Studio or a standalone debugging tool.<\/p>\n
When you run the sqldumper<\/code> 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:\\<\/code>, 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<\/code> utility, so finding answers might not be easy.<\/p>\n
The sqllocaldb utility<\/h1>\n
Here’s a fun utility for you: sqllocaldb<\/code>. 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.<\/p>\n
The sqllocaldb<\/code> utility is simple enough to use. For example, the following command creates a LocalDB instance named express12<\/code> and then starts that instance:<\/p>\n
sqllocaldb create express12 11.0 -s<\/pre>\n
After specifying the utility name, I added the create<\/code> argument, followed by the instance name.  I then included 11.0<\/code> to specify the instance version. The -s<\/code> 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.<\/p>\n
The sqllogship utility<\/h1>\n
Not surprisingly, the sqllogship<\/code> 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.<\/p>\n
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<\/code> system stored procedure. Finally, I ran the following sqllogship<\/code> command to perform a backup:<\/p>\n
sqllogship -server localhost\\sqlsrv2008r2 -backup 1810E0B3-3A0C-4F22-8F3B-1FE187D257DD<\/pre>\n
Notice  \t\tthat I use the -server<\/code> argument to specify the SQL Server instance (sqlsrv2008r2<\/code>), which contains the primary database. I next used the -backup<\/code> 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.<\/p>\n
Microsoft recommends that, when possible, you schedule SQL Server Agent jobs to perform copy, restore, and backup operations, rather than relying on the sqllogship<\/code> utility. The reason, it seems, is that the log shipping history created by sqllogship<\/code> 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.<\/p>\n
The sqlservr application<\/h1>\n
It might seem odd to include sqlservr<\/code> 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. <\/p>\n
When running a sqlservr<\/code> command, you must do so from the appropriate Binn<\/code> folder for that instance, which can be found in a path structure similar to the following:<\/p>\n
install_drive\\Program Files\\Microsoft SQL Server\\MSSQL11[.instance_name]\\MSSQL\\Binn<\/pre>\n
Once you’re in that folder, you can run the sqlservr<\/code> application for that instance. For example, the following command first changes the command prompt to the Binn<\/code> folder for a local instance of SQL Server 2008 R2:<\/p>\n
cd c:\\program files\\microsoft sql server\\mssql10_50.sqlsrv2008r2\\mssql\\binn\\<\/pre>\n
Next comes the sqlservr<\/code> command that starts the instance:<\/p>\n
sqlservr -s sqlsrv2008R2<\/pre>\n
Notice I include only the -s<\/code> 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<\/code>, which will prompt you to confirm that you do indeed want to stop the application.<\/p>\n
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<\/code> commands. However a number of options are available to the sqlservr<\/code> application when starting it at a command prompt (in addition to the -s<\/code> option shown above). For example, you can start the instance in single-user mode or with minimal configuration.<\/p>\n
Plenty more where those came from<\/h1>\n
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<\/code> utility, which I covered in a separate article<\/a> light years ago. <\/p>\n
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.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
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
\nall 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.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4178,4170,4150,4151,5927],"coauthors":[],"class_list":["post-1767","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-bi","tag-database-administration","tag-sql","tag-sql-server","tag-sqlio-dta-sqldumper-osql-sqldiag-sqllocaldb-sqllogship-sqlserver"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1767","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1767"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1767\/revisions"}],"predecessor-version":[{"id":41006,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1767\/revisions\/41006"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1767"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1767"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1767"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1767"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}