DIR Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
HTML https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
DIR Return to: General
*****************************************************
#Post#: 49--------------------------------------------------
Seven Under Utilised SQL Server utilities
By: srinivasma_exceldbp Date: April 16, 2014, 2:07 am
---------------------------------------------------------
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.
*****************************************************