[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Shrink my database

Posted on 2011-03-08
10
Medium Priority
?
339 Views
Last Modified: 2012-05-11
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
Comment
Question by:ruffone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35079575
try:-

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

Open in new window

0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35079597
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
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35079630
if you want to shrink your database regularly then
on Database Properties-->Options set Auto Shrink=True.
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 4

Author Comment

by:ruffone
ID: 35079771
That does nothing. I am becomming convinced that 600mb is the solid size of this database dispite what is in the table
0
 
LVL 5

Accepted Solution

by:
ThakurVinay earned 2000 total points
ID: 35079992
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
 
LVL 4

Author Comment

by:ruffone
ID: 35080114
I would, but that is almost the max that my host will allow on the server that I share
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35080164
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35080181
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35080215
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
 
LVL 4

Author Closing Comment

by:ruffone
ID: 35212541
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question