June 30, 2009 at 11:24 am
I've seen a few threads on this question but I'm not quite sure my scenario was answered or not so I apologize in advance if this question has already been answered.
I a SQL 2005 database that is is about 175Gig in size (not including log files). We have purged some data out of the database and want to shrink it but it won't shrink. I also notice that the initial size in SSMS is growing and currently shows 175GB. Here are the particulars.
1. Database was originally created on SQL 2000 server and was less than 100MB in size.
2. Migrated to a SQL 2005 server in the past and changed it to run in mode 90.
3. Full recovery model -- Full backups weekly and transaction log backups 3x a day.
I've tried doing a DBCC SHRINKFILE, but it won't shrink.
1. How do I get the database shrunk down.
2. Why is the initial size value increasing as the database gets larger?
Thanks in advance for your help.
July 6, 2009 at 6:06 am
Did you try checking all the system objects size to start with? This will allow you to findout if all the objects in the database are really utilising that space.
Vivek
Vivek Shukla - MCTS SQL Server 2008
July 6, 2009 at 6:48 am
If the databse if 175gig , how much free space do you have available? as this will determine how much the db can shrink..
you can use
SP_spaceused
to check unallocated space
July 6, 2009 at 9:45 am
if you are using sql 2005 onwards, you can even use SQL Server in-built reports to find out about space usage.
Vivek Shukla - MCTS SQL Server 2008
July 6, 2009 at 12:02 pm
My guess is that you don't have any available space in the data file. The reason the initial size is growing is because you are experiencing autogrowth events.
I am also guessing that your autogrowth increment is quite small. But, if it was left as the default value it should be 10% (which is very bad, btw).
Use the Disk Usage report to see what is being used in the data file, how much is available - and the frequency of your autogrowth events. If you have more than one per day - you have way too many. I don't let any of my critical databases autogrow - ever.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2009 at 3:02 am
use this T-SQL for shrinking the database
DBCC SHRINKDATABASE THIS Shrinks the size of the data and log files
instead of Shrinkfile
And the Initial size will grow according to the percent which has given at the time of creation of database.
By default its 10.So it will grow accordingly.
July 15, 2009 at 3:07 pm
It might be your log file that is causing the size to be so large as well. You can use this sql to see how much your log files are being used:
DBCC SQLPERF(LOGSPACE)
You may not want to shrink both files at the same time as constant autogrowth is bad for both data and log files. You can run:
exec sp_helpdb 'DatabaseName'
to see what your file names are and then run:
backup log 'DatabaseName' WITH TRUNCATE_ONLY
to purge the log file, and then:
DBCC SHRINKFILE ('FileName', xxx, TRUNCATEONLY)
to shrink either file and purge them of data
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply