SQL Server : How to move or add Tempdb data and log files

Problem:

– TempDB has grown big and the current disk drive does not have enough space.
– Adding TempDB data file to another physical drive helps to improve performance, as they can be read simultaneously.

Solution : Here I have tried to explain how can we move tempdb data files from one place to another. Continue reading

MOVING SYSTEM DATABASE TO A DIFFERENT LOCATION IN SQL SERVER 2008 R2

Scenario:

A couple of days back, I was examining the configuration of one of our production DB servers. This server was involved in Merge Replication. All the database files on this server were dumped on one SAN drive of a subsequently small size. The replication cleanup job for some reasons was not able to delete the historical records from the system tables present in the distribution database due to which all of a sudden the drive which contained the data files as well as the log files got filled up hardly leaving any space for the SQL Server to start. After a detailed examination of the system, I came up with the following recommendations: Continue reading

How to Change Server Name in SQL server

How to use ‘SP_addserver’ and ‘SP_dropserver’

We often use to change server name for SQL server 2000, 2005 or 2008 instance when we migrate sql instance from one place to another or while changing the name of the computer running the SQL Server.

We have to perform following steps to ensure that SQL Server also uses the new updated servername. Continue reading

Script all jobs on SQL 2005 and 2008 Server

In SQL Server 2000, there is an option to script out all jobs in an instance. That option disappeared in SQL Server 2005.

You can still do it. Continue reading

Migrating SQL Server Databases – The DBA’s Checklist

It is a fact of life: SQL Server databases change homes. They move from one instance to another, from one version to the next, from old servers to new ones.  They move around as an organisation’s data grows, applications are enhanced or new versions of the database software are released. Continue reading