January 25, 2012 at 9:13 am
I have an MDF file of size 40 GB with unallocated space of 1GB. The ldf file size is 300 MB. I am thinking to get the MDF file reduced to <20GB. I tried reducing the size using SSMS, under properites->Files. But, didn't see much difference, just some 1GB got reduced. Is that because, we do not have unallocated space we couldn't reduce anymore of the mdf file size? Also, I know dbcc shrink file is an option, but it further gives the fragmentation issues, which I don't like to take that step. Can some give me suggestion to reduce the file size, please. Thank you.
January 25, 2012 at 9:18 am
You have answered your own question really, you have only 1 GB unallocated so this is all you can reduce the size by.
January 25, 2012 at 9:25 am
Why do you want to reduce the file size to 20GB?
Second, if you have a 40GB file, with 1GB unallocated, then you have 39GB of data. If you need to get to 20GB, you need to delete some data.
I ask the question first, because it seems you're not understanding what you have, or how SQL Server works, and you might get yourself into problems if you start deleting things.
January 25, 2012 at 9:55 am
Three things here.
1) Both of the guys above make valid points.
2) Shrinking database can cause performance issues, so be aware of that.
3) To find the tables taking up space use the following scripts.
-- Create the temp table for further querying
CREATE TABLE #temp(
rec_idint IDENTITY (1, 1),
table_namevarchar(128),
nbr_of_rowsint,
data_spacedecimal(15,2),
index_spacedecimal(15,2),
total_sizedecimal(15,2),
percent_of_dbdecimal(15,12),
db_sizedecimal(15,2))
-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"
-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)
-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100
-- Get the data
SELECT *
FROM #temp
ORDER BY total_size DESC
-- Comment out the following line if you want to do further querying
DROP TABLE #temp
January 25, 2012 at 12:08 pm
Oh no It's not to reduct to 20 GB, it is to reduce to <10GB and express edition supports only 10GB per DB. I know deleting data is not a good idea as we may face constraint issues and by shrinking fragmentation issues. So, the point is with no unallocated space and no data removal can we reduce the size of DB. I know answer would be NO, but if any ideas also would be appreciated.
January 25, 2012 at 12:16 pm
Why do you need to move to Express? I'm asking, so that I can try to see if there is something you can do.
The short answer is no, you need to delete data to get to 10GB.
If this is a dev system, why not use developer edition instead of Express?
January 25, 2012 at 2:18 pm
That is what the infratructure we have in our school. We get further ediitons after few months. For time being we want to work at DB. Two DBA's in our organization has Dev and Std editions but rest of developers has only express edition. We will upgrade only after few months.
January 25, 2012 at 2:31 pm
You have three choices
1. delete 30GB of data
2. move data to separate databases. Meaning either move a portion of data from each table to a new database
3.move certain tables to a new database.
If you do #2, that will work with your application, but you won't be able to see all the data since only a portion will be in each database.
If you do #3, you will need application code changes to find the data that is moved.
January 25, 2012 at 2:56 pm
Steve,
Would this be an option for SHARD? I know the database doesn't support it, but I have seen some stuff on CodePlex for .NET and the process.
Thoughts?
Fraggle
January 25, 2012 at 2:59 pm
Steve Jones - SSC Editor (1/25/2012)
3.move certain tables to a new database.If you do #3, you will need application code changes to find the data that is moved.
This is an interesting option, and can be done without changing the application. I can't see anything that will stop you from creating a number of databases, each less than 10GB, each with a number of tables from the original database, then creating views in the original database, with the same names as the original tables but selecting data from their related tables in the new databases. Possibly a neat work around for the 10GB limit on DB size in Express, but a bit of a headache to manage.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 25, 2012 at 3:06 pm
To the OP; the point is you cannot shrink a database smaller than the size of the data it contains. DBCC SHRINKFILE doesn't magically shrink the file while keeping all of your data the same. It releases unallocated space. So this is why the only options that are available involve getting data out of your database by moving it or deleting it.
Jared
CE - Microsoft
January 25, 2012 at 7:30 pm
Steve Jones - SSC Editor (1/25/2012)
If you do #3, you will need application code changes to find the data that is moved.
Maybe not. You can use synonyms or "pass through" views to do such a thing with zero changes to managed code.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2012 at 8:20 pm
True, you can use views of synonyms. That might work, although performance wouldn't be great.
For dev/test, it's probably fine.
January 26, 2012 at 7:42 am
Steve,
I have thought of option 3. I actually noticed some tables in my database which doesn't have any relationship with tables in database. I thought of moving those tables into another database. But, is there any way to link two databases. I know the process of extracting the data between databases using naming conventions but also can we link two databases?
January 26, 2012 at 8:49 am
Steve Jones - SSC Editor (1/25/2012)
True, you can use views of synonyms. That might work, although performance wouldn't be great.For dev/test, it's probably fine.
I've not experienced any noticiable slowdowns with inter-db synonyms or passthrough views. The code for them is evaluated in the execution plan as if they were a part of the original query much like an iTVF or derived table is. Are you sure you're not thinking of "Linked Servers" which would provide a slowdown?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply