Create a Custom View in Event Viewer to show Reboot Events

Scenario

A server reboot can explain related events and alarms triggered by monitoring systems, so it’s always handy to check for reboots first when troubleshooting.

With this in mind, you want a quick and easy way of finding out when the server last rebooted.

Solution

You need to create a Custom View in Event Viewer, that will only show shutdown and startup events, including reasons why the server rebooted.

Here’s the steps to create the Custom View:

[Read more…]

List all installed hotfixes

Run the following from a command prompt to display all installed hotfixes

wmic qfe get HotfixID,ServicePackInEffect,InstallDate,InstalledBy,InstalledOn > "c:\path\filename.txt"

or

wmic /output:d:\hotfixes.txt qfe list

SSPI handshake failed with error code 0x80090308, state 14

Problem

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]].

Solution

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:

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
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)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

Source

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”: http://calypsotesters.com/up-to-sustained-steady-state-ssd-performance
  • 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: www.intel.com/support/go/ssdtoolbox/index.htm
  • 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:
      • SORT_IN_TEMPDB
      • MAXDOP=1 (this keep writes sequential)
      • COMPRESSION
  • 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: http://www.sqlservercentral.com/articles/Corruption/96117/

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: http://www.sqlskills.com/blogs/paul/disaster-recovery-101-backing-up-the-tail-of-the-log/

DNS Scavenging Explained

I just read this excellent post explaining DNS Scavenging: http://blogs.technet.com/b/networking/archive/2008/03/19/don-t-be-afraid-of-dns-scavenging-just-be-patient.aspx

Well worth a read, as this subject can be a little confusing.

Best Windows Shortcuts and Tips

I use keyboard shortcuts whenever possible, so I’m going to keep my favourite ones here for reference.

Shortcuts

  • WIN + E = Opens Explorer window – details view is my fave.
  • CTRL + [ ‘+’ key on number pad] = Auto expands Explorer window columns to fit contents.
  • WIN + L = Locks computer.
  • WIN + D = Shows desktop. Another press maximises windows as they were previously.
  • WIN + R = Shows Run command box.
  • WIN + P = Shows options for multiple screens and projector.
  • WIN + X = Shows Windows Mobility Center.

Window Movement

  • Win + [Left Arrow] = Snaps window to left half of screen.
  • Win + [Right Arrow] = Snaps window to right half of screen.
  • Win + [Up Arrow] = Maximises window to full screen.
  • Win + Shift + [Left Arrow] = Moves window to the monitor on the left.
  • Win + Shift + [Right Arrow] = Moves window to the monitor on the right.

Tips

Jump into the commandline from a deep path in Explorer
Type ‘cmd’ into the address bar, or shift + right-click the folder and choose ‘open command window here’.

Make pinned Explorer shortcut open My Computer instead of Libraries

 

  1. Right-click the pinned Windows Explorer icon on your taskbar:
    Windows-Explorer-Shortcut
  2. Right-click the Windows Explorer again in the list, then select Properties:
    Windows-Explorer_002
  3. Change target to %windir%\explorer.exe Shell:MyComputerFolder

The trust relationship between this workstation and the primary domain failed

Scenario

You’ve just reverted to a previous snapshot using VMware vSphere 5.1, and the next time you try to login, you get the following error:

The trust relationship between this workstation and the primary domain failed

Solution

Unjoin then rejoin the computer to the domain.

You can also change some settings in GPO for computer passwords.

Configuring the Password Expiry

Contrary to user account password policy, the machine account password is managed by two options:

  • The change interval specified the time between forced changes of the machine account password.
  • The expiry defines whether machine account password expires at all.

Both options are configured through group policies under the following node:

Computer Configuration > Policies > Windows Settings > Security Settings > Local Policies > Security Options

  • Domain member: Disable machine account password changes
  • Domain member: Maximum machine account password age

Both options are not configured by default.

Best Practices in Virtual Environments

In virtualised environments, machine account password changes should be disabled. By preventing machines from changing this password automatically, domain synchronization issues are effectively remedied.

By default, a machine account password is changed every 30 days. When a virtual machine has been in use for more than 30 days and is then reset to an earlier state, the snapshot contains an outdated password causing the machine to loose its connection to the domain.

Source

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,
 cpu_time,
 ((total_elapsed_time / 1000/60 ) % 60) as 'Total Elapsed Mins'
 FROM sys .dm_exec_requests
 WHERE percent_complete > 0
 ORDER BY start_time DESC