Want to find transaction log size before taking backup

Question

Want to find transaction log size before taking backup

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

paruchuri4 SSC Rookie Points: 49 More actions · Answer 1

I am not considering about space but some databases are generating 0Kb size when they are taking backup for that purpose i need to know the size, if the size is zero then i want to eliminate the backup

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

paruchuri4 (9/26/2012)
I am not considering about space but some databases are generating 0Kb size when they are taking backup for that purpose i need to know the size, if the size is zero then i want to eliminate the backup

A 0KB backup should be almost instantaneously completed. You still have not relayed a concern that might materialize in a measurable savings in overhead. What is your concern in taking a log backup even if there are no pages to be backed up?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 3

paruchuri4 (9/26/2012)
I am not considering about space but some databases are generating 0Kb size when they are taking backup for that purpose i need to know the size, if the size is zero then i want to eliminate the backup

There is a DMV (I forget the name offhand but easy enough for you to find. Something about index usage IIRC) that shows whether or not a DB has been inserted, updated, or deleted since the last reboot. You could use that instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

george sibbald SSC Guru Points: 104210 More actions · Answer 4

paruchuri4 (9/26/2012)
I am not considering about space but some databases are generating 0Kb size when they are taking backup for that purpose i need to know the size, if the size is zero then i want to eliminate the backup

I am sure I have never seen a log backup that was 0KB even when there has been no activity, but If your concern is not to take a backup if nothing has changed then in another thread Gus described a process where he checked the LSN, if it had not changed from the last backup, another backup was not taken.

---------------------------------------------------------------------

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

Perry Whittle (9/25/2012)
opc.three (9/25/2012)
CREATE TABLE #LOGSPACE ( DB SYSNAME, LogSize FLOAT, SpaceUsed FLOAT, Stat BIT ); INSERT INTO #LOGSPACE ( DB, LogSize, SpaceUsed, Stat ) EXEC ( 'DBCC SQLPERF (LOGSPACE)' ); SELECT DB, LogSize, SpaceUsed, LogSize * (SpaceUsed / 100.0) AS SpaceUsedMB, -- approximate log backup size Stat
FROM #LOGSPACE;
Also that's a rather convoluted way of getting space used, I prefer
Use yourdb
GO
Select name, size / 128,
fileproperty(name, 'spaceused') / 128
From sys.database_files
Where type = 1 -- tlog files only

Here's my variation of what you provided:

WITH cte

AS (

SELECT name AS DB,

size / 128.0 AS LogSize,

FILEPROPERTY(name, 'spaceused') / 128.0 AS SpaceUsedMB

FROM sys.database_files

WHERE type = 1 -- tlog files only

)

SELECT DB,

LogSize,

(SpaceUsedMB / LogSize) * 100.0 AS SpaceUsedPercent,

SpaceUsedMB

FROM cte;

The query I am using will probably stay in use in my world for a while longer because it shows me all log files on the instance in one result. I have it in a snippet so it's no bother to bring it up.

If I get ambitious sometime I'll recreate the functionality using sys.database_files and dynamic sql or maybe the undocumented foreach proc.

I do like the fact that sys.databases gives the info per file. I have one database in my environment with two log files (on separate disks due to space limitations) where it would be useful to see percentage-full in each file.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato