Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reduce the size of database Expand / Collapse
Author
Message
Posted Friday, September 20, 2013 4:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 08, 2013 1:20 AM
Points: 9, Visits: 22
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
Post #1496789
Posted Friday, September 20, 2013 4:39 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 11:09 AM
Points: 583, Visits: 962
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 | MCSA SQL Server 2012
Post #1496795
Posted Friday, September 20, 2013 4:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 08, 2013 1:20 AM
Points: 9, Visits: 22
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.
Post #1496797
Posted Friday, September 20, 2013 4:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 08, 2013 1:20 AM
Points: 9, Visits: 22
for information

database size is 16938.25 MB
and available space is 1455.23 MB
Post #1496801
Posted Friday, September 20, 2013 4:55 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 11:09 AM
Points: 583, Visits: 962
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 | MCSA SQL Server 2012
Post #1496802
Posted Friday, September 20, 2013 5:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 2,728, Visits: 2,645
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
Post #1496808
Posted Friday, September 20, 2013 3:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 31, 2013 5:45 PM
Points: 127, Visits: 306
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.
Post #1497051
Posted Saturday, September 21, 2013 10:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 35,967, Visits: 30,258
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497147
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse