Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James' SQL Footprint

Love SQL Server, Love life.

Set DAC port with a specific number

Usually named instance can uses dynamic port, if firewall setup on the server, you can use SQL Server Configuration Manager to assign a specific port to SQL Server instance. However, there is no UI to reconfigure the DAC port, you need to use regedit.exe to configure a specific port number.

Read more

0 comments, 1,357 reads

Posted in James' SQL Footprint on 10 April 2013

Use delegated session configuration in Powershell 3.0

Powershell 3.0 has many new feature, and they are useful in some circumstance.

let's say User A log in server A, and he wants to access the resource on Server B, however User A doesn't have permission to access the resource, User B has resource access permission, so User A…

Read more

0 comments, 444 reads

Posted in James' SQL Footprint on 5 April 2013

Compression Backup with BUFFERCOUNT parameter

If you want to make you backup faster, you can try BUFFERCOUNT parameter with compression backup.

From Book Online description:

BUFFERCOUNT = { buffercount | @buffercount_variable }
Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers…

Read more

0 comments, 917 reads

Posted in James' SQL Footprint on 3 April 2013

Get Perfmon data with Powershell

Sometimes you just want to have a look at the system performance, or you feel tired with log on the server, open the perfmon and configure the performance counter, perhaps you want to run a simple command to get the performance data from remote server, here we have a convenient way check the system performance counter…

Read more

1 comments, 887 reads

Posted in James' SQL Footprint on 21 February 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…

Read more

0 comments, 530 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, 2,929 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, 475 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]…

Read more

0 comments, 478 reads

Posted in James' SQL Footprint on 3 January 2013

Test Hadoop cluster on vmware

SQL Server MVP Jeremiah Peschka posted 2 articles about Hadoop, which makes me be interested on the nosql skill.

I don't have much knowledge on Nosql and Linux system, so I am going to setup a testing environment on my laptop in holidays

1. download CentOS Linux setup iso file
http://www.centos.org/

2. download java jdk 1.6

Read more

2 comments, 1,440 reads

Posted in James' SQL Footprint on 29 December 2012

Explore file physical structure - IAM


1. Create sample db

use master
go
CREATE DATABASE [test] ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test_log.ldf' , SIZE = 1024KB , FILEGROWTH =…

Read more

0 comments, 455 reads

Posted in James' SQL Footprint on 25 November 2012

Explore file physical structure - Delete


1. Create sample db

use master
go
CREATE DATABASE [test] ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test_log.ldf' , SIZE = 1024KB , FILEGROWTH =…

Read more

0 comments, 1,272 reads

Posted in James' SQL Footprint on 21 November 2012

Explore file physical structure - Insert Row - Part 2


1. Create sample db
use master
go
CREATE DATABASE [test] ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test_log.ldf' , SIZE = 1024KB , FILEGROWTH =…

Read more

0 comments, 291 reads

Posted in James' SQL Footprint on 20 November 2012

Explore file physical structure - Insert Row - part 1


1. Create sample db
use master
go
CREATE DATABASE [test] ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test_log.ldf' , SIZE = 1024KB , FILEGROWTH =…

Read more

0 comments, 261 reads

Posted in James' SQL Footprint on 20 November 2012

Explore file physical structure - variable length row

1. Create sample db
use master
go
CREATE DATABASE [test] ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test_log.ldf' , SIZE = 1024KB , FILEGROWTH =…

Read more

0 comments, 424 reads

Posted in James' SQL Footprint on 20 November 2012

Explore file physical structure - fixed length row

1. Create sample db


use master
go
CREATE DATABASE [test] ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test_log.ldf' , SIZE = 1024KB , FILEGROWTH =…

Read more

1 comments, 359 reads

Posted in James' SQL Footprint on 20 November 2012

Monitor Blocking with Extented Events in SQL 2012

1. Set the "blocked process threshold (s)"
sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 10 ;
GO
RECONFIGURE ;
GO

here we set the threshold to 10 seconds, which generating a blocked process report for each task that is blocked.

2. Create Extented…

Read more

0 comments, 811 reads

Posted in James' SQL Footprint on 29 October 2012

Change file logical name for mirror database

Today I encountered a interesting issue when rename logical name for mirror database.

We have a database which has been setup mirror on it, and we need to change the logical file name for data and log file. As usually, I rename the logical name by SSMS on the primary server(SQL01),…

Read more

0 comments, 1,286 reads

Posted in James' SQL Footprint on 9 September 2012

Install product update during SQL 2012 installation

SQL Server 2012 setup program has 2 new installation parameter
1. /UpdateEnabled:
Specify whether SQL Server setup should discover and include product updates. The valid values are True and False or 1 and 0. By default, SQL Server setup will include updates that are found.

2. /UpdateSource
Specify the location…

Read more

0 comments, 1,118 reads

Posted in James' SQL Footprint on 30 August 2012

Restore Database In SQL 2012

In the post below I mentioned a store procedure which  can generate the database restore script based on the msdb history table
http://jamessql.blogspot.com/2012/04/generate-restore-script-automatically.html


Obviously, I had not tried restore in SQL Server 2012 Management Studio at that time. Now we have SQL 2012, which can provide more powerful ways to…

Read more

4 comments, 3,975 reads

Posted in James' SQL Footprint on 21 August 2012

Monitor Deadlock in SQL 2012

Do you still use trace flag 1204 and 1222 to monitor Deadlock? or using profile to capture deadlock? Now we are in SQL Server 2012!  One of the biggest improvement of SQL 2012 is Extended Events.

Extended Events can replace SQL Profiler, and it is more powerful with less performance…

Read more

0 comments, 5,152 reads

Posted in James' SQL Footprint on 12 August 2012

Newer posts

Older posts