How do I get the current size of the transaction log? How do I get the size limit?
I'd like to monitor this so I can determine how often I need to backup the transaction log.
I usually have a problem with the transaction log when I perform large operations.
3条答案
按热度按时间e1xvtsh31#
From SQL Server 2012, there is another DMV (Dynamic Management View) available - sys.dm_db_log_space_usage . It has the advantage of returning the transaction log size as opposed to the transaction log file size (which might include unused space). This will change as space is consumed and not just when the file grows.
This view alone would support stopping prior to the point at which the log file grows.
It could be used in combination with the sys.database_files view and its
max_size
column to get a more granular value for 'percentage of maximum size that has already been consumed' than would be possible with the sys.database_files view alone.sshcrbum2#
Based on SQL Server 2005, try this
Change YourDB to your database name
For an overall of all database sizes try DBCC SQLPERF
This should work in SQL 2000/2005/2008
mkh04yzy3#
If you want to monitor it in real time, try Performance Monitor (perfmon) while you are doing those large operations.
Perfmon can be used in many different scenarios.
Find out more from Technet .