Monthly Archives: October 2010

How to fetch all Login Failed messages from default traces

Posted on by

I was recently investigating a login failed issue on a SQL instance and required to quickly find out all the Login Failed events that had occurred on the server. I could have scoured the SQL Errorlogs but I didn’t want to do this due to the bloated size of the Errorlogs. An easier way would be to read the Default Traces (available from SQL Server 2005 and above) and look for all the Audit Login Failed events (event class = 20) using fn_trace_gettable function.

The query that can be used to get this information is given below:


declare @path varchar(1000)

select @path = path from sys.traces where id = 1

select trc.Spid, TextData, State, Error, StartTime, EventSubClass, LoginName, NTDomainName, NTUserName, ClientProcessID, HostName, ApplicationName

from fn_trace_gettable(@path,DEFAULT) trc

where EventClass = 20

order by StartTime desc
Advertisement

T-SQL Tuesday: Common SQL Server myths – Series I–Setup and Services

Posted on by

imageAs part of the Microsoft CSS group, we get to deal with a lot of production as well as non-production related issues. While we are troubleshooting such issues, we also end of dispelling some or the other myth that exists regarding SQL Server architecture.

So, this time when Sankar Reddy (Blog | Twitter), a SQL Server MVP, decided to host T-SQL Tuesday, a community blogging effort started by long time SQL Server MVP, Adam Machanic (Blog | Twitter), I decided to contribute a post on this month’s topic “Misconceptions in SQL Server”.

There are multiple posts by very eminent people in the SQL community that have debunked various SQL Server myths. If you are not a SQL Server specialist and buy these myths that have spread around in the community like wild fires, you would probably think that SQL Server and MS Access are the same. And as any good SQL Server DBA would know that this is not true! Since, T-SQL Tuesday is an effort to enhance and share community knowledge, I shall attempt to refute and clarify certain common myths that I deal with day-in and day-out during the course of my work with SQL CSS. My first series will be on

Series #1: SQL Server Services and Patching

Thumbs downIt is alright to change the SQL Service account and password from the Service Manager snap-in
This is one of the most common misconceptions that people across the world have in this area. The SQL Server Configuration Manager has been designed to do the following (other than a host of other configuration activities):

  1. Change SQL service account
  2. Update the password of the SQL service account
  3. Make configuration related changes like adding trace flags

If you have not used Configuration Manager to do any of the above and have not faced an issue, then you are lucky! But Lady Luck may not always be on your side! Sarcastic smileThe Configuration Manager (available from SQL Server 2005 onwards) does a host of other activities when you make a change to your service account like granting the necessary permissions required on the Registry for the SQL service account etc. which the Services Manager doesn’t do. Even in SQL Server 2000, service account changes should be done through Enterprise Manager as it grants the necessary permissions required to use the Full-text feature. As new features get added to the product, a lot of considerations w.r.t. ACLs, Registry permissions, encryption keys, etc. need to be taken into account while modifying SQL Service accounts. The Configuration Manager was designed keeping these considerations in mind. So, this is not a new recommendation but has been around for more than a decade.

The other option that you have is to use WMI and the SQL Server namespaces to programmatically change the SQL service account. I had posted an example of this on Technet. If you want to modify Reporting Services service accounts, then it would be a bit more complex because you would need to manage the encryption keys etc.

Thumbs downWindows Cleanup Utility can be used to uninstall SQL Server
All I have to say to this is NOT a good idea if you still want to be the DBA, Windows Cleanup Utility shouldn’t be used unless and until Microsoft has published the same (via an official blog post or KB Article) to be used for a certain scenario. Windows Cleanup Utility is a generic tool that uses Windows Installation GUIDs from the registry to annihilate the product’s existence from your box! Since, we use Windows Installer to install and patch SQL Server (true for SQL Server 2005 to SQL Server 2008 R2), the SQL component registry entries are centrifugal to the SQL’s existence on the box. If these are not cleaned up by our uninstaller program correctly, then you can have a server which behaves very goofily the next time you run a SQL installation program. The worst case scenarios that I have seen are:

  1. No further patching possible for existing instances of SQL on the box
  2. No further new installations of SQL Server on the box

You wouldn’t want to land up in either situation. In such situations, you might even have to rebuild the box! Not pretty when you have multiple instances running on the server! Surprised smile

Thumbs downDifferent nodes can have different builds for the same SQL instance
SQL Server clusters are not supported in scenarios where different nodes belonging to the same clustered instance are on different builds. The reason behind this is that are be code differences in the SQL builds that we release. So if you failover to a lower build of the SQL Server, then you can expect to see some known issues surfacing which do not occur on the other node/nodes.

Thumbs downSQL Server setup will automatically patch the newly added node
If you have recently added a node to the SQL instance, then you should patch it immediately to the current build of the SQL clustered instance that you added the node to, before putting that node into production. Till SQL Server 2005, there was no option of slip-streaming such installations. You can use the steps mentioned in the following KB Article in case you run into issues while patching the newly added node.

Thumbs downSQL Server services don’t go offline while patching the SQL instance
Another common misconception that makes applications connecting to the SQL Server instance unhappy Sad smile (read: application timeouts) if they are kept online during SQL setup. At a high level, I like to look at SQL Server setup as a two step process. The first step involves MSI (Windows Installer) actions, which are responsible for making the necessary registry changes, laying down the binaries and other Windows related configuration changes including .NET (if necessary). The second step is when the SQL Server instance is started up and configuration scripts (T-SQL) are executed to configure the SQL instance and make the necessary meta-date changes required for the system metadata residing in the SQL system databases. For the first step, the SQL instance needs to be offline as the system database files(like mssqlsystemresource) cannot be updated if the SQL instance has a lock on the files. When the SQL Service is started for configuring the instance, it is in single user mode with certain parameters, so that applications cannot connect to it during that time. It is always advisable to take a downtime during patching of SQL instances. Hot patching is still not available in SQL Server.

Thumbs downDeleting files from the C:\Windows\Installer folder will free up disk space
A mistake that I have seen so many people make, that I have lost count. Yes, it will free up your disk space but at a cost where it is possible that you might not be able to patch your existing applications which use Windows Installer for installation/patching. Surprisingly, this is a hidden folder and still people think it’s wise to delete files from that folder. We made it hidden for a reason!! As mentioned before in this blog post, that SQL installation uses Windows Installer. The files .MSI and .MSP) used during installation are cached in the Windows Installer folder. If you delete these files, then you will land up with various missing MSI/MSP files error. SQLSeverFAQ (Blog | Twitter), a SQL CSS MSDN blog, has multiple posts has step-by-step instructions when you run into such issues. Disclaimer: The steps mentioned in the posts are NOT for the faint-hearted! So, avoid Shift+Delete of such files!

Reference :
Part – 1 – SQL Server 2005 Patch Fails to install with an error Unable to install Windows Installer MSP File
SQL SERVER 2005 SP3 Install Fails with Error 1612- Unable to install Windows Installer MSP file

In case I have missed out any other common myth related to SQL Services and Setup, please feel free to leave a comment and I shall add it to the post. Leave your name and Twitter handle (if you have one) and I shall acknowledge the credit for the myth to you.

How to find out top version store generating objects

Posted on by

I recently found the need to write a query to find out which tables are contributing to version store usage on the server, then you can use the following piece of T-SQL:

EXEC sp_MSforeachdb 'IF ((select DB_ID(''?'')) in (select distinct database_id from sys.dm_tran_top_version_generators)) begin print ''Database Name :'' + ''?'' + char(13) select a.*,object_name(b.object_id) as TableName from sys.dm_tran_top_version_generators a inner join ?.sys.partitions b on a.rowset_id = b.partition_id end'

The reason this got so convoluted is because the Object_Name function doesn’t accept a partition ID or a allocation hobt ID. So, I have to get the object ID from sys.partitions for the associated database and then use the object_name function.

If anyone has an alternative quicker way, then please feel to leave a comment.

Data Type Mapping for OLEDB Providers

Posted on by

I recently had a question on the #sqlhelp hashtag on Twitter regarding how OLE DB Providers map the data columns to SQL Server data types.

The Data Type Mappings for SQL Server for distributed queries are mentioned here. The DBType values for each data that you are retrieving from a non-SQL data source like Oracle, Excel, Access can be found out using RowSet Viewer. The Microsoft® Developer Network (MSDN®) Platform SDK contains an OLE DB RowsetViewer sample application written in Microsoft Visual C++®. This application enables you to connect to either the Microsoft OLE DB Provider for AS/400 and VSAM or the Microsoft OLE DB Provider for DB2, open a table window, type the host file name or DB2 database, return a rowset, and browse the contents.

Using Rowset Viewer you can get the DBType of each column returned from the remote data source. The function used is IColumnsRowset::GetColumnsRowset. Using this you can create the necessary schema on the SQL Server database which will act as the destination for the data received from the remote data source.

Happy DB Scheming! Smile

[Blog Update] SQLServerFAQ posts for September

Posted on by

September has been a content month for me both on the SQLServerFAQ blog on MSDN as well on this blog. Here is a round up of my posts on SQLServerFAQ for September:

I was intrigued by this when someone asked me what the Scan Count value meant w.r.t. SEEKs/SCANs:
SCAN COUNT meaning in SET STATISTICS IO output

I had worked on multiple issues for SQL Server 2005 regarding 511. Wanted to demonstrate how easy it is to troubleshoot this on SQL Server 2008 with XEvents:
Troubleshooting Error 511 using XEvents

Ran into a Compile Lock blocking issue because the Stored Procedure being used had an Open Symmetric Key command in it. Here is the post talking about the issue and how to workaround it:
Open Symmetric Key command prevents plan caching

I ended up doing a sequel for the Scan Count values w.r.t. Joins in the post below:
Scan Count meaning in SET STATISTICS IO output: Part 2

Technorati Tags: ,,