Subscribe to this blog
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]
GO
ALTER…
here is the script.
USE [master]
GO
ALTER…
0 comments, 175 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…
The target principal name is incorrect. Cannot generate SSPI context."
Here is the troubleshooting step I used
1. Rollback service startup account to old…
0 comments, 408 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.
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.
0 comments, 274 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
go
CREATE DATABASE [test]…
1. Create testing DB
use master
go
CREATE DATABASE [test]…
0 comments, 247 reads
Posted in James' SQL Footprint on 3 January 2013