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 |
|
brkonthru
Starting Member
31 Posts |
 Posted - 2004-02-16 : 19:34:33
|
| I have a 1.8GB database file in my SQL2K server. I made several design changes on a few large tables, they all went just fine.After that I looked at the DB size and it has grown to 3.5GB !Looking at the enterprise manager, all the new space is just empty space. How can i remove this empty reserved space ?I have tried all the options in shrink database menu but it doesnt seem to be affecting the empty space.I would appreciate any suggestions, thanks. |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-02-16 : 19:54:27
|
| What is the Recovery Model set to on your database? What is your backup scheme? (Nightly full backups? Backup the transaction log every 15 minutes?)For the Recovery Model, try this:In Enterprise Manager,right-click the database and select Properties. Click on the Options tab. At the bottom of the screen you'll see recovery model. Tell us what that is set to, and that will help us tell you what the next step is.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
brkonthru
Starting Member
31 Posts |
Posted - 2004-02-16 : 20:02:15
|
| The recovery model set is "simple". |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-02-16 : 20:09:51
|
| When you do a shrink, you should see an option that says "reorder pages" or something along those lines. That might help get rid of some of the "wasted" space, but take heed. DO THIS AFTER HOURS WHEN NOONE IS USING THE SYSTEM!!! It takes a long time to do, and performance of the system will be seriously affected while this is running.Maybe Tara has a better idea as to where all this space has gone?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-02-16 : 20:16:40
|
| Look at DBCC SHRINKFILE in BOL..I remember a thread by Tara about dropping a column from a large table and worrying about space be reclaimed.... And here it is... [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26744&SearchTerms=add,column[/url]DavidM"SQL-3 is an abomination.." |
|
|
|
|
|
|
Truncating Empty space from DB file