Need to look into Log file.

All replies

• 

  

  1

  Sign in to vote

  You can read log file by below command

  fn_dump_dblog()
  DBCC Loginfo

  • Marked as answer by Geoferry Toburen Sunday, September 1, 2013 4:22 PM

  Friday, August 30, 2013 10:45 PM
• 

  

  1

  Sign in to vote

  Hi ,

  Select * from fn_dblog(NULL,NULL)

  Try these links

  http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

  http://raresql.com/2013/04/15/sql-server-undocumented-function-fn_dblog/

  http://technet.microsoft.com/en-us/library/ms189768.aspx

  http://www.confio.com/logicalread/sql-server-dbcc-log-command-details-and-example/#.UiFmIH-y4hA

  http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/

  ---

  sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

  • Proposed as answer by Shanky_621MVP Saturday, August 31, 2013 7:50 AM

  Saturday, August 31, 2013 3:45 AM
• 

  

  1

  Sign in to vote

  Hello ,

  All commands and links suggested above can give you great info about whats present in transaction log ,but remember they are undocumented command and you cannot claim support from MS for them.

  If Using below commands doesn't provide you info what you are looking for,may be then you have to give some bucks and look for Some third party tool in market which reads complete transaction log.I have never used TP tool just wrote this to give you more information

  ---

  Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

  Saturday, August 31, 2013 6:21 AM
• 

  

  1

  Sign in to vote

  Not really, but what are  you looking for? For example you can identify transactions that dropped the table

  The following code works for 2000, 2005, and 2008:

  SELECT [Transaction Id], [Begin Time], [UID], [SPID]
  FROM ::fn_dblog (NULL, NULL)
  WHERE [Transaction Name] = 'DROPOBJ'

  You can turning on trace flag 2537. It allows the function to look at *all* possible log, not just the active log.

  Select [PAGE ID],[Slot ID],[AllocUnitId],[Transaction ID] ,[RowLog Contents 0]
  , [RowLog Contents 1],[RowLog Contents 3],[RowLog Contents 4] ,[Log Record]
  FROM    sys.fn_dblog(NULL, NULL)
  WHERE AllocUnitId IN
  (Select [Allocation_unit_id] from sys.allocation_units allocunits
  INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
  AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2  AND partitions.partition_id = allocunits.container_id)
  Where object_id=object_ID('' + 'dbo.student' + ''))
  AND Operation in ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS')
  And [Context] IN   ('LCX_HEAP','LCX_CLUSTERED')

  ---

  Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
  
  MS SQL optimization: MS SQL Development and Optimization
  MS SQL Blog: Large scale of database and data cleansing
  Remote DBA Services: Improves MS SQL Database Performance
  

  • Marked as answer by Geoferry Toburen Sunday, September 1, 2013 4:22 PM

  Saturday, August 31, 2013 8:39 AM

  Answerer