Thursday, December 15, 2011

Blog has moved

I've now moved to and will no longer be using or updating this SQL-Kevin blog.

Thursday, September 29, 2011

Error: 18456, Severity: 14, State: 58

Recently I was alerted to the following error that was caused when a legacy application was attempting to use an ODBC connection to connect to a SQL Server.

Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.
Error: 18456, Severity: 14, State: 58.

The SQL Server was definitely using Mixed Mode Authentication.

The lack of the SQL Login name and incorrect authentication mode being reported implied that the application connection to SQL Server was being interpreted by SQL Server incorrectly.

After a couple of various tests, the solution in this case was to set the ODBC connection for the application to use Named Pipes.

Monday, July 25, 2011

Configure Database Mirroring to use a specific network card

The following article demonstrates how you can reconfigure your existing database mirroring to use a specific network card. Both the principal instance and mirroring instance servers have 2 network cards.
  • The principal instance is currently using
  • The mirror instance is currently using
  • Both are communicating over their local port of 5022

As shown on the principal instance, by default, the ip_address used for the mirroring endpoint is not specified so it will use any network card.

The steps to set the principal to only use the ip address and the mirroring instance to use only are listed below. Please note only 1 database is mirrored in the demo.
1.   Remove database mirroring for the database.

2.    Alter the Mirroring endpoint on the principal instance to use local network card I'd suggest that it would be better to assign a DNS entry for the ip address and use that instead of hard coding an ip address.
3.   Verify that the ip_address of the Mirroring endpoint is now showing the ip address specified.
4.   On the mirror instance, alter the Mirroring endpoint to use local network card

5.   Verify that the ip_address of the Mirroring endpoint is now showing the ip address specified.

6.   On the mirror instance set the partner to be the principal instance database.
7.   Finally, on the principal instance set the partner to be the mirror instance database.
You should now see mirroring using the specified network card.

I found that I had to remove database mirroring to get this to work, as the ALTER DATABASE command did not allow me to specify a new partner address while database mirroring was in use. The error given is shown below:

Friday, June 3, 2011

Auto Update Statistics and Auto Update Statistics Asynchronously Investigation

Below are my findings into the Auto Update Statistics and Auto Update Statistics Asynchronously database options.

What I discovered using profiler was:
Scenario 1:
  • Auto Update Statistics = True
  • Auto Update Statistics Asynchronously = False
This resulted in the relevant table statistics being updated as part of the user’s session before the user query starts retrieving data. This is the default database setting.

Scenario 2:
  • Auto Update Statistics = True
  • Auto Update Statistics Asynchronously = True
This resulted in the relevant table statistics being updated by a system session (not the user's session) after the user query has completed.

Scenario 3:
  • Auto Update Statistics = False
  • Auto Update Statistics Asynchronously = True
This resulted in no statistics being updated. This was interesting as it means that if Auto Update Statistics is set to False, it does not matter that Auto Update Statistics Asynchronously is set to True as the statistics will not be auto updated.

The code used to test this was:
-- Create the test table
CREATE TABLE [dbo].[t1](
      [c1] [bigint] IDENTITY(1,1) NOT NULL,
      [c2] [varchar](50) NULL
-- Insert first data row into table
VALUES      ('x')
-- Stats are only created once you add the where clause
FROM   dbo.t1
WHERE  c1 < 5 
-- Use the below to show when stats were last updated
       NAME                            AS STATS_NAME,
-- Insert several rows to the table
VALUES      ('x')
GO 1000
When you run the below the stats will be updated and this is the data I captured in Profiler. Specifically the EventClasses Auto Stats, Lock:Acquired, Lock:Released, SQL:StmtStarting, SQL:StmtCompleted, Performance statistics and all columns.
WHERE  C1 > 5 

Monday, April 11, 2011

SQL Server 2008 R2 upgrade perf-ReportServer-rsctr.dll error

While performing an in place upgrade from SQL Server 2008 to SQL Server 2008 R2 I received the following perf-ReportServer-rsctr.dll error.

Process Explorer revealed that WmiPrvSE.exe was using the file.

To resolve this issue you could try stop the 'Windows Management Instrumentation' service.

Monday, April 4, 2011

Create Test Windows Clusters using Microsoft iSCSI Software Target 3.3

Creating Test Windows clusters has got a whole lot easier now that Microsoft iSCSI Software Target 3.3 is freely available to download.

It can be found here

VirtualBox is a good option to host your VMs as I have found this software to be the simplest and quickest way to get my Test environments built.

The iSCSI Initiator Users Guide for Windows 7 and Windows Server 2008 R2 can be found here:

Saturday, February 12, 2011

SQL Cluster Install 'Network binding order' warning

When installing SQL Server 2008 on a cluster you may get a 'Network binding order' warning.

To identify why the setup is reporting this error:
  1. Open your ..\Setup Bootstrap\Log\..\Detail.txt file
  2. Navigate to the 'Network binding order' rule section
Below shows that 'Local Area Connection* 9' was top of the list, when it should have been 'public'

The problem is that sometimes you cannot see the device or cannot get the correct order despite using

So what can you do?
1. Run regedt32
2. Navigate to 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Linkage'
3. Right click on 'Bind' and select 'Modify'
4. Move the correct Device (identified in Detail.txt) to the top of the list and that is it.

Saturday, February 5, 2011

Using VirtualBox to resize a virtual disk

Recently I needed to resize the C drive on a Windows 2008 R2 virtual machine from its existing 10GB to 20GB.

I performed the following steps using VirtualBox v4.

1. Execute from the command prompt (Note the Virtual Machine was Powered Off)
VBoxManage modifyhd C:\VM_Test_Env\Servers\W2K8R2-04.vdi --resize 20480

2. Start the virtual machine.

3. In Disk Management – right click the C drive – select Extend Volume – follow the Wizard prompts.

You should end up with a larger C drive.

Tuesday, February 1, 2011

WinDbg & mdmp files

Often you see mdmp files in the Log folder from when SQL Server has an issue.

The below steps, which use WinDbg, may be able to assist you find the cause of the issue.

1. Download and install Microsoft .NET Framework 4 (Standalone Installer)

2. Download Windows SDK

3. Install the below option

4. Open WinDbg and add path to symbol files


5. Open the mdmp file

6. Run !analyze -v

The FAULTING_IP field shows the instruction pointer at the time of the fault and is the most probable cause for the issue.

7. Use lmvm to get more information on the module and then search the net for issues with the object e.g. for above you would use lmvm msvcrt


More information on the various outputs from ‘!analyze  -v’ can be found here 

Friday, January 28, 2011

GhostCleanupTask & Exception Error 1222

If you experience Exception Error 1222 Severity 16 State 18 with the SPID being less than 50 and the LoginName shows 'sa', then these may be generated by the GhostCleanupTask.

To verify if indeed the GhostCleanupTask is the cause, try add the EventClasses 'Exception', 'Lock:Timout' and 'SQLTransaction' as well as filter on the LoginName 'sa' in SQL Server Profiler.

As mentioned in this link the below will stop these errors:


To re-enable the GhostCleanupTask use:


Thursday, January 13, 2011

Extended Events Quick Test Code

Below is an example of creating, starting, viewing, stopping and dropping an extended event in SQL Server 2008.

-- Code to create an extended event
-- obtain events using SELECT * FROM sys.dm_xe_objects WHERE object_type = 'event' ORDER BY name
-- sqlserver below is used by comparing package_guid in sys.dm_xe_objects with name in SELECT * FROM sys.dm_xe_packages
ADD EVENT sqlserver.checkpoint_begin
-- obtain target for results by using SELECT * FROM sys.dm_xe_objects WHERE object_type = 'target'
-- package0 below is used by comparing package_guid in sys.dm_xe_objects with name in sys.dm_xe_packages
ADD TARGET package0.asynchronous_file_target
-- obtain SET permissions for this target from SELECT * FROM sys.dm_xe_object_columns WHERE object_name = asynchronous_file_target'
-- re below, look in the description column in sys.dm_xe_object_columns for more info
(SET filename = N'C:\sqlkevin_testlog.xel', metadatafile = N'C:\sqlkevin_testmetadata.xem')

-- Code to start an extended event

-- Code to see extended event
SELECT * FROM sys.server_event_sessions
SELECT * FROM sys.server_event_session_actions
SELECT * FROM sys.server_event_session_events
SELECT * FROM sys.server_event_session_fields
SELECT * FROM sys.server_event_session_targets
SELECT * FROM sys.dm_xe_sessions
SELECT * FROM sys.dm_xe_session_targets
SELECT * FROM sys.dm_xe_session_events
SELECT * FROM sys.dm_xe_session_object_columns

-- Create an Event

-- Read the output file
-- Note that if there is no data to check point you may have to retry the below select statement until a checkpoint occurs
SELECT * FROM sys.fn_xe_file_target_read_file
('C:\sqlkevin_testlog*.xel', 'C:\sqlkevin_testmetadata*.xem', null, null)

-- Code to stop an extended event

-- Code to remove an extended event

SQL Server and disk drives with 512 emulation (512e)

Interesting article about the 512e mode on hard disks when used with SQL Server.

Key extract from this link

'For SQL Server the best recommendation is to work with the hardware manufacture to make sure the 512e mode is disabled on drives that hold the SQL Server database and log files and that the Windows API is reporting 4K sector sizes.'

Further reading:

DBMIRROR_DBM_EVENT Wait Type Observation

In SQL Server 2008, if you change the mirrored databases from synchronous to asynchronous mode, the DBMIRROR_DBM_EVENT wait type does not appear to occur on the principal server.