Log File Shrink

I'm unable to shrink my log file. The databse is about 2 GB & the log file
has grown to 11.5 GB. I've run transaction backups every 3 hours, but the log
won't shrink. I need to reduce the size, to free up space on the drive. I've
tried to shrink the file & it says it is successful, but I cannot shrink it
enough. I've tried changing the recovery model to Simple & then shrinking the
log, but this doesn't work either.
Any suggestions will be appreciated.
BigSamBigSam wrote:
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the log
> won't shrink. I need to reduce the size, to free up space on the drive. I've
> tried to shrink the file & it says it is successful, but I cannot shrink it
> enough. I've tried changing the recovery model to Simple & then shrinking the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
> BigSam
--
I had a heap of trouble with the same problem one day. When I found
the solution, I saved the steps. Here they are:
1) DBCC Shrinkfile (<logfilename>, TruncateOnly)
2) Backup Log <dbname> With Truncate_Only
3) DBCC Shrinkfile (<logfilename>, TruncateOnly)
That should fix it.
Mike|||Sam,
If what Mike wrote doesn't help (and only as a last resort):
First, iiii Be sure to take a full backup of the database!!!!
Then, change the recovery mode to simple.
Next issue the syntax CHECKPOINT in Query Analyzer on that server then
issue DBCC Shrinkfile (<logfilename>, TruncateOnly).
Last, set your recovery mode back to Full.
SQLPoet
MikeR wrote:
> BigSam wrote:
> > I'm unable to shrink my log file. The databse is about 2 GB & the log file
> > has grown to 11.5 GB. I've run transaction backups every 3 hours, but the log
> > won't shrink. I need to reduce the size, to free up space on the drive. I've
> > tried to shrink the file & it says it is successful, but I cannot shrink it
> > enough. I've tried changing the recovery model to Simple & then shrinking the
> > log, but this doesn't work either.
> > Any suggestions will be appreciated.
> >
> > BigSam
> --
> I had a heap of trouble with the same problem one day. When I found
> the solution, I saved the steps. Here they are:
> 1) DBCC Shrinkfile (<logfilename>, TruncateOnly)
> 2) Backup Log <dbname> With Truncate_Only
> 3) DBCC Shrinkfile (<logfilename>, TruncateOnly)
> That should fix it.
> Mike|||Read about backup and especially recovery model in Books Online, Them this will probably help:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:7C3F7F32-36EF-49C3-B83C-FCE89FDF4147@.microsoft.com...
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the log
> won't shrink. I need to reduce the size, to free up space on the drive. I've
> tried to shrink the file & it says it is successful, but I cannot shrink it
> enough. I've tried changing the recovery model to Simple & then shrinking the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
> BigSam|||"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:7C3F7F32-36EF-49C3-B83C-FCE89FDF4147@.microsoft.com...
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the
log
> won't shrink. I need to reduce the size, to free up space on the drive.
I've
> tried to shrink the file & it says it is successful, but I cannot shrink
it
> enough. I've tried changing the recovery model to Simple & then shrinking
the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
Do a DBCC OPENTRAN on the database and make sure there are no open
transactions.
But it'snot clear to me if youmean the log is 11.5 gb and FULL or 11.5GB on
disk, but only partially full.
> BigSam