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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment