Common Reasons for Unable to Access to SQL Server and the Database

Summary: Check the error log and system/application log to diagnose the problems. Restore the backup typically is the optimal option. In some cases, you may fix the problem by using DBCC CheckDB.

 

1       Cannot Access to SQL Server

1.1    SQL Server does not start because the service account does not have proper authority to access to the registry values and/or other resources for SQL Server.

1.1.1          Reason: The service account was set up in control panel and it does not have proper permissions to start SQL Server.

1.1.2          Troubleshooting: check Windows System Log

1.1.3          Solution: set up the service account using SQL Server Configuration Manager. It automatically assign the account to the proper built-in group with sufficient permission to start SQL Server.

1.2    SQL Server does not start due to problems with the error log file and folder

1.2.1          Reason: The error log file cannot be created due to the wrong file path or locked by an antivirus program.

1.2.2          Troubleshooting: check Windows System Log/Application Event Log

1.2.3          Solution: depending on the errors reported in the system/application log

1.3    SQL Server does not start because the master database cannot be started

1.3.1          Case 1: cannot find the mdf or ldf files from the specified path.

1.3.2          Case 2: Access is denied for the service account

1.3.3          Case 3: The master database is corrupted such as torn pages

1.3.4          Troubleshooting: check the errorLog

1.3.5          Solutions (for Case 3): restore the backup, or rebuild the master using setup.exe if no backups

1.4    SQL Server does not start because the mssqlsystemresource database cannot be started

1.4.1          Case 1: cannot find the mdf or ldf files from the specified path.

1.4.2          Case 2: Access is denied for the service account

1.4.3          Case 3: The mssqlsystemresource database is corrupted such as torn pages

1.4.4          Case 4: Version of the resource database is different from that for SQL Server

1.4.5          Troubleshooting: check the errorLog

1.4.6          Solutions: easy to fix – use right files in the right place

1.5    SQL Server does not start because the model database cannot be started

1.5.1          Case 1: cannot find the mdf or ldf files from the specified path.

1.5.2          Case 2: Access is denied for the service account

1.5.3          Case 3: The model database is corrupted such as torn pages

1.5.4          Troubleshooting: check the errorLog

1.5.5          Solutions:

