SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 How to shrink a SQL 2005 database !!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 10/02/2008 :  16:47:41  Show Profile  Reply with Quote
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

USA
347 Posts

Posted - 10/02/2008 :  17:04:06  Show Profile  Reply with Quote
Can ayone help please.

Thank you soooooooo much in advance.

Zee
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 10/02/2008 :  17:07:34  Show Profile  Reply with Quote
Are you out of Disk space?

Read this from Tibor:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 10/02/2008 :  17:21:20  Show Profile  Reply with Quote
Yes. And I want to reduce the size of the database as much as possible.
Please help !!!!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36797 Posts

Posted - 10/02/2008 :  17:33:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 10/02/2008 :  18:07:53  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36797 Posts

Posted - 10/02/2008 :  18:10:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 10/02/2008 :  18:30:26  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36797 Posts

Posted - 10/02/2008 :  18:45:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

saurabhsrivastava
Posting Yak Master

USA
216 Posts

Posted - 10/03/2008 :  07:17:54  Show Profile  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 10/03/2008 :  09:07:30  Show Profile  Reply with Quote
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.
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 10/03/2008 :  12:12:04  Show Profile  Reply with Quote
Thanks everyone for providing useful information.

Go to Top of Page

saurabhsrivastava
Posting Yak Master

USA
216 Posts

Posted - 10/12/2008 :  22:17:05  Show Profile  Reply with Quote
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.
Go to Top of Page

ArthurZ
Starting Member

1 Posts

Posted - 08/25/2010 :  13:11:13  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000