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_type) AS resource_type,
request_session_id,
CONVERT(CHAR(15),request_mode) AS request_mode,
CONVERT(CHAR(15),request_status) AS 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',300) WHERE 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 1：CHECKDB + 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 2：restore 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.
