Home > Handy Scripts > Transaction log file information

Transaction log file information

General T-Log info

all databases

select name
		, physical_name
		, state_desc
		, (size * 8)/1024 AS [Total Size in MB]
		, (size * 8)/1024 - ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) as [Used Space In MB]
		, ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) AS [Available Space In MB]
		, max_size
		, growth
		, is_percent_growth
from sys.master_files where type_desc = 'LOG';

given database

select name
		, physical_name
		, state_desc
		, (size * 8)/1024 AS [Total Size in MB]
		, (size * 8)/1024 - ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) as [Used Space In MB]
		, ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) AS [Available Space In MB]
		, max_size
		, growth
		, is_percent_growth
from sys.database_files where type_desc = 'LOG';

detailed transaction log info

select * from ::fn_dblog(null, null);
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: