can I Shrink the database?

Question

Post reply

can I Shrink the database?

KT-478389

Grasshopper

Points: 18
More actions
November 12, 2007 at 2:37 pm

#181044

I have a huge database and the sql server shows me I have 47gig free space because the percentage allocation, but the disk is out space (have only 200mg) and want to shrink the database.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Look at DBCC SHRINKFILE in BOL.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 2

How big is your data and transaction log file?? i think you can truncate then shrink your transaction log but truncating and shrinking the data is not recommended. If you want to shrink your transaction log use the following tsql commands...

USE your_database

GO

CHECKPOINT

GO

BACKUP LOG your_database WITH TRUNCATE_ONLY

GO

DBCC SHRINKFILE (your_database_log, 2) -- this needs to be the name of the log_file from EM

--This is from this topic http://www.sqlservercentral.com/Forums/Topic225600-5-3.aspx#bm421214

"-=Still Learning=-"

Lester Policarpio

KT-478389 Grasshopper Points: 18 More actions · Answer 3

My database is 500 gig big the log file is about 37gig I will try this in the morning. Thanks for tip

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

I've never understood this... a 300GB hard drive costs about $110 and a 500GB hard drive costs only about $140. Your database is going to do nothing but to continue to grow... why not take this opportunity to give it some room?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KT-478389 Grasshopper Points: 18 More actions · Answer 5

keep in mind that this is a DL380 and E is currently over 550GB so would this additional space be for moving E or would it be simply adding a new partition

Vivien Xing SSChampion Points: 12297 More actions · Answer 6

You may have to shrink DB to release some free space if you have critical disk space issue.

But I would do the following:

1. Is the database size expected? Any purge or archive in place? If OLTP database, it should be light for performance/recovery/etc.

2. If database grows as business does, add more disks.

3. You'd better monitor the database size so you have the fact data to estimate the growth and plan ahead.

My 2 cents.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

The big question is - is it growing bigger at a regular pace, or did something "blow it up"? A bad update process, out of control INSERT, etc... could have oopsed your DB into being much bigger than it needs to be. That MIGHT be when you'd consider shrinking the DB, but then you should a. leave a fair amount of empty space for growth and/or working space, and b. look at rebuilding most of your indexes and reorg'ing the DB, since a shrink tends to jumble the file up internally.

Assuming you can add a new RAID set (which would be SEVERAL drives), that would buy you some extra space AND some extra performance on the disk storage side. You can then add files to the existing DB, so that you can spread this data over multiple disk sets (should give you a bit of a performance boost).

Or - if your data and log files and tempdb aren't already isolated onto their own disk sets, that's another serious opportunity for performance gains.

You'd have to look under the hood to see what you have in place already, and work with your server guy/hardware guy to know what you might need (and how many disks that involves, etc...)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

KT (11/14/2007)
keep in mind that this is a DL380 and E is currently over 550GB so would this additional space be for moving E or would it be simply adding a new partition

New partition... if you have an available slot...

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)