patternsqlMinor
Restore database with a FileTable
Viewed 0 times
databasewithrestorefiletable
Problem
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,
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:
So far I've this outcome:
At 2016-06-08 10:35:
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'
ENDSo far I've this outcome:
At 2016-06-08 10:35:
Solution
EDIT:
I've finally managed to get the database restored.
It took only 124 minutes, when we changed some settings on the server.
I found the settings here: https://support.microsoft.com/da-dk/kb/2160002
and here https://technet.microsoft.com/en-us/library/cc778996.aspx
In case the links stops working:
in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
NtfsDisableLastAccessUpdate and NtfsDisable8dot3NameCreation should be set to 1
I've finally managed to get the database restored.
It took only 124 minutes, when we changed some settings on the server.
I found the settings here: https://support.microsoft.com/da-dk/kb/2160002
and here https://technet.microsoft.com/en-us/library/cc778996.aspx
In case the links stops working:
in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
NtfsDisableLastAccessUpdate and NtfsDisable8dot3NameCreation should be set to 1
Context
StackExchange Database Administrators Q#140703, answer score: 2
Revisions (0)
No revisions yet.