transaction log

 DBCC LOG(DB-Name, 3)

DBCC OPENTRAN 找出数据库中最起始的活动事务，特别是事务起始时间时

DBCC SQLPERF(LOGSPACE) 找出每一个数据库已经使用的日志空间大小

DBCC LOGINFO 找出事务日志使用了多少虚拟日志数量，可以使用DBCC LOGINFO命令。它显示的细节内容就是你当前所连接数据库的内容

=============================================

 

--01 將資料庫 Northwind 切換為「簡單復原模式」，便會自動截斷交易記錄。  

ALTER DATABASE Northwind  

SET RECOVERY SIMPLE  

GO  

--02 若決定要備份交易記錄檔(*.ldf)，再將資料庫 Northwind 切換回「完整復原模式」  

ALTER DATABASE Northwind  

SET RECOVERY FULL  

GO  

 

===================================

 

DBCC LOG(<databasename >, <output >)

0: Return only the minimum of information for each operation -- the operation, its context and the transaction ID. (Default)
1: As 0, but also retrieve any flags and the log record length.
2: As 1, but also retrieve the object name, index name, page ID and slot ID.
3: Full informational dump of each operation.
4: As 3 but includes a hex dump of the current transaction log row.

Note that the larger the transaction log and the more detailed the information you ask for, the longer the dump will take.

DBCC LOG can also be called as a system function for use in a SELECTstatement or other queries via the fn_dblog function. For instance, the following command will retrieve the top 10 rows with all of the available transaction log columns from the currently-selected database's transaction log.

USE MASTER
SELECT TOP 10 * FROM ::fn_dblog(<start>, <end>)

To change the database context here, change the USE statement. The <start> and <end> parameter are the starting and ending logical sequence numbers (LSNs) for the retrieved records.

One very useful application of this is to determine which tables are suffering from page splits, a performance problem that can become chronic on systems with high database activity. To understand how often this happens on a given database, try the following command:

USE <database>
select [Object Name], [Index Name]
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'

This will tell you which tables and indices suffer from splits. If you get no records returned, then your database is probably not suffering from page splitting problems all that often.

Hint: Do this at times when your database is fairly heavily populated and has not yet been backed up or had records rotated out for archiving, as this gives you a more realistic picture of page splits.