Tag Archives: tools

Export the Event Logs without opening MMC

I recently worked on a database corruption issue. In order to troubleshoot this I had to collect all the Event Logs from the server. But when I tried to open Event Viewer, MMC would fail with the following error.

MMC could not create the snap-in.
Name: Event Viewer
CLSID: FX:{b05566ad-fe9c-7a4cbb7cb510}

For that matter any MMC snap-in on the server would fail with the same error. But getting the event logs was critical to get troubleshooting going. Thats when Wevtutil.exe which ships with the Windows Operating System came to the rescue.

Wevtutil.exe is by default located in the C:\windows\system32 folder.

I had to just execute the following command to export the System Event Log to C:\SystemLogBackup.evtx

wevtutil.exe epl System C:\SystemLogBackup.evtx

The “epl” parameter exports the event log specified (System in this case) to the destination file.
All I had to do is copy the exported file to my local desktop and double click it to open in the Event Viewer snap-in.

SavedLogs

This is indeed a good tool to have in a DBA’s armory.

Webcast | Cool Tools to have for SQL Server DBA

On May 5, 2011 I will be delivering a webcast on “Cool Tools to have for SQL Server DBA“. The intention of this webcast is to familiarize oneself with some of the tools that are of great use to the SQL Server DBA. I use these tools very often and helped me resolve some of the interesting cases. In this webcast I will be talking about SQL Nexus, SQL Server Backup Simulator, Process Explorer, PerfMon and WinDirStat.

Cool Tools to have for SQL Server DBA
Date: 5th May, 2011
Time: 2:30 pm – 3:45 pm IST

Apart from my session there are other webcasts being delivered this week. Here are the details.

Managing and Optimizing Resources for SQL Server
Speaker: Balmukund Lakhani
Date: 2nd May, 2011
Time: 2:30 pm – 3:45 pm IST

Optimizing and Tuning Full Text Search for SQL Server
Speaker: Sudarshan Narasimhan
Date: 3rd May, 2011
Time: 2:30 pm – 3:45 pm IST

Understanding Performance Bottlenecks using Performance Dashboard
Speaker: Amit Banerjee
Date: 4th May, 2011
Time: 2:30 pm – 3:45 pm IST

Learn Underappreciated Features of SQL Server to Improve Productivity
Speaker: Nakul Vachhrajani
Date: 6th May, 2011
Time: 2:30 pm – 3:45 pm IST

If you are interested, please register at
http://virtualtechdays.com/SQLServer2008R2/

Generate script for objects along with data using Microsoft SQL Server Database Publishing Wizard

Last week, we had a request from the Application Team to generate the DDL script for all objects on a Production database. Since the number of objects on that database was quite high (more than 100,000), the Generate Script wizard in SSMS stopped responding. Later on we informed the Application Team to go for backup/restore the database, instead of waiting forever for the DDL script to be generated.

While I was looking out for better options to script out the database, I stumbled upon the Microsoft SQL Server Database Publishing Wizard tool by Microsoft. This is a good utility to generate scripts for exporting a database (SQL Server 2000 & 2005) to a file. This generates the T-SQL commands for the schema and data inside the tables. Here are some of the screenshots of the Microsoft SQL Server Database Publishing Wizard.

The script generated by this tool looks like the one below.

/****** Object:  Table [dbo].[TimeTable]    Script Date: 03/19/2011 12:38:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TimeTable]') AND type in (N'U'))
DROP TABLE [dbo].[TimeTable]
GO
/****** Object:  Table [dbo].[TimeTable]    Script Date: 03/19/2011 12:38:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TimeTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TimeTable](
	[StartTime] [datetime] NOT NULL,
	[stoptime] [datetime] NULL
)
END
GO

INSERT [dbo].[TimeTable] ([StartTime], [stoptime], [downtime])
	VALUES
	(CAST(0x00009D1800E8F0C2 AS DateTime), CAST(0x00009D1800E8C4AF AS DateTime))
INSERT [dbo].[TimeTable] ([StartTime], [stoptime], [downtime])
	VALUES
	(CAST(0x00009D1800F3E452 AS DateTime), CAST(0x00009D1800F3B415 AS DateTime))

Since the data export progress is not very elaborate, while exporting huge databases one gets the feeling that the tool is hung. However, this utility does a good job while exporting data for relatively small databases and does it quickly.

When was SQL Server restarted?

Over the weekend, I interacted with some of the “wanna be” DBAs. During the interaction I checked with them how they would find when was the SQL Server instance restarted? Everyone had a different approach to find out the information. Here are some of the ways to find out the date & time on which SQL Server service was last restarted.

SQL Server Error Log

By making use of sp_readerrorlog, first time stamp in the SQL Server Error Log can be treated as the time when SQL Server service was restarted.

sp_readerrorlog 0,1,'Copyright (c)'

This approach is not useful if the SQL Server Error Log is cycled manually.

Using sys.dm_os_sys_info DMV

Starting SQL Server 2008, sys.dm_os_sys_info DMV has a column named sqlserver_start_time. This column as the name suggests stores the time when SQL Server was restarted.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

But this query does not work in versions which are lower than SQL Server 2008.

Using sys.dm_exec_sessions DMV

The following query will get the Login time of the Session Id 1.

SELECT login_time FROM sys.dm_exec_sessions
WHERE session_id = 1;

session_id is created when the SQL Server is started and the login time remains the same until the service is restarted again.

Start time of the Default Trace

The Default Trace is started when the SQL Server is started. The start_time of this trace can also be taken as the time when the SQL Server service was restarted.

select start_time from sys.traces
where is_default = 1

This approach is useful except for the instances where the Default Trace is explicitly disabled.

Creating date of tempdb

The creation date of tempdb database will also be the time when SQL Server service was restarted. This is because the tempdb database is re-created whenever the SQL Server service starts up.

SELECT create_date FROM sys.databases WHERE name = 'tempdb'

The above query works on SQL Server 2005 and above. For SQL Server 2000 the same query will vary a little bit.

SELECT crdate FROM sysdatabases WHERE name='tempdb'

This is the query which I always use to find out when the SQL Server service was restarted.

Converting the .safe backup to native format using SafeToSQL

On Wednesday I came across a question in MSDN. The user had a .safe file located on one of the servers and was not sure how to read that file.

The .safe files are created by the Idera SQL Safe backup utility. This tool takes the SQL Server database backup and stores it as .safe file. When compared to the native SQL database backup, .safe files have good compression ratio. Also these files are encrypted using the password passed on to the utility while initiating the database backup.

Now we know that .safe files contain SQL Server database backup, the only way to *read* it is by restoring it. SQL Safe backup utility can be used to restore these files. What if the SQL Safe utility is not installed on the server. Is there a way to convert .safe files into native SQL Server database backup format (.bak)? The Idera SQL Safe backup comes with a tool called SafeToSQL. This tool can be used to uncompress and decrypt the .safe files and convert them to .bak files (the file extension is irrelevant).

Here is how it works. The SafeToSQL.exe is located in SafeToSQL folder under the folder in which the SQL Safe Backup software is installed.

SafeToSQL [sourcebackupfile.safe] -password [TheSafePassword]

The output of this command would look like the one below.

This tool comes in very handy in an environment where the Production databases are backed up using SQL Safe Backup and the non-production instances do not have this tool installed.

How Dependency Walker helps in resolving DLL issues

A couple of weeks ago I was working with one of my team members to fix a failing SQL Server 2005 Service Pack 4 setup. He was was trying to install SP4 on his laptop and the Database Services component was failing with the following error message.

MSP Error: 1920 Service 'SQL Server VSS Writer' (SQLWriter) failed to start.

The SQL Server VSS Writer service was not starting even as a service or on invoking the sqlwriter.exe.

The below error message was logged in the System Event Log.

Activation context generation failed for "C:....sqlwriter.exe".
Dependednt Assemnly Microsoft.VV80.ATL.processorArchitecture="x86", publicKeyToken="1fc8b3b9a1e18e3b", type"win32", version="8.0.50727.1833" could not be found.
Please ue sxstrace.exe for detailed diagnosis.

I tried to import/register the assembly as I had done it here, but none of the attempts were fruitful. Since the SQL Server was running on his personal laptop, we uninstalled Microsoft SQL Server VSS Writer. After this the Service Pack 4 was installed successfully. After this we manually installed VSS Writer component using the .msi file from the Setup folder. Still the VSS Writer service was failing with the same error. Since this was not a High Severity incident we decided to leave it alone.

While I was trying to import the ATL80.dll into GAC, it failed initially stating some issues with the DLL. Whenever a DLL fails to register or any application reports issues with a DLL the first tool that comes to my mind is Dependency Walker. This tool lists all the DLLs on which a given DLL (or exe, ocx etc.) is dependent on. If any of those DLLs are found to have issues, it is reported in red along with a description. In the below screenshot (click to enlarge) LINKINFO.dll on which ATL80.dll has a dependency, is missing.

Since the LINKINFO.dll was missing, I copied it from a working instance to this laptop. After this ATL80.dll got registered successfully.

With Dependency Walker finding the missing dependencies is so easy. The next time you run into an issue with a DLL, remember to use Dependency Walker before proceeding with further.

On which port is SQL Server listening on?

It is a well known fact that whenever SQL Server starts it binds itself with the IP Address of the Server and it starts listening on a port assigned to it. In SQL Server 2005 and above, the port on which SQL Server should listen on is configured using the SQL Server Configuration Manager.

The values defined in TCP Port under IPAll section is the port on which SQL Server is configured to listen on. This script on SQL Server Central helps to read this information from the registry.

At times when the SQL Server instance is starting up, due to some reasons it will not be able to bind with the port assigned which is usually logged in the SQL Server Error Log.

server SuperSocket Info: Bind failed on TCP port 1433

On this error usually SQL Server will start listening on Named Pipes. Restarting the SQL Server usually helps SQL Server to listen on the configured port.

How to check on which port SQL Server is listening on at a given point in time? The first place to look for is the SQL Server Error Log. When SQL Server starts up this information is logged in it.

But on my client’s instances the SQL Server Error Log is recycled every night. If a server was restarted a week ago, it will be a little time consuming to find out the Error Log number and then reading it. The other way to get this information is through the netstat command.

The following command can be used to get the port number of SQL Server.

netstat -ano | find /i "ProcessIDofSQLServer"

The definition of the parameters used for the netstat command is as below.

  • -a : Displays all active TCP connections and the TCP and UDP ports on which the computer is listening.
  • -n : Displays active TCP connections, however, addresses and port numbers are expressed numerically and no attempt is made to determine names.
  • -o : Displays active TCP connections and includes the process ID (PID) for each connection. You can find the application based on the PID on the Processes tab in Windows Task Manager.

The Process ID of SQL Server can be obtained by following the steps that I had posted yesterday. In this example, the SQL Server Process ID is 2276. The netstat command would be

netstat -ano | find /i "2276"

The output of this command will be

The second column in the output refers to Local Address and the third column refers to the Foreign Address. The entry after the IP address which begins with a “:” is the port number on which SQL Server is listening on. In the above example it is 1433. The corresponding entry in the third column i,e. the Foreign Address, has the details of the IP Address and port number of the client which has connected to the SQL Server on the port in the Local Address. Whenever a client connects to SQL Server, one connection which has Local Address and Foreign Address is ESTABLISHED. The rows which have a status of LISTENING, correspond to the ports on which SQL Server is listening. In the above example it is listening on ports 1433 and 1434 (for DAC).

Hence a quick look at the Local Address column will tell on which port SQL Server is listening on. Please feel free share if you are following some other methods to find out the port details.