Ideas on shrinkfile

Last Post 25 Jan 2008 02:23 PM by phsatech. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
phsatech
New Member
New Member

--
24 Jan 2008 12:19 PM
Our current Production currently runs with about 38% free space on with a size of 250GB. I will be deleting about 70GB of data in the near future and will have more free space as of this. Since I'm trying to reduce the size of the datafile to speed up database refresh time, I have some questions on Shrinkfile and other process that should be carried out as well as to find the best methods to run with our setup. We run SQL2000.
Since the shrink process may take some time, would it be advisable to to this in small batches, say reduce 10GB at a time. Recovery mode would be set to simple. I have 8 hours every other Sat. night when no users are on the system. (I'm currently getting some timings in a test environment)
Reading up on Shrinkfile, it mentions when using the TRUNCATEONLY option it ignores the target size, so if thats the case I don't have any control on the amount I want to shrink. Any ideas on what Shrinkfile syntax may work best?
One the above has been determined would it be advisable to run the backup before or after this process? And would it be advisable to run dbreindex before or after as well?

Appreciate insight.
phsatech
New Member
New Member

--
25 Jan 2008 02:23 PM
Thnxs for the information. The current sizing we have in production: H:\MDF=213,359,168KB and LDF is I:\ 42,381,312KB. These are seperated on different drives sitting on our SAN. We don't use mulitple filegroups.

spaced_used=data-249746.56 MB, unallocated, unallocated-54913.25 MB

let me know if you need anymore detail

thnxs
phsatech
New Member
New Member

--
28 Jan 2008 11:13 AM
We actually run regular tran log backups every 30mins. Its actually not that we don't have enough space available. The main reason I'm trying to reduce the size to the DB is so that refresh time can become faster. I usually have to refresh a copy of Production into our Test environment once a week. This sized DB can take up to 7+ hours, whereas say our HR DB (45)GB can have a refresh done in 3hrs.
I usually run dbreindex every 2 weeks.
SQLUSA
New Member
New Member

--
28 Jan 2008 12:10 PM
You can be selective with dbreindex.

What is the issue?

Shrinking appears to be unrelated issue?

You can also detach the db (when not in use) and bring it back without the log file. A new logfile would be created. You can just delete the old one.

Kalman Toth, Business Intelligence Architect
SQLUSA: http://www.sqlusa.com - OLAP Reporting, Performance Management
phsatech
New Member
New Member

--
29 Jan 2008 12:10 PM
I disagree with Gunney on restoring 45GB of data across the network in minutes. What are you smoking?! We are talking 2 different Servers on 2 different speed switches.
The LAN is 1000GB on Production and 10/100 in DEV. Dev is getting refreshed using dual 3Ghz Procs, 4GB Ram and RAID 1 O/S W2K3 with Data on RAID 5 10K disks.
I'm going to add a gigabyte card to the Server and we will see if that cranks down the time some.
You are not authorized to post a reply.

Acceptable Use Policy