Log shipping Error:945

Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details

This error is very common and many times, I have seen affect of this error as Suspected Database, Database Operation Ceased, Database Stopped transactions. Solution to this error is simple but very important.

Fix/Solution/Work Around:

1) If possible add more hard drive space either by removing of unnecessary files from hard drive or add new hard drive with larger size.

2) Check if the database is set to Auto grow on.

3) Check if the account which is trying to access the database has enough permission to perform operation.

4) Make sure that .mdf and .ldf file are not marked as read only on operating system file system level.

Solution Steps:

1.    Yes it is the common issue for DBA, here’s my troubleshooting steps:
STEP1: identify the db status:
use master
select databaseproperty(‘db_name’,'isShutdown’)
Most of them it would return 1 in this situation

STEP2: Clear up the internal db status:
use master
alter database db_name set offline
it would return with no error in most cases

STEP3: Get detail error message:
use master
alter database db_name set online

After step3, sql server will first verify the log file, if the log file is okay, it will verify the rest of the data file(s). Most of time it is because of the file location or file properties setting. For example if it is file location issue:

alter database db_name
modify (file=’logical name’, filename=’physical name’)
go

If you got any Error message while executing second step follow the below procedure:

1.     USE master;
GO
ALTER DATABASE TEKADIM SET EMERGENCY
GO
ALTER DATABASE TEKADIM SET SINGLE_USER
GO
DBCC CHECKDB (TEKADIM, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

USE TEKADIM;

DBCC CHECKDB WITH NO_INFOMSGS;
GO

USE TEKADIM
ALTER DATABASE [TEKADIM] SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE ([TEKADIM_log])
GO
ALTER DATABASE [TEKADIM] SET RECOVERY FULL
DBCC SHRINKFILE (TEKADIM, 1) WITH NO_INFOMSGS

ALTER DATABASE TEKADIM SET MULTI_USER