Restore existing database failed due to space issue

Question

Restore existing database failed due to space issue

Compassionate

SSC Veteran

Points: 221
More actions
June 9, 2013 at 10:34 am

#295147

All,
I have two data servers S1 and S2. I have a database D1. D1 exists in both S1 and S2. S2 is a prod database and we have to restore D1 in S1 frequently. We are getting space issues while restoring the D1 db. We have removed data from all the tables but its reducing the MDF and LDF size to the extent I can restore new backup.
We have used dbcc_shrinkdatabse command but of no use.
DB size is 65 GB and after deleting data, it has reduced 2 GB. Available space in the drive is 10GB and I require 50GB to restore now.
Could you advise please?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Compassionate (6/9/2013)
All,
I have two data servers S1 and S2. I have a database D1. D1 exists in both S1 and S2. S2 is a prod database and we have to restore D1 in S1 frequently. We are getting space issues while restoring the D1 db. We have removed data from all the tables but its reducing the MDF and LDF size to the extent I can restore new backup.
We have used dbcc_shrinkdatabse command but of no use.
DB size is 65 GB and after deleting data, it has reduced 2 GB. Available space in the drive is 10GB and I require 50GB to restore now.
Could you advise please?

1. How large is the LDF?

2. What is the total space allocated to the MDF?

3. What is the Recovery Model of the database?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Compassionate SSC Veteran Points: 221 More actions · Answer 2

I have executed shrink database option from MSMS.After this below are the stats

1) LDF - 1024KB

2) MDF - 154MB

3) Recovery modle - simple

Now I have 83GB space to restore the db and the db which I am trying to restore is 73GB(backup files size) . But its still asking for more space now around 95GB.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You need free space equal to the size of all the files of the source database, not the size of the backup file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Compassionate (6/9/2013)
I have executed shrink database option from MSMS.After this below are the stats
1) LDF - 1024KB
2) MDF - 154MB
3) Recovery modle - simple
Now I have 83GB space to restore the db and the db which I am trying to restore is 73GB(backup files size) . But its still asking for more space now around 95GB.

Gail is correct especially when it comes to the logfile. That backup file for the logfile might be less than a megabyte but the restore size will be whatever size (could be absolutely huge) the logfile was (empty or not) at the time of the backup.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ronang SSC Veteran Points: 238 More actions · Answer 5

Hi

The process you're using sounds like alot of work. Perhaps you might want to investigate developing a log-shipping process for this. Not sure if I can add any more value to the previous comments, but my manual process for this would be:

1. Determine the space used by the db (sp_helpdb '[DATABASE_NAME]') on the Prod Server

2. Determine if the target machine (S1 in your case) has enough space for the full files

3. If not:

- script the Indexes as DROP on the Prod server <Script_1>

- script the Indexes as CREATE on the Prod Server <Script_2>

- script the Indexes as CREATE on the Prod Server WITH DATA | PAGE COMPRESSION <Script_3>

4. Run the DROP Script on Prod Before backing up (If I need to tell you to do this during off-peak hours, then you really shouldn't be doing this 🙂

5. Run the Backup with max compression (longer time to backup, smaller backup file created)

6. Run <Script_2> to re-create the Indexes on Prod (Remember to include the Update Stats command)

7. Copy the Backup file to the Target Machine or even better a shared Network location

8. Restore db as per normal on the Target Machine

9. Run <Script_3> to create the Indexes with Compression on the target machine. Your restored db should now be smaller than your Prod version

A few things to note:

1. This process is not fool proof, please test it first on two other servers.

2. You will have to ensure at some point that disk space is added to your target machine to match your prod server.

3. Avoid using DBCC shrinkfile | Shrink Database; this eventually causes more issues than it solves.

4. I have not taken into account the version of SQL you are using so the key to a successful implementation of this process is Test, Test, Test

5. Creating Indexes with COMPRESSION hint, may make your db footprint smaller but it also increases CPU utilisation, so ensure that the Target Machine's CPU and RAM can handle this.

Hope this helps you, I've been there where business refuses to fork out extra cash for more disk space. Eventually though they will have to or risk not being able perform this type of constant restore.