I am looking to shrink a database file using DBCC Shrinkfile to try to reclaim some disk space. For some unexplained reason I have some unsettled feelings. I need to confirm:
1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
2. If I do not have a clue as to what target size I might go for, what is safe?
3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
--
Message posted via http://www.sqlmonster.com"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.com...
> I am looking to shrink a database file using DBCC Shrinkfile to try to
reclaim some disk space. For some unexplained reason I have some unsettled
feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
Shrinkfile?
>
1. That's the option you should use -- you might have to defragment some
indexes first, though. If you have heavy fragmentation you probably won't
reclaim much space (if any is available).
2. I usually use 1 if I want to reclaim ALL possible space. What that
option is actually doing is specifying a new minimum size for the database
(overriding whatever was set when it was created). So there's no real
danger in using whatever value you want...
3. None that I'm aware of -- probably a small performance hit if you run it
on a very active system, though...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||What version of sql are you using?
"Robert Richards via SQLMonster.com" wrote:
> I am looking to shrink a database file using DBCC Shrinkfile to try to reclaim some disk space. For some unexplained reason I have some unsettled feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is safe?
> 3. Are there any unknown dangers lurking out their when running DBCC Shrinkfile?
> --
> Message posted via http://www.sqlmonster.com
>|||I am running SQL 2K.
--
Message posted via http://www.sqlmonster.com|||I am running SQL 2K.
--
Message posted via http://www.sqlmonster.com|||1. Sure it may free up some space but what happens when you need more room
in the db? You also need plenty of free space to do things like creating
and reindexing.
2. Reindexing an index will require at least 1.2 times the size of the
index or in the case of a clustered index the table itself.
3. Shrinking a file simply to gain more disk space is a bad idea in most
cases. There was some reason why the file got that large in the first place
and there is a good chance it will need that much space again. If you
shrink the file and use that space for something else what is going to
happen when the DB grows again? Chances are you will stop any modifications
on your sql server for that db. If you are that low on disk space I suggest
you get another disk before you really run into trouble.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:30b3c02dee4a4a4988cebcf126e266f3@.SQLMonster.com...
>I am looking to shrink a database file using DBCC Shrinkfile to try to
>reclaim some disk space. For some unexplained reason I have some unsettled
>feelings. I need to confirm:
> 1. I was considering running DBCC Shrinkfile with the TruncateOnly
> argument, believing this is how I might free up some disk space?
> 2. If I do not have a clue as to what target size I might go for, what is
> safe?
> 3. Are there any unknown dangers lurking out their when running DBCC
> Shrinkfile?
> --
> Message posted via http://www.sqlmonster.comsql
No comments:
Post a Comment