File Size from T-SQL

Question

Post reply

File Size from T-SQL

ralu_k_17

SSCrazy

Points: 2190
More actions
October 17, 2014 at 5:35 am

#397523

Hi,
I need a way to get a physical file's size from T-SQL, without using xp_cmdshell.
My exact situation is this:
I need to query a folder (TB of data), with the number of subfolders not known exactly, located on a different server.
The big folder, contains numerous subfolders, each containing other subfolders, and so on. In the end subfolders, I've got files, for which I need to know the attributes.
I used xp_dirtree with a loop function to get the tree structure for every file.
At this point in my database I've got saved every file name with its coresponding tree structure.
I now need to get the attributes of each file. Mainly the file size, but also the creation and modification dates for each file.
xp_cmdshell is not a good solution for me, because I've good way to many files and I'm on a production environment and it's working extremely slow and taking up all of the resources and blocking everything.
Thank you so much for any help!

Viewing 15 posts - 1 through 15 (of 19 total)

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

Not sure you can do this directly in T-SQL without using xp_cmdshell.

I'd highly recommend investigating Powershell as a solution.

Note if there's a lot of files it's likely to be slow whatever your solution.

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Doesn't look like a good fit for a T-SQL solution.

I would use Powershell or a separate application instead.

-- Gianluca Sartori

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 3

You may want to create SQLCLR TVF proc returnig file list with poperties see sample code http://www.sqlservercentral.com/articles/SQLCLR/65656/

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

What about CLR and calling it from T-SQL?

Igor Micev,My blog: www.igormicev.com

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

CLR is a possible solution, but I'm not sure I would use it.

I tend to do data focused operations in sql server and the rest outside SQL Server.

Are you sure you need to do it from T-SQL? Shouldn't it be done in the application?

-- Gianluca Sartori

ralu_k_17 SSCrazy Points: 2190 More actions · Answer 6

At this point, i am trying to do everything at the database level. There is no application involved, as it is an import (of large amount of data) project. And so, all my scripts and all my tasks are done at the database level.

If there are no good options for doing this, I will move on to the application option

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

If this is an import project, SSIS might be what you're looking for

-- Gianluca Sartori

Gazareth One Orange Chip Points: 27737 More actions · Answer 8

spaghettidba (10/17/2014)
If this is an import project, SSIS might be what you're looking for

Seconded.

ScottPletcher SSC Guru Points: 101076 More actions · Answer 9

Try fso. It's reasonably fast if you don't go too crazy on the number of files. I left out the error checking, you can fill that in if needed.

DECLARE @fso_result INT

DECLARE @fso_anchor INT

DECLARE @fso_fileid INT

DECLARE @file_size BIGINT

DECLARE @file_name nvarchar(255)

EXEC @fso_result = sp_OACreate 'Scripting.FileSystemObject', @fso_anchor OUT

--grab a random file name just to test the result

SELECT TOP (1) @file_name = physical_name

FROM sys.database_files

EXEC @fso_result = sp_OAMethod @fso_anchor, 'GetFile', @fso_fileid OUT, @file_name

EXEC @fso_result = sp_OAGetProperty @fso_fileid, 'Size', @file_size OUT

SELECT @file_name AS file_name, @file_size AS file_size

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004289 More actions · Answer 10

Gazareth (10/17/2014)
spaghettidba (10/17/2014)
If this is an import project, SSIS might be what you're looking for
Seconded.

Heh... I like the OP's idea of doing this all from T-SQL. I do it all the time. 😉

--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)

spaghettidba SSC Guru Points: 105732 More actions · Answer 11

Jeff Moden (10/17/2014)
Gazareth (10/17/2014)
spaghettidba (10/17/2014)
If this is an import project, SSIS might be what you're looking for
Seconded.
Heh... I like the OP's idea of doing this all from T-SQL. I do it all the time. 😉

Anything related to the last line in your signature? 😀

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004289 More actions · Answer 12

spaghettidba (10/19/2014)
Jeff Moden (10/17/2014)
Gazareth (10/17/2014)
spaghettidba (10/17/2014)
If this is an import project, SSIS might be what you're looking for
Seconded.
Heh... I like the OP's idea of doing this all from T-SQL. I do it all the time. 😉
Anything related to the last line in your signature? 😀

Heh... z'actly. 😛

--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)

Jeff Moden SSC Guru Points: 1004289 More actions · Answer 13

ralu_k_17 (10/17/2014)
Hi,
I need a way to get a physical file's size from T-SQL, without using xp_cmdshell.
My exact situation is this:
I need to query a folder (TB of data), with the number of subfolders not known exactly, located on a different server.
The big folder, contains numerous subfolders, each containing other subfolders, and so on. In the end subfolders, I've got files, for which I need to know the attributes.
I used xp_dirtree with a loop function to get the tree structure for every file.
At this point in my database I've got saved every file name with its coresponding tree structure.
I now need to get the attributes of each file. Mainly the file size, but also the creation and modification dates for each file.
xp_cmdshell is not a good solution for me, because I've good way to many files and I'm on a production environment and it's working extremely slow and taking up all of the resources and blocking everything.
Thank you so much for any help!

You say that your current method is slow. Please post it. It will give us a much better idea of what you're trying to pull off so we can come up with a high speed method for you.

--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)

ralu_k_17 SSCrazy Points: 2190 More actions · Answer 14

Hi,

First thing I tried was xp_cmdshell. That blocked everything for me as it took a lot of time and resources. Than, using xp_dirtree I populated my table with all the file names and their path, without adding the attributes for each file (file size, creation date or modification date). Next, for adding the attributes, i tried a CLR option and the fso option. The fso option is a bit faster, but still not fast enough. I've got a number of about 100 million rows (paths) that should be treated.

With the fso option around 100.000 rows are treated in about 12 min. It's taking to much time.