restore database without the extra free space inside?

We have a SQL Server 2008 R2 Enteprise edition database with 6 file groups. The Primary one, and 5 for a table's clustered index and it's 4 non clustered indexes. The issue is that the primary file group was originally 1TB in side. once we pulled out the clustered index it made the free space in the file be roughly 90%. however it causes decent IO hits and takes an insane amount of time to reorganize the primary file so that there's only 10% free space. We don't want to take all of it so that it can grow without expanding over-head (though the remaining pieces in the PRIMARY filegroup are less than 1% of all growth). We have full backups every day and I was hoping that we could somehow restore the database without it being so big and bloated with free space. Those 900GB of free space can be put to good use easily.

If there is no way, I have a follow up question: when it does a restore, is it restored contiguously inside the file with essentially zero fragmentation or will it be restored in the same fragmented state it was in when backed up? If it is defragged then I could easily run the shrink or reorg command after a restore and it should move lightening fast compared to shrinking/reorg-ing in it's current state.
MrVaultAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TempDBACommented:
the restoration will restore in the same state as it was backed up. So, after you backup, it will be having same extra space and you can't make any changes there. Only thing you can do, as you earlier said, is shrink after it is restored.
       You can go with shrinking the database in batches, i.e. 5 GB each. Do it as shrinkfile i.e. shrinking the data file for the database. So, it will be easier.
        It would be great if you can explain (for my understanding) as how you made changes with the filegroups and pulled the indexes out there.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MrVaultAuthor Commented:
but do you know if the restored database will be as fragmented? If you re-org a file in SQL you can then free up the space that gets put at the end. I was hoping the file would be restored with all the data in the beginning of the file and the whitespace at the end. if not there's no point in restoring it to free up the space. might as well try to shrink it while it's live.

not sure what you mean by the batch method. can you be more specific?

initially the main primary filegroup had the whole database in it. for other reason we created 5 new file groups. and then one by one we dropped the indexes of a table and recreated them, this time inside each one's respective filegroup and thus each of the 5 indexes had their own file. However when you pull out the clustered index from the primary group it leaves a ton of free space behind (almost 50% since this table makes up the vast majority of the size of the database as whole). we need to recover most of that space for capacity reasons. we will leave  healthy buffer so it's not wasting resources growing all the time though.
0
TempDBACommented:
Will give your reply tomorrow. And thanks for sharing the process.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

jogosCommented:
With
USE yourdbname;
GO
-- check fileid
SELECT file_id, name
FROM sys.database_files;
GO
-- shrink file with id 1
DBCC SHRINKFILE (1, TRUNCATEONLY);

Open in new window

you maybe already can free some space, the maybe is that it only frees unused space at the end of the db-file.

You can do the real shrinkfile gradualy by using the target_size (in MB) so you can do it in small portions of for ex 50MB at a time.
http://msdn.microsoft.com/en-us/library/ms189493.aspx

btw when moving the clustered index, your table followed it's way to the same filegroup as the clustered index.




0
MrVaultAuthor Commented:
thanks jogos. what is the downside of shrinking the file through the gui? doesn't that drop whatever it can off the end that's pure whitespace?

0
jogosCommented:
The TRUNCATEONLY option just truncs whitespace at the end, so this could be 500Gb or could be nearly nothing. So if your lucky it's already a lot and it's done in a blink of the eye without locking  and massive IO so certainly worth to try first.

Throught the gui or by a script is the same for shrinking completely or to a certain size, the gui just is a way to present the options the command wil be the same.
But I can immagine (not sure) the result will be better organized if you shrink a file completely in one movement as you try to do it in 10 iterations of 80GB a time.
0
jogosCommented:
Shrinking in little pieses will fragment what's in it, so better in one movement.
0
MrVaultAuthor Commented:
High. So turns out there is little to nothing at the end that can be chopped off. Which means we have to do a reorg, not truncate. Unfortunately the reorg always fails after 5 minutes to 2 hours (it's 1TB in size). The only way it appears we can free up the space is to stop the services and do a reorg. However the last time I did this it only freed up 20GB after an hour. At that rate it will take over 24 hours of various downtimes to free up close to 500GB.
0
TempDBACommented:
Sorry, was little busy. The shrinking in batches of 5 GB, can be done as


IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE [name] LIKE '%DBShrink_Timing_tbl%')
BEGIN
      CREATE TABLE [dbo].[DBShrink_Timing_tbl](
            [shrinkId] [int] IDENTITY(1,1) NOT NULL,
            [DBName] [varchar](250) NULL,
            [fileName] [varchar](max) NULL,
            [initSizeMB] [int] NULL,
            [newSizeMB] [int] NULL,
            [startTime] [datetime] NULL,
            [endTime] [datetime] NULL,
            [timeTakenMin]  AS (datediff(minute,[endTime],[startTime]))
      ) ON [PRIMARY]
      GO

      ALTER TABLE [dbo].[DBShrink_Timing_tbl] ADD  DEFAULT (getdate()) FOR [startTime]
      GO
END


IF OBJECT_ID('tempdb..#fileDetails') IS NOT NULL
    DROP TABLE #fileDetails  

 

CREATE TABLE #FileDetails
(
      FileId        INT,
      FileGroupId   INT,
      TotalExtents  INT,
      UsedExtents   INT,
      [Name]        NVARCHAR(128),
      [FileName]    NVARCHAR(500),
      TotalSizeMB   AS ((TotalExtents * 64.0) / 1024),
      UsedSizeMB    AS ((UsedExtents * 64.0) / 1024),
      shrinkSize    AS (
          CASE
               WHEN ((UsedExtents * 64.0) / 1024) < (((TotalExtents * 64.0) / 1024) - 6000) THEN ((TotalExtents * 64.0) / 1024)
                    - 5000
               ELSE -1
          END
      )
      
      -- If reserved > allocated + 6GB, then set reserved - 5GB, else -1
)  

   

INSERT INTO #fileDetails
EXEC (N'dbcc showfilestats with tableresults')  

 
DECLARE @fileId INT,
        @newSize INT,
        @sql NVARCHAR(4000),
        @fileName VARCHAR(250),
        @initSizeMB INT,
        @currentID INT  

WHILE EXISTS (
          SELECT 1
          FROM   #fileDetails
          WHERE  shrinkSize > 0
                 AND fileid <> 2
      )
BEGIN
    SELECT TOP 1 @fileId = fileId,
           @newSize = shrinkSize,
           @initSizeMB = totalSizeMB,
           @filename = [filename]
    FROM   #fileDetails
    WHERE  shrinkSize > 0
           AND fileid <> 2
    ORDER BY
           TotalSizeMB DESC  
   
   
   
    INSERT INTO dbo.DBShrink_Timing_tbl
    SELECT DB_NAME(),
           @fileName,
           @initSizeMB,
           @newSize,
           GETDATE(),
           NULL  
   
   
   
    SELECT @currentId = SCOPE_IDENTITY()  
   
   
   
    SET @sql = N'dbcc shrinkfile(' + CAST(@fileId AS NVARCHAR) + ', ' + CAST(@newSize AS NVARCHAR)
        + ')'
   
    PRINT @sql  
   
    EXEC (@sql)  
   
   
   
    UPDATE dbo.DBShrink_Timing_tbl
    SET    endTime = GETDATE()
    WHERE  shrinkID = @currentId
   
   
   
    TRUNCATE TABLE #fileDetails  
   
   
   
    INSERT INTO #fileDetails
    EXEC (N'dbcc showfilestats with tableresults')
END  

0
MrVaultAuthor Commented:
wow. thanks. any chance you can comment each part of that? I'm not that advnaced in t-sql to know what that is doing. I get the general idea though. how long does it run the shrink command for?
0
jogosCommented:
About the script what it does
- create a table  dbo.DBShrink_Timing_tbl to contain the timelog from the actions
- create a temporary table  #fileDetails
  and fils it up with the result of DBCC SHOWFILESTATS
- loops until the full shrink is done (leave 6 Gb)
  -- get info from #fileDetails to instert in timelog
  -- shrink dbfile with to previous size - 5GB
  -- updatese the timelog
  -- empties (truncate) the temporary table
  -- fils again the temporary table with the new statistics
end loop

But as I said earlier, this will leave your database much more fragmented as if you will do it in one shrinkfile.  
On top of that some datapages may be moved multiple times.

 
0
MrVaultAuthor Commented:
thanks. I'll award points for now since I have to find time to run this on a test system before I can run it in production. I'm not that worried about fragmentation. we can take decent downtime spots to defrag later once we have most of the space back. and the parts of the database that need it do not impact performance in any noticeable way.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.