·       Option 1 - Restore the model database backup (need to use the /m /t3608 parameters to start SQL Server to bypass starting the model database

o   Net start mssqlserver /f /m /T3608

o   Restore database model from disk=’c:\model.bak’ with MOVE ‘modeldev’ to ‘d:\xxx\model.mdf’, Move ‘ModelLog’ to ‘D:\xxx\model.ldf’, replace

·       Option 2 - Restore the mdf and ldf file backups (copy and paste the files – easy!)

·       Option 3 - Rebuild the system databases with setup.exe (hard)

1.6    SQL Server does not start because the tempdb database cannot be started

1.6.1          Case 1: wrong file path due to move of the system databases.

1.6.2          Case 2: Access is denied for the service account

1.6.3          Case 3: Not enough space on the disk

1.6.4          Troubleshooting: check the errorLog

1.6.5          Solution:

·       Case 1 - Point to the right path, need to use the /m /t3608 parameters to start SQL Server

o   Net start mssqlserver /f /m /T3608

o   Alter database tempdb MODIFY FILE (Name= ‘ TempDev ‘, FileName = ‘c:\xxx\tempdb.mdf)

o   Alter database tempdb MODIFY FILE (Name= ‘ TempLog‘, FileName = ‘c:\xxx\templog.mdf)

·       Case 2 – Give the permission

·       Case 3 – Change the size for the tempdb

o   Net start mssqlserver /f /m /T3608

o   Alter database tempdb MODIFY FILE (Name= ‘ TempDev ‘, Size=100MB)

o   Alter database tempdb MODIFY FILE (Name= ‘ TempLog‘, Size=100MB)

 

2       SQL Server cannot use AWE on 32-bit server

2.1    SQL Server cannot use more than 2G memory on a 32-bit server and you see “SQL Server cannot use Address Windowing Extensions because lock memory privilege was not granted” in errorlog

2.2    Troubleshooting: check the error log to see if you see “SQL Server Using Locked Pages for Buffer Pool” which is desired.

2.3    Solution: give the SQL Server service account the Lock Pages in Memory permission:

Control Panel|Administrative Tools|Local Security Policy|Security Settings|Local Policies|User Rights Assignment|Local Pages in Memory|Properties|Add User or Group

3       Cannot Access to the user databases

3.1    The mdf and ndf files in the primary filegroup cannot be opened.

3.1.1          The database is in recovery_pending status.

3.1.2          Troubleshooting: error log

3.1.3          Solution: make the files available, or restore the backups

3.2    The ndf files in the secondary filegroup cannot be opened.

3.2.1          You still can open the database after making the secondary FG offline

ALTER DATABASE Sales MODIFY FILE (NAME=SGrp2fil_dat, OFFLINE)

ALTER DATABASE Sales SET ONLINE

3.2.2          Then, you can restore the corrupted or missed secondary FG

3.3    The log file is not accessible.

3.3.1          No problem for SIMPLE, SQL Server will create a new one automatically

3.3.2          If it is FULL and not shut down clearly, the database may not be in consistent status

·       Solution 1: Restore the backup

·       Solution 2: Use DBCC CHECKDB to repair database if data loss is acceptable

ALTER DATABASE ForEmergency SET EMERGENCY

GO

 

ALTER DATABASE ForEmergency SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

 

DBCC CHECKDB (ForEmergency, REPAIR_ALLOW_DATA_LOSS)

GO

 

ALTER DATABASE ForEmergency SET MULTI_USER WITH ROLLBACK IMMEDIATE

GO

 

ALTER DATABASE ForEmergency SET ONLINE

GO

3.4    Encounter a deferrable error in the recovery process

3.4.1          Deferrable error on specific pages in the Redo process

·       The entire database is OK

·       But the corrupted page is marked with Restore Pending and cannot be accessed in the Redo process

·       Solutions: DBCC CheckDB or restore the backup

3.4.2          Deferrable error on specific pages in the Undo or Rollback process

·       The entire database is OK

·       But some pages have errors, the transaction on these pages are deferred during the rollback process.

·       When it happens, SQL Server locks these transactions. You can use the following script to check if there are such locks in your database:

SELECT CONVERT(CHAR(22),resource_description)AS resource_desc,

       CONVERT(CHAR(15),resource_typeAS resource_type,

       request_session_id,

       CONVERT(CHAR(15),request_modeAS request_mode,

       CONVERT(CHAR(15),request_statusAS request_status

from sys.dm_tran_locks

WHERE request_session_id=-3

 

·       Solutions: the best way is to restore the backup. You may also try DBCC CheckDB or DBCC CheckTable with REPAIR_ALLOW_DATA_LOSS to fix the physical problems. But DBCC CheckDB or CHECKTable does not help if it is a logical problem.

3.4.3          Database in Suspect Mode in the recovery process

·       It happens when the error is severe such as too much damage or problems on the important pages, or the error cannot be deferred. The entire database is inaccessible.

·       Solution 1: hardware problem? If yes, fix it and restart SQL Server again.

·       Solution 2: backup/restore

·       Solution 3: rebuild the log

o   Move the log file to somewhere else

o   Start SQL Server and it is in recovery_pending state now

o   Run the following script to build a new log

ALTER DATABASE sales SET EMERGENCY

GO

ALTER DATABASE sales SET SINGLE_USER

GO

DBCC CHECKDB(sales,REPAIR_ALLOW_DATA_LOSS)

GO

ALTER DATABASE sales SET MULTI_USER

GO

o   The database can be online now. But the problematic pages are still inaccessible. You can export the data to a new database at this point. Some data may be lost due to the errors on the pages.

·       Case Study:

The database pagenumber is in Full Recovery Model. There is a full backup, all of the data value for the table test is AAA...

 

The following statement has not been committed when the SQL Server was shut down last time.

 

UPDATE test SET col2=REPLICATE('B',300WHERE col1<100

 

Now when SQL Server starts, there is 824 error on page 1:200 in the rollback process. The transaction is marked deferrable. The database can be opened, but you will find some transaction locks due to the recovery failure.

 

-- Query the locks

USE PageNumber

GO

 

SELECT  p.object_id ,

        OBJECT_NAME(p.object_id) AS object_name ,

        request_session_id ,

        resource_type ,

        resource_description ,

        request_mode ,

        resource_associated_entity_id ,

        request_status

FROM    sys.dm_tran_locks

        LEFT JOIN sys.partitions p ON sys.dm_tran_locks.resource_associated_entity_id = p.hobt_id

WHERE   resource_database_id = DB_ID('PageNumber')

ORDER BY request_session_id ,

        request_mode ,

        resource_type ,

        resource_associated_entity_id ,

        request_session_id ,

        request_mode ,

        resource_type ,

        resource_associated_entity_id

GO

 

--Query to the table is blocked at this point.

SELECT  *

FROM    pagenumber..test

 

 --You can further confirm the process was blocked by the -3.

SELECT  *

FROM    sys.sysprocesses

WHERE   spid > 50

 

--How to fix the problem?

--Option 1CHECKDB + REPAIR_ALLOW_DATA_LOSS.

--Result: only partially rolled back or repaired

 

ALTER DATABASE PageNumber

SET EMERGENCY

GO

 

ALTER DATABASE PageNumber SET SINGLE_USER

GO

 

DBCC CHECKDB ('PageNumber', REPAIR_ALLOW_DATA_LOSS)

GO

 

ALTER DATABASE PageNumber SET MULTI_USER

GO

 

--Option 2restore the corrupted pages

--find the corrupted pages through the error log or DBCC CheckDB (if the database is not too large)

 

ALTER DATABASE PageNumber

SET EMERGENCY

GO

 

DBCC CHECKDB ('PageNumber')

GO

 

ALTER DATABASE PageNumber SET ONLINE

GO

 

--let's assume page 200 corrupted

USE master

GO

 

RESTORE DATABASE PageNumber PAGE = '1:200'

FROM DISK = 'PageNumberGood.bak'

 

--as the transaction has not rolled back, we have to back up the tail log

BACKUP LOG PageNumber TO DISK = 'PageNumberGood.trn'WITH INIT , FORMAT

GO

 

--restore all logs, now assuming the only one we need is the tail log.

RESTORE LOG PageNumber FROM DISK = 'PageNumberGood.trn'

WITH RECOVERY

GO

 

--Now all of the transactions have been rolled back. The values are AAAA…in the test table.