Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Restore existing database failed due to space issue Expand / Collapse
Author
Message
Posted Sunday, June 9, 2013 10:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 9, 2013 11:51 AM
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?
Post #1461340
Posted Sunday, June 9, 2013 11:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 35,547, Visits: 32,137
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1461341
Posted Sunday, June 9, 2013 11:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 9, 2013 11:51 AM
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.
Post #1461343
Posted Sunday, June 9, 2013 12:48 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #1461346
Posted Sunday, June 9, 2013 9:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 35,547, Visits: 32,137
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1461373
Posted Tuesday, June 11, 2013 3:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:14 AM
Points: 22, Visits: 127
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.
Post #1461958
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse