SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore existing database failed due to space issue


Restore existing database failed due to space issue

Author
Message
Compassionate
Compassionate
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 31
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87286 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Compassionate
Compassionate
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 31
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88296 Visits: 45277
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
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87286 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ronang
ronang
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 136
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search