Greg, thanks for the detailed explanation. As I am not a network
adminstrator, I might not know something that a network admin should.
Appreciate if you could tell me how to:
- Configure the logfiles so they don't need to expand and shrink.
- Update Stats.
- Index defrag.
And, do you actually recommend doing shrinking?
Thanks
David
On Wed, 05 Nov 2003 04:16:38 GMT, "Greg D. Moore \(Strider\)"
<mooregr@greenms.com> wrote:
[color=blue]
>
>"dchow" <dchow@hotmail.com> wrote in message
>news:fghgqvolcc28i5jbvqs039vpc0o567d9tj@4ax.com.. .[color=green]
>> We do backup nightly and perform integrity check as well as shrinking
>> the files weekly.
>> Does shrinking the database increase overhead like Greg said? I
>> thought shrinking the database is similar to compacting database in
>> Access. Is there a compacting process in SQL server?[/color]
>
>No, shrinking the database file merely shrinks the database file.
>
>And this can be a problem for the following reason:
>
>(btw, generally folks shrink the transaction log since that grows and
>shrinks a lot).
>
>If you have say two databases on a server:
>
>Both start out with a 25 MB transaction log and grow in 25 MB increments.
>(and nightly you shrink it back to 25 MB).
>
>You start out with 200 MB of free disk space, all in one big block.
>
>Log for D1 exceeds 25 MB and grows to 50MB
>
>Ideally this is contiguous space, let's assume it is.
>
>The log for D1 exceeds 50 MB and grows to 75 MB.
>
>This repeats until it reaches 175 MB. So far once nice big contiguous log
>file.
>
>Now, D2 exceeds its log of 25 MB and grows. Where's this chunk going to
>grow into? The remaining 25 MB of free space. Ok, not, a major problem,
>but you've just decreased the likelihood that that space was contiguous with
>D2's original log file.
>
>Now, you shrink D1's log back to 25 MB after backing it up.
>
>Meanwhile D2's log grows again. So it's not allocated in the free space
>previous used by part of D1's log file. This we already know is not
>contiguous to D2's 1st growth. So now D2's log file is spread across 3
>separate blocks on the disk.
>
>
>Now D1's log starts to grow again. It grows into the now free space.
>Again, this ends up not being contiguous.
>
>Rinse, lather repeat.
>
>Now, this is a rather derived example and in reality there are things that
>will help (the fact that SQL Server will be using virtual logs within your
>log file space, you're going to do a lot of linear reads so it'll read all
>from one block before moving on, etc.) But, keep in mind that while the
>server is expanding the log file, any pending transactions are held up until
>the space is allocated since obviously SQL Server can't write them to disk
>if the current space is filled.
>
>But, over all, the end effect is you end up with a fragmented log file which
>can affect performance.
>
>Is this is a huge problem? Probably not.
>
>But in general you should try to configure your logfiles so they don't need
>to expand and shrink.
>
>Do I practice what I preach? Of course not. :-) I have one server that
>does nightly rollups. The logfile can expand by a couple of gig overnight
>and then shrink again.
>
>Unfortunately, due to lack of enough disk space on this server, I have
>another process that runs at a different time that also needs disk space.
>So in this case I do compact the transaction log. I'd much rather not
>though.
>
>
>BTW, due to the way SQL Server operates, there's really no exact equivalent
>to ACCESS's compaction. However, updating Stats is generally a good idea if
>your data changes a lot. And index defrag from time to time might help, but
>I don't do that myself. (Though looking into doing it on a few dbs we have.)
>
>Hope that helps.
>
>
>[color=green]
>>
>>
>>
>> On Wed, 29 Oct 2003 09:38:01 -0000, "John Bell"
>> <jbellnewsposts@hotmail.com> wrote:
>>[color=darkred]
>> >Hi
>> >
>> >I don't think anyone has mentioned that you should have a maintenance[/color][/color]
>plan[color=green][color=darkred]
>> >(or equivalent jobs) that backups up the database, checks integrity, and
>> >shrinks the files.
>> >
>> >John
>> >
>> >If this is in place you should have the ability to recover in case of
>> >disaster and
>> >"dchow" <dchow@hotmail.com> wrote in message
>> >news:bfjtpvsqke2rc56chrvdvl8i0c8jcpeccp@4ax.com.. .
>> >> Thanks Simon. If fact we have RAID. But because I am not a network
>> >> admin guy, I didn't know too much about it. All I know was that I have
>> >> 50G on the data partition. Having learned that 45MB growth per day is
>> >> not particularly large made me more comfortable.
>> >>
>> >>
>> >> On 28 Oct 2003 01:01:20 -0800,
sql@hayes.ch (Simon Hayes) wrote:
>> >>
>> >> >dchow <dchow@hotmail.com> wrote in message
>> >news:<inerpv06qsnkscsnavmf249t71aqv7ge7v@4ax.com> ...
>> >> >> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI[/color][/color]
>hard[color=green][color=darkred]
>> >> >> disk. Didn't have the server and CPU model with me.
>> >> >>
>> >> >>
>> >> >> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
>> >> >> <damorgan@x.washington.edu> wrote:
>> >> >>
>> >> >> >dchow wrote:
>> >> >> >
>> >> >> >>Our database size is currently 4G and is incrementing at a rate of
>> >> >> >>45M/day. What is the max size of a SQL database? And what is the
>> >> >> >>size beyond which the server performance will start to go down?
>> >> >> >>
>> >> >
>> >> ><snip>
>> >> >
>> >> >Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
>> >> >that most people will ever need to consider. In practice the
>> >> >limitations are storage capacity and your ability to manage and
>> >> >backup/restore the database. A 4GB database is not large, and 45MB per
>> >> >day is a growth of about 16GB per year - 20GB is not particularly
>> >> >large either.
>> >> >
>> >> >It's not possible to say when performance will go down - it depends on
>> >> >the load you place on the server. You can use Performance Monitor and
>> >> >other tools to monitor CPU, disk access, memory use etc. to see if
>> >> >there's a bottleneck somewhere. Having a single 50GB hard drive seems
>> >> >rather limiting, if that's what you have - disk space is cheap, so
>> >> >most people can afford to get extra disks and use RAID (or perhaps a
>> >> >SAN/NAS) to improve performance by spreading the databases across
>> >> >multiple disks.
>> >> >
>> >> >In any case, discussing the size of a database or the hardware it runs
>> >> >on usually isn't as important as how well it has been designed. If you
>> >> >have a well designed database which is properly indexed and accessed
>> >> >using well-written code, then it will perform and scale well up to
>> >> >very large amounts of data. If you don't, then you can have
>> >> >performance problems with even small amounts of data.
>> >> >
>> >> >Simon
>> >>
>> >[/color]
>>[/color]
>[/color]
