Release unused space in the mdf file

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Release unused space in the mdf file

17 posts, Page 1 of 2

1 2 »»

Release unused space in the mdf file

Rate Topic

Display Mode

Topic Options

Author

Message

dsanchez-630020

Posted Tuesday, October 16, 2012 5:32 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 9:54 AM
Points: 6, Visits: 70

We recently upgrade to sql standard to sql Enterprise. We ran a maintenance job which defrags, reindex and cleans the table of our largest tables and runs a shrinkfiles on the logs to clean up after each one. Our database grew from 74 gig to 95 gig we ran the task to shrink the data file and released the unused space this freed up 2 gig. Would upgrading to standard to enterprise cause the increase in data size, or do we need to tweak the settings in the maintenance job? Dbreindex is set to 100 and the cleantable set to 0 shrink file set to 0 and the truncate only any assistance is greatly appreciated

Post #1373586

Vegard Hagen

Posted Tuesday, October 16, 2012 6:39 PM

SSC-Addicted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 11:41 PM
Points: 494, Visits: 325

I'm not sure what caused your data file to grow and why you were unable to shrink it more than 2 gb, I wouldn't worry about it unless I really REALLY needed that disk space for something else and was absolutely sure that I'd never need it for this database.

Now, there is a time and a place for everything, but shrinking can be bad for your SQL Server's health and is usually better left undone except on an ad hoc basis where it's either absolutely necessary or you know for sure you have a lot more filespace than you'll need in the foreseeable future. Shrinking as part of a regular scheduled maintenance job is usually a good way to cause more problems than you fix: It will add extra overhead and consume system resources + your file systems will gradually get more and more fragmented when you're constantly shrinking a little here and growing a little there.

Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

"It is better to light a candle than to curse the darkness."
(Chinese proverb)

Post #1373591

dsanchez-630020

Posted Friday, November 09, 2012 10:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 9:54 AM
Points: 6, Visits: 70

Is there away without shrinking the database to get back the space when we delete big files in our database?

Post #1383134

Bobby Glover

Posted Friday, November 09, 2012 11:38 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:33 AM
Points: 162, Visits: 659

Its not wise to do shrink operations on production dbs.

I have a script (not on this laptop) that shows the size of all the tables in the db.
If you restore the previous db elsewhere if you have a backup of it. Then run the script on both dbs it will tell you what tables have grown. You should be able to find such scripts on the net.

SQL COMPARE(REDGATE) will show the row mismatch.You maybe able to get an EVAL version for free.

Post #1383173

CapnHector

Posted Friday, November 09, 2012 2:29 PM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:07 AM
Points: 935, Visits: 1,709

Bobby Glover (11/9/2012)

...

SQL COMPARE(REDGATE) will show the row mismatch.You maybe able to get an EVAL version for free.

If you need to compare the data sql compare is a good choice (Note this site is run by redgate but i dont work for them, they just make good stuff) and it has a 14 day trial.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler

Post #1383243

Michael Valentine Jones

Posted Friday, November 09, 2012 4:32 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:14 PM
Points: 2,941, Visits: 10,481

What are the results when you run the script on the link below in your database?

Script to analyze table space usage:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

Post #1383293

dsanchez-630020

Posted Friday, November 09, 2012 4:33 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 9:54 AM
Points: 6, Visits: 70

let me check

Post #1383294

dsanchez-630020

Posted Friday, November 09, 2012 4:51 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 9:54 AM
Points: 6, Visits: 70

i get this

FileSizeMB UsedSpaceMB UnusedSpaceMB
0.75 0.29 0.46
83198.00 74874.75 8323.25
83198.75 74875.04 8323.71

Post #1383301

Michael Valentine Jones

Posted Friday, November 09, 2012 7:24 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:14 PM
Points: 2,941, Visits: 10,481

dsanchez-630020 (11/9/2012)

i get this

FileSizeMB UsedSpaceMB UnusedSpaceMB
0.75 0.29 0.46
83198.00 74874.75 8323.25
83198.75 74875.04 8323.71

Why did you remove the Type and DBFileName columns from the query?

Why did you leave out the table size query result set?

Post #1383316

Bobby Glover

Posted Saturday, November 10, 2012 2:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:33 AM
Points: 162, Visits: 659

Try this

-- Table and row count information
SELECT OBJECT_NAME(ps.[object_id]) AS [TableName],
i.name AS [IndexName], SUM(ps.row_count) AS [RowCount]
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.indexes AS i
ON i.[object_id] = ps.[object_id]
AND i.index_id = ps.index_id
WHERE i.type_desc IN ('CLUSTERED','HEAP')
AND i.[object_id] > 100
AND OBJECT_SCHEMA_NAME(ps.[object_id]) <> 'sys'
GROUP BY ps.[object_id], i.name
ORDER BY SUM(ps.row_count) DESC;

Post #1383348

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions