Tuesday 21 April 2015

SQL Server :: Moving Table to secondary data file or Different Filegroups

You may have already seen many posts on same topics as i experienced but i never got all of my answers in single post hence i thought to write this post.

Question: Is it online or offline operation?

Answer: It's online operation with few consideration. Also it's strongly recommended to do this during non-business hours if you are doing it in production and that too for huge tables.

You can follow below mentioned steps to move a table to a particular file group. A FILE-GROUP may contain one or more data files. If the FILE-GROUP contains more than one data file, SQL Server uses a proportional fill algorithm to make use of all data files in the FILE-GROUP. On the other hand, if the FILE-GROUP contains a single data file, then you can say that you have moved the table to a particular file.

To move a table to a particular file-group, you need to re-create the clustered index on the table. Recreating an index effectively moves the table to the new FILE-GROUP.


Database Name:[AdventureWorks2012]
Table Name: [Person].[Person]

Important Note:

You can not add or remove a file while a BACKUP statement is running.

Create New File-Group:

USE Master;
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP FG_Secondary
GO

Add file to Newly created File-Group:

ALTER DATABASE [AdventureWorks2012]
ADD FILE
(
NAME = FG_Secondary,
FILENAME = 'F:\mpdbs001\sqlappdb_V4RC11\AdventureWorks2012_Data2.ndf',
SIZE = 500MB,
MAXSIZE = 1000MB,
FILEGROWTH = 50MB
) TO FILEGROUP FG_Secondary;
GO

Select the table name and press Alt+ F1 and verify the data located on file-group column.
Currently it is on PRIMARY file-group.
























Run Below mentioned script to move table named [Person].[Person] to filegroup named FG_Secondary.

CREATE Unique CLUSTERED INDEX [PK_Person_BusinessEntityID]
 ON [Person].[Person]
 (
  [BusinessEntityID] ASC
  )
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON)
  ON FG_Secondary
GO

-----------------You may experience below mentioned error ----------

Error: Could not drop the primary key constraint 'PK_Person_BusinessEntityID' because the table has an XML or spatial index.
Solution : Script out and Drop the XML Index First and then drop the Primary Key Constraint

-----------------------------------------------------------------------

You can see in below screen nip that table is now reside in secondary file group.












Hope it will help you.

Warm Regards,
Chhavinath Mishra 
Sr. Specialist Database Administrator

Problem related to databases restore via RedGate :: Process terminated unexpectedly. Error code: -2139684860

Problem related to databases restore via RedGate :

While restoring database using Red Gate SQL Backup, was getting below error messages.

Error Msg:

4/20/2015 10:31:44 AM: Thread 3 error: 
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
4/20/2015 10:31:44 AM: 
4/20/2015 10:31:45 AM: SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
4/20/2015 10:31:45 AM: SQL error 3117: SQL error 3117: The log or differential backup cannot be restored because no files are ready to rollforward.

Solution:

You could also try increasing SQL Backup's timeout value to 60 seconds, by adding/modifying the VDITimeout value in the registry (HKEY_LOCAL_MACHINE\\SOFTWARE\\Red Gate\\SQL Backup\\BackupSettingsGlobal\\[InstanceName]). The VDITimeout value is a DWORD type.

It worked perfectly fine for me so thought to share with you all.

Thanks

P.S: https://forums.red-gate.com/viewtopic.php?t=16174


Warm Regards,
Chhavinath Mishra 
Sr. Specialist Database Administrator