Reduce the size of database

Question

Post reply

Reduce the size of database

shiwani.plrs

SSC-Addicted

Points: 465
More actions
September 20, 2013 at 4:24 am

#277324

hello
The Size of my database has been increased to 10-20 GB, although the RAM of my server is quite good, still it takes lot of time to open reports ....is there is any way to reduce the size of database other than using shrink.
thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

psingla Hall of Fame Points: 3840 More actions · Answer 1

Shrink release the unused space from the databases.

There could be multiple reasons your report is running slow.

Either improper/bad/missing index,

bad queries.

to reclaim the space first check the db log size and data size separately

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

shiwani.plrs SSC-Addicted Points: 465 More actions · Answer 2

thanks for your reply , I agree shrink will only work on log files and data files will always remain same.

I also agree to your point that reports may be slow coz of query or lac of proper indexing,,cud plz suggest some way to work on indexes , or some other possibility to work on database , meanwhile i work on indexes.

shiwani.plrs SSC-Addicted Points: 465 More actions · Answer 3

for information

database size is 16938.25 MB

and available space is 1455.23 MB

psingla Hall of Fame Points: 3840 More actions · Answer 4

I didn't say that shrink work only on log file ,it can work on both.

but none is advisable until necessary.

back to your question:

you can use dmvs to find missing,unused indexes

you can use profiler

create a maintenance plan for index rebuilding/reorganizing if you don't have one

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

shiwani.plrs (9/20/2013)
for information
database size is 16938.25 MB
and available space is 1455.23 MB

Hi,

According to your info, you should not shrink the database's files.

If you do that, then do not forget to rebuild all the indexes. Shrink causes very high fragmentation.

Try to find some missing indexes, or check if your code is causing the low performance behaivour.

16GB is small size.

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

Dhananjay-440114 Ten Centuries Points: 1365 More actions · Answer 6

If space is not a problem ... Do not go for shrink.. In your care you are facing problem in getting new connection if i am correct. That may be because to performance issue with the DB, may locking or blocking. You need to check various factor causing this issue.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

shiwani.plrs (9/20/2013)
hello
The Size of my database has been increased to 10-20 GB, although the RAM of my server is quite good, still it takes lot of time to open reports ....is there is any way to reduce the size of database other than using shrink.
thanks

When is the last time you rebuilt/reorged the indexes and when is the last time you updated statistics? I wouldn't mess around with anything else until those two things have been done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)