SQL Server FILESTREAM 总文件大小

Question

有没有一种查询可以获取在磁盘上FILESTREAM文件夹中的文件的总文件大小？

- user55474

Answer 1

以下查询将返回filestreamcolumn列的字节长度：

SELECT SUM(DATALENGTH(filestreamcolumn)) FROM filestreamtable;

来源

- Remus Rusanu

Answer 2

Remus 的解决方案的一个缺点是，它不包括可以进行垃圾回收的旧文件版本。 它们将不再是逻辑数据库的一部分，但仍会占用磁盘空间，直到 Filestream 垃圾回收器将其删除。

从 Sql Server 2008 R2 开始，您可以查询 sys.database_files 的 size 列，以获取给定 filestream 容器的近似大小（即使用的磁盘空间）。

- Pawel Marciniak

---

我正在运行 SQL 2008 R2 SP0 CU8，对于文件流而言，大小列始终为0。 - JohnW

Answer 3

use <DATABASE_NAME_HERE>
declare @table as nvarchar(100);
declare @columnName as nvarchar(100);
declare @query as nvarchar(500);
declare @sizeValue as nvarchar(100);
declare @generalValue as decimal(38);
declare @generalValueInMb as decimal(15,2);
set @generalValue = 0;

DECLARE tableCursor CURSOR FOR
(
SELECT DB_NAME()+'.**<schemaname_here>**.' + OBJECT_NAME(object_id) AS [FullTableName], name As [ColumnName]
FROM sys.columns
WHERE is_filestream = 1 
)

Open tableCursor

fetch next from tableCursor into @table, @columnName

while @@FETCH_STATUS = 0
begin
set @query = 'select @sizeValue = (select sum(Datalength('+@columnName+')) FROM ' + @table +')';
exec sp_executesql @query, N'@sizeValue int output', @sizeValue output;

if @sizeValue is not NULL
begin
set @generalValue = @generalValue + @sizeValue
end

fetch next from tableCursor into @table, @columnName
end

close tableCursor
deallocate tableCursor

select @generalValueInMb = round(@generalValue / 1024 / 1024, 2)

select @generalValueInMb

请注意，在上述代码中，您需要在适当的位置键入模式名称。

- AGR

Answer 4

-- 带有模式支持的AGR答案的改进版本

declare @table as nvarchar(100);
declare @columnName as nvarchar(100);
declare @query as nvarchar(500);
declare @sizeValue as nvarchar(100);
declare @generalValue as decimal(38);
declare @generalValueInMb as decimal(15,2);
set @generalValue = 0;

DECLARE tableCursor CURSOR FOR
(
SELECT DB_NAME()+'.'+ OBJECT_SCHEMA_NAME((object_id)) +'.'+ OBJECT_NAME(object_id) AS [FullTableName], name As [ColumnName]
FROM sys.columns
WHERE is_filestream = 1 
)

Open tableCursor

fetch next from tableCursor into @table, @columnName

while @@FETCH_STATUS = 0
begin
set @query = 'select @sizeValue = (select sum(Datalength(['+@columnName+'])) FROM ' + @table +' with (nolock))';
exec sp_executesql @query, N'@sizeValue int output', @sizeValue output;

if @sizeValue is not NULL
begin
set @generalValue = @generalValue + @sizeValue
end

fetch next from tableCursor into @table, @columnName
end

close tableCursor
deallocate tableCursor

select @generalValueInMb = round(@generalValue / 1024 / 1024, 2)

select @generalValueInMb

- Scott Gall