It seems like insufficient info to tell something definitely. For example, are you deleting through Transact-SQL or through Win32API, etc.
The FILESTREAM data is not deleted immediately from file system because SQL Server transaction logging under full and bulk recovery models permit the crash recovery.
Have you deleted with CHECKPOINT
delete from tablename CHECKPOINT
or tried to execute CHECKPOINT statement or use simple recovery model?
Also, IMO,:
- changing of FILESTREAM (re)configuration require restart of SQL Server
- FILESTREAM data is restricted to LOCAL only drives
- FILESTREAM operations depend on hardware, see Paul Randal's white paper
- "deleting or renaming any FILESTREAM files directly through the file system will result in database corruption"
Check Security and Reliability sections of Paul Randal's white paper
Related discussions containing many subreferences:
Update:
Have you checked permissions of FILESTREAM (Windows) share/container vs. account under which SQL Server runs under. It should have local administrator permissions. It is recommended that no other account be granted permissions on the data container
Update2:
From Paul Randal's white paper:
- "The file system access open operations do not wait for any locks. Instead, the open operations fail immediately if they cannot access the data because of transaction isolation. The streaming API calls fail with ERROR_SHARING_VIOLATION if the open operation cannot continue because of isolation violation"
- "Antivirus software ... access to the BLOB data in the affected file will be prevented, and to SQL Server the file will appear to have been deleted."
- "Note that for a table to have one or more FILESTREAM columns, it must also have a column of the uniqueidentifier data type that has the ROWGUIDCOL attribute. This column must not allow null values and must have either a UNIQUE or PRIMARY KEY single-column constraint"
- "anything that can prevent transaction log truncation may also prevent a FILESTREAM file being physically deleted. Some examples are:"
- FILESTREAM data containers cannot be nested
Run
DBCC CHECKDB
Update3:
I cannot coach you online. This is Q&A board. I already had been banned many times for infringing the rules on StackExchange having discussions instead of posting question or answer
Run
DBCC CheckDB (QPS8, repair_rebuild)
DBCC CheckDB (QPS8) WITH No_INFOMSGS, ALL_ERRORMSGS
and put output here.
To check/set NTFS permissions: right-click on folder in Windows --> Properties --> Security
To check/set MSSQLServer: in command line type
SQLServerManager10.msc
navigate to SQL Server Services and double-click corresponding instance of SQL Server
Update 3b:
Probably, you should drop and recreate index or you have further deeper issues and you should restore database from backup or perform database repair permitting loss of data. This is quite separate topic/question.
I do not want to take any responsibility to guide you on it.
Don't know if its the fastest but I grouped the Filestream column into batches of 5000 rows then calculated each batch size before creating a running total:
CREATE PROCEDURE CalculateFSSize @RowCnt
AS
BEGIN
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'FSSizeBatch' AND TABLE_SCHEMA = 'dbo')
DROP TABLE dbo.FSSizeBatch
CREATE TABLE FSSizeBatch(BatchID INT
,StartID INT
,EndID INT
,BatchCnt INT
,BatchSize DECIMAL(18,4)
,RunningTotal DECIMAL(18,4))
DECLARE @BatchID INT
,@StartID INT
,@EndID INT
,@BatchCnt INT
DECLARE cur_1 CURSOR FOR
SELECT ((Rank - 1) / @RowCnt) + 1 AS BatchID
,MIN(ID) AS StartID
,MAX(ID) AS EndID
,COUNT(*) AS BatchCnt
FROM (
SELECT RANK() OVER (ORDER BY ID) Rank
,ID
FROM [dbo].[FS]
) T
GROUP BY ((Rank - 1) / @RowCnt) + 1
ORDER BY BatchID
OPEN cur_1
FETCH NEXT FROM cur_1 INTO @BatchID, @StartID, @EndID, @BatchCnt
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO FSSizeBatch(BatchID, StartID, EndID, BatchCnt, BatchSize)
SELECT @BatchID
,@StartID
,@EndID
,@BatchCnt
,SUM(CAST(DATALENGTH([JSONDATA]) + DATALENGTH([ZIPDATA]) AS DECIMAL)/1024/1014 )
FROM [dbo].[FS]
WHERE ID BETWEEN @StartID AND @EndID
FETCH NEXT FROM cur_1 INTO @BatchID, @StartID, @EndID, @BatchCnt
END
CLOSE cur_1
DEALLOCATE cur_1
;WITH a AS (
SELECT BatchID, RunningTotal, SUM(BatchSize) OVER(ORDER BY BatchID ROWS UNBOUNDED PRECEDING) AS RowTotal
FROM FSSizeBatch
)
UPDATE a
SET RunningTotal = RowTotal
SELECT *
FROM FSSizeBatch
END
Best Answer
Really not sure what else aside from PBM might be set up to try so forcibly to disable
xp_cmdshell
, but it definitely sounds like something set up by IT in some way. I don't think this is really causing any problems, so you could just wait until the rest of your team is available and bring it up to them. If you want to get more information about it, you could set up a server-side trace that captures all calls tosp_configure
- this will at least tell you the host name, application name, user name etc.Probably more event columns there than necessary, but it's not like you're going to be running this for a long time. You should be able to turn it off as soon as you see another message pop up in the log (because I doubt this is coming from multiple sources).