We have a SQL Server 2014 (build 12.0.24457.0) database with two tables in, both are FileStream tables, the biggest has 2979466 rows, SUM(cached_file_size)= 40564905472
.
We have tried to restore this database on to our test server, but it takes quite a long time. Our DBA gave up after 14 days.
Has anyone else tried to restore a database with FileStream tables, with more that a few files in?
I'm running this script to monitor the progress:
BEGIN TRY
DROP TABLE #h
END TRY
BEGIN CATCH
END CATCH
DECLARE @r INT=1, @percent_complete NUMERIC(9,2), @Extimated_Completion_Datetime DATETIME2(0)
CREATE TABLE #h ( start_time DATETIME2(0), percent_complete NUMERIC(9,2), Estimated_completion_time NUMERIC(9,2), HoursSinceStart NUMERIC(9,2), Extimated_Completion_Datetime DATETIME2(0), session_id INT, created_date datetime2(0))
DECLARE @d VARCHAR(19) , @e VARCHAR(max)
WHILE @r > 0 BEGIN
INSERT INTO #h
SELECT start_time, percent_complete, CAST(ROUND(estimated_completion_time/3600000.0,1) AS DECIMAL(9,1)) AS Estimated_completion_time
, CAST(ROUND(total_elapsed_time/3600000.0,1) AS DECIMAL(9,1)) AS HoursSinceStart
, DATEADD(HOUR, CAST(ROUND(estimated_completion_time/3600000.0,1) AS DECIMAL(9,1)), GETDATE()) AS Extimated_Completion_Datetime
, session_id
, GETDATE()
FROM
sys.dm_exec_requests AS r
WHERE
r.session_id <> @@SPID
AND r.session_id > 50
AND command LIKE 'restore database'
SELECT @r = @@ROWCOUNT
select top 1 @percent_complete=percent_complete, @Extimated_Completion_Datetime=Extimated_Completion_Datetime from #h ORDER BY created_date DESC
SET @d = CONVERT(VARCHAR(19), @Extimated_Completion_Datetime, 121)
SET @e = CONVERT(VARCHAR(19), GETDATE(), 121) + ' we are ' + LTRIM(@percent_complete) + '% complete. We estimate to finish at: ' + @d
RAISERROR('At %s ', 10, 1, @e) WITH NOWAIT
--WAITFOR DELAY '00:00:10'
WAITFOR DELAY '00:01:00'
END
So far I've this outcome:
At 2016-06-08 10:35:46 we are 00.01% complete. At 2016-06-08 14:22:46 we are 39.26% complete. We estimate to finish at: 2016-06-08 18:22:47 At 2016-06-08 14:23:46 we are 39.27% complete. We estimate to finish at: 2016-06-08 18:23:47 At 2016-06-08 14:24:46 we are 39.28% complete. We estimate to finish at: 2016-06-08 18:24:47 ... At 2016-06-09 08:33:07 we are 44.80% complete. We estimate to finish at: 2016-06-10 09:33:08 At 2016-06-09 08:34:07 we are 44.80% complete. We estimate to finish at: 2016-06-10 09:34:08 At 2016-06-09 08:35:07 we are 44.80% complete. We estimate to finish at: 2016-06-10 09:35:08 At 2016-06-09 08:36:07 we are 44.81% complete. We estimate to finish at: 2016-06-10 09:36:08 At 2016-06-09 08:37:07 we are 44.81% complete. We estimate to finish at: 2016-06-10 09:37:08 At 2016-06-09 08:38:07 we are 44.81% complete. We estimate to finish at: 2016-06-10 09:38:08 At 2016-06-09 08:39:07 we are 44.82% complete. We estimate to finish at: 2016-06-10 09:39:08 ... At 2016-06-10 08:12:01 we are 47.86% complete. We estimate to finish at: 2016-06-12 08:12:02 At 2016-06-10 08:13:01 we are 47.86% complete. We estimate to finish at: 2016-06-12 08:13:02
Not exactly fast. It has managed to go from 42% to 45% in 13:45, so with the current speed it looks to finish at 2016-06-27 13:45, or in 18 days. For 44 GB!
SELECT top 10000 resource_description AS resource_description, *
FROM sys.dm_os_waiting_tasks
WHERE session_id=64
says:
wait_type=
BACKUPTHREAD
…sometimes BACKUPIO
too
So I've set up another restore to get Restore Messages:
DBCC TRACEON(3604, 3605, 3014);
RESTORE DATABASE [VDCFileStreamhespotest] FROM DISK = N'\\dkrdsvdcp19\MSSQL_Backup\Full\Misc\VDCFileStream_backup_2016_06_07_180004_7123139.bak' WITH FILE = 1,
NOUNLOAD, REPLACE, STATS = 1
, move 'VDCFileStream' to 'P:\MSSQL\DPA\System\MSSQL10_50.DPA\MSSQL\DATA\UserDBs\VDCFileStream\VDCFileStreamDBhespo.mdf'
, MOVE 'VDCFileStream_log' to 'P:\MSSQL\DPA\Log\Log02\VDCFileStream\VDCFileStreamDBhespo_log.ldf'
, MOVE 'VDCFileStreamF1' to 'G:\VDCFileStream\FileStreamDatahespo'
Restore(VDCFileStreamhespotest): RESTORE DATABASE started Restore(VDCFileStreamhespotest): Opening the backup set Restore(VDCFileStreamhespotest): Processing the leading metadata Restore(VDCFileStreamhespotest): Planning begins Backup/Restore buffer configuration parameters Memory limit: 32767 MB BufferCount: 6 Sets Of Buffers: 2 MaxTransferSize: 1024 KB Min MaxTransferSize: 1024 KB Total buffer space: 12 MB Tabular data device count: 1 Fulltext data device count: 0 Filestream device count: 1 TXF device count: 0 Filesystem i/o alignment: 512 Media Buffer count: 6 Media Buffer size: 1024 KB Encode Buffer count: 6 Restore(VDCFileStreamhespotest): Effective options: Checksum=1, Compression=1, Encryption=0, BufferCount=6, MaxTransferSize=1024 KB Restore(VDCFileStreamhespotest): Planning is complete Restore(VDCFileStreamhespotest): Beginning OFFLINE restore Restore(VDCFileStreamhespotest): Attached database as DB_ID=48 Restore(VDCFileStreamhespotest): Preparing containers Restore(VDCFileStreamhespotest): Containers are ready Restore(VDCFileStreamhespotest): Restoring the backup set Restore(VDCFileStreamhespotest): Estimated total size to transfer = 45540792320 bytes Restore(VDCFileStreamhespotest): Transferring data 1 percent processed.
Best Answer
You are trying to restore a 45GB database with 32GB memory on the server.
What you can do is bump up the memory (RAM) on the server and play with
BUFFERCOUNT
,BLOCKSIZE
,MAXTRANSFERSIZE
backup parameters along withINIT
&COMPRESSION
(you are using it as I see from your output) to tune your backup.As of now, you are only getting 6MB which is pretty low.