How to shrink a SQL 2005 database !!!

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

SQL Server 2005 Forums

SQL Server Administration (2005)

How to shrink a SQL 2005 database !!!

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-10-02 : 16:47:41

Hi All,

I have a 72GB database. I want to shrink this database as much as possible.

Under SQL Managment studio when I see the property of this dataabase, the size of the database is 73071.01 MB and the space available is 4705.14MB.

The Files for this database and have following sizes.

MDF file size is 14,272 KB
NDF file size is 74,805,440 KB
LDF log file size is 5,000 KB.

Please suggest the best & easist way to reduce the size of this database as much as possible.

Thanks a million for your prompt help.

Zee

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-10-02 : 17:04:06

Can ayone help please.

Thank you soooooooo much in advance.

Zee

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-02 : 17:07:34

Are you out of Disk space?

Read this from Tibor:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-10-02 : 17:21:20

Yes. And I want to reduce the size of the database as much as possible.
Please help !!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-02 : 17:33:59

Have you tried shrinking the NDF file? If so and since the data is in the NDF file, you need to see what objects are in that and then purge as necessary.

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

Subscribe to my blog

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-10-02 : 18:07:53

No I did not try to shrink anything so far. I haven't done it before, and was looking for some guidelines to follow.
SO are you saying that I have to remove the actuall data? I want to keep all the data that is been written into the database, but want to remove any free space, or logs.

When I run sp_spaceused, I get the following no's for this database:

database_size: 73071.01 MB
unallocated space: 4705.14 MB
reserved: 70001648 KB
data: 56472072 KB
index_size: 13491712 KB
unused: 37864 KB

Please help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-02 : 18:10:03

I wouldn't bother with shrinking it as there isn't much disk space to recover.

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

Subscribe to my blog

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-10-02 : 18:30:26

tkizer,

Thanks for the information. But in order for me to communite to my team mates, I need to tell them exactly why this database cannot be shrink. Based on the sp_spaceused results how does it can be understood that it cannot be shrink. Please exaplain. Also could you please explain each element of my sp_spaceused result?

Thank you so much for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-02 : 18:45:00

I didn't say it couldn't be shrunk. Try running DBCC SHRINKFILE against the ndf file.

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

Subscribe to my blog

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2008-10-03 : 07:17:54

what is the fillfactor mentioned while creating indexes, if you do not see lot of updates/inserts etc. reduce fillfactor if not already, that will buy you some space. And I also do not wory about recovering 4GB space whereas database size is 74GB until I am completely out of free space. I would search other unused files on disk and delete it.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-03 : 09:07:30

quote:
Originally posted by saurabhsrivastava

what is the fillfactor mentioned while creating indexes, if you do not see lot of updates/inserts etc. reduce fillfactor if not already, that will buy you some space. And I also do not wory about recovering 4GB space whereas database size is 74GB until I am completely out of free space. I would search other unused files on disk and delete it.

This is not correct. It is just opposite of what he said.

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-10-03 : 12:12:04

Thanks everyone for providing useful information.

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2008-10-12 : 22:17:05

quote:
Originally posted by sodeep

quote:
Originally posted by saurabhsrivastava

what is the fillfactor mentioned while creating indexes, if you do not see lot of updates/inserts etc. reduce fillfactor if not already, that will buy you some space. And I also do not wory about recovering 4GB space whereas database size is 74GB until I am completely out of free space. I would search other unused files on disk and delete it.

This is not correct. It is just opposite of what he said.

Reduce fillfactor I meant to say reduce free space at page level if insert/update/delete r not much in db. Anyway thanks for pointing that out.

ArthurZ
Starting Member

1 Post

Posted - 2010-08-25 : 13:11:13

Hello all:

Well, in my case Release unused space option did not yield any shrinkage, but my colleague used Reorganize pages before releasing unused space and it shrunk the log file to like 95% less.

_____________________
Regards,

Arthur

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources