Tag Archives: tips and tricks

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.

How to extract the contents of .MSP files

I had encountered an issue while installing a SQL Server service pack. The resolution for it was to replace the existing SQL Server binaries from that of a Service Pack. To extract the contents of a Service Pack executable we need to just type “ServicePackExeName /extract” as I explained in this post.

I was looking for the sqlservr.exe but it wasn’t directly available in the extracted folder. The folder structure looked something like this.

ExtractedFolder

From my past experience I knew that the Service Pack installer would copy sqlservr.exe by extracting sqlrun_sql.msp file.

sqlrun_sql.msp

Now how do I get the sqlservr.exe from the .msp file? The command “sqlrun_sql.msp /extract” wouldn’t work here.

One of the options is to extract the contents of the .msp file using a file archiver like 7-zip. After extracting the contents, look for a file which has the name CAB in it. In this case, the file name is “PCW_CAB_Family01”.

extracted

Again extract this file to a folder and the contents would look something like this.

sqlservr.exe

Now just rename the sqlservr.exe.******** to sqlservr.exe. That’s all. You have just extracted the requisite file from an .msp file.

The other alternative is to use the MsiX utility by Heath Stewart. The usage of this tool is very straight forward. Just type the following command in the Command Prompt and it would extract the contents of the .msp file

MsiX sqlrun_sql.msp

MsiX_cmdprompt

After this navigate to the folder where the file was extracted and extract the contents of the *CAB* file.

MsiX_extractedFollow the same steps and rename the file that you need (sqlservr.exe.****) to the requisite file.

Mission accomplished!

Create and connect to a SQL Database on Windows Azure

Azure, is one of the fastest growing cloud platform by Microsoft. In this simple post, I will create and connect to a SQL Database on Microsoft Azure.

To get started logon to the Microsoft Azure Management Portal , navitage to SQL Database and click on New.

sqldatabases_new

In the next screen, give the database a unique name and select the appropriate values.

CreateNewDatabase

The database gets created in a few seconds and it would be ready to use when the status changes to ONLINE.

DatabaseOnline

Clicking on the Database Name (in this case is is TheTestDatabase), would give the detailed information about the database including the address of the database.DatabaseProperties

Now you can use this information in the Connection String or just connect to it using SQL Server Management Studio (SSMS). But when you try to connect to this database from a different computer, it would fail with the following error.firewallerror

Error text:


Cannot open server 'ServerName' requested by the login. Client with IP address '{IPAddress}' is not allowed to access the server.
To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.
It may take up to five minutes for this change to take effect.
Login failed for user 'pradeep'.
This session has been assigned a tracing ID of '{TrackingID}'.
Provide this tracing ID to customer support when you need assistance. (Microsoft SQL Server, Error: 40615)

As seen in the error message, by default all IP addresses are not allowed to connect to a SQL Database. The requisite IP Addresses/IP Address ranges needs to explicitly configured for the server hosting the SQL Database, else the connections will fail with the above error.

To configure the Firewall rules, first we need to navigate to the server on which the SQL database is hosted.

ServerName

In the Configure screen, give the rule a name and specify the IP address range which is allowed to connect to the database.

FirewallRule

After entering the details make sure to click on the “Save” button at the bottom of the screen. Now if we try connecting to the database from the same host, the connection would succeed and the database would be listed in SSMS.

DatabaseConnected

Hope you found this post useful. Happy cloud computing!

Find SQL Server version details from the Registry

Last week, the customer wanted to know the Edition information of a Non-Production SQL Server instance. We can easily get this information by executing the following query in SSMS.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

 

This article discusses various approaches that are available to get the SQL Server version information.

To get the Edition information on this Instance was not that straight forward. The Production DBA team was part of the Local Administrators but our group was not granted access on the SQL Server Instance. Hence executing the above query against the instance was not an option.

The information related to SQL Server Setup/version is stored in the Windows Registry. This helped us to get the information related to SQL Server Edition without having access to the instance.

In Windows Registry, this information is stored in the following path.

The highlighted part in the above screenshot varies depending on how many instances are installed on a server. The remaining part in the path does not vary. The information under the Setup key looks like this (click on the image to enlarge).

Do you think of any other approach to get this information? Please feel free to post it in Comments.

Stop database backup information from appearing in the Error Log

This post is in response to a question from one of my blog readers. Whenever a Database or Transaction Log is backed up, a corresponding entry is logged in the SQL Server Error Log regarding the backup. On an instance which has quite a lot of databases or if the transaction log backup is scheduled to taken very frequently, the entries regarding the backup operation will eventually bloat the Error Log like in the below screenshot.

Is there a way to stop these messages from appearing in the Error Log?” was the question. Yes, it is possible. The Trace Flag 3226 has to be enabled as a start-up parameter as seen in this screenshot.

Once the SQL Server Instance is restarted after enabling this trace flag, the information about the successful backup operations will not be logged in the Error Log. However, whenever any backup fails it will be logged in the Error Log even with T3226 on.

Stop the command prompt from disappearing after execution

For most of the tasks I use the keyboard shortcuts. The touchpad comes in to picture only when there is no shortcut for a particular task or I don’t recollect the shortcut. Hence to launch any applications I use Start –> Run dialogue more often than not.

As a DBA while troubleshooting issues, I need to quickly check the IP Address of a computer. The easiest approach is to run the ipconfig command. By practice, I open Start –> Run and execute ipconfig.

But soon after executing this, the ipconfig window appears but it disappears quickly because the command prompt window does not “stay”. Then I would open cmd from the Run dialogue and hit ipconfig.

If you find this approach too long and would like to save a couple of key depressions, here is the approach that I use. Instead of executing ipconfig or any other command from Run, cmd.exe has a switch named /k. Using this switch, the given command would be executed but the command prompt window does not disappear but it would remain. The new approach to execute ipconfig from Run, would be like the one below.

After executing with cmd with the /k switch, the command prompt would stay.

This might not be a big trick but it is useful while doing certain repeating tasks from the Run dialogue.

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.