Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reduce the size of database


Reduce the size of database

Author
Message
shiwani.plrs
shiwani.plrs
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 24
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
psingla
psingla
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 1249
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
shiwani.plrs
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 24
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
shiwani.plrs
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 24
for information

database size is 16938.25 MB
and available space is 1455.23 MB
psingla
psingla
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 1249
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
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4170 Visits: 4849
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,
‌SQL Server developer at Seavus
www.seavus.com
Dhananjay-440114
Dhananjay-440114
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 307
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45137 Visits: 39923
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search