Re: Setting database size

• Previous message: LMiller: "Re: Enterprise Management MMC Plug-in"
• In reply to: Amol Kasbekar: "Re: Setting database size"
• Messages sorted by: [ date ] [ thread ]

Date: Sat, 1 May 2004 21:04:03 +0100

Amol,

Yes you can use ALTER DATABASE command, look it up in Books Online for
further information.

I suspect you would need something like

ALTER DATABASE Amol
MODIFY FILE (NAME = Amol_Data, SIZE = 5TB)

This would expand the database Amol, logical file Amol_data to 5TB (assuming
you have that much disk space) :-)

Be aware that some disk activity will have to take place, and will consume
server resources.

-- 
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Amol Kasbekar" <apk@nospam.com> wrote in message
news:ehVc3jrLEHA.2148@TK2MSFTNGP09.phx.gbl...
> Mark,
>   Can this be done for an existing DB. For eg. If I already have a db in
use
> at 250Mb and I want to set it at a max size of 1 gigs with no growth, how
> can I do that ?
>
>  Amol.
>
> "Mark Allison" <marka@no.tinned.meat.mvps.org> wrote in message
> news:OVYjP8pLEHA.3052@TK2MSFTNGP12.phx.gbl...
> > Bill,
> >
> > Yes, it is always a good idea to create your database at the final
> > anticipated size to prevent fragmentation. Before you create the
database
> > file, stop MSSQLServer and do a disk defragmentation.
> >
> > Then, create your database file to be the max you expect it to be (same
> for
> > log). Switch off autogrow and autoshrink. You should have a contiguous
> > high-performing data file now.
> >
> > -- 
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> >
> >
> >
> > "Bill Bradley" <wdbradley3@comcast.net> wrote in message
> > news:umbLSmnLEHA.1348@TK2MSFTNGP12.phx.gbl...
> > > We're using a patch scanning product, Shavlik HFNetChkPro, with SQL2K
as
> > the
> > > backend running on a Win2K3 server, dual CPU, and 4 GB RAM.
> > >
> > > I have five drives, OS, SWAP, APPS, SQL, LOGS, and have all the
products
> > > separated out on individual drives as named.
> > >
> > > We regularly scan up to 1950 computers, and have noticed that the
> > ShavlikDB
> > > will get as large as 180 MB and its logfile will get up to 80 MB.
> During
> > > actual scans, HFNetChkPro has consumed as high as 600 MB of memory,
and
> > SQL
> > > up to 180 MB.  I periodically delete old scans and reboot the machine
> > which
> > > seems to lower both HD and memory consumption.
> > >
> > > I was wondering if it would do any good to just set the database to a
> > large
> > > value (250 MB and 100 MB logfile) and defrag those drives, so that
> there's
> > > no issue with growing or shrinking or fragmentation?  I have set
TempDB
> to
> > > 20/20 MB, as it was getting into the 12-15 MB range.
> > >
> > > Thanks!
> > >
> > >
> >
> >
>
>

• Previous message: LMiller: "Re: Enterprise Management MMC Plug-in"
• In reply to: Amol Kasbekar: "Re: Setting database size"
• Messages sorted by: [ date ] [ thread ]