Blog has moved

I've now moved to http://akawn.com/blog and will no longer be using or updating this SQL-Kevin blog.
Cheers
Kevin

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.

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 10.10.10.16
• The mirror instance is currently using 10.10.10.15
• 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 10.100.10.16 and the mirroring instance to use only 10.100.10.15 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 10.100.10.16. 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 10.100.10.15.

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:

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

) ON [PRIMARY]

GO

-- Insert first data row into table

INSERT INTO dbo.t1

            (c2)

VALUES      ('x')

GO 

-- Stats are only created once you add the where clause

SELECT *

FROM   dbo.t1

WHERE  c1 < 5 

-- Use the below to show when stats were last updated

SELECT OBJECT_NAME(OBJECT_ID)          AS TABLENAME,

       NAME                            AS STATS_NAME,

       STATS_DATE(OBJECT_ID, STATS_ID) AS STATISTICS_UPDATE_DATE

FROM   SYS.STATS

WHERE  OBJECT_NAME(OBJECT_ID) = 't1' 

-- Insert several rows to the table

INSERT INTO DBO.T1

            (C2)

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.

*/

SELECT *

FROM   DBO.T1

WHERE  C1 > 5 

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.

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 http://www.microsoft.com/downloads/en/details.aspx?FamilyID=45105d7f-8c6c-4666-a305-c8189062a0d0.

VirtualBox http://www.virtualbox.org 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: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=00d7c33c-93cc-4449-8d5e-3fd12d820b56

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 http://support.microsoft.com/kb/955963.

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.

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.

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) http://www.microsoft.com/downloads/en/confirmation.aspx?FamilyID=0A391ABD-25C1-4FC0-919F-B21F31AB88B7

2. Download Windows SDK http://msdn.microsoft.com/en-us/windows/bb980924

3. Install the below option

4. Open WinDbg and add path to symbol files

SRV*c:\symbols*http://msdl.microsoft.com/download/symbols

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 http://msdn.microsoft.com/en-us/library/ff560201%28v=vs.85%29.aspx 

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 http://support.microsoft.com/kb/920093 the below will stop these errors:

DBCC TRACEON (661,-1)

To re-enable the GhostCleanupTask use:

DBCC TRACEOFF (661,-1)

MAP Toolkit 5.5 is now available

MAP Toolkit 5.5 is now available:

http://technet.microsoft.com/en-us/library/bb977556.aspx

Theoretical device data speed limits

This handy link should help if you need to understand the theoretical data speed limits of various devices:

http://en.wikipedia.org/wiki/List_of_device_bit_rates

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
CREATE EVENT SESSION sqlkevin
ON SERVER
-- 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
ALTER EVENT SESSION sqlkevin
ON SERVER
STATE = START

-- 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
CHECKPOINT

-- 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
ALTER EVENT SESSION sqlkevin
ON SERVER
STATE = STOP

-- Code to remove an extended event
DROP EVENT SESSION sqlkevin
ON SERVER 

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

http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx

'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:

http://en.wikipedia.org/wiki/Advanced_Format

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.

SQL Server 7.0 Extended Support Ends Today

The Microsoft Support Lifecycle Policy FAQ can be found here:

http://support.microsoft.com/gp/lifepolicy

SQL Server Memory Manager Changes for SQL Server 2011

There appears to be some significant changes for the memory manager in SQL Server 2011

http://sqlblog.com/blogs/sqlos_team/archive/2011/01/04/sql-server-memory-manager-changes-in-denali.aspx