SQL Server : To ensure efficient tempdb operation (Microsoft Recommendations)

While working on a performance issue, I have seen that many a times we have Long IO Errors on Tempdb database, which indicates that we have scope of optimization for our tempdb Database.

As per Microsoft, there are a few Recommendation which we should Test on Benchmark Servers for sure before making any changes in production environment.

We could see the below options for Tempdb Optimization. These are always the best practices which often increase performance. As we know the Role of Tempdb has increased Drastically after the introduction of Sql Server 2005 so , why not Try them on Bench and implement on production.

To ensure efficient tempdb operation:

·         Create one tempdb file per physical CPU core. (But should ideally be used when we have data files on separate disk spindles).
  This reduces page free space (PFS) contention.

·         Pre-size the tempdb files, and make the files equal in size.

·         Do not rely on autogrow.

·         Use startup trace flag 1118.

For more information about this SQL Server trace flag, pls. go through this link  http://support.microsoft.com/kb/328551

Note: There’s only one tempdb per instance, and lots of things use it, so it’s often a performance bottleneck. You guys know that already. But when does a performance problem merit creating extra tempdb data files?

There should be only 8 data files even we have more than 8 processor as per Microsoft recommendation unless there is  in-memory contention

Hope it will help you.

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server : Copy backup files from one SQL Box to Another SQL Box using FTP

While working on a database migration project , we would require to transfer a huge backup file to new server which is running SQL Server 2012 but there was no trust enable between 2 different domain so normal copy operation was not allowed hence we thought to use FTP . I feel that there is always a need to FTP files to and from an SQL Server so I thought to make note of simple commands which will be useful in future.

FTP is an acronym for File Transfer Protocol. As the name suggests, FTP is used to transfer files between Servers/computers on a network.

Requirements:

1. FTP location name or IP @

2. User name & password ( In my case, I have used Anonymous FTP)

Anonymous FTP :

At times you may wish to copy files from a remote machine on which you do not have a loginname. This can be done using anonymous FTP. When the remote machine asks for your loginname, you should type in the word anonymous. Instead of a password, you should enter your own e-mail address. This allows the remote site to keep records of the anonymous FTP requests.

Note: However, you are only able to copy the files from the remote machine to your own local machine; you are not able to write on the remote machine or to delete any files there.

Step1:  Connect to server in which you want to copy the backup file ( Target server which is running SQL Server 2012)

Step2: Go to command prompt ( Run > cmd)

Step3 : C:\Users\Admin>FTP AB0010-SQL-FT

Connected to XXXXXXXXXXXXXXXXXXXXXXX

220 Microsoft FTP Service

User (AB0010-SQL-FT:(none)): anonymous

331 Anonymous access allowed, send identity (e-mail name) as password.

Password:

230 Anonymous user logged in.

ftp>

ftp> cd backup -- cd for change directory in remote server

250 CWD command successful.

ftp> pwd -- pwd means present working directory

257 "/backup" is current directory.

ftp> ls -- list all file and folder

ftp> lcd F:\  -- lcd to change directory in local server

Local directory now F:\.

ftp> lcd db_backup

Local directory now F:\DB_Backup.

ftp>

ftp> binary

200 Type set to I.

ftp> hash

Hash mark printing On  ftp: (2048 bytes/hash mark) .

ftp> get DB_Full_backup.bak

200 PORT command successful.

150 Opening BINARY mode data connection for DB_Full_backup.bak

ftp> bye

421 Timeout (120 seconds): closing control connection.

Last but not least, we can use put command instead get command to place backup file to source server.

Note: C:\Users\Admin>FTP -A AB0010-SQL-FT ( -A means Anonymous FTP)

Hope it will help you.

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server : Drop database in emergency mode

You'll always feel good when you learn/know new things and it is very much true with people who want to learn more and more.

Today, I was working with one corrupted database on SQL Server 2012 to recover it and I came to know one interesting thing (at least for me) that when you drop one database which is set in emergency mode then it only delete data file . Yes, It does not delete log file.

To validate the above fact, I have dropped corrupted database and tried to create same database again with old DB creation script it ended up with error that log file exist.

Second, I have created another database ( To reconfirm that it is not something related with corruption) and set to emergency mode and then dropped it but again it didn't deleted log file.

I'll share the more details when I'll complete assigned task.

Thanks/

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)