Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Want to find transaction log size before taking backup Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 1:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 7, Visits: 355
HI,

I want to estimate the transaction log size before taking backup not the used space

Post #1364249
Posted Tuesday, September 25, 2012 1:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 36,015, Visits: 30,302
Do you mean the size of the backup file it will create?

--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1364265
Posted Tuesday, September 25, 2012 2:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 7, Visits: 355
i need the size of the backup file it will create
Post #1364277
Posted Tuesday, September 25, 2012 4:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1364335
Posted Tuesday, September 25, 2012 6:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 5,970, Visits: 12,872
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"
Post #1364383
Posted Tuesday, September 25, 2012 6:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1364386
Posted Tuesday, September 25, 2012 6:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 36,015, Visits: 30,302
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
"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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1364391
Posted Tuesday, September 25, 2012 7:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1364393
Posted Tuesday, September 25, 2012 7:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 36,015, Visits: 30,302
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
"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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1364396
Posted Tuesday, September 25, 2012 8:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 5,970, Visits: 12,872
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"
Post #1364398
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse