Move table to filegroup


Dealing with a database that has been around for some time may require some basic reconfiguration. In this case I had one (or more...) database that had some performance issues. One major cause was that the database only had one datafile (mdf) and the I/O was running serial. I could add some extra datafiles, but to benefit from more datafiles they should be of equal size. This would either require a massive extra amount of storage or a trade off.
I went for a solution with one new filegroup that had several datafiles for parallel I/O. To use the new filegroup I had to move data to the new filegroup. Usually by moving clustered indexes. The data was chosen with the developers and expert users on what was used most for central activities.

Prepare storage

Due to the IT infrastructure I work in I added some extra drives to the database server. On each drive I created a folder in the root to hold the datafiles.
To make the folders useful to the database service the service account was made owner of the folder. As we use Active Directory Managed Service Accounts (MSA) as service account for SQL Server services the service account name must be entered with a trailing dollar-sign.

Get index info

This statement I used to get some basic info about the indexes on a given table, so I could have an idea on what I was about to touch.
  [tables].[name] AS [table_name],
  [schemas].[name] AS [schema_name],
  [indexes].[name] AS [index_name],
  [idx_phys_stats].[page_count]*8 AS [index_size_in_kb],
  ([idx_phys_stats].[page_count]*8.0)/(1024*1024) AS [index_size_in_mb]
FROM [master].[sys].[dm_db_index_physical_stats](DB_ID('BoPind'),OBJECT_ID('STOCKFLOW'),NULL,NULL,'LIMITED') AS [idx_phys_stats] -- filter by parameter values
  INNER JOIN [sys].[tables] ON [idx_phys_stats].[object_id] = [tables].[object_id]
  INNER JOIN [sys].[schemas] ON [tables].[schema_id] = [schemas].[schema_id]
  INNER JOIN [sys].[indexes] ON [idx_phys_stats].[index_id] = [indexes].[index_id] AND [idx_phys_stats].[object_id] = [indexes].[object_id];

The statement is inspired by the answer by Mikael Eriksson to the question „T-SQL query to get Index fragmentation information“ on stackoverflow (StackExchange).

Create Filegroup

ALTER DATABASE [my_database] ADD FILEGROUP [standard];
NAME = N'standard_0',
FILENAME = N'D:\MSSQL.Data\my_database.standard_0.mdf',
SIZE = 42GB,
NAME = N'standard_1',
FILENAME = N'D:\MSSQL.Data\my_database.standard_1.ndf',
SIZE = 42GB,


Setting the filegroup as default

The existence of the filegroup can be verified immidetaly with
SELECT * FROM [my_database].[sys].[filegroups];

Move table

The data are in a clustered index – if such is defined on the table. This article „Move an Existing Index to a Different Filegroup“ has a central remark about using SQL Server Management Studio GUI to move a table to anorther filegroup:
You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio.
To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.
This makes sense as uniquenes is a central property of a primary key. Remember that a primary key does not require a clustered index and vice versa.
Move clustered index
Generating a initial script using Management Studio is a quick way to get a good start.
USE [my_database];
CREATE CLUSTERED INDEX [IX_1st_table] ON [dbo].[1st_table] (
  [1st_table_id] ASC,
  [1st_table_col2] ASC,
  [1st_table_col3] ASC

The ONLINE = OFF part is to be sure not to collide with KB2969896 on installations not fully patched.
Move non-clustered index
Generate initial script using Management Studio...
USE [my_database];
CREATE NONCLUSTERED INDEX [ix_2nd_table] ON [dbo].[2nd_table] (
  [2nd_table_code] ASC

This is also an example on moving a including index. But that does not make a signifikant difference.
Move unique (clustered) index
In this case I didn't get much help from Management Studio script generation. Most of the script is done by hand.
USE [my_database];
CREATE UNIQUE CLUSTERED INDEX [PK_3rd_table] ON [dbo].[3rd_table]([3rd_table_id])
ON [standard];

Move Primary Key index
Also inn this case I didn't get much help from Management Studio script generation.
Please notice that the activity is by several statements. To ensure the integrity of the data the statements are put in a transaction.
BEGIN TRANSACTION [move_pk_idx] WITH MARK 'Change 42';
USE [my_database];
ALTER TABLE [dbo].[4th_table] DROP CONSTRAINT [PK_4th_table];
ALTER TABLE [dbo].[my_table] ADD  CONSTRAINT [PK_4th_table] PRIMARY KEY NONCLUSTERED ([4th_table_id] ASC)
WITH (ONLINE = OFF) ON [standard];

This solution can be used for other constrains like foreign key.
I was inspired by this discussion on StachExchange: „Move Primary Key to Filegroup (SQL Server 2012)“.


When moving large amounts of data I wrapped the T-SQL statements in a timing structure like discribed in „Job history template using SQL Server Agent tokens“.


Code Region in Management Studio

In PowerShell ISE I am used to put the code in collapsable/expandable blocks with #region, but that is not direct possible in SQL Server Management Studio.

But this entry in stackoverflow gives a workaround: "sql server #region".
The precise details on how to configure Management Studio is given by Buck Woody in the elder blog post "Code Collapse and Expand Feature in SQL Server Management Studio 2008".
Actually Buck gives the whole workaround, but my search gave me stackoverflow first...

A short example:
BEGIN -- Region Title
END -- Region Title

If you put a space or two last in the region title the region collapse in a visual pleasant way.
I repeat the region title at the end of the block. Sometimes there are more than one screen full in one block, and this way I am reminded about the context of the END-line.

And it works fine in Management Studio 2014, also with SQL Prompt installed.


CheckDB error because snapshot file already exists

A ordinary Maintenance Plan with a ordinary Check Database Integrity task failed with this error in the Maintenance Plan log:
A database snapshot cannot be created because it failed to start.
Cannot create file 'D:\\Data\\MyDatabase_Data.mdf_MSSQL_DBCC75' because it already exists. Change the file path or the file name, and retry the operation.

The error text indicates that the file exists which is true.
Also the text indicates that the file path or name can be changed. This is not true in this case as one can't define the physical database snapshot when running a Check Database Integrity task. This is why it can't be defined when running DBCC CHECKDB statement.
In the SQL Server Errorlog the failure is also logged:
2015-08-16 03:11:19.77 spid42      Error: 5170, Severity: 16, State: 1.
2015-08-16 03:11:19.77 spid42      Cannot create file 'D:\Data\MyDatabase_Data.mdf_MSSQL_DBCC75' because it already exists. Change the file path or the file name, and retry the operation.

As the information is similar the failure is confirmed on file details.

In this case the server had a hard shutdown (ups!!!) which caused the CheckDB failure.
To confirm if the database snapshot on the file is still active this statement:
FROM [master].[sys].[databases]
  [databases].[name] = 'MyDatabase' AND
  [databases].[source_database_id] IS NOT NULL;

The result is empty which is confirmed by the message:
(0 row(s) affected)

The snapshot file can be inspected on the metadata through PowerShell using the CmdLet Get-Item:
Get-Item -LiteralPath 'E:\Data\MyDatabase_Data.mdf_MSSQL_DBCC75'

As I found no other reference on this error I preserved the database snapshot file by moving it to another location.

Testing the solution is quite simple as I only have to run a CheckDB on the database:
DBCC CHECKDB ('MyDatabase');
As planned the run is good:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Attach database

I had to get some data from a old database, where the server is recycled. But at that time I was present enough to get a copy of the data files of the database (mdf & ndf).
The quick solution is to attach the database on my workstation where I have a SQL Server Developer Edition running. This statement should do the trick:
  (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\Repository.mdf'),
  (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\Repository_2.ndf')

But when I tried to attach the database I got a error on file access.
Msg 5120, Level 16, State 101, Line nn
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\Repository.mdf". Operating system error 5: "5(Access denied.)".

A quick look on the other SQL Server files in the same folder indicated that the service account should be owner of the files. The error message is on the first file in the statement, but this goes on all files for the database.
This can be done in Windows Explorer through the properties of the file (right-click > properties). On the Security tab you click the Advanced button. Then you get another window named Advanced Security Settings for ... In this windows Owner tab you click the Edit button. Now you might have to log on as you are required to be local administrator.
Click the button Other users or groups to enter the service account. This is not straight forward if you are using a virtual service account. First you have to pick the computer as location. Then you can limit the object type to Builtin service accounts.
Finally entering the name of the service account is a little tricky - in my case I entered "nt service\mssql$mssql2014". Notice the space after "nt"!
I am working on the named instance MSSQL2014 which gives the name of the account after the backslash.
Before clicking the OK button I do recommend that you verify the account name by clicking Check names. If the name is changed to capitals, the "nt service\" is removed and the name is underlined it is OK.

The service account is (default) a Virtual Service Account. This is a local edition of a Managed Service Account. To get the name of the service account you can look in the Server Facet of the instance with Management Studio.

But still I got error on file access.
Msg 5120, Level 16, State 101, Line nn
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\Repository.mdf". Operating system error 5: "5(Access denied.)".

I usually work on installation where DBAs are isolated from other admins. In this case the database files are moved/copied around using one account and the attach statement is executed with another account. When I then added the user of SQL Server Management Studio (SSMS) who executes the statement to the files with Full Control permissions everything went smooth.
I first tried to limit the rights for the SSMS user the Read permissions, but that didn't work as I got this error
Msg 1813, Level 16, State 2, Line nn
Could not open new database 'Repository'. CREATE DATABASE is aborted.

In this case I did not have the transaction log file (ldf), but I really didn't need it. This is why the statement is with FOR ATTACH_REBUILD_LOG. There are some issues you have to consider like a clean shutdown of the database. Please take a look at the documentation for CREATE DATABASE.
The original transaction log file could not be found by the instance, as expected. This generates a failure message like
File activation failure. The physical file name "H:\MSSQL\Data\Repository_log.ldf" may be incorrect.
But right after the new transaction log file is created automatic
New log file 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\Repository_old_log.ldf' was created.


Leap Second and Day

Leap Second

A positive leap second 2015-06-30 has been announced by International Earth Rotation & Reference Systems Service:
SQL Server gets the time from Windows, who knows how to handle a leap second (KB909614).
The leap second is „smeared“ so that the timestamps are not affected.
This gives that the leap second can not be registered.

In T-SQL it will look like this
DECLARE @before_leap_second DATETIME = '2015-06-30 23:59:59'
SELECT @before_leap_second AS [normal_time];

2015-06-30 23:59:59.000

DECLARE @leap_second DATETIME = '2015-06-30 23:59:60';
SELECT @leap_second AS [leap_second];

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


Leap Day

A positive leap day is more precise defined. This gives that it can be defined in a data type, and calculations can be done correct. The Leap Day is nicely handled by T-SQL like this
DECLARE @leap_day DATETIME = '2012-02-29';
SELECT @leap_day AS [leap_second];

2012-02-29 00:00:00.000

Also a more tricky Leap Day is handled correct by T-SQL like this
DECLARE @leap_day DATETIME = '2000-02-29';
SELECT @leap_day AS [leap_day];

2000-02-29 00:00:00.000

An attempt to use a wrong Leap Day will give an error like above
DECLARE @not_leap_day DATETIME = '2013-02-29'
SELECT @not_leap_day AS [not_leap_day];

Msg 242, Level 16, State 3, Line 11
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.



2015-01-09 Blog entry about Leap Second.
2015-02-10 Blog entry added section about Leap Year.