I am using SQL 2005 64x on Server 2003 64x. My current db size is
approximently 30gb. My log file is around 211gb. I don't really have any
issues with speed or performance. This is a Dell Dual Xeon 2.6 with 16gb of
memory, but I am getting a little nervous on the size of the log file.

I am using Full recovery mode and I have done a full back up on the db and
log files with no reduction in the size of the log file. Is there another way
of reducing the log file size? Is there a certain order that I need to follow
when backing up the db? Should I be concerned with the size of the log file?

Thanks

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:C9762905-E3AB-4B34-B238-8325A6A9FBA0@microsoft.com...
>I am using SQL 2005 64x on Server 2003 64x. My current db size is
> approximently 30gb. My log file is around 211gb. I don't really have any
> issues with speed or performance. This is a Dell Dual Xeon 2.6 with 16gb of
> memory, but I am getting a little nervous on the size of the log file.
>
> I am using Full recovery mode and I have done a full back up on the db and
> log files with no reduction in the size of the log file. Is there another way
> of reducing the log file size? Is there a certain order that I need to follow
> when backing up the db? Should I be concerned with the size of the log file?
>
> Thanks

On Sep 11, 8:14 pm, Jeff <J...@discussions.microsoft.com> wrote:
> I am using SQL 2005 64x on Server 2003 64x. My current db size is
> approximently 30gb. My log file is around 211gb. I don't really have any
> issues with speed or performance. This is a Dell Dual Xeon 2.6 with 16gb of
> memory, but I am getting a little nervous on the size of the log file.
>
> I am using Full recovery mode and I have done a full back up on the db and
> log files with no reduction in the size of the log file. Is there another way
> of reducing the log file size? Is there a certain order that I need to follow
> when backing up the db? Should I be concerned with the size of the log file?
>
> Thanks

you also need to regularly take log backup regularly if your recovery
mode is full.
Read BOL in more detail for log backup.

Amish Shah
http://shahamishm.blogspot.com

Did you regularly take a backup of the tlog, or did you just do fulls until
some later time when the tlog had already grown huge? Full backups do NOT
flush out committed transactions from the tlog like tlog backups do.

run dbcc sqlperf(logspace) and see how full your tlog is. If it is a tiny
percentage (which it should be if you have recently done a tlog backup), you
can shrink the tlog back to something reasonable with the expectation that
it won't grow in the future because you are now doing routine tlog backups.
I would suggest 50% of the database size for the tlog size. Oh, and make
sure it has a reasonable growth increment. NOTE: shrinking the tlog should
be done off-hours as it hits the IO hard and will block database activity
(IIRC).

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:C9762905-E3AB-4B34-B238-8325A6A9FBA0@microsoft.com...
>I am using SQL 2005 64x on Server 2003 64x. My current db size is
> approximently 30gb. My log file is around 211gb. I don't really have any
> issues with speed or performance. This is a Dell Dual Xeon 2.6 with 16gb
> of
> memory, but I am getting a little nervous on the size of the log file.
>
> I am using Full recovery mode and I have done a full back up on the db and
> log files with no reduction in the size of the log file. Is there another
> way
> of reducing the log file size? Is there a certain order that I need to
> follow
> when backing up the db? Should I be concerned with the size of the log
> file?
>
> Thanks

You are correct. I have not been doing backups through SQL. This db is my
Sharepoint db and I have been using Veritas' addon to backup the db. I was
assuming that Veritas dealt with the log files but I found out that it did
not.

Running the dbcc sqlperf(logspace) shows a small percentage (1.1258%), right
again.

My db growth is unrestricted and grows by 1meg. The 1meg seems kind of small
does it not.

Also the log file is now restricted to what the file size was before I
backed it up. Should this be unrestriced as well?



"TheSQLGuru" wrote:

> Did you regularly take a backup of the tlog, or did you just do fulls until
> some later time when the tlog had already grown huge? Full backups do NOT
> flush out committed transactions from the tlog like tlog backups do.
>
> run dbcc sqlperf(logspace) and see how full your tlog is. If it is a tiny
> percentage (which it should be if you have recently done a tlog backup), you
> can shrink the tlog back to something reasonable with the expectation that
> it won't grow in the future because you are now doing routine tlog backups.
> I would suggest 50% of the database size for the tlog size. Oh, and make
> sure it has a reasonable growth increment. NOTE: shrinking the tlog should
> be done off-hours as it hits the IO hard and will block database activity
> (IIRC).
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Jeff" <Jeff@discussions.microsoft.com> wrote in message
> news:C9762905-E3AB-4B34-B238-8325A6A9FBA0@microsoft.com...
> >I am using SQL 2005 64x on Server 2003 64x. My current db size is
> > approximently 30gb. My log file is around 211gb. I don't really have any
> > issues with speed or performance. This is a Dell Dual Xeon 2.6 with 16gb
> > of
> > memory, but I am getting a little nervous on the size of the log file.
> >
> > I am using Full recovery mode and I have done a full back up on the db and
> > log files with no reduction in the size of the log file. Is there another
> > way
> > of reducing the log file size? Is there a certain order that I need to
> > follow
> > when backing up the db? Should I be concerned with the size of the log
> > file?
> >
> > Thanks
>
>
>

1mb is the default data file growth increment and is simply disasterous from
a performance standpoint for those that accept defaults and never modify the
settings. 10% for the log file.

you can set the max growth size to whatever you like, just be aware that if
you actually hit that your database operations stop cold.


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:57C2044E-7C9D-413A-8AF9-73D099665C16@microsoft.com...
> You are correct. I have not been doing backups through SQL. This db is my
> Sharepoint db and I have been using Veritas' addon to backup the db. I was
> assuming that Veritas dealt with the log files but I found out that it did
> not.
>
> Running the dbcc sqlperf(logspace) shows a small percentage (1.1258%),
> right
> again.
>
> My db growth is unrestricted and grows by 1meg. The 1meg seems kind of
> small
> does it not.
>
> Also the log file is now restricted to what the file size was before I
> backed it up. Should this be unrestriced as well?
>
>
>
> "TheSQLGuru" wrote:
>
>> Did you regularly take a backup of the tlog, or did you just do fulls
>> until
>> some later time when the tlog had already grown huge? Full backups do
>> NOT
>> flush out committed transactions from the tlog like tlog backups do.
>>
>> run dbcc sqlperf(logspace) and see how full your tlog is. If it is a
>> tiny
>> percentage (which it should be if you have recently done a tlog backup),
>> you
>> can shrink the tlog back to something reasonable with the expectation
>> that
>> it won't grow in the future because you are now doing routine tlog
>> backups.
>> I would suggest 50% of the database size for the tlog size. Oh, and make
>> sure it has a reasonable growth increment. NOTE: shrinking the tlog
>> should
>> be done off-hours as it hits the IO hard and will block database activity
>> (IIRC).
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>>
>> "Jeff" <Jeff@discussions.microsoft.com> wrote in message
>> news:C9762905-E3AB-4B34-B238-8325A6A9FBA0@microsoft.com...
>> >I am using SQL 2005 64x on Server 2003 64x. My current db size is
>> > approximently 30gb. My log file is around 211gb. I don't really have
>> > any
>> > issues with speed or performance. This is a Dell Dual Xeon 2.6 with
>> > 16gb
>> > of
>> > memory, but I am getting a little nervous on the size of the log file.
>> >
>> > I am using Full recovery mode and I have done a full back up on the db
>> > and
>> > log files with no reduction in the size of the log file. Is there
>> > another
>> > way
>> > of reducing the log file size? Is there a certain order that I need to
>> > follow
>> > when backing up the db? Should I be concerned with the size of the log
>> > file?
>> >
>> > Thanks
>>
>>
>>