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

James' SQL Footprint

Love SQL Server, Love life.

Error : Property Size is not available for Database "[tempdb]"

Today when I clicked Properties of tempdb in SSMS, I got the error message below:

At first I thought the database has size problem, so I extended the file size of tempdb, but I was still not able to see the properties of tempdb, and I confirmed that the there…

Read more

4 comments, 2,662 reads

Posted in James' SQL Footprint on 23 January 2014

Use application lock to synchronize T-SQL code

Application lock can be used to synchronize T-SQL code, you can control if the session is able to run the t-sql code at same time, it is like the lock keyword in C#.

1. Get lock
you can get application lock with store procedure sp_getapplock, which must be run from within…

Read more

2 comments, 4,801 reads

Posted in James' SQL Footprint on 15 November 2013

log shipping restore job failed on secondary server

Several Situation will cause the log shipping restore job failed on secondary server

1. Restore job is not able to access the transaction log backup file.

    a) Other process is accessing the backup file, and lock the file, so restore job can not access it at the same time. 

Read more

3 comments, 8,343 reads

Posted in James' SQL Footprint on 9 November 2013

"Failover Partner" keyword in connection string - Q&A

1. How to connect client to a Database Mirroring Session with failover aware

A: add FailoverPartner keyword in the connection string, for instance:
"Server=sq01;Failover Partner=sq02; Database=mydb; Network=dbmssocn;Trusted_Connection=yes"

2. why I get timeout error more frequently when I connect to mirrored database with keywords "Failover Partner"?

A: You probably encounter a…

Read more

1 comments, 7,370 reads

Posted in James' SQL Footprint on 8 November 2013

One Database Mirror disconnected case

Today I got a database mirror case, I was told the all databases were in disconnected\in recovery status on both primary and mirror server, it is a dev environment.

Here just share my troubleshooting steps:

1. First, I login primary and mirror server. I got the same database status on…

Read more

0 comments, 985 reads

Posted in James' SQL Footprint on 6 November 2013

is count(1) faster than count(*)?

Recently I saw a post regarding the count(1) and count(*)

the post said "
You know that doing COUNT(*) on a table with a lot of columns and a lot of rows can take a lot of time and memory
so use count(1) instead of count(*).  Is…

Read more

3 comments, 5,677 reads

Posted in James' SQL Footprint on 20 October 2013

Enable Instant File Initialization to accelerate database restore

Today my colleague come to me and ask me why her database restore query was hang. She was going to restore a database with 200GB data file and 11GB log file.

She had run the restore command for about 15 minutes, but the restore process is still in 0%, it…

Read more

0 comments, 1,178 reads

Posted in James' SQL Footprint on 1 October 2013

List all mapping users of SQL login account

Find a useful system store procedure to list all mapping user of SQL login account.

sp_msloginmappings @Loginname , @Flags

@loginname: the login account name, If loginname is not specified, results are returned for the login accounts
@Flags: value can be 0 and 1, by default 0. 0 means show mapping user in…

Read more

0 comments, 8,774 reads

Posted in James' SQL Footprint on 24 September 2013

Get SQL Command Compile time

It not only tell you the compile time of sql command, but also show you the execution times. when you enable it, all the sql command in the current session will be impacted, so it is a good tool to troubleshooting  only 1 sql command…

Read more

0 comments, 6,702 reads

Posted in James' SQL Footprint on 19 September 2013

SQL Server 2012 Express installation failed issue

I got an strange error when installing SQL Server 2012 Express

Window Server 2008 R2 Enterprise + SP1
Microsoft Visual Studio 2012 installed
.Net 4.5 framework Installed

When I double clicked SQLEXPRWT_x64_ENU.exe, it just disappeared immediately, no setup window popped up. then I tried
1. unzip the installation code 

Read more

2 comments, 1,726 reads

Posted in James' SQL Footprint on 7 August 2013

Alwayson availability groups failover monitoring - Part 1

There are a lot of ways to monitor AG, sql server errorlog, window event log, cluster log......, here I list another way to check and monitor AG state with the log files below:

1. AlwaysOn Health Diagnostics Log
These files in the SQL Server Log directory have the following format:…

Read more

2 comments, 5,919 reads

Posted in James' SQL Footprint on 4 August 2013

Gap issue in SQL Server 2012 IDENTITY column

Found a gap issue when using IDENTITY column in SQL Server 2012, every time I restarted sql server service, the current identity number will jump to 1000. here is the repro script

CREATE_DATE datetime);


GO 10


Read more

0 comments, 3,623 reads

Posted in James' SQL Footprint on 23 July 2013

Find out orphan database file on disk

Sometimes you detach databases,  or you delete a database which is in restoring status,  the data and log file will not be removed from local disk. if you forget the files,  It might cause issues
1. The files waste disk space
2. If you restore the database back to the…

Read more

4 comments, 1,823 reads

Posted in James' SQL Footprint on 9 July 2013

trace flag for backup and restore operation

1. 3004
show the internal backup/restore operation for every step.
dbcc traceon(3004,3605,-1)

3605 means output the message to errorlog. you can use 3604 to direct the output to client, but it doesn't have the time info which 3605 has. The output looks like below:
LogDate      …

Read more

1 comments, 2,156 reads

Posted in James' SQL Footprint on 2 July 2013

SQL Server 2012 AlwaysOn Availability Groups setup scripts - Part 3

4. Setup 2 nodes alwayson availability group 
    a) Enable alwayson on both 2 nodes
enable-sqlalwayson -Path "SQLSERVER:\SQL\SQL2012-01\DEFAULT"
enable-sqlalwayson -Path "SQLSERVER:\SQL\SQL2012-02\DEFAULT"

    b) Restore database "MYHA" to secondary server "SQL2012-02"
Backup-SqlDatabase -Database "MYHA" -ServerInstance "SQL2012-01" -BackupAction Database -CompressionOption On -backupfile "\\WIN-JIASUMAU0DF\Backup\MYHA_FULL.bak"

Restore-SqlDatabase -Database MYHA -ServerInstance sql2012-02 -BackupFile "\\WIN-JIASUMAU0DF\Backup\MYHA_FULL.bak"…

Read more

0 comments, 2,214 reads

Posted in James' SQL Footprint on 25 June 2013

SQL Server 2012 AlwaysOn Availability Groups setup scripts - Part 2

3. Install SQL Instance
   a) Configure the firewall setting on both 2 nodes.
echo Default Instance
netsh advfirewall firewall add rule name="SQLServer" dir=in action=allow protocol=TCP localport=1433 profile=DOMAIN

echo Dedicated Admin Connection
netsh advfirewall firewall add rule name="SQL DAC" dir=in action=allow protocol=TCP localport=1434 profile=DOMAIN

echo SQL Browser Service 
netsh advfirewall…

Read more

0 comments, 2,008 reads

Posted in James' SQL Footprint on 25 June 2013

SQL Server 2012 AlwaysOn Availability Groups setup scripts - Part 1

Here are some scripts which can be used for setting up 2 nodes AlwaysOn Availability Groups, you can make your own automation script based on them.

Environment :
Name IP Address OS Description
SQL2012-01 Win2012 Primary
SQL2012-02 Win2012 Secondary
SQL2012HA N/A AG Listener
SQLClUSTER N/A Cluster Name

Read more

0 comments, 2,328 reads

Posted in James' SQL Footprint on 25 June 2013

Huge collection of Free Microsoft eBooks

I must share it, there are many useful resource




Thank you , Eric

Read more

1 comments, 1,858 reads

Posted in James' SQL Footprint on 21 June 2013

use Table Variables in transaction

when using table variable, please note table variable doesn't support transaction rollback. here is the words from BOL

Because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.

let's do a testing:
DECLARE @MyTableVar table(

Read more

0 comments, 2,271 reads

Posted in James' SQL Footprint on 20 June 2013

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…

Read more

3 comments, 1,337 reads

Posted in James' SQL Footprint on 28 April 2013

Older posts