calculate needed space on hard disk

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

calculate needed space on hard disk

Author

Topic

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-06 : 11:54:13

Dear friends,
now I have a question and not an answer

.

I have a table named dokument.
In this table I have a column named dokVal.
dokVal is datatype IMAGE.
An application stores a PDF-file in this column.

My mission is to calculate the needed space on hard disk if this PDF is extracted from table to file system by that application.

My solution (in byte) is this:

len(convert(varbinary(max),dokVal))

and I know there could be a little difference because of different management on hard disk by OS.

My question is:
Is my above shown code OK?

Thank you
Fredinator

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 12:11:16

You can use DATALENGTH, just in case there are several 0x00 or 0x20 at the end of document, which LEN doesn't discover.
See http://weblogs.sqlteam.com/peterl/archive/2008/07/22/Why-LEN-differs-from-DATALENGTH-when-using-BINARY-data.aspx

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 12:12:03

Fredinator? Isn't that the name of one character in the Otto movies?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 12:16:48

And to adjust for block size of 4k, use

SELECT DATALENGTH(dokVal) + CASE DATALENGTH(dokVal) % 4096 WHEN 0 THEN 0 ELSE 4096 - DATALENGTH(dokVal) % 4096 END

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-06 : 12:21:28

Don't know, can't recall exactly every stuff from Otto.
My colleagues sometimes call me Fredinator when we are going to play counterstrike.

But many thanks for your tip with DATALENGTH!

Greetings
Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-06 : 12:27:01

quote:
Originally posted by Peso

And to adjust for block size of 4k, use

SELECT DATALENGTH(dokVal) + CASE DATALENGTH(dokVal) % 4096 WHEN 0 THEN 0 ELSE 4096 - DATALENGTH(dokVal) % 4096 END

N 56°04'39.26"
E 12°55'05.63"

Oh that's fine.
Does block size always matter or do I have to determine some information like NTFS or anything else?

Thank you again
Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 14:28:05

It was "Amboss"...
http://www.imdb.com/title/tt0095799/

However, this is probably his best movie
http://www.imdb.com/title/tt0105073/

I love the scene where Otto wants to write "a langes langes liebes brief" (translated into "A long long lover letter"), and send it by pidgeon.
The problems are

1. First open window before throwing pidgeon through it
2. A pidgeon cannot fly with a 3 pound love letter tied to its feet

N 56°04'39.26"
E 12°55'05.63"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-06 : 14:44:45

4096 is the default block size for NTFS volumns above a certain size, but it can be larger. The actual space used will depend on this. If you don't know that block size, you might just have to give it a best guess using a 4K block, or show how much would be used for various sizes.

The actual space on disk would also change if the files are stored in an encrypted file system, a compressed directory, one of the other file systems supported by Windows, or a share on a non-Windows server.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources