Smaller database

Smaller database

Post by Jason Po » Tue, 04 Mar 2003 15:01:47



Hi

Is there any other way to make a database size smaller (megabytes) other
than to do the "all tasks, shrink database" route?
Reason i ask, is because i have two database with the same content, but the
one database is twice the size of the other...
The bigger database has been worked on a lot more.

I have deleted the log files, so i NEED to get the actual MDF file down in
size

Thanks
Jason

 
 
 

Smaller database

Post by Allan Mitchel » Tue, 04 Mar 2003 15:24:18


Hello, Jason!

If they both have the same content and structure what is it that is the
difference in Size.  Is it the T Log.?  Is there extra space in the Data
file ?

Q272318   INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
SHRINKFILE

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

: Is there any other way to make a database size smaller (megabytes) other
: than to do the "all tasks, shrink database" route?
: Reason i ask, is because i have two database with the same content, but
the
: one database is twice the size of the other...
: The bigger database has been worked on a lot more.

: I have deleted the log files, so i NEED to get the actual MDF file down in
: size

      ---  Microsoft Outlook Express 6.00.2800.1106

 
 
 

Smaller database

Post by Uri Diman » Tue, 04 Mar 2003 15:28:54


Jason
It should not be any differnce with perfomance on big size or small size
databases
You did mention that both databases containt the same amount of data
I actually have two databases with the same amount of data but differnt size
So when i run two queries (identical)on these databases  i did not see any
difference with perfomance.

Also when you create a new database try not specify automatic growth ,
we want to minimize how often automatic growth occurs. One way to help do
this is to size the database and transaction logs as accurately as possible
to their "final" size. Sure, this is virtually impossible to get
right-on-target. But the more accurate your estimates (and some times it
takes a some time to come up with a good estimate), the less SQL Server will
have to automatically grow its database and transaction logs.


Quote:> Hi

> Is there any other way to make a database size smaller (megabytes) other
> than to do the "all tasks, shrink database" route?
> Reason i ask, is because i have two database with the same content, but
the
> one database is twice the size of the other...
> The bigger database has been worked on a lot more.

> I have deleted the log files, so i NEED to get the actual MDF file down in
> size

> Thanks
> Jason

 
 
 

Smaller database

Post by Jason Po » Tue, 04 Mar 2003 15:40:12


Thanks

Well i am not worried about performance, its more of an issue of deployment.
This database gets packaged - and smaller is preferable.

It's not the transaction log that is getting huge, and there is no extra
space in the data file. (to answer the other post)

Jason


> Jason
> It should not be any differnce with perfomance on big size or small size
> databases
> You did mention that both databases containt the same amount of data
> I actually have two databases with the same amount of data but differnt
size
> So when i run two queries (identical)on these databases  i did not see any
> difference with perfomance.

> Also when you create a new database try not specify automatic growth ,
> we want to minimize how often automatic growth occurs. One way to help do
> this is to size the database and transaction logs as accurately as
possible
> to their "final" size. Sure, this is virtually impossible to get
> right-on-target. But the more accurate your estimates (and some times it
> takes a some time to come up with a good estimate), the less SQL Server
will
> have to automatically grow its database and transaction logs.



> > Hi

> > Is there any other way to make a database size smaller (megabytes) other
> > than to do the "all tasks, shrink database" route?
> > Reason i ask, is because i have two database with the same content, but
> the
> > one database is twice the size of the other...
> > The bigger database has been worked on a lot more.

> > I have deleted the log files, so i NEED to get the actual MDF file down
in
> > size

> > Thanks
> > Jason

 
 
 

Smaller database

Post by Uri Diman » Tue, 04 Mar 2003 15:44:09


Well, so what did you mean
<The bigger database has been worked on a lot more.> ?


> Thanks

> Well i am not worried about performance, its more of an issue of
deployment.
> This database gets packaged - and smaller is preferable.

> It's not the transaction log that is getting huge, and there is no extra
> space in the data file. (to answer the other post)

> Jason



> > Jason
> > It should not be any differnce with perfomance on big size or small size
> > databases
> > You did mention that both databases containt the same amount of data
> > I actually have two databases with the same amount of data but differnt
> size
> > So when i run two queries (identical)on these databases  i did not see
any
> > difference with perfomance.

