This issue is a characteristic symptom of heavy I/O on the host. Perhaps there are multiple backups, DBCCs, and other "maintenance" activities happening on the SAN at that time.
Work with your VMware guy to ensure the SQL Server VMs are using the PVSCSI adapter for the LUNs present in the VM. VMware's SQL Server Best Practices documentation says this about SCSI adapters:
Utilize the VMware Paravirtualized SCSI (PVSCSI) Controller as the virtual SCSI Controller for data and log VMDKs.
The PVSCSI Controller is the optimal SCSI controller for an I/O-intensive application on vSphere. This controller has a queue depth of 64 (Per Device) and 254 (Per Controller) by default (double the size of an LSI Logic SAS controller). The PVSCSI controller’s per-device and per-controller queue
depths can also be increased to 254 and 1024 respectively, providing even more increased I/O bandwidth for the virtualized workload. See Configuring disks to use VMware Paravirtual SCSI (PVSCSI) adapters (1010298).
Also, see how to increase queue depths in a VMware vSphere environment.
Note:
While increasing the default queue depth of a virtual SCSI controller can be beneficial to an SQL Server-based VM, the configuration can also introduce unintended adverse effects in overall performance if not done properly.
VMware highly recommends that customers consult and work with the appropriate storage vendor’s support personnel to evaluate the impact of such changes and obtain recommendations or other adjustments that may be required to support the increase in queue depth of a virtual SCSI controller.
Use multiple vSCSI adapters. Placing OS, data, and transaction logs onto a separate vSCSI adapter optimizes I/O by distributing load across multiple target devices and allowing for more queues on the operating system level.
Spread the I/O load across all PVSCSI adapters to help optimize the I/O from the guest. In cases where there is a requirement for many data and
transaction log disks, it will be beneficial to set the OS boot disk
to use PVSCSI as well.
I would check the SQL Server default trace to see if DatabaseID 11 shows up.
This reads the default trace into a temporary table:
DECLARE @trcfilename VARCHAR(1000);
SELECT @trcfilename = path
FROM sys.traces
WHERE is_default = 1;
IF OBJECT_ID(N'tempdb..#trctemp', N'U') IS NOT NULL
BEGIN
DROP TABLE #trctemp;
END
SELECT *
INTO #trctemp
FROM sys.fn_trace_gettable(@trcfilename, default) tt;
This will show any trace events where DatabaseID = 11.
SELECT tt.DatabaseID
, tt.DatabaseName
, tt.StartTime
, tt.HostName
, tt.LoginName
, tt.ApplicationName
, te.name
FROM #trctemp tt
LEFT JOIN sys.trace_events te ON tt.EventClass = te.trace_event_id
WHERE tt.DatabaseID = 11 --missing database ID
ORDER BY tt.StartTime DESC;
DBCC CHECKDB (db_name) will create an internal snapshot of the database. You can see the snapshot files as an NTFS alternate data stream attached to the actual database files. So, if you run dir C:\some\path /r where C:\some\path is the location of your database files, you'll see the internal database snapshot files listed like this:
2018-03-29 03:06 PM 402,653,184 Test_DB.mdf
402,653,184 Test_DB.mdf:MSSQL_DBCC25:$DATA
In the sample above, you can see I have a database with a file named Test_DB.mdf. There is also an alternate data stream named Test_DB.mdf:MSSQL_DBCC25:$DATA. By correlating the existing database IDs in sys.databases, I am able to deduce that 25 in the DBCC25 part of the name is the database id for the snapshot.
Best Answer
I would recommend check space on data file of your database and extended in case there is only 5% free of space.