November 1, 2004 at 9:33 am
In an effort to improve performance I changed my weekly optimazation task to change fee space per page percentage to 75%. This increased my DB data file size form 2.1GB to 5.6GB. I re-ran the optimaztion set at 10% but this did not help.
How do I get my DB back to its proper size?
Thanks,
Sean Wyatt
seanwyatt.com
November 1, 2004 at 11:28 am
Look up DBCC SHRINKDATABASE and DBCC SHRINKFILE in BOL.
--
Adam Machanic
whoisactive
November 1, 2004 at 4:25 pm
I looked at these and don't seem to be using the right options. The best I was able to do was 4.8GB.
Sean Wyatt
seanwyatt.com
November 2, 2004 at 2:43 am
What version of SQL Server are you using?
November 2, 2004 at 8:55 am
SQL SERVER 2000 SP3a on Win2000 Server SP4
The DB has 1 datafile and I ran DBCC SHRINKFILE (1)
This only brings the datfile down to 4.8GB. It was at ~ 2GB before I ran the optimization wizard to det the fill factor to 75%. I reran the optimaztion set to 25 and then 10 % with no change. This really has me confused...
Sean Wyatt
seanwyatt.com
November 2, 2004 at 10:07 am
Sean,
To get a database to minimum size, I usually rebuild all of the indexes first ( sp_msforeachtable 'dbcc dbreindex(''?'')' ), then shrink the data file(s) ( run sp_helpfile to get the file IDs, then run DBCC SHRINKFILE for each ), then backup the log file(s) with TRUNCATE_ONLY, then run DBCC SHRINKFILE on the log file(s) with the EMPTYFILE option.
--
Adam Machanic
whoisactive
November 2, 2004 at 11:46 am
Just remember if you to a backup TRUNCATE ONLY and you are currently backing up the tlogs you will screw up your restore options. Very rarely do I EVER recommend using the truncate only option I'd rather have the log backup if I needed it than not have it and need it.
Wes
November 2, 2004 at 2:57 pm
If you use the EMPTYFILE option on the SHRINKFILE, you will render the file unuseable. EMPTYFILE is only used if your goal is to completely delete the file. It prevents any new data from going into the file.
Also, if you're attempting to shrink the data file, you don't need to do ANYTHING to the log file unless it grows dramatically in the process.
Steve
November 2, 2004 at 4:36 pm
Yay. I just wrote I reply and it disappeared into thin air. Here goes again, if i can damn well remember what I wrote.
I am assuming the extra file size you are seeing is free space taken up by the database.
You can remove this extra free space by using the following command in Query Analyzer:
DBCC SHRINKDATABASE (DBName, TargetPercenatge)
Where DBName and TargetPercentage are your own criteria.
Remember that specifying a TargetPercentage of 1 on a 2G database will give you 1% of freespace on top of the amount of data you have, so the resulting total size will be 2020 Megabytes or so.
I hope this helps. The last post I wrote that disappeared seemed a bit clearer. Let me know how you go.
November 2, 2004 at 4:52 pm
I ran this:
sp_msforeachtable 'dbcc dbreindex(''?'')'
then:
DBCC SHRINKFILE (1)
Then:
DBCC SHRINKDATABASE (PLAY2,1)
The SHRINKDATABASE command only took 1 second to complete.(?)
In Enterprise Manager I see:
Data Fles Tab shows 4603MB Space allocated
Transaction log 50MB Space allocated
General Space Available 0.00MB
Is there a way to verify the amount of data vs the amount of empty space to ensure that somehow my DB didn't grow for some reason.
Thanks to all for your replies. Since the size increase I have seen average CPU and Physical Disk times double so I am motivated to get this solved and appreciate your assistance.
Sean Wyatt
seanwyatt.com
November 2, 2004 at 5:19 pm
Try sp_spaceused for some info on the database size
November 2, 2004 at 5:23 pm
Or use
sp_spaceused null, true
for up to date info...
November 3, 2004 at 8:50 am
ran sp_spaceused null, true:
~ Old Normal Sized DB Results ~
database_name database_size unallocated space
----------------------------------------- ------------------ ------------------
PLAY3 2124.94 MB 156.98 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
1963992 KB 1126240 KB 708176 KB 129576 KB
~ New SuperSized DB Results ~
database_name database_size unallocated space
------------------------------------------ ------------------ ------------------
VMFG 5624.94 MB 987.37 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
4697672 KB 2663872 KB 1963280 KB 70520 KB
If I am reading this correctly it means all my extra space is tied up in indexes (1.9GB).
In my fat DB - sysindexes.OrigFillFactor = 25 for most rows
In my normal DB - sysindexes.OrigFillFactor = 0 for all rows
I ran the optimization plan on the fat DB and set index fillfactor to 1% still only brings DB down to 4.6GB.
Sean Wyatt
seanwyatt.com
November 3, 2004 at 9:32 am
Neither of those are true. Try using EMPTYFILE and see if your log files are really unusable.
Next, read this article, which proves that you the log file will grow due to a data file shrink... so you DO want to shrink the log file afterwards:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Adam Machanic
whoisactive
November 3, 2004 at 9:59 am
The log file is only showing as 50MB. What is your take on the results of sp_spaceused null, true showing 1,963,280 KB as index space?
Sean Wyatt
seanwyatt.com
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply