Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
andresasongko
Starting Member
1 Post |
 Posted - 2008-05-07 : 16:01:51
|
| Hi,I'm just wondering if I can reduce the size of my database (therefore the MDF file), without losing any data. I've got a very big DB (approx 14GB!), and I suspect it's a way bigger than it should be.Any clue will help. Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-07 : 16:20:18
|
| Check out DBCC SHRINKDATABASE and DBCC SHRINKFILE in Books Online. These will only help with the MDF file size if you have free space in the file. If you don't have free space in the file, then you'll need to delete some data.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-05-07 : 16:23:11
|
There is nothing technically wrong with having a 14GB database. SQL Server happily deals with databases 10 times that size and more. in order to prove that there is free space in the database, though, you can run this:select fileproperty(name, 'SpaceUsed')/128, size/128from sysfiles Compare the two numbers to see how much space there is (in MB).Shrinking a data file tends to be more work than it is worth, unless you are really hard up for disk space, though. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-05-07 : 16:36:12
|
| And if you do shrink, rebuild all your indexes after. Shrink tends to shuffle index pages very badly.--Gail Shaw |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-07 : 16:37:12
|
| and that will increase your log space. |
|
|
|
|
|
|
compress/minimize database (mdf file)