SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

James' SQL Footprint

Love SQL Server, Love life.

Archives: January 2013

"Auto update statistics" option on tempdb

"Auto update statistics" option is enabled by default on tempdb, if it is disabled, you may get trouble in some case.Today when I tested script, I found the index created on the temp table didn't work because of "Auto update statistics" disabled.

here is the script.

USE [master]

Read more

0 comments, 2,095 reads

Posted in James' SQL Footprint on 28 January 2013

Got "Cannot generate SSPI context" error message after changing sql service account

Today I changed a sql server service startup account during testing, then when I tried to connect sql server with powershell, I got error message below:

The target principal name is incorrect.  Cannot generate SSPI context."

Here is the troubleshooting step I used
1. Rollback service startup account to old…

Read more

1 comments, 11,277 reads

Posted in James' SQL Footprint on 19 January 2013

Row of nonclustered index page

Below is the formula of calculating the row size of non-clustered index page

so unique non-clustered index created on unique clustered index use smallest space, while non-unique non-clustered index created on non-unique clustered index use largest space.

Read more

0 comments, 1,147 reads

Posted in James' SQL Footprint on 4 January 2013

Heap might take more space than clustered Index when inserting row

Sometimes when you insert row on heap, even if the page has enough free space, the new row can not be inserted into that page, and a new page will be created for the new row. Here is interesting example:

1. Create testing DB

use master

Read more

0 comments, 923 reads

Posted in James' SQL Footprint on 3 January 2013