Daniel Calbimonte

SQL Server utilities you probably didn’t know about – SQLdiag, osql and SqlLocalDB

October 26, 2017 by

Introduction

There are several tools included in SQL Server on-premises that you may not know.

In this article we will explain the following tools:

  1. SQLdiag
  2. osql
  3. SqlLocalDB

After reading this article, you will learn how to use them and understand what are they for.

Requirements

  • SQL Server on-premises Installed 2016 (older versions can be used and later versions may not include osql).

Getting Started

1. SQLdiag

This tool is used by Microsoft tech support to diagnostic problems with SQL Server. However, it contains useful information that you can use to detect SQL Server problems. You can collect performance counter information, locks, deadlocks, event logs, SQL Server configuration, OS information and traces.

In drive:\Program Files\Microsoft SQL Server\130\Tools\Binn, you will find the SQLdiag (unless you specify another folder using the /O parameter):

It collects log and data from your server to troubleshoot problems.

Using SQLdiag /? or SQLdiag -?, you can get help about parameters and the command use:

In order to start, in the cmd, write SQLdiag:

If there are warnings and red messages, you will need to press escape to continue collecting data. It takes a while to collect all the data. You can always press Control+C to stop. In this example, try to wait 15-30 minutes after stopping the data collection:

SQLdiag collects the information of all the instances. You can specify the machines used in the data collection using the /M parameter. You can also specify the start and end date of the data collection using the /B and /E parameters (to begin and end the data collection).

In drive:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqldiag\internal or a similar path, you will find the diagnostic files created:

The ##console file contains a resume of all the files collected by the SQLdiag:

##Envvars file contains all the environment variables of the operative system:

##Files contains the dir information of the SQLdiag folder:

##SQLdiag contains information of the data being collected:

The ##Server_MSDiagProcs_Startup file creates some stored procedures in the tempdb:

You also have in the drive:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqldiag, some log files that can be useful:

The log files can be opened in SQL Server profiler, they are trace logs:

In the ServerName_sp_sqldiag_Shutdown file, you can see the errorlog information including SQL Server version, Service Pack and errors:

The Server_Name_MSINFO32. Contains system information like the system, name, processor, BIOS, IRQ. It is the information that can be obtained from the systeminfo command:

As you can see, SQLdiag collects a lot of information about traces, logs, hardware, files, paths and more. It can be very useful to detect the cause of an error.

It is also possible to run SQLdiag in quiet mode using the /Q argument. You can also run the SQLdiag as a service using the /R argument to register SQLdiag as a service.

2. osql

Some students asked me what is osql.

In the drive:\Program Files\Microsoft SQL Server\130\Tools\Binn\ or a similar path, you will find the osql:

I could not believe that osql was still in SQL Server 2016. According to the documentation osql will be removed soon. I heard that rummor a long time ago, but it is still there.

Osql is the old command line used in SQL Server 2000. Before that, the command line was isql (removed after SQL Server 2005). Now, SQL Server uses sqlcmd.

Why should I care about this old command line tool? The answer is that it can be useful if you have to deal with old SQL Server versions.

Osql uses ODBC to connect to the server, sqlcmd uses OLEDB and it is faster.

Sqlcmd supports parametrized parameters, XML, connection to Azure, DAC. Osql do not support that.

To get the help you can run the osql /? or osql -?:

If you are familiar with sqlcmd, you will easily be able to learn this tool, because many arguments are exacly the same like the -i argument to run scripts or the -o to store results in an outoput file. Note that you need to be careful with the uppercased or lowercased arguments. For example the -O argument uses the isql behavior and -o lowercased is to handle output files.

To connect to osql, you can use the osql -E to connect to SQL Server using the trusted connection. If the instance name is not the default instance you may need to add the server parameter (-S Instance name):

The funcionallity is like sqlcmd. You need to write your query, press Enter and then press GO and Enter again:

The following example shows how to get the server name in osql:

You can run backups, create tables, indexes, queries or any administrative task related to T-SQL in osql. It my be useful if you need for some reason to handle an old SQL Server version that does not include UI (Enterprise Manager or Query Analyzer) or if you want to automate tasks in old SQL Server versions.

3. SqlLocalDB

Another tool included is SqlLocalDB. This tools is used by developers to install a light SQL Server that can be installed easily in few minutes. To install a SQL Server instance, you need the SQL Installer and configure several options. With SqlLocalDB, you can have an instance fast and easily. This feature is supported in SQL Server 2012 and later versions. It started as a SQL Server Express feature.

To run the help, just write SqlLocalDB -? or SqlLocalDB /?

To list the instances available, write SqlLocalDB info:

To create a SqlLocalDB instance, write SqlLocalDB create and specify the name of the instance using quotes:

In SQL Server Management Studio, you can connect to the instance using the syntax (localdb)\instancename:

Once connected, you can work as a normal database:

The restrictions are the following:

  • LocalDB does not support the FILESTREAM feature.
  • The instance cannot be a subscriber in a merge replication.
  • Only local queues are supported in Service Brokers.

For more information about LocalDB, we have 2 nice articles written by Marko Zivkovic:

Conclusion

In this article, we presented the following tools:

  • SQLdiag
  • osql
  • LocalDB

SQLdiag is used to troubleshoot problems related to SQL Server. It connects information about counters, logs, errors, traces, etc.

The other tool is osql. This tool may be useful if you need to migrate old SQL Server information without installing extra tools. For example, I used osql to create a database backup in SQL Server 2000, because the Enterprise Manager (the old SSMS) was corrupt in an old server for the government in my country.

LocalDB can be used by developers if they do not want to install an instance of SQL Server and want to optimize resources to start using SQL Server.


Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)

Related posts:

  1. How to install Microsoft SQL Server Express LocalDB
  2. How to connect and use Microsoft SQL Server Express LocalDB
  3. A DBA guide to SQL Server performance troubleshooting – Part 2 – Monitoring utilities
  4. Cómo instalar Microsoft SQL Server Express LocalDB
  5. Cómo conectar y usar Microsoft SQL Server Express LocalDB
Utilities

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams. He also helps with translating SQLShack articles to Spanish View all posts by Daniel Calbimonte

168 Views