log shipping file size
in [Server Configuration]
|
Prev: Update works in one database, but not another
Next: Reminder - Microsoft Responds to the Evolution of Community
From: someone on 19 May 2010 15:15 Hi, just notice one of log shipping file size is very big(almost 2 GB), normally the transation log file size is 40 MB. How to know more about the big file? Can I track what's transation cause the file big? Please advice. Thanks.
From: Erland Sommarskog on 19 May 2010 17:45 (someone(a)js.com) writes: > Hi, just notice one of log shipping file size is very big(almost 2 GB), > normally the transation log file size is 40 MB. How to know more about > the big file? Can I track what's transation cause the file big? Please > advice. Thanks. Do you have any maintenance jobs that reindexes tables? That is certainly a suspect. I don't think there is any practical way to read the log to find out what the source was. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: someone on 20 May 2010 11:12 Thanks Erlan, I checked, no job for reindexes, but may be someone manuly did it. I'm thinking the big log file should have some info about that? Please advcie. Can I use fn_dblog to find out? "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D7DF1A595B31Yazorman(a)127.0.0.1... > (someone(a)js.com) writes: > > Hi, just notice one of log shipping file size is very big(almost 2 GB), > > normally the transation log file size is 40 MB. How to know more about > > the big file? Can I track what's transation cause the file big? Please > > advice. Thanks. > > Do you have any maintenance jobs that reindexes tables? That is certainly > a suspect. > > I don't think there is any practical way to read the log to find out > what the source was. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Erland Sommarskog on 20 May 2010 17:46
(someone(a)js.com) writes: > I checked, no job for reindexes, but may be someone manuly did it. I'm > thinking the big log file should have some info about that? Please advcie. > Can I use fn_dblog to find out? The information from fn_dblog is not very easy to interpret. At least it does not tell me very much. One way to find out what happned is to restore a backup into two databases, and the apply logs to different points in time, and then compare the database for differences with for instance SQL Compare from Red Gate. But I think you would have a lot of time on your hands to do this. What size the transaction log file have before this event? There may be an auto-grow event in the default trace that can give you some information. (Not command, but spid and hostname.) -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |