Investigating what makes my log file grow and grow and grow

I need to learn the steps to investigate which
transactions are not getting replicated, thus causing my
log file to grow. I periodically check the dbs I
administer and wait until after hours to force
replacation done using sp_ReplDone, followed by a backup
and shrink, for those dbs with excessively large log
files. I only have this problem on the server that acts
as distributor and subscriber.
I even set up jobs that do the following, in order (by
start time):
1)exec sp_ReplDone @.xactid = NULL, @.xact_segno = NULL,
@.numtrans = 0, @.time = 0, @.reset = 1
2)Backup transaction log (part of maintenance plan)
3)Backup db (part of maintenance plan)
4)DBCC Shrinkfile
I have these jobs spread out every 1/2 hour to give them
plenty of time to complete before the next one begins.
Yet the log file doesn't shrink.
When I run DBCC OPENTRAN on the db, I get the following:
Transaction information for database 'Database1'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (570647:21697:1)
DBCC execution completed.
So, my two requests are:
1) why doesn't my shrink procedure work? (If I do these
steps by hand I can shrink the log file down to 1MB)
2) how do I determine what's causing the hangup that
starts the log file to grow.
Roger.
When you get a value for DBCC Opentran Oldest non-distributed LSN it means
there are transaction in your tlog that the log reader has not read yet, or
has not marked as replicated.
There is a problem with your log reader. I can't tell you what it is,
perhaps there is an error message on it. Perhaps your system is under load
and not enough resources are avaliable for the log reader - this is only
solved by a reboot.
I would never run sp_repldone the way you are. Basically what you are in
effect doing is saying I need all commands replicated for a time period, and
then when I run sp_repldone if there are any commands in the log, throw them
away.
It could also be that you are not dumping your log frequently enough. You
should be dumping it very frequently, perhaps every 20 minutes, or even
every 1-3 minutes if your database is large.
Pruning your tlog this way will improve overall system performance, and mean
your log reader has less log to read, hence its read performance will be
better. You might want to decrease its PollingInterval to 1s.
Also run a dbcc loginfo. Ideally you should have a small number of rows
which correspond to your virtual log files, ie 16 or so. Best of all 4. If
you have a large number you should try to size your tlog to something large
to prevent frequent autogrows.
Another factor which could cause your log to grow is orphaned transactions.
DBCC opentran should show this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Roger Denison" <anonymous@.discussions.microsoft.com> wrote in message
news:19be01c4bdd2$48a792e0$a401280a@.phx.gbl...
> I need to learn the steps to investigate which
> transactions are not getting replicated, thus causing my
> log file to grow. I periodically check the dbs I
> administer and wait until after hours to force
> replacation done using sp_ReplDone, followed by a backup
> and shrink, for those dbs with excessively large log
> files. I only have this problem on the server that acts
> as distributor and subscriber.
> I even set up jobs that do the following, in order (by
> start time):
> 1)exec sp_ReplDone @.xactid = NULL, @.xact_segno = NULL,
> @.numtrans = 0, @.time = 0, @.reset = 1
> 2)Backup transaction log (part of maintenance plan)
> 3)Backup db (part of maintenance plan)
> 4)DBCC Shrinkfile
> I have these jobs spread out every 1/2 hour to give them
> plenty of time to complete before the next one begins.
> Yet the log file doesn't shrink.
> When I run DBCC OPENTRAN on the db, I get the following:
> Transaction information for database 'Database1'.
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (570647:21697:1)
> DBCC execution completed.
> So, my two requests are:
> 1) why doesn't my shrink procedure work? (If I do these
> steps by hand I can shrink the log file down to 1MB)
> 2) how do I determine what's causing the hangup that
> starts the log file to grow.
> Roger.

Invalid Log Scan

I do not understand it but by connection to my database seems to keep crapping out. I get the below error and my app appears to be unable to access the connection. Then a couple restarts later it tells me that my login under my Windows User failed . The database is there and besides maybe a couple records longer there isnt anything corrupt about the data, and in fact, if I restart the entire PC most times it will allow me to connect again. I can even still access the data tables through the C# editor. Any idea why this error is being thrown?

Thx

Code Snippet

System.Data.SqlClient.SqlException was unhandled
Message="A system assertion check has failed. Check the SQL Server error log for details\r\nThe log scan number (35:460:2) passed to log scan in database 'F:\\PDLOGSHEET\\PDLOGSHEET\\BIN\\DEBUG\\DATABASE1.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. \r\nAn error occurred during recovery, preventing the database 'F:\\PDLOGSHEET\\PDLOGSHEET\\BIN\\DEBUG\\DATABASE1.MDF' (database ID 26) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.\r\nCannot open user default database. Login failed.\r\nLogin failed for user 'GC\\cbcottier'.\r\nLocation:\t logscan.cpp:1925\nExpression:\t UtilDbccIsInsideDbcc ()\nSPID:\t\t 51\nProcess ID:\t 3640\r\nLocation:\t logscan.cpp:1925\nExpression:\t UtilDbccIsInsideDbcc ()\nSPID:\t\t 51\nProcess ID:\t 3640"
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=20
LineNumber=65536
Number=3624
Procedure=""
Server="\\\\.\\pipe\\E74940B1-0BD7-42\\tsql\\query"
State=1
StackTrace:
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at PDLogSheet.Database1DataSetTableAdapters.ReportTitlesTableAdapter.Fill(ReportTitlesDataTable dataTable) in F:\PDLogSheet\PDLogSheet\Database1DataSet.Designer.cs:line 4362
at PDLogSheet.frmLogSheet.frmLogSheet_Load(Object sender, EventArgs e) in F:\PDLogSheet\PDLogSheet\frmLogSheet.cs:line 42
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at PDLogSheet.Program.Main() in F:\PDLogSheet\PDLogSheet\Program.cs:line 17
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

