Can't shrink a Transaction Log File

19 Mar 2005 11:45 PM

gemel

I backed up my log file and attempted to shrink it with DBCC
SHRINKFILE. The command executes but leaves the transaction log at
it's current size. I know that an uncommited tansaction can cause this
to occur.

a) How do I test for this?
b)How do I get rid of it?

Regards

John L

20 Mar 2005 12:21 AM

CC&JM

Hi Gemel,

Try to backup your transaction log again and then try to shrink the file.
If its possible to loose youre trans log info(i guess you dont) change the
recovery model to simple(you'll loose your log info), shrink your tlog to the
desired size, then change recovery model to full and finally backup youre
database.

Hope that this can help you.
Regards

Show quote

"gemel" wrote:

> I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
>
> a) How do I test for this?
> b)How do I get rid of it?
>
> Regards
>
> John L
>

20 Mar 2005 3:58 PM

Wayne Snyder

If you have a long running open transaction that could prevent the log from
shrinking..
DBCC opentran will show you the oldest transaction still in transaction
state.

You might also try
DBCC SQLPERF(logspace) to see how much space in the log is used

also
DBCC loginfo ( google on this to find more details.)

hope this helps

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

Show quote

"gemel" <jo***@glenavon1.demon.co.uk> wrote in message
news:gdhm31tl8rs5t2804ibhc9onv36lee5evb@4ax.com...
>I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
>
> a) How do I test for this?
> b)How do I get rid of it?
>
> Regards
>
> John L