• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

Shrink my database

Database refuse to releast it's bloeted space to NTFS
My mdf file showa 600mb even after I try to scrink it. How do I scrink the database. I am expecting the "mdf" size to be under 100mb with the command below.

Thanks

dbcc shrinkfile (shop_data,76800)

--DbId      FileId      CurrentSize      MinimumSize      UsedPages      EstimatedPages
--494      1      76744      128      76736      76736
0
ruffone
Asked:
ruffone
  • 3
  • 3
  • 2
  • +2
1 Solution
 
mayank_joshiCommented:
try:-

USE [database_name]
GO
DBCC SHRINKDATABASE('database_name' )
GO

Open in new window

0
 
mayank_joshiCommented:
if you want to shrink only mdf file you may try:-

USE [database_name]
GO
DBCC SHRINKFILE ('logical_name_of_mdf_file'  , 3)
GO

Open in new window


where 3 MB is the default minimum limit for mdf file.
by default 'logical_name_of_mdf_file' is same as 'database_name'.
0
 
mayank_joshiCommented:
if you want to shrink your database regularly then
on Database Properties-->Options set Auto Shrink=True.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
ruffoneAuthor Commented:
That does nothing. I am becomming convinced that 600mb is the solid size of this database dispite what is in the table
0
 
ThakurVinayCommented:
As a professional DBA. you should not shirnk the database specially never shrink mdf.

also in your case its only 600MB. so i would suggest let it be.

my one cent.

HTH
Vinay
0
 
ruffoneAuthor Commented:
I would, but that is almost the max that my host will allow on the server that I share
0
 
Daniel_PLDB Expert/ArchitectCommented:
You should never ever shrink your databases, only in rare cases poeople do that - in case of sending database to another department and db size is too big to restore it to other location. As I said it's verry rare.
I wouldn't suggest you shrinking database regularly - it's a crime ;)

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/13/629059.aspx
http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/
http://blog.sqlauthority.com/2010/08/12/sql-server-shrinkdatabase-for-every-database-in-the-sql-server/

Take care,
Daniel
0
 
Daniel_PLDB Expert/ArchitectCommented:
Can you take backups of your database and store them in other location?
Maybe you can change data types in tables to store smaller rows etc. etc.
You could taking backups and then truncating tables, it depends on your vision.
0
 
OP_ZaharinCommented:
reason not to overuse shrink is that it increase fragmentation of the index and may lead to file system level fragmentation as well.

if you have specified 600MB during the creation of the database, it will not get smaller even if you use shrink. if the database grows to 800MB, by using shrink it will only be reduced to 600MB.
0
 
ruffoneAuthor Commented:
This database has seen every server since SQL 7.0. I usually just attach it to the new server. Who knows what that might have done to it over the years. So I am copping all the data into a new database and redoing the indexes. and doing some testing before I upload it to the host. Right now the new database is less than half the size of the old one. I am a bit surprised so I will have to test all the applications to be sure I am not missing anything. It is a process
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now