How do I find out what prevents my log file from being truncated

Hi
I think I need a little help on Log file architecture.
I have a database where I temporarily disabled my log backup schedules. We
did some updates on the database, and I didn't wanted the backup jobs to
kick in in the middle of the update.
Now I've enabled the log backups again, but of course my logfiles has now
grown to a size that it will never reach again in normal production. I'd
therefore like to shrink the logfile to a more feasible size, but I don't
seem to have much luck with that.
I've backed up the logfile, but when I then issue a DBCC SHRINKFILE command,
I get the message -
'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
use.'
If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
open transaction but that reports no active transactions.
My question is now, how do I find out that it is that prevents me from
shrinking the file. I assume that I'll have to chekc the Virtual Log Files,
but how do I do this? I've looked in BOL but I can't find any description of
how I can "see" the logfile "structure"? I know that this has been covered
in this newsgroup before, but I can't find any threads that helps me right
now.
Regards
SteenSteen
What target size have you specified in DBCC SHRINKFILE command? Try to
increase it.
DECLARE @.db INT
SELECT @.db=db_id()
DBCC LOGINFO (@.db)
Pay attention on status column . If you see that value=2 is the last row
that means you have 'opened' ',in process' transaction
Perfom dummy inserst on order to move this value=2 at the top and then try
to shrink file
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE
command,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log
Files,
> but how do I do this? I've looked in BOL but I can't find any description
of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>|||Steen
Have a look at these articles.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Regards
John
"Steen Persson" wrote:

> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE comman
d,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log Files
,
> but how do I do this? I've looked in BOL but I can't find any description
of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>
>|||Hi Uri
Thanks for your reply.
I have actually tried to increase the target size, but that still doesn't
seems to do the trick. Also if I look at the physical file, it is approx. 21
GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 11
% i.e. the file should in theory only be taking up approx. 2 GB. Also if I
run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
records has the status 2. I can't find any describtion in BOL of the LOGINFO
command, so I'm not quite sure how to read it, but does the status 0 means
that it's "old" transactions that can be removed?
It might very well be that I still have some active transcations that
prevents my logfile from shrinking, but it puzzles me that I don't seems to
be able to "see" these transcations anywhere.
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Steen
> What target size have you specified in DBCC SHRINKFILE command? Try to
> increase it.
> DECLARE @.db INT
> SELECT @.db=db_id()
> DBCC LOGINFO (@.db)
> Pay attention on status column . If you see that value=2 is the last
> row that means you have 'opened' ',in process' transaction
> Perfom dummy inserst on order to move this value=2 at the top and
> then try to shrink file
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...|||The file can only be shrunk from the end towards the beginning. You need to
get 0 at the end. backup
the log to see if that makes you have 0 at the end. If not, do some dummy mo
difications so the head
of the log moves toward the beginning (in 2000, the shrink command should do
this for you). Then
backup again. When you have 0 at the end, you can shrink it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx
.gbl...
> Hi Uri
> Thanks for your reply.
> I have actually tried to increase the target size, but that still doesn't
> seems to do the trick. Also if I look at the physical file, it is approx.
21
> GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 1
1
> % i.e. the file should in theory only be taking up approx. 2 GB. Also if I
> run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
> records has the status 2. I can't find any describtion in BOL of the LOGIN
FO
> command, so I'm not quite sure how to read it, but does the status 0 means
> that it's "old" transactions that can be removed?
> It might very well be that I still have some active transcations that
> prevents my logfile from shrinking, but it puzzles me that I don't seems t
o
> be able to "see" these transcations anywhere.
> Regards
> Steen
>
>
> Uri Dimant wrote:
>|||Hi Tibor
I've also read that SQL 2000 fills in the "dummy" records when I do the
SHRINKFILE, but I think I've missed something. You say that the dummys are
being inserted by the SHRINKFILE command and then I have to backup again and
then I can run the SHRINKFILE once more. I haven't got that little "twist"
until now, so it sounds like it's the 2nd. backup I'm missing. When I think
about it I should have thought about that, but from BOL I had got the
impression that SQL2000 did it "behind the scenes".
After I've done the backup once more, it works like a charm....:-).
Thanks for your inputs everybody...
Regards
Steen
Tibor Karaszi wrote:[vbcol=seagreen]
> The file can only be shrunk from the end towards the beginning. You
> need to get 0 at the end. backup the log to see if that makes you
> have 0 at the end. If not, do some dummy modifications so the head of
> the log moves toward the beginning (in 2000, the shrink command
> should do this for you). Then backup again. When you have 0 at the
> end, you can shrink it.
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx.gbl...