> > Also when you create a new database try not specify automatic growth ,
> > we want to minimize how often automatic growth occurs. One way to help
do
> > this is to size the database and transaction logs as accurately as
> possible
> > to their "final" size. Sure, this is virtually impossible to get
> > right-on-target. But the more accurate your estimates (and some times it
> > takes a some time to come up with a good estimate), the less SQL Server
> will
> > have to automatically grow its database and transaction logs.



> > > Hi

> > > Is there any other way to make a database size smaller (megabytes)
other
> > > than to do the "all tasks, shrink database" route?
> > > Reason i ask, is because i have two database with the same content,
but
> > the
> > > one database is twice the size of the other...
> > > The bigger database has been worked on a lot more.

> > > I have deleted the log files, so i NEED to get the actual MDF file
down
> in
> > > size

> > > Thanks
> > > Jason

 
 
 

Smaller database

Post by Tibor Karasz » Tue, 04 Mar 2003 15:51:59


The GUI does DBCC SHRINKDB. I recommend that you do DBCC SHRINKFILE instead; it "bites" better.
Also, fragmentation among datapages and index pages (inside and between) and be a contributing
factor. You might want to read up on DBCC DBREINDEX.

--
Tibor Karaszi, SQL Server MVP
For help on TSQL, please provide code we can execute in Query Analyzer


> Hi

> Is there any other way to make a database size smaller (megabytes) other
> than to do the "all tasks, shrink database" route?
> Reason i ask, is because i have two database with the same content, but the
> one database is twice the size of the other...
> The bigger database has been worked on a lot more.

> I have deleted the log files, so i NEED to get the actual MDF file down in
> size

> Thanks
> Jason

 
 
 

Smaller database

Post by Jason Po » Tue, 04 Mar 2003 15:58:32


Well yes :-)
The bigger of the two database has been more active. I mean, the one
database is a production database, so activity on that one is minimal. and
the other database is the development database. a lot more activity because
of testing, and development, etc...
The development database is bigger that the production database. I am not
talking about log files, just the .mdf files

Thanks
Jason


> Well, so what did you mean
> <The bigger database has been worked on a lot more.> ?



> > Thanks

> > Well i am not worried about performance, its more of an issue of
> deployment.
> > This database gets packaged - and smaller is preferable.

> > It's not the transaction log that is getting huge, and there is no extra
> > space in the data file. (to answer the other post)

> > Jason



> > > Jason
> > > It should not be any differnce with perfomance on big size or small
size
> > > databases
> > > You did mention that both databases containt the same amount of data
> > > I actually have two databases with the same amount of data but
differnt
> > size
> > > So when i run two queries (identical)on these databases  i did not see
> any
> > > difference with perfomance.

> > > Also when you create a new database try not specify automatic growth ,
> > > we want to minimize how often automatic growth occurs. One way to help
> do
> > > this is to size the database and transaction logs as accurately as
> > possible
> > > to their "final" size. Sure, this is virtually impossible to get
> > > right-on-target. But the more accurate your estimates (and some times
it
> > > takes a some time to come up with a good estimate), the less SQL
Server
> > will
> > > have to automatically grow its database and transaction logs.



> > > > Hi

> > > > Is there any other way to make a database size smaller (megabytes)
> other
> > > > than to do the "all tasks, shrink database" route?
> > > > Reason i ask, is because i have two database with the same content,
> but
> > > the
> > > > one database is twice the size of the other...
> > > > The bigger database has been worked on a lot more.

> > > > I have deleted the log files, so i NEED to get the actual MDF file
> down
> > in
> > > > size

> > > > Thanks
> > > > Jason

 
 
 

1. 1 big database against many small databases....????

Im having problems designing db in sql7, i need to manage a lot of
information in my enterprise...and i dont know how should it be better, to
have a big database or multiple small databases...

Can any one tell me about it???
help me please!!!

2. Closing OnLine session

3. One Big Database or 5 small databases

4. Agent

5. Multiple small databases vs one large database

6. Prioritize Your Sales Leads

7. Choice of database solution for a small database.

8. Select help needed

9. big tble in small database --how to handle?

10. big table in a small database?

11. One Large vs Many Smaller databases

12. Big table in a small database?

13. Big or Small Database Devices?