James' SQL Footprint - Blogs - SQLServerCentral

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Get Your Own Blog

If you would like to blog on SQLServerCentral.com then send an email to webmaster@sqlservercentral.com.

Contact the author

Please log in or register to contact the author of this blog

All Blogs

All Bloggers on SQL Server Central

Feeds

Subscribe to this blog

Archives for this blog

James' SQL Footprint

Love SQL Server, Love life.

Limit running Powershell script under specific path with AppLocker

Say if you have a management server or a script repository server, sometimes users save/test/run their script from different path, it is different to maintain the script version, and it is hard to determine which script can be removed or kept on the server.

here is a solution, you can save all…

0 comments, 101 reads

Posted in James' SQL Footprint on 28 April 2013

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.

0 comments, 638 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…

0 comments, 188 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…

0 comments, 421 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…

0 comments, 337 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…

0 comments, 171 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…

0 comments, 401 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.

0 comments, 272 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]…

0 comments, 240 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

0 comments, 805 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 =…

0 comments, 226 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 =…

0 comments, 1,068 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 =…

0 comments, 178 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 =…

0 comments, 131 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 =…

0 comments, 227 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 =…

0 comments, 181 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…

0 comments, 361 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),…

0 comments, 961 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…

0 comments, 681 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…

3 comments, 1,516 reads

Posted in James' SQL Footprint on 21 August 2012