Log File Shrink

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 i
t
> 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.
MikeI'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 lo
g
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 th
e
log, but this doesn't work either.
Any suggestions will be appreciated.
BigSam|||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 i
t
> 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 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 w
ill 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 i
t
> 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|||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 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 w
ill 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 i
t
> 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.

> BigSamsql

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

Log Agent fails "could not execute sp_repldone / sp_replcounters"

The Log Reader Agent has failed again with the error "Could not execute
sp_repldone/sp_replcounters.." and we are unable to restart the agent.
MSrepl_transactions table holds two entries on the Distribution database one
dated 9-24 and the other 10-10, and the xact_id is not the same as the one
listed with sp_replcmds run on the publisher. What can we do to restart the
Log Agent and not loose any transactions?
Please help...
Thanks,
Fidel Arbizu.
enable logging as per this kb and post the log back here.
http://support.microsoft.com/default...b;en-us;312292
"farbizu" <farbizu@.discussions.microsoft.com> wrote in message
news:8903046C-0E27-4200-AED8-C411BDF18E34@.microsoft.com...
> The Log Reader Agent has failed again with the error "Could not execute
> sp_repldone/sp_replcounters.." and we are unable to restart the agent.
> MSrepl_transactions table holds two entries on the Distribution database
> one
> dated 9-24 and the other 10-10, and the xact_id is not the same as the one
> listed with sp_replcmds run on the publisher. What can we do to restart
> the
> Log Agent and not loose any transactions?
> Please help...
> Thanks,
> Fidel Arbizu.
>

LOG

Please help.
I received the mnessage below in my SQL server Agent
Error log file.
[LOG] Unable to read local eventlog (reason: The data
area passed to a system call is too small)
I run SQL 2k, SP3, on WIN (adnvanced)2k,SP3.
Please advice or help?.This should help :-
http://support.microsoft.com/?id=811484
--
HTH
Ryan Waight, MCDBA, MCSE
"Olutimi" <olutimi@.hotmail.com> wrote in message
news:3e7e01c37c2e$c79f1fa0$a501280a@.phx.gbl...
> Please help.
> I received the mnessage below in my SQL server Agent
> Error log file.
> [LOG] Unable to read local eventlog (reason: The data
> area passed to a system call is too small)
> I run SQL 2k, SP3, on WIN (adnvanced)2k,SP3.
> Please advice or help?.
>|||This happens when the eventlog service empties then eventlog (which is done regularly). Nothing to
worry about, IMO.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Olutimi" <olutimi@.hotmail.com> wrote in message news:3e7e01c37c2e$c79f1fa0$a501280a@.phx.gbl...
> Please help.
> I received the mnessage below in my SQL server Agent
> Error log file.
> [LOG] Unable to read local eventlog (reason: The data
> area passed to a system call is too small)
> I run SQL 2k, SP3, on WIN (adnvanced)2k,SP3.
> Please advice or help?.
>

Log

When analysing my SQLAGENT.OUT FILE i get this error:
Unable to read local eventlog (reason: The handle is
invalid)
Anyone know what it is?Hi Nefasha,
This "Invalid Handle" error may occur when another process (could be third
party application) is trying to access the handle the same time that the
SQL Server Agent is trying to access the Event Log handler.
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.