out of control T-Log? NEED HELP PLEASE...

Hello -
I have a problem with a T-Log file that just seems to keep growing no matter
what I do...
Here are the file sizes:
MDF 563,648 KB
LDF 3,244,544 KB !!!
I have done full backups on this database
I have done DBCC shrinkdatabase
I have done DBCC shrinkfile (<LDF filename>, TRUNCATEONLY) (where <LDF filen
ame> is the name of the logfile)
Nothing seems to affect the size of this file.
I even, in desperation, thought that perhaps there was an open transaction i
n the log file, so I ran DBCC OpenTran ('<DBNAME>') (where DBNAME is the na
me of the database) I got back no active open transactions.
There is just no way that I can see that this database (either MDF or LDF fi
le should be that big.
Does anyone have any advise ? Thanks in advance.
RandyvolFull backup doesn't empty the log backup. And shrink won't work either as
the haven't emptied the log file. Either do regular transaction log backups
or set the database to simple recovery model. As for shrinking of the log
files, there are a few useful links in below article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"randyvol" <randy.volters@.whaleyfoodservice.com> wrote in message
news:08ED6880-34CD-4A06-A18B-26DE8DA5AA27@.microsoft.com...
> Hello -
> I have a problem with a T-Log file that just seems to keep growing no
matter what I do...
> Here are the file sizes:
> MDF 563,648 KB
> LDF 3,244,544 KB !!!
> I have done full backups on this database
> I have done DBCC shrinkdatabase
> I have done DBCC shrinkfile (<LDF filename>, TRUNCATEONLY) (where <LDF
filename> is the name of the logfile)
> Nothing seems to affect the size of this file.
> I even, in desperation, thought that perhaps there was an open transaction
in the log file, so I ran DBCC OpenTran ('<DBNAME>') (where DBNAME is the
name of the database) I got back no active open transactions.
> There is just no way that I can see that this database (either MDF or LDF
file should be that big.
> Does anyone have any advise ? Thanks in advance.
> Randyvol|||first thing you can do is search for answers to this question. this questio
n is probably the most asked question in this newsgroup.
short and sweet answer is that you need to do transaction log backups.
randyvol wrote:

> Hello -
> I have a problem with a T-Log file that just seems to keep growing no matt
er what I do...
> Here are the file sizes:
> MDF 563,648 KB
> LDF 3,244,544 KB !!!
> I have done full backups on this database
> I have done DBCC shrinkdatabase
> I have done DBCC shrinkfile (<LDF filename>, TRUNCATEONLY) (where <LDF fil
ename> is the name of the logfile)
> Nothing seems to affect the size of this file.
> I even, in desperation, thought that perhaps there was an open transaction
in the log file, so I ran DBCC OpenTran ('<DBNAME>') (where DBNAME is the
name of the database) I got back no active open transactions.
> There is just no way that I can see that this database (either MDF or LDF
file should be that big.
> Does anyone have any advise ? Thanks in advance.
> Randyvol|||Sorry -
I neglected to state that I have also done T-log backups... at least I think
I have. Maybe you can tell me...
Here's the script for the particular DB in question...
<snip>
BACKUP LOG WHALEYAPPS TO
DISK = 'D:\EMSQLT_BKPS\WHALEYAPPS_LOG.trn'
WITH INIT, STATS = 25
<end snip>
-- Tibor Karaszi wrote: --
Full backup doesn't empty the log backup. And shrink won't work either as
the haven't emptied the log file. Either do regular transaction log backups
or set the database to simple recovery model. As for shrinking of the log
files, there are a few useful links in below article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"randyvol" <randy.volters@.whaleyfoodservice.com> wrote in message
news:08ED6880-34CD-4A06-A18B-26DE8DA5AA27@.microsoft.com...
> Hello -
matter what I do...
> MDF 563,648 KB
> LDF 3,244,544 KB !!!
> I have done DBCC shrinkdatabase
> I have done DBCC shrinkfile (<LDF filename>, TRUNCATEONLY) (where <LDF
filename> is the name of the logfile)
in the log file, so I ran DBCC OpenTran ('<DBNAME>') (where DBNAME is the
name of the database) I got back no active open transactions.
file should be that big.|||Yes, that is a log backup. Check out the links I have on the web page.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"randyvol" <anonymous@.discussions.microsoft.com> wrote in message
news:5769B966-0388-4C2E-A336-ADBF015123D2@.microsoft.com...
> Sorry -
> I neglected to state that I have also done T-log backups... at least I
think I have. Maybe you can tell me...
> Here's the script for the particular DB in question...
> <snip>
> BACKUP LOG WHALEYAPPS TO
> DISK = 'D:\EMSQLT_BKPS\WHALEYAPPS_LOG.trn'
> WITH INIT, STATS = 25
> <end snip>
>
> -- Tibor Karaszi wrote: --
> Full backup doesn't empty the log backup. And shrink won't work
either as
> the haven't emptied the log file. Either do regular transaction log
backups
> or set the database to simple recovery model. As for shrinking of the
log
> files, there are a few useful links in below article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "randyvol" <randy.volters@.whaleyfoodservice.com> wrote in message
> news:08ED6880-34CD-4A06-A18B-26DE8DA5AA27@.microsoft.com...
no
> matter what I do...
<LDF
> filename> is the name of the logfile)
transaction
> in the log file, so I ran DBCC OpenTran ('<DBNAME>') (where DBNAME
is the
> name of the database) I got back no active open transactions.
or LDF
> file should be that big.
>
>|||Hi Randy,
I am reviewing you post and since we have not heard from you for some time
in the newsgroup, I wonder if you still have some question about it besides
our community member and MVP's information. For any more question, please
post your message here and we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Our database is growing very fast. Please help us

Hello all,
We have microsoft sql database abouit 18-19 Gb. After each big calculation
our database grows on 3-5 Gb (mdf-file) sometimes on 25-35Gb but we don't ad
d
anything into database.
Please help us asap.Hi
Do you have any performance problem?
Have you tried using SQL Server Profiler to see what is going on the Server
during the calculation?
Do you backup the LOG file?
"AccessDevelopers" <AccessDevelopers@.discussions.microsoft.com> wrote in
message news:E238FB9F-534D-4569-9B21-1A552FAF2571@.microsoft.com...
> Hello all,
> We have microsoft sql database abouit 18-19 Gb. After each big calculation
> our database grows on 3-5 Gb (mdf-file) sometimes on 25-35Gb but we don't
add
> anything into database.
> Please help us asap.
>|||A database will not grow on its own.
What is a "big calculation"? Index rebuilds can add space to the
transaction log, are you sure it's the data file that is growing and not
the transaction log? Can you run sp_spacedused on every table in the
database to find out where your growth is occurring? You can then query
the tables that are growing the fastest to find out what is happening.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
AccessDevelopers wrote:
> Hello all,
> We have microsoft sql database abouit 18-19 Gb. After each big calculation
> our database grows on 3-5 Gb (mdf-file) sometimes on 25-35Gb but we don't
add
> anything into database.
> Please help us asap.
>|||Hi
If it really is your mdf and not the ldf that is growing, I would suspect
that your "big calculation" is causing a lot of fragmentation. A lot of page
splits can cause your database to need a lot more space even though you have
the same amount of data.
Run DBCC SHOWCONTIG (see BOL for details) against your tables to see how
much fragmentation you have. If the fragmentation is high, schedule a time t
o
perform some index rebuilds on the affected tables.
If this is the case you may want to make index rebuilds a part of the
process that performs your "big calculation". (Make sure it runs after it.
Hope this helps.
John
"Mark Allison" wrote:

> A database will not grow on its own.
> What is a "big calculation"? Index rebuilds can add space to the
> transaction log, are you sure it's the data file that is growing and not
> the transaction log? Can you run sp_spacedused on every table in the
> database to find out where your growth is occurring? You can then query
> the tables that are growing the fastest to find out what is happening.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> AccessDevelopers wrote:
>

Our database is growing very fast. Please help us

Hello all,
We have microsoft sql database abouit 18-19 Gb. After each big calculation
our database grows on 3-5 Gb (mdf-file) sometimes on 25-35Gb but we don't add
anything into database.
Please help us asap.
Hi
Do you have any performance problem?
Have you tried using SQL Server Profiler to see what is going on the Server
during the calculation?
Do you backup the LOG file?
"AccessDevelopers" <AccessDevelopers@.discussions.microsoft.com> wrote in
message news:E238FB9F-534D-4569-9B21-1A552FAF2571@.microsoft.com...
> Hello all,
> We have microsoft sql database abouit 18-19 Gb. After each big calculation
> our database grows on 3-5 Gb (mdf-file) sometimes on 25-35Gb but we don't
add
> anything into database.
> Please help us asap.
>
|||A database will not grow on its own.
What is a "big calculation"? Index rebuilds can add space to the
transaction log, are you sure it's the data file that is growing and not
the transaction log? Can you run sp_spacedused on every table in the
database to find out where your growth is occurring? You can then query
the tables that are growing the fastest to find out what is happening.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
AccessDevelopers wrote:
> Hello all,
> We have microsoft sql database abouit 18-19 Gb. After each big calculation
> our database grows on 3-5 Gb (mdf-file) sometimes on 25-35Gb but we don't add
> anything into database.
> Please help us asap.
>
|||Hi
If it really is your mdf and not the ldf that is growing, I would suspect
that your "big calculation" is causing a lot of fragmentation. A lot of page
splits can cause your database to need a lot more space even though you have
the same amount of data.
Run DBCC SHOWCONTIG (see BOL for details) against your tables to see how
much fragmentation you have. If the fragmentation is high, schedule a time to
perform some index rebuilds on the affected tables.
If this is the case you may want to make index rebuilds a part of the
process that performs your "big calculation". (Make sure it runs after it.
Hope this helps.
John
"Mark Allison" wrote:

> A database will not grow on its own.
> What is a "big calculation"? Index rebuilds can add space to the
> transaction log, are you sure it's the data file that is growing and not
> the transaction log? Can you run sp_spacedused on every table in the
> database to find out where your growth is occurring? You can then query
> the tables that are growing the fastest to find out what is happening.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> AccessDevelopers wrote:
>

Our database is growing very fast. Please help us

Hello all,
We have microsoft sql database abouit 18-19 Gb. After each big calculation
our database grows on 3-5 Gb (mdf-file) sometimes on 25-35Gb but we don't add
anything into database.
Please help us asap.Hi
Do you have any performance problem?
Have you tried using SQL Server Profiler to see what is going on the Server
during the calculation?
Do you backup the LOG file?
"AccessDevelopers" <AccessDevelopers@.discussions.microsoft.com> wrote in
message news:E238FB9F-534D-4569-9B21-1A552FAF2571@.microsoft.com...
> Hello all,
> We have microsoft sql database abouit 18-19 Gb. After each big calculation
> our database grows on 3-5 Gb (mdf-file) sometimes on 25-35Gb but we don't
add
> anything into database.
> Please help us asap.
>|||A database will not grow on its own.
What is a "big calculation"? Index rebuilds can add space to the
transaction log, are you sure it's the data file that is growing and not
the transaction log? Can you run sp_spacedused on every table in the
database to find out where your growth is occurring? You can then query
the tables that are growing the fastest to find out what is happening.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
AccessDevelopers wrote:
> Hello all,
> We have microsoft sql database abouit 18-19 Gb. After each big calculation
> our database grows on 3-5 Gb (mdf-file) sometimes on 25-35Gb but we don't add
> anything into database.
> Please help us asap.
>|||Hi
If it really is your mdf and not the ldf that is growing, I would suspect
that your "big calculation" is causing a lot of fragmentation. A lot of page
splits can cause your database to need a lot more space even though you have
the same amount of data.
Run DBCC SHOWCONTIG (see BOL for details) against your tables to see how
much fragmentation you have. If the fragmentation is high, schedule a time to
perform some index rebuilds on the affected tables.
If this is the case you may want to make index rebuilds a part of the
process that performs your "big calculation". (Make sure it runs after it.
Hope this helps.
John
"Mark Allison" wrote:
> A database will not grow on its own.
> What is a "big calculation"? Index rebuilds can add space to the
> transaction log, are you sure it's the data file that is growing and not
> the transaction log? Can you run sp_spacedused on every table in the
> database to find out where your growth is occurring? You can then query
> the tables that are growing the fastest to find out what is happening.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> AccessDevelopers wrote:
> > Hello all,
> > We have microsoft sql database abouit 18-19 Gb. After each big calculation
> > our database grows on 3-5 Gb (mdf-file) sometimes on 25-35Gb but we don't add
> > anything into database.
> >
> > Please help us asap.
> >
> >
>