Database and log file size

I've been reading SQL Server High Availability and went thru some recent
webcasts by Kimberly Tripp and from what I've found out, I think we need to
do some adjustments to our database. It is SQL Server 2000 back end for an
ecommerce site. When it was set up, we just went with default options, but
now we need to do some changing. When I did dbcc loginfo, it came back with
275 vlfs. The database size after about a year and a half is 3.9 GB, it says
space available 1.4 GB.
Space Allocated is
Data File 2.4 gb
Transaction log 1.4 GB
It is running in full recovery mode. Transaction logs are backed up every 2
hours. Database is backed up once a day.
I'm afraid that some of these properties are causing slowdowns and deadlocks
.
What would be a reasonable size to set the db and transaction log to at this
growth rate? And grow it by what % or MB? I don't really know what to even
start with and I don't want to do something that will make performance worse
.
Thanks for any help.
--
JudyJudy,
These settings basically look fine and are probably not associated with
slowdowns and especially not deadlocks. You should always have plenty of
free space in the data and log files. It sounds like the log file is fine
but I can't tell from that how much free space you have in the datafile.
Right click on the database node in EM and choose "view - Task Pad" to see
the breakdown for each file.The growth should be a fixed size in MB. The
size should be small enough so that it can grow in about 10 seconds or less
but not too small that it would grow over and over.
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy|||The database shows 2438.75MB allocated with 2372.06MB used.
The transaction log shows .1441.3 MB allocated with 104.9 used after a log
backup an hour and 1/2 previously.
The growth is fixed at 100MB for both database and log files.
--
Judy
"Andrew J. Kelly" wrote:

> Judy,
> These settings basically look fine and are probably not associated with
> slowdowns and especially not deadlocks. You should always have plenty of
> free space in the data and log files. It sounds like the log file is fine
> but I can't tell from that how much free space you have in the datafile.
> Right click on the database node in EM and choose "view - Task Pad" to see
> the breakdown for each file.The growth should be a fixed size in MB. The
> size should be small enough so that it can grow in about 10 seconds or les
s
> but not too small that it would grow over and over.
> --
> Andrew J. Kelly SQL MVP
>
> "Judy" <Judy@.discussions.microsoft.com> wrote in message
> news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
>
>|||You will want more free space in the data file. The log seems fine.
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:0EF8EC72-769A-4304-8107-36ECB56B465B@.microsoft.com...[vbcol=seagreen]
> The database shows 2438.75MB allocated with 2372.06MB used.
> The transaction log shows .1441.3 MB allocated with 104.9 used after a log
> backup an hour and 1/2 previously.
> The growth is fixed at 100MB for both database and log files.
> --
> Judy
>
> "Andrew J. Kelly" wrote:
>|||The idea log size is the smallest that you can make it without it having to
grow before you back it up. If the transaction log really reaches 1.4GB used
before a backup, then that is your ideal size - well...maybe a little bit of
room to spare...like perhaps 2GB.
So to follow Kimberly's suggestion, backup the transcaction log, shrink it,
and then set it's size back to 1.4GB. The idea is that having to grow the
log is what you want to avoid.
As for the database file grow amounts, i personally like the 20% grow
amount. Every time the database grows, 20% allocates a lot of space (A 2MB
file growing by 20% is a lot less space added compared to a 2GB file growing
by 20%)
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy