Log File Backup Size

Question

Log File Backup Size

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
August 20, 2010 at 12:11 pm

#225048

Hello Everyone
I am working to manually create Log Shipping. Please don't ask........ I really dislike a poorly designed network, with no architecture.
Ok, now to the question. The info for the log file backup is stored in the msdb..backupset table, in that table there is the name of the log file and the file size. I am letting the first part of SQL Log Shipping take the Log backup and place the file on to a drive for me. I already had that code, and it assigns a nice, but different file name to each log file, so why not.
Is there a way using t-sql to get the file size from the actual drive? Like when you use the 'dir' command at a command prompt, you can see the file size of each file. Any way to get the file size from within SQL? Without using the msdb..backupset table?
Thanks in advance
Andrew SQLDBA

Viewing 5 posts - 1 through 4 (of 4 total)

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

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

You could do xp_cmdshell('dir c:\whatever') and then filter it based on that...that's the only way I know of. I'm sure it would be stupidly simple with powershell, but unfortunately I haven't had a chance to learn much about that yet.

jjtetzlo SSCrazy Points: 2511 More actions · Answer 2

Is there a specific problem with using the backupset table?

At any rate, check out RESTORE HEADERONLY, it returns a BackupSize column.

http://msdn.microsoft.com/en-us/library/ms178536(SQL.90).aspx

- Jeff

Adiga One Orange Chip Points: 27284 More actions · Answer 3

Is there a way using t-sql to get the file size from the actual drive? Like when you use the 'dir' command at a command prompt, you can see the file size of each file. Any way to get the file size from within SQL?

Create a batch file (say size.bat) which has the following code

@echo off

echo %~z1

Then call the batch file in TSQL

XP_CMDSHELL 'size pathofthefile

You can find some more interesting commands here

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

Sorry Dood

That did not work.

I have the file size in the table, I want to compare that file size with the actual file. Just something that I would like to try. That is why I am not selecting the file size from the backupset table.

Thanks

Andrew SQLDBA