Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Database size does not change

Author  Topic 

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2009-12-01 : 01:41:49
Hi,

I had delete some data from my sql server 2000 database, but after I deleted it, when i checked the properties of my database, the size did not change where it should decrease after i delete some of the data.

Anyone can help me on this issue???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-01 : 02:07:23
The file size will not decrease when you delete data. In order to decrease the file size, you must run DBCC SHRINKFILE. But be warned that this will cause performance issues down the road, so be sure that you will not need that bigger file size in the near future.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2009-12-01 : 03:35:34
Hi, is there any sample for me to have a look for the DBCC SHRINKFILE because I never know about this.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2009-12-01 : 03:38:23
USE MyDB
DBCC SHRINKFILE (dataFile, 10)
GO


If you don't include the target size (in MB) , then the file will be shrunk to a default size


Jack Vamvas
--------------------
http://www.ITjobfeed.com (IT jobs)
Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2009-12-01 : 03:41:56
Hi, I has searched for the DBCC Shrinkfile, it actually is use to reduce the database size. But what I needed is I need to know the size of database after I delete my data.
Go to Top of Page

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-01 : 03:56:05
Hi

More clear

All data and log files for a specific database. Execute DBCC SHRINKDATABASE.
One data or log file at a time for a specific database. Execute DBCC SHRINKFILE.



-------------------------
R...
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-01 : 07:44:25
quote:
Originally posted by daniel50096230

Hi, I has searched for the DBCC Shrinkfile, it actually is use to reduce the database size. But what I needed is I need to know the size of database after I delete my data.


Have you read Tara's reply?
Re-read it

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2009-12-01 : 09:32:16
Frankly speaking, I just want to know the size of my database after I deleted some data... But what I can see is the size still remain the same as before the data been deleted after I had deleted my data.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-01 : 10:20:46
In 2005 click on your database in Object Explorer,
then choose Disk Usage fom Report in Summary window.

In 2008 right click database - reports - disk Usage.

There are all inforamtion provided that you want.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-01 : 10:51:55
quote:
Originally posted by daniel50096230

Frankly speaking, I just want to know the size of my database after I deleted some data... But what I can see is the size still remain the same as before the data been deleted after I had deleted my data.



If you can see the size remains the same after the delete, then how can you not see the database size?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2009-12-01 : 19:43:36
Hi, I think I has confused you all. For example, before I delete my data, the database size is 200mb, but after I delete a lot of data, it still remain 200mb. I just want to know how can be like this???
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-02 : 00:02:28
How are you viewing the database size? I have a feeling that what you are using to view it is showing you the file sizes, which means it will only go down in size if you run DBCC SHRINKFILE.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page
   

- Advertisement -