What happens when a file on a remote disk is not reachable while inserting records?

Suppose I have database with two filegroups A and B. filegroup B contains a single file on a remote server.

What will happen when I am inserting records in a table defined on filegroup B, while the remote server is offline?

Will the database engine store everything in RAM until the remote server is available again? Will it timeout? Will it write to a temporary file on the local disk?

Just to be clear: the database engine is running local, but the table is stored on a remote server.

Answer

The table will be “accessible” for any operations that do not involve access to the file storage. You could test that easily by creating a secondary filegroup on a usb drive and performing actions when the usb drive is disconnected.

use [master]
go

create database [mysplitdb] on primary 
(name = N'FileA', filename = N'C:SQLDatamysplitdbA.mdf'),
filegroup B
(name = 'FileB', filename = 'E:FileB.ndf') --<-- file on usb drive
log on 
(name = N'mysplitdb_log', filename = N'C:SQLDatamysplitdb_log.LDF')
go

use [mysplitdb]
go

create table dbo.OnSecFileGroup
(
    id int identity primary key clustered,
    col1 int,
    col2 varchar(100)
) on [B];


insert into dbo.OnSecFileGroup(col1, col2)
select top (10000) row_number() over(order by (select null)), a.name
from master.dbo.spt_values as a
cross join master.dbo.spt_values as b;

checkpoint;

select *
from dbo.OnSecFileGroup;

--remove the usb drive(abruptly)

--works, from cache
select top 500 *
from dbo.OnSecFileGroup
where id between 5000 and 6000

--works
select top 500 *
from dbo.OnSecFileGroup
where id > 9000


--insert new rows
--works (log file available, dirty pages in cache)
insert into dbo.OnSecFileGroup(col1, col2)
select top (1000) row_number() over(order by (select null)), a.name
from master.dbo.spt_values as a
cross join master.dbo.spt_values as b;

--works (log file available, table read from cache)
insert into dbo.OnSecFileGroup(col1, col2)
select top (2000) col1, col2
from dbo.OnSecFileGroup;

--works
update top(500) s
set col1 = col1+100
from dbo.OnSecFileGroup as s;


--force flush to disk
--error: 
--The operating system returned error ....
--Additional messages in the SQL Server error log and system event log may provide more detail. 
--This is a severe system-level error condition that threatens database integrity and must be corrected immediately...
checkpoint;


use master
go

--db still online
select state_desc --ONLINE
from sys.databases
where name = 'mysplitdb'

--write more rows
use mysplitdb
go
--works
insert into dbo.OnSecFileGroup(col1, col2)
select top (12000) col1, col2
from dbo.OnSecFileGroup;

--error:
--Could not allocate space for object 'dbo.OnSecFileGroup'.'PK__OnSecFil__3213E83F0425A276' in database 'mysplitdb' because the 'B' filegroup is full.
--Create disk space by deleting unneeded files, dropping objects in the filegroup ...
insert into dbo.OnSecFileGroup(col1, col2)
select top (12000) col1, col2
from dbo.OnSecFileGroup;


--connection still open
--table accessible
select top (120) col1, col2
from dbo.OnSecFileGroup
order by newid();

--stop & start sql server
use master
go

--db not online
select state_desc --RECOVERY_PENDING
from sys.databases
where name = 'mysplitdb'

--stop sql server and connect usb

--start sql server

--mysplitdb online

use master
go

--db online
select state_desc --ONLINE
from sys.databases
where name = 'mysplitdb'


--remove usb
use  mysplitdb
go

--needs access to file storage, after restart, nothing in cache
select top (120) col1, col2
from dbo.OnSecFileGroup
order by newid();

--db still online

--error..
dbcc checkdb('mysplitdb')

--cleanup
drop database mysplitdb
go