none
Table's data is transfered from old to new location but old drive did not drop it's size.

    Question

  • We had one large table in frimary file and we moved that to other drive where we have created secondary file group for it.

    But after creating that table to secondary file with diffrent drive still we have same space in old drive.

    I checked that table is moved secondary file group but old drive should drop the size as per my concern.

    Table structure was:

    CREATE TABLE [dbo].[SystemHistory](
     [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
     [PrimaryObjectId] [bigint] NULL,
     [ObjectType] [varchar](1000) NULL,
     [EntityType] [int] NULL,
     [OrigionalXml] [varchar](max) NULL,
     [ChangedXml] [varchar](max) NULL,
     [CreatedDate] [datetime] NOT NULL,
     [UpdatedBy] [varchar](50) NULL,
     [UpdatedById] [int] NULL,
     [UniqueId] [bigint] NULL,
     [MigratedFrom] [int] NULL,
     [ModifiedXml] [varchar](max) NULL,
     CONSTRAINT [PK_SystemHistory] PRIMARY KEY NONCLUSTERED
    (
     [Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 50) ON [PRIMARY]
    ) ON [PRIMARY]

     Table has full text index on ModifiedXml column.

    Please do the needfull ...bcz database size was 220 GB now 440 bc we added 220 gb For secondary file with secondary disk.


    • Edited by Jamanu Dora Wednesday, February 08, 2012 10:45 AM
    Wednesday, February 08, 2012 10:44 AM

Answers

  • Hi,

    Reserved disk space will not be freed up automtically. It can be done by DBCC SHRINKFILE, which is not recommended due to some sideeffects (eg: index fragmentation)

    You are right, MOVE TO is not valid for nonclustered indexes.

    just a recap: if you want to move the data from a filegroup to another, you can  create a tmp_tablename table with the same schema as the original on the new filegroup. then copy the data and drop the old table + rename the new one. finally, run DBCC SHRINKFILE.

    Here is a sample script:

    USE [master]
    GO
    ALTER DATABASE [TestTableFGChg] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    DROP DATABASE [TestTableFGChg]
    GO
    
    CREATE DATABASE [TestTableFGChg]
    GO
    
    ALTER DATABASE [TestTableFGChg] ADD FILEGROUP [SECONDARY]
    GO
    ALTER DATABASE [TestTableFGChg] ADD FILE ( NAME = N'2ND', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\DATA\2ND.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [SECONDARY]
    GO
    
    USE [TestTableFGChg]
    GO
    
    CREATE TABLE [dbo].[SystemHistory](
     [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
     [PrimaryObjectId] [bigint] NULL,
     [ObjectType] [varchar](1000) NULL,
     [EntityType] [int] NULL,
     [OrigionalXml] [varchar](max) NULL,
     [ChangedXml] [varchar](max) NULL,
     [CreatedDate] [datetime] NOT NULL,
     [UpdatedBy] [varchar](50) NULL,
     [UpdatedById] [int] NULL,
     [UniqueId] [bigint] NULL,
     [MigratedFrom] [int] NULL,
     [ModifiedXml] [varchar](max) NULL,
     CONSTRAINT [PK_SystemHistory] PRIMARY KEY NONCLUSTERED
    (
     [Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 50) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    --add some sample data data
    INSERT INTO [dbo].[SystemHistory]
               ([Id]
               ,[PrimaryObjectId]
               ,[ObjectType]
               ,[EntityType]
               ,[OrigionalXml]
               ,[ChangedXml]
               ,[CreatedDate]
               ,[UpdatedBy]
               ,[UpdatedById]
               ,[UniqueId]
               ,[MigratedFrom]
               ,[ModifiedXml])
         VALUES
               (
    		NEWID()
               ,1
               ,'objecttype'
               ,1
               ,'xml'
               ,'xml'
               ,current_timestamp
               ,'someone'
               ,5
               ,55
               ,5
               ,'mod_xml')
    GO 10000
    
    GO
    
    --create the temp table
    CREATE TABLE [dbo].[tmp_SystemHistory](
     [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
     [PrimaryObjectId] [bigint] NULL,
     [ObjectType] [varchar](1000) NULL,
     [EntityType] [int] NULL,
     [OrigionalXml] [varchar](max) NULL,
     [ChangedXml] [varchar](max) NULL,
     [CreatedDate] [datetime] NOT NULL,
     [UpdatedBy] [varchar](50) NULL,
     [UpdatedById] [int] NULL,
     [UniqueId] [bigint] NULL,
     [MigratedFrom] [int] NULL,
     [ModifiedXml] [varchar](max) NULL,
     CONSTRAINT [tmp_PK_SystemHistory] PRIMARY KEY NONCLUSTERED
    (
     [Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 50) ON [PRIMARY]
    ) ON [SECONDARY]
    GO
    
    --check available free spave in each files
    SELECT 
    	name ,
    	size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
    FROM sys.database_files;
    GO
    
    --move the data
    INSERT INTO [dbo].[tmp_SystemHistory] 
    SELECT * FROM [dbo].[SystemHistory];
    GO
    --delete data/table
    DROP TABLE [dbo].[SystemHistory];
    GO
    
    --rename table and PK
    EXEC sp_rename '[dbo].[tmp_SystemHistory]', '[dbo].[SystemHistory]';
    EXEC sp_rename 'tmp_PK_SystemHistory', 'PK_SystemHistory';
    GO
    
    
    --shrink db file if you wish ;-)
    DBCC SHRINKFILE (1, 1)
    GO
    
    --check available free spave in each files
    SELECT 
    	name ,
    	size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
    FROM sys.database_files;
    GO
    Script is pretty simplified, please make sure it is applicabe to your environment.

    I'd not recommend DBCC SHRINKFILE still, due to the sideeffects.

    Another option may be partitioning and the SWITCH operator if you can partition your table.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    • Proposed as answer by Janos Berke Saturday, February 18, 2012 2:40 PM
    • Marked as answer by Kalman TothEditor Friday, March 02, 2012 10:35 PM
    Wednesday, February 08, 2012 2:55 PM

All replies

  • Hi,

    did you add a new file to the PRIMARY filegroup or created another filegroup? If you did the latter, then you may try the code below:

    ALTER TABLE [dbo].[SystemHistory] DROP CONSTRAINT [PK_SystemHistory] WITH (MOVE TO [SECONDARY])

    GO


    It is not tested, make sure you review and test before applying in production.

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog


    • Edited by Janos Berke Wednesday, February 08, 2012 12:41 PM
    Wednesday, February 08, 2012 12:41 PM
  • Yes,I tried but not reflected.

    Please look into this changes.

    1.Use GTTest

    GO
    ALTER DATABASE DBName ADD FILEGROUP [SECONDERYDATA]
    GO

    2.

    USE GTTest

    GO
    ALTER DATABASE GTTest

    ADD FILE
    ( NAME = SECONDERYDATA,
    FILENAME = 'Y:\SECONDERYDATAFILES\GTTest.ndf',
    SIZE = XMB,
    MAXSIZE = XMB,
    FILEGROWTH = XMB)
    TO FILEGROUP [SECONDERYDATA]
    GO

    3. Droped primary key

    4. ALTER TABLE SystemHistory
    ADD CONSTRAINT
    PK_SystemHistory PRIMARY KEY NONCLUSTERED
    (
    [Id]
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDERYDATA]

    same thing I implemented on staging server i.e.step 1,2,3 and 4.Initially that was in primary group.

    Your comment is right sir but i think, it will be used for in the case of a table that has a primary key constraint and has a
    clustered index.But my case is primary key with non clustered index.Please let me know if i am wrong.



    • Edited by Jamanu Dora Wednesday, February 08, 2012 2:07 PM
    Wednesday, February 08, 2012 2:04 PM
  • Hi,

    Reserved disk space will not be freed up automtically. It can be done by DBCC SHRINKFILE, which is not recommended due to some sideeffects (eg: index fragmentation)

    You are right, MOVE TO is not valid for nonclustered indexes.

    just a recap: if you want to move the data from a filegroup to another, you can  create a tmp_tablename table with the same schema as the original on the new filegroup. then copy the data and drop the old table + rename the new one. finally, run DBCC SHRINKFILE.

    Here is a sample script:

    USE [master]
    GO
    ALTER DATABASE [TestTableFGChg] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    DROP DATABASE [TestTableFGChg]
    GO
    
    CREATE DATABASE [TestTableFGChg]
    GO
    
    ALTER DATABASE [TestTableFGChg] ADD FILEGROUP [SECONDARY]
    GO
    ALTER DATABASE [TestTableFGChg] ADD FILE ( NAME = N'2ND', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\DATA\2ND.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [SECONDARY]
    GO
    
    USE [TestTableFGChg]
    GO
    
    CREATE TABLE [dbo].[SystemHistory](
     [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
     [PrimaryObjectId] [bigint] NULL,
     [ObjectType] [varchar](1000) NULL,
     [EntityType] [int] NULL,
     [OrigionalXml] [varchar](max) NULL,
     [ChangedXml] [varchar](max) NULL,
     [CreatedDate] [datetime] NOT NULL,
     [UpdatedBy] [varchar](50) NULL,
     [UpdatedById] [int] NULL,
     [UniqueId] [bigint] NULL,
     [MigratedFrom] [int] NULL,
     [ModifiedXml] [varchar](max) NULL,
     CONSTRAINT [PK_SystemHistory] PRIMARY KEY NONCLUSTERED
    (
     [Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 50) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    --add some sample data data
    INSERT INTO [dbo].[SystemHistory]
               ([Id]
               ,[PrimaryObjectId]
               ,[ObjectType]
               ,[EntityType]
               ,[OrigionalXml]
               ,[ChangedXml]
               ,[CreatedDate]
               ,[UpdatedBy]
               ,[UpdatedById]
               ,[UniqueId]
               ,[MigratedFrom]
               ,[ModifiedXml])
         VALUES
               (
    		NEWID()
               ,1
               ,'objecttype'
               ,1
               ,'xml'
               ,'xml'
               ,current_timestamp
               ,'someone'
               ,5
               ,55
               ,5
               ,'mod_xml')
    GO 10000
    
    GO
    
    --create the temp table
    CREATE TABLE [dbo].[tmp_SystemHistory](
     [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
     [PrimaryObjectId] [bigint] NULL,
     [ObjectType] [varchar](1000) NULL,
     [EntityType] [int] NULL,
     [OrigionalXml] [varchar](max) NULL,
     [ChangedXml] [varchar](max) NULL,
     [CreatedDate] [datetime] NOT NULL,
     [UpdatedBy] [varchar](50) NULL,
     [UpdatedById] [int] NULL,
     [UniqueId] [bigint] NULL,
     [MigratedFrom] [int] NULL,
     [ModifiedXml] [varchar](max) NULL,
     CONSTRAINT [tmp_PK_SystemHistory] PRIMARY KEY NONCLUSTERED
    (
     [Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 50) ON [PRIMARY]
    ) ON [SECONDARY]
    GO
    
    --check available free spave in each files
    SELECT 
    	name ,
    	size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
    FROM sys.database_files;
    GO
    
    --move the data
    INSERT INTO [dbo].[tmp_SystemHistory] 
    SELECT * FROM [dbo].[SystemHistory];
    GO
    --delete data/table
    DROP TABLE [dbo].[SystemHistory];
    GO
    
    --rename table and PK
    EXEC sp_rename '[dbo].[tmp_SystemHistory]', '[dbo].[SystemHistory]';
    EXEC sp_rename 'tmp_PK_SystemHistory', 'PK_SystemHistory';
    GO
    
    
    --shrink db file if you wish ;-)
    DBCC SHRINKFILE (1, 1)
    GO
    
    --check available free spave in each files
    SELECT 
    	name ,
    	size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
    FROM sys.database_files;
    GO
    Script is pretty simplified, please make sure it is applicabe to your environment.

    I'd not recommend DBCC SHRINKFILE still, due to the sideeffects.

    Another option may be partitioning and the SWITCH operator if you can partition your table.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    • Proposed as answer by Janos Berke Saturday, February 18, 2012 2:40 PM
    • Marked as answer by Kalman TothEditor Friday, March 02, 2012 10:35 PM
    Wednesday, February 08, 2012 2:55 PM
  • Thanks sir,I will do it today on staging server,hope so it will help......I will do reply once done.

    Thursday, February 09, 2012 9:56 AM
  • Any update?

    Thanks,

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    Saturday, February 18, 2012 2:40 PM
  • Sorry sir delay reply.I tried in staging but we are facing same issues .

    Monday, February 20, 2012 12:04 PM