September 25, 2012 at 1:15 pm
HI,
I want to estimate the transaction log size before taking backup not the used space
September 25, 2012 at 1:37 pm
Do you mean the size of the backup file it will create?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2012 at 2:04 pm
i need the size of the backup file it will create
September 25, 2012 at 4:04 pm
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;
The SpaceUsedMB could be low if you ever switch the DB into the Bulk Logged Recovery Model and perform any minimally logged operations.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 25, 2012 at 6:14 pm
opc.three (9/25/2012)
The SpaceUsedMB could be low if you ever switch the DB into the Bulk Logged Recovery Model and perform any minimally logged operations.
Yes, but bear in mind that log backups could potentially be quite large
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 25, 2012 at 6:23 pm
Perry Whittle (9/25/2012)
opc.three (9/25/2012)
The SpaceUsedMB could be low if you ever switch the DB into the Bulk Logged Recovery Model and perform any minimally logged operations.Yes, but bear in mind that log backups could potentially be quite large
You're misunderstanding what I said. The number from the query (SpaceUsedMB) could be lower than the actual backup size if bulk logged was used, i.e. log space used would only count the extents, not the actual data which would come from the data file.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 25, 2012 at 6:59 pm
What I'd like to know is.... why does someone need to know what size the backup is? SQL Server figures that out and if there's not enough space for the backup, it'll throw an error before the backup actually starts.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2012 at 7:05 pm
Jeff Moden (9/25/2012)
What I'd like to know is.... why does someone need to know what size the backup is? SQL Server figures that out and if there's not enough space for the backup, it'll throw an error before the backup actually starts.
Capacity planning?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 25, 2012 at 7:15 pm
opc.three (9/25/2012)
Jeff Moden (9/25/2012)
What I'd like to know is.... why does someone need to know what size the backup is? SQL Server figures that out and if there's not enough space for the backup, it'll throw an error before the backup actually starts.Capacity planning?
Maybe but I still don't see why even that would be a problem. If the database exists well enough to have a log file to take a guess at, then just do a log backup and see how big it is. It's something that should be occuring anyway.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2012 at 8:14 pm
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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 25, 2012 at 8:26 pm
Heh, that looks nicer. I'll have to try it out. Maybe I'll update my toolkit-script. Thanks.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 26, 2012 at 4:52 am
You can also check the performance counters too:
SELECT d.name, logsize.cntr_value AS LogSize, logused.cntr_value AS PercentLogUsed
FROM sys.databases d
LEFT JOIN sys.dm_os_performance_counters logsize ON logsize.counter_name = 'Log File(s) Size (KB)' and logsize.instance_name = d.name
LEFT JOIN sys.dm_os_performance_counters logused ON logused.counter_name = 'Percent Log Used' and logused.instance_name = d.name
September 26, 2012 at 8:31 am
the script is giving the used space but i need to know the size of backup file.
for example i have a log of 50 gb and it used space is 0.02 % , by running the script the value is 10 mb but size is 1 mb after backup
September 26, 2012 at 8:44 am
It's not an exact science but it should give you an idea. Why are you worried about the diff between a 1MB backup and 10MB space used? if you're that tight on space then you have bigger fish to fry. The only way to know is to take the backup.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 26, 2012 at 8:46 am
One other note. If you're compressing your backups then all estimates will be off because compression rates are data-dependent. In round numbers though I see about a 90% compression rate for most OLTP databases.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply