SQL Server profiler trace parsing tool – Build performance reports based on profiler traces only

Imagine the scenario that you application has just been deployed into production environment and SQL Server is extremely slow consuming too many resources. Sometimes a single store procedure can be easily identified by looking into DMV’s or just profiler trace. But how you can summarize entire profiler trace in simple reports that aggregate all store…

2

SQL Server 2008 – Performance counters registry hive consistency fails on non English Operating Systems

Trying to install SQL Server 2008 on a non English OS and setup is being blocked by this rule, however looking to performance counters everything looks fine. On Operating Systems pt-PT (Portuguese) setups is trying to look for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\016 which don’t exist, setup should be looking for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\0816 (0816 is LCID code for…

15

SQL Server 2008 installation aborts due an access denied

Trying to install SQL Server 2008 on a standalone environment using an administrator account and getting an access denied? The problem could happen in case you have removed Local Administrator from ‘Debug privileges’ on local security policies or just because your local domain GPO removes it. SQL Server installation log: (…) The Detail.txt indicates 3…

3

Bulk insert using UNICODE data files

  Did you ever tried to use bulk insert with unicode instead of ansi data files using a format file as described below: BULK INSERT MyTestTable FROM ‘c:\data2.txt’ WITH ( FORMATFILE = ‘c:\format.fmt’,DATAFILETYPE = ‘widechar’ ) go data2.txt (UNICODE format) "test1",test2,Praha 5 – Reporyjezøíujc "test1",test2,Praha 5 – Reporyjezøíujc format.fmt (ANSI format otherwise SQL Server asks…

2

SSIS 2008 SCD wizard does not show non-SQL Server connections

This change has been implemented due a problem/limitation on non SQL OLEDB that generated invalid packages when using wizard, this problem has been reported by many customers so a new topic will be added on books online to mention this restriction. However using advanced editor is still possible to select non-SQL OLEDB providers since mappings…

0

SQL Server checkpoint problems

On high end performance servers with a huge amount of memory you might notice some spikes on I/O activity due checkpoint executions as examplified below:   On SQL Server error log after enabling trace flags 3504 and 3502 to enable checkpoint verbose logging you might notice:                   (…) 2008-11-13 16:10:19.57 spid13s     Ckpt dbid 5 started…

0

SQL Server log growing out of control due Query subscription warning messages (SQLDependency API)

When using a query notification enabled application that is using SqlDependency, if Stop()method is called very frequently, which throws away the machinery for receiving a querynotification, but it doesn’t actually cancel the subscription. So, when a DMLshows up, that subscription will be deleted since it’s orphan however this results in an error message from Service…

0

SQL Server installation troubleshooting, interesting articles

General info, best practices 907284 – Changes to the readme file for SQL Server 2005 910229 – SQL Server 2005 Express Edition Readme 905395 – A change to the Readme.htm file for SQL Server 2005 Mobile Edition 910070 – The SQL Server 2005 Setup program may take a very long time to be completed 916760…

1

How to use Windbg to debug a dump of a 32bit .NET app running on a x64 machine

Imagine we are running a 32bit .NET app in a x64 machine. This app is failing so we have taken a memory dump of the app when a specific CLR exception gets raised. We open the dump with our 64bit version of Windbg (Windbg x64), and we verify that we actualy got the dump when the…

2

How to Rebuild System Databases in SQL Server 2008

In SQL Server 2005, we introduced a different method than in previous versions to rebuild system databases (affectionately known as “rebuild master”). You were required to use the setup.exe program with command line switches. This is no different in SQL Server 2008 but the command line switches have changed some and the process behind the…

0