How to move SQL database files to a different location

Sometimes when SQL database files (.mdf and .ldf files) grow too large, the easiest solution might be to move them to a different location so we can free up disk space quickly.

Here are the steps to move these files:

1. Turn off all services or processes that are using the database

2. Note down the file locations of the .mdf and ldf files (by default, they are located at SQL Server application directory e.g., \\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA

3. Launch SQL Management Studio

4. Login and access the SQL server instance has the database

5. Click and highlight the database

6. Open Query Editor

7. Run following SQL script (please change the database name and location to suit)

1 2 3 4 5 6 7 8 9

ALTER DATABASE AdventureWorks2014       MODIFY FILE ( NAME = AdventureWorks2014_Data,                     FILENAME = 'E:\New_location\AdventureWorks2014_Data.mdf');   GO   ALTER DATABASE AdventureWorks2014       MODIFY FILE ( NAME = AdventureWorks2014_Log,                     FILENAME = 'E:\New_location\AdventureWorks2014_Log.ldf');   GO

8. Run following SQL script to set the database to be offline

1 2	ALTER DATABASE AdventureWorks2014 SET OFFLINE;   GO

Note: if the database is too large and this statement may take a while to run. Or you can use the following statement instead.

1 2	ALTER DATABASE AdventureWorks2014 SET OFFLINE WITH ROLLBACK IMMEDIATE;   GO

9. Browse to the files location (we noted down in Step 2) and move the files to the new location

10. In Query Editor, run following SQL script to bring the database online again

1 2	ALTER DATABASE AdventureWorks2014 SET ONLINE;   GO

Note: if it fails with permission error, please change the user who runs the SQL services. You may do that by following the steps below via Services application.

1. Right click on the SQL Server service e.g., like the one highlighted in the screen shot below

2. Choose Properties option

3. Click Log On tab to change to the ideal user as required

OR

You may also grant access permission to the SQL Server service user on the new folder, where the database files are.

11. You may verify if the database is made online successfully by running the following statement

1 2 3 4

SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus FROM sys.master_files   WHERE database_id = DB_ID(N'AdventureWorks2014')   GO

This article is originally sourced from this one and you may find more details there. Thank you. 

SQL server database collation explained

When we installing a new SQL server instance, we have to choose a database collation. This will be the default collation for all databases that going to be created on this instance later on.

However, this default database collation can be overridden in following ways.

1. When creating a new database, we can specify a different collation for the database.

For example

CREATE DATABASE DbWithNewLanguage COLLATE SQL_Latin1_General_CP1_CI_AS

2. When creating a new table, we can specify different collations for individual column.

For example

CREAT TABLE MixLanguageTable (
ID INT IDENTITY,
EnglishName nvarchar(150) COLLATE Latin1_General_CS_AS,
GreekName nvarchar(150) COLLATE Greek_CS_AS_KS,
JapaneseName nvarhcar(150) COLLATE Japanese_90_CI_AS_KS_WS
)

3. When querying the tables, we can specify different collations.

For example

--EnglishName column in the example above is case sensitive. But we can change the collation so when we query the table on this column, we can set it to be case insensitive.

SELECT * FROM dbo.MixLanguageTable WHERE EnglishName = ‘test name’ COLLATE Latin1_General_CI_AS

Things to know about TRY...CATCH in T-SQL

With TRY…CATCH statement in T-SQL, I attempted to use it for a table name resolution the other day. However, I found the statement will not handle following errors, so it will not work if we are using this statement for checking the table name if it exists or not.

1. Compile errors, such as syntax errors, that prevent a batch from running.
2. Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

Meanwhile, when using SET XACT_ABORT ON setting, if a T-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. However, this will not help if we want to resolve the “table name does not exist” error.

Reference:

http://stackoverflow.com/questions/15015051/sql-try-catch-doesnt-catch-an-error-when-attempting-to-access-a-table-that-it

How to rename a database

I had to rename a database recently and googled the result. There are many usual resources and with ready-to-use examples. I will record them here for my own references and at mean time, I will add more details on what the commands do.

With renaming database, we need to set the database to single user mode first. Once the name is changed, we will set the multi-user mode back to the database.

This script does just that for SQL Server 2000:

ALTER DATABASE orig_db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC sp_renamedb 'orig_db_name', 'new_db_name'
ALTER DATABASE new_db_name SET MULTI_USER

SQL Server 2005+ should use this slightly different version:

ALTER DATABASE orig_db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE orig_db_name MODIFY NAME = new_db_name
ALTER DATABASE new_db_name SET MULTI_USER

First of all, I would like talk about the single user mode. If this mode is on, this means only one user can connect to the SQL server at one time. It will enable the user to change any server configuration or recover a damaged master database or other system database. Single user mode can also be set via the main plan configured on the SQL instance with Check Database Integrity task in the plan along with the “attempt to repair minor problems” option enabled. This will put the database in single user mode before executing CHECKDB.

However, sometimes, it is also can be a pain as if the connection is overtaken by another user, the actual user will not be able to logon and we need to seek other solution to break in e.g., kill the other process first, etc.

Meanwhile, the single user mode can also be stuck and you may not able to set the multi-user mode back on. See this post for a good example http://blogs.msdn.com/b/dfurman/archive/2012/01/20/getting-out-of-single-user-mode.aspx.

The next is the WITH ROLLBACK IMMEDIATE clause. It is a clause that we do not use usually but it is quite helpful. This simply means that if you would like to roll back immediately or not. There are another two options here.

Option 1: WITH ROLLBACK AFTER integer [SECONDS] – this means the transaction will still roll back if it encounters problem but within the seconds specified.

Option 2: WITH NO_WAIT – it simply means the transaction will not wait and if it encounters problem it will fail immediate without wait for roll back or commit.

NOLOCK hint in SQL Server

I have been reading some articles and get more understanding on NOLOCK hint in SQL server. A good article I found to understand NOLOCK hint is here. However, I will now briefly summarise what I understood here for quick references.

1. NOLOCK reads uncommitted data i.e., dirty data. This is the key point that needs to be considered before using this keyword. This will determine the accuracy of the final result. Learn more about this issue, please visit  http://msdn.microsoft.com/en-us/library/ms190805.aspx

2. NOLOCK will be ignored in the DELETE, UPDATE or INSERT statement but on the table to be updated/inserted. The NOLOCK syntax on UPDATE/DELETE /INSERT will be removed in the future release of SQL Server. See examples (from the MSDN forum) below:

Example 1:

UPDATE orders
SET no_of_orderlines = (
 SELECT COUNT(*)
 FROM orderlines WITH (NOLOCK)
 WHERE orderlines.order_id = orders.order_id
)

The NOLOCK hint in the above example is okay and will be honored.

Example 2:

UPDATE orders
SET no_of_orderlines = COUNT(*)
FROM orders   O WITH (NOLOCK)
JOIN orderlines OL ON OL.order_id = O.order_id

However, the NOLOCK in the above example will be ignored as it is on the table being updated.

3. NOLOCK equals to READUNCOMMITED and they are exchangeable. Have seen people suggest that the NOLOCK keyword should be removed in the future release but keep READUNCOMMITED keyword in use only.

4. Use isolation level as shown below to apply NOLOCK on a group of statements

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on 

SELECT * FROM Person.Contact WHERE ContactID < 20
UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID = 1
SELECT * FROM Person.Contact WHERE ContactID < 20  

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off

5. Use READPAST hint instead of NOLOCK hint will eliminate the dirty data. However, everything else will be the same as NOLOCK. See this article for more details.

What is TempDB?

Working with SQL Server for many years but in recent years, performance and tuning becomes a more and more popular topic at my work. At recent TechEd Australia, I have heard over so many sessions about these topics. Within the most mentioned, it is the TempDB. The TempDB is the critical part for SQL performance as it is the most read workplace in SQL Server and therefore, the configuration and disk IO would impact how SQL Server interact TempDB thus influence the performance. Thank you for these sessions and the presenters and I have gained a new level of knowledge.

I have collected some of the links here and hoping this can be help myself and others who are in the same boat keep learning.

The Basics 

What is TempDB?
http://technet.microsoft.com/en-us/library/ms178028.aspx

Compilation of SQL Server TempDB IO Best Practices
http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications
http://msdn.microsoft.com/en-us/library/ee410782.aspx

SQL Server Performance in the Cloud, Windows Azure

Windows Azure SQL Database and SQL Server -- Performance and Scalability Compared and Contrasted
http://msdn.microsoft.com/en-us/library/windowsazure/jj879332.aspx

Other Reading

SQL IaaS Basics

SQL Server in Windows Azure Virtual Machines
http://msdn.microsoft.com/en-us/library/windowsazure/jj823132.aspx

High Availability and Disaster Recovery for SQL Server in Windows Azure Virtual Machines
http://msdn.microsoft.com/en-us/library/jj870962.aspx

Window Azure Storage

SOSP Paper - Windows Azure Storage: A Highly Available Cloud Storage Service with Strong Consistency
http://blogs.msdn.com/b/windowsazurestorage/archive/2011/11/20/windows-azure-storage-a-highly-available-cloud-storage-service-with-strong-consistency.aspx

SQL Server Backup and Restore with Windows Azure Blob Storage Service
http://msdn.microsoft.com/en-us/library/jj919148.aspx

USENIX Best Paper Award: Erasure Coding in Windows Azure Storage
http://blogs.msdn.com/b/windowsazurestorage/archive/2012/06/13/usenix-best-paper-award-erasure-coding-in-windows-azure-storage.aspx