How much log is full
-
Hi all,
How to know how much the log file is used or what percentage of logfile is been used.
from
killer
-
Try running DBCC SQLPERF (LOGSPACE). It will give you log size and percent utilization for all databases.
-
This stored procedure goes slightly further by enabling a program to query the log space used easily:
CREATE PROCEDURE dbo.DBO_LogSpace
as
declare @x int
set nocount on
if exists (select * from dbo.sysobjects where id = object_id(N'#logspace') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
delete from #logspace
end
else
begin
create table #logspace
(
VC_Database varchar(50),
DC_Log_Size decimal(9,3),
DC_Log_Used decimal(9,3),
IN_Status int
 
end
declare @dbcc varchar(50)
set @dbcc = 'dbcc sqlperf(logspace) WITH NO_INFOMSGS'
insert #logspace
exec (@dbcc)
set @x = (select cast(dc_log_used as int) from #logspace where VC_database = db_name())
return @x
I wrote it some time ago and seem to recall that I tried to use a table variable instead of the temp table, but it wouldn't work as there is some limitation with the use of table variables in this way.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply