| Author |
 Topic  |
|
|
dayve
Forum Moderator
USA
5820 Posts |
 Posted - 15 September 2005 : 18:15:07
|
I have recently taken over a project that has an SQL Server DB that was created with a fixed size of 20GB! Problem is the database probably only holds about 1GB of information. I thought I could simply shrink the DB but it didn't do what I thought it should have done. I've also tried DBCC SHRINKDATABASE but that seemed to have no affect whatsoever. While I am still Googling to find a way I can resize the database to a much smaller size, I figured I would ask here and see if anyone can enlighten me on how to decrease the size of the database.
PS. I already have Logs set to Simple. The 20GB size noted earlier is the actual size of the database which was set to a fixed size by some *^%$^&^ vendor in the db properties.
|
 |
Edited by - dayve on 15 September 2005 18:19:32 |
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
Posted - 15 September 2005 : 18:36:52
|
So, the actual 'file' is 20 Gb ?
I think that this might be usefull.
But, as stated there, allocating a fixed bigger size than necessary is a wise thing to do, when taking just performance into account.
quote:
The above paragraph may get you thinking about turning on the AUTOSHRINK database option. Do yourself a huge favor and forget that idea. Turning on AUTOSHRINK in a production environment can degrade system performance significantly. The only time you might want to use AUTOSHRINK is on your development server(s), where optimal performance is not as necessary.
edit: Another usefull link |
portfolio - linkshrinker - oxle - twitter |
Edited by - MarcelG on 15 September 2005 18:42:27 |
 |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 15 September 2005 : 18:43:28
|
The actual MDF data file is 20GB. The article you linked I have read and unfortunately proved to do nothing to help decrease the size of the DB.
I don't mind having a fixed bigger size, but 20 GB is way too much, especially when you're trying to do backups/restores/migrations. |
|
Edited by - dayve on 15 September 2005 18:43:45 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 15 September 2005 : 18:54:00
|
Have you tried
DBCC SHRINKFILE filename, targetsize (In MB), TRUNCATEONLY
If you omit targetsize parameter, the file will be shrunk to the minimum possible size. TRUNCATEONLY needs to be there, so that the free space in the file can be reclaimed.
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 15 September 2005 : 18:55:41
|
quote:
Originally posted by ruirib
Have you tried
DBCC SHRINKFILE filename, targetsize (In MB), TRUNCATEONLY
If you omit targetsize parameter, the file will be shrunk to the minimum possible size. TRUNCATEONLY needs to be there, so that the free space in the file can be reclaimed.
yes. this is the same thing noted in the link that Marcel posted.
actually, I did it without entering a targetsize and nothing happened, but using a targetsize seemed to work. don't know why I didn't try that.
is it possible to enter a target size less than the actual database size? would I get an error or other problem occurring? |
|
Edited by - dayve on 15 September 2005 18:59:34 |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 15 September 2005 : 19:01:21
|
quote:
Originally posted by dayve
quote:
Originally posted by ruirib
Have you tried
DBCC SHRINKFILE filename, targetsize (In MB), TRUNCATEONLY
If you omit targetsize parameter, the file will be shrunk to the minimum possible size. TRUNCATEONLY needs to be there, so that the free space in the file can be reclaimed.
yes. this is the same thing noted in the link that Marcel posted.
It should work, however if the file is 20Gb it is going to take a very very long time to do the shrink, probaly hours rather than minutes, your best bet would be to create a new db, do a DTS copy of all the tables and data to the new db, drop the existing db and delete it's files, now recreate the first db and dts copy the tables and data back. If at all possible you need to do this while logged on to the sql server locally or using TS not remotely via EM. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
| |
Topic |
|
Topic Locked