This error indicates you have some corruption in your database.

You can run the dbcc checkdb command to verify this.

|||

1) how/where would I run the command

2)That would be the case even though it functions for a time after the PC is restarted and I can still access the data tables in C# immediately following getting the error?

|||*bump*|||

Yes. You can have a single corrupted page in a table for example and the table will work fine until you hit the bad page.

So run dbcc checkdb and dbcc checktable to find + fix corruption.

interview question

here is a question asked in a interview:
u created the sales database with the initial size of hte transaction log of 50 mb with autogrow, but no autoshrink
u do not make any backup and donot set the truncate log on check point option to on.
what will be the transaction log size after the next check point has passed?Umm... >= 50mb|||You are in a job interview where the person hiring asks you inane and pointless questions that have little or no value in either database administration or database design, and for which the answers could easily be looked up in Books On Line. At the end of the interview, you are offered the job at a substantial salary bump. Do you work for the dolt, or do you continue seeking an employer that has actual competence?|||lol@.blindman. In the instance above, you tell him he hasn't provided you enough information (which he hasn't btw). If he could do some research though, create a business proposition for why he should waste your time answering stupid questions, get business signoff, send it through QA and user-acceptance testing, then test the release, you'll have a production answer for his highness right away.|||First, there's no "Trancate on checkpoint" in 2K, which means they are talking about 7.0 or 6.5, which is bad in itself.

Second, unless a user process kicked in within less than 0.00000001 millisecond after the "create database..." statement completed, peterlemonjello's answer is almost absolutely correct, - except it will be 50MB exactly, not more or equal to.|||hmmm what an interesting way of asking for clarification... I like it. :D
by the way, it was't me who was being interviewed and it was a colleague of mine, I wasn't sure about the question itself so thought to get some expert comments from the guru's here.
anyways thnks for the replies :p

Interpret Agent Log Entry

The log entry
2006-09-19 21:16:06 - + [000] Request to run job
0x6A98EE728DB2FC498C379735F3CE7567 (from Alert 11) refused
appears in our SQL Server Agent log. I'm try to track the source. So I'm
wondering what the hex value refer to and the bit "(from Alert 11)".
Thanks,
TomHard to say, could you query select * from msdb.dbo.sysjobs where
jobid=0x6A98EE728DB2FC498C379735F3CE7567
to determine which job this id belongs to
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"T Morris" <TMorris@.discussions.microsoft.com> wrote in message
news:40AB8EB6-A0F7-40BF-93D3-ABBBB73F9706@.microsoft.com...
> The log entry
> 2006-09-19 21:16:06 - + [000] Request to run job
> 0x6A98EE728DB2FC498C379735F3CE7567 (from Alert 11) refused
> appears in our SQL Server Agent log. I'm try to track the source. So I'm
> wondering what the hex value refer to and the bit "(from Alert 11)".
> Thanks,
> Tom

Interpret Agent Log Entry

The log entry
2006-09-19 21:16:06 - + [000] Request to run job
0x6A98EE728DB2FC498C379735F3CE7567 (from Alert 11) refused
appears in our SQL Server Agent log. I'm try to track the source. So I'm
wondering what the hex value refer to and the bit "(from Alert 11)".
Thanks,
TomHard to say, could you query select * from msdb.dbo.sysjobs where
jobid=0x6A98EE728DB2FC498C379735F3CE7567
to determine which job this id belongs to
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"T Morris" <TMorris@.discussions.microsoft.com> wrote in message
news:40AB8EB6-A0F7-40BF-93D3-ABBBB73F9706@.microsoft.com...
> The log entry
> 2006-09-19 21:16:06 - + [000] Request to run job
> 0x6A98EE728DB2FC498C379735F3CE7567 (from Alert 11) refused
> appears in our SQL Server Agent log. I'm try to track the source. So I'm
> wondering what the hex value refer to and the bit "(from Alert 11)".
> Thanks,
> Tom

Interpret Agent Log Entry

The log entry
2006-09-19 21:16:06 - + [000] Request to run job
0x6A98EE728DB2FC498C379735F3CE7567 (from Alert 11) refused
appears in our SQL Server Agent log. I'm try to track the source. So I'm
wondering what the hex value refer to and the bit "(from Alert 11)".
Thanks,
Tom
Hard to say, could you query select * from msdb.dbo.sysjobs where
jobid=0x6A98EE728DB2FC498C379735F3CE7567
to determine which job this id belongs to
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"T Morris" <TMorris@.discussions.microsoft.com> wrote in message
news:40AB8EB6-A0F7-40BF-93D3-ABBBB73F9706@.microsoft.com...
> The log entry
> 2006-09-19 21:16:06 - + [000] Request to run job
> 0x6A98EE728DB2FC498C379735F3CE7567 (from Alert 11) refused
> appears in our SQL Server Agent log. I'm try to track the source. So I'm
> wondering what the hex value refer to and the bit "(from Alert 11)".
> Thanks,
> Tom