SSPI handshake failed with error code 0x80090308, state 14


You cannot login to SQL Server, and see the following error in the logs:

SSPI handshake failed with error code 0x80090308, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: [IP ADDRESS]].


There can be several reasons for this error, so rather than listing them all, read the following useful links:

--Show details of all connections
select auth_scheme, * from sys.dm_exec_connections

List all SQL Server backups for the previous 7 days

The following script will list all backups for every database for the previous 7 days, including backup type:

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
END AS backup_type,
msdb.dbo.backupmediafamily.physical_device_name, AS backupset_name,
FROM   msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)


Using SSD Drives for SQL Server

I’ve  just watched a great video explaining How to Optimally Use SQL Server with SSD’s Without Burning Them Out.

Here’s some notes:

  • 1 x SSD can give 4-5x the IOPS a 15k HDD gives. (I thought it would be more than this. Maybe because the video is from SQL Server Days 2011, and is a little out of date.)
  • The file allocation unit size (cluster size) recommended for SQL Server is 64 KB; this changes things when comparing IOPS stats from SAN. Ask for “Steady State IOPS”:
  • 4KB pages are standard on SSD
    • Smallest read/write structure.
  • Pages are grouped into blocks (128).
    • 512KB per block.
    • Smallest erasable structure
  • SSD write performance will degrade over time as it gets full.
  • SSD read performance stays roughly the same.
  • TRIM will help slow this down:
    • Check your OS supports TRIM by running this command
    • fsutil behavior query DisableDeleteNotify (should return 0)
    • If not 0, try to set it with this command:
    • fsutil behavior set DisableDeleteNotify 0
  • DO NOT defrag a SSD.
  • SSD typically don’t handle random writes very well.
  • Always use Enterprise SSDs, as they can last 10x longer than Prosumer SSDs.
  • Useful SMART tool is SSD Toolbox:
  • DO NOT use SSDs for TempDB, and maybe not even Log.
  • Base Solution – Tiered Infrastructure:
    • Eliminate bottlenecks:
      • Think 6Gbps and higher
      • Multilink
    • Use good controllers with Wear Levelling
    • Look at IOPS and response rates in Steady State
  • Problems with TempDB:
    • TempDB involves heavy Random writes.
    • Most used database
    • Enormous amounts of reads and erases
    • Kill (standard) SSDs extremely fast!
  • Log Files
    • Consider using Enterprise SSDs only, with RAID1 (mirrored).
    • Be careful with autogrowth.
    • Log files have more sequential reads and writes; more suited to HDDs.
  • Indexes
    • This is the best candidate for using SSD.
    • Place index filegroup on SSD.
    • Rebuild options that matter for SSDs:
      • MAXDOP=1 (this keep writes sequential)
  • Problem drive order of failure:
    • TempDB
    • Log
    • Data
  • Data Writes:
    • Logically view this as log entries being written and then used as instructions to write data pages out to the disk by an asynchronous process.
    • Heavily random access pattern; great for SSD.
  • Conclusion:
    • SSDs are not so good for sequential writes
    • SSDs are great for Random reads; indexes and Data files.
    • IO Volume matters, not just IOPS.

Recover a Corrupt Database Using SQL Server

Here’s an informative post explaining some options when recovering a corrupt database using SQL Server:

Don’t forget to backup the tail of the log!

Here’s the process, including a few extra tips if the original server is unavailable:

Find remaining time left for Backup or Restore using SQL Server

Execute the following query:

SELECT r. session_id,r .command, CONVERT(NUMERIC (6, 2),r .percent_complete)
 AS [Percent Complete],CONVERT( VARCHAR(20 ),DATEADD( ms,r .estimated_completion_time, GetDate()),20 ) AS [ETA Completion Time],
 CONVERT(NUMERIC (6, 2),r .total_elapsed_time/ 1000.0/60.0 ) AS [Elapsed Min],
 CONVERT(NUMERIC (6, 2),r .estimated_completion_time/ 1000.0/60.0 ) AS [ETA Min],
 CONVERT(NUMERIC (6, 2),r .estimated_completion_time/ 1000.0/60.0 /60.0) AS [ETA Hours],
 CONVERT(VARCHAR (100),( SELECT SUBSTRING (text, r.statement_start_offset /2,
 CASE WHEN r.statement_end_offset = - 1 THEN 1000 ELSE (r. statement_end_offset-r .statement_start_offset)/ 2 END)
 FROM sys .dm_exec_sql_text( sql_handle)))
 FROM sys .dm_exec_requests r WHERE command IN ('RESTORE DATABASE' ,'BACKUP DATABASE')

or this one:

SELECT percent_complete, start_time, status, command , estimated_completion_time as est_complete,
 (estimated_completion_time / 1000/60 /60/ 24) as Days ,
 ((estimated_completion_time / 1000/60 /60) % 24 ) as Hours,
 ((estimated_completion_time / 1000/60 ) % 60) as Mins,
 ((estimated_completion_time / 1000) % 60) as Secs,
 ((total_elapsed_time / 1000/60 ) % 60) as 'Total Elapsed Mins'
 FROM sys .dm_exec_requests
 WHERE percent_complete > 0
 ORDER BY start_time DESC

Check last database restore date/time using SQL Server

Use one of the following scripts.

The first script is quick and simple:

FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = DB_NAME()
ORDER BY restore_date DESC

The second script also shows the backup file used during the restore:

SELECT [rs].[destination_database_name],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC


Get Current Database Names and IDs in SQL Server

-- Get Current DatabaseID
SELECT DB_ID() DatabaseID;

-- Get Current DatabaseName
SELECT DB_NAME() DatabaseName;

-- Get DatabaseName from DatabaseID

-- Get DatabaseID from DatabaseName
SELECT DB_ID('tempdb') DatabaseName;

-- Get all DatabaseName and DBID
SELECT name,database_id
FROM sys.databases;


Moving tempdb file location for SQL Server 2008 R2


You’ve ran out of disk space on the drive where the tempdb files are located.


You need to move the tempdb files to a new location with sufficient disk space.

  1. Execute the following query:
    USE master; GO
    MODIFY FILE (NAME = tempdev, FILENAME = '[new_path]\tempdb.mdf');
    MODIFY FILE (NAME = templog, FILENAME = '[new_path]\templog.ldf');
  2. Stop and restart the instance of SQL Server.
  3. Once the service has been restarted, verify the changes using this query:
    sp_helpdb tempdb


Useful Perfmon Counters for SQL Server

I recently came across a useful poster showing Perfmon Counters for SQL Server.

You can download the PDF file here: SQLServer-Perfmonance-Poster

Setting Up Windows Service Accounts for SQL Server

A useful link explaining the permissions for built-in system accounts and recommended usage for SQL Server 2008:

More recent information on Windows Service Account and Permissions for SQL Server 2012 can be found here: