One or more files do not match the primary file of the database – Error 5173

A SQL Server Instance  was shut down suddenly. When the SQL Server recovery a number of databases were in a RECOVERY_PENDING state.

The error messages in the SQL Server Error Log were :

--An example error message
Log file 'F:\MSSQLSERVER\MSSQL$MYINST\Data\db1_log.LDF' does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.
Error: 5173, Severity: 16, State: 1.
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

 

RECOVERY_PENDING means “Recovery of the file has been postponed. The file was online or did not exist before being changed to RECOVERY_PENDING by a partial restore”

Main reasons for database stuck in RECOVERY_PENDING after a sudden shutdown

1) Log is missing

2) Log is corrupt

3) Drive not online

Possible actions

1)      If the drive is available and online – try a SQL Server restart , as the drive may have been unavailable during restart

2)      If  the restart doesn’t solve the problem  , then RESTORE from a valid BACKUP

3)      A Workaround  For Error: 5173, Severity: 16, State: 1 try :

a)       Detach the database

b)       Delete the LDF file

c)       Reattach the database again

 Read More

 SQL Database Status with sys.databases - SQL Server DBA

SQL Server Database Status,monitoring and Restores

SQL Server Database SUSPECT state - SQL Server DBA

SQL Server – Memory leak detection

Memory leak detection can be tricky. You’ve been through every possible change on the server , you’ve spoken to the server administrators who confirm nothing has changed and  the network administrators say all is OK. But still , you’re left with a sever decrease in performance on an applications that’s been working fine for years.

Typical symptoms of the sudden issue are: unhandled exceptions , communication errors and the service  crashing without any log entries.   Memory pressure is a common cause of applications crashing. A quick and dirty method to identify a memory leak is using Perfmon. It may not give you the greatest detail , but it will give you a strong indication of a memory leak.

Once you’ve established there is a memory leak , identify the program and troubleshoot why the pogram is defective.

 Create a Performance Monitor and add the counters

 

“\Memory\Pool Nonpaged Bytes”
“\Memory\Pool Paged Bytes”
“\Paging File(_Total)\% Usage”

 

If you want to script these steps use : Window Perfmon scripting, SQL Server perfmon and how to perfmon

Notes:

1)       Adjust the update time to 400 seconds – this evens out the graph over time

2)       Start the app – and monitor over a period of time. Memory leaks can be very subtle , and may take awhile to  become evident

3)       Analayse Pool Paged Bytes  and Usage for user mode memory leaks .

4)       Analyse Pool Nonpaged Bytes  for kernel mode leaks. There are some situations where kernel mode leaks affect  pageable  memory

5)       Always cross – check with other evidence

6)       Finding the root cause is the next step. Rule of thumb: look for applications consuming memory and never levelling. You’ll notice a steady increase in memory usage.

 Related Posts

SQL Server Performance ,memory pressure and memory usage

SQL server OLE Automation memory pressure

SQL Server – MSDB Suspect pages table and bad pages

 The MSDB Suspect_pages table records suspect pages. Regular monitoring of the MSDB Suspect_pages table is advised ,  particularly for 823 errors ,824 errors,Bad CheckSum and Torn Page.

 How does SQL Server define a page as suspect?  If an 823 or 824 error is experienced

The 823 error (B_HARDERR) is caused when a Windows read\ write failed. The OS issues a cyclic redundancy check (CRC) – an example is a disk error

 The 824 error (B_HARDSSERR)  means the page is read successfully , but there is something wrong with the page.

Examples of  operations where an 823 or 824 error occurs:

1)       Query reading a page

2)       DBCC CHECKDB operation\ DBCC repair operation

3)       Backup\Restore Operation

4)       Drop Database

Troubleshooting notes for msdb.suspect_table

 1)       Some actions automatically clear out the table, such as : ALTER DATABASE REMOVE FILE, DROP DATABASE

2)       Set up a regular process to manage the rows in the msdb..suspect_table. Give priority to restored or repaired (Event Type 5 & 7)

3)       A big clue there may be IO subsystem issues is to monitor the record count in the table. If there are sudden spikes investigate further by talking to the storage guys

4)       The 824 error is an interesting one. It means there is a logical consistency error was detected during a read operation. In most cases I’ve seen the 824 correlates with an IO subsystem problem. Fix immediately. Contact me if you need any help

 

SELECT DB_NAME(database_id),[file_id],page_id,
CASE event_type 
WHEN 1 THEN '823 or 824 or Torn Page'
WHEN 2 THEN 'Bad Checksum'
WHEN 3 THEN 'Torn Page'
WHEN 4 THEN 'Restored'
WHEN 5 THEN 'Repaired (DBCC)'
WHEN 7 THEN 'Deallocated (DBCC)'
END,
error_count,
last_update_date
FROM msdb..suspect_pages

 Related Posts

 SQL Server – The server is down and when to call for External Help

DBCC CHECKDB

SQL Server - Recover from a Suspect database

SQL Server - sys.dm_os_loaded_modules

SQL Server – The server is down and when to call for External Help

Requesting External Help for a SQL Server Issue can be tricky. Let’s look at a scenario

At 11.35 am on a business day –  users start to report connectivity issues on a Primary Source application supporting a very large financial system . The DBA gets a call very soon afterwards by the application owner  asking to investigate. Immediately it’s obvious there is a SQL Server database issue. The DBA has a quick discussion with the Incident management team – a decision is taken not to failover the server until there’s  better understanding of the problem. The management team agrees to make the system unavailable for a 1 hr period – to allow some investigation.

After an hr – the DBA has a good idea of the problem and proposes a RESTORE of the last nights FULL BACKUP and roll forward the logs to just a few minutes before the problem started. The RESTORE starts and when it comes to rolling forward the logs and problem arises on the restore client. The problem amounts to not being able to skip over log backups occurring during the FULL BACKUP . It’s a long story – but the backup server was unavailable during the night – and the Operation team decided to run Production server backups in the morning. Essentially it’s a bug on the restore client software

At this point the DBA is stuck – they can either try and quickly develop some knowledge on querying the TSM server directly or contact  External Help. Keep in mind , at this point the outage  is about 3hrs – and pressure is mounting . I also forgot to mention , it’s the height of the holiday season and most of the DBA team is on holiday with just a skeletal staff.

The restore client vendor is contacted- with details of the bug. They have a  patch – available as a download. In the meanwhile management need to get Production services going . External help is requested , the consultant steps in , grasps the problem – and fortunately had worked on similar problem recently. Systems are back up.  This takes the pressure off the DBA who can progress with some root cause analysis

 Lessons learnt

 1) No DBA is an expert on very topic

 2)  External help can be expensive but compare to expense of the server outage

 3) Weigh up the cost of an outage and subsequent cost of an inappropriate solution – with the potential recurrence of  future outages.

 4) A consultant may be much cheaper than 100 employees not being able to process orders. It’s important for management to have a perspective on business impact. Typical scenarios – may be database corruption, unfamiliar error messages, complex performance issues

 5) The DBA ego. In many situation the DBA has in-depth knowledge and skills for solving a wide range of problems. They develop an aura of expertise. This can be a barrier to an awareness of their own limitations. Calling external help is an opportunity for a DBA to witness alternative approaches. 

6) Documenting the problem , steps taken and generating traces. This makes the consultant life easier and can speed up the solution

Related Posts

What is a SQL Server Expert?

How to report a SQL Server performance problem

How to request SQL Server troubleshooting

SQL Server - Display restore history for a single database  

SQL Sever - Dedicated Administrator Connection - DAC

The SQL Server Dedicated Administration Connection (DAC) once again saved the day!  A bad logon trigger on a development SQL Server caused all Logons to go into ROLLBACK. In effect , the Logons were authenticating but at the point of creating a user session the Logon would ROLLBACK.

1) The DAC is only allowed on the local server. Remote connections are not allowed except if configured with the sp_configure.  For local server access , you’ll either need to connect locally or use RDP. If you can't RDP logon  because other users logged on read How to View Remote Desktop Sessions  on how to view logons and disconnect them.

2) Sysadmin group - The logon account must be part of the sysadmin group. Read Find current user and sysadmin to identify sysadmin

3) Restrictions. The main restriction is only one DAC session can be open at any time. There are other restrictions , check SQL Server Book Online for details

4) How to use . Once connected use either SQLCMD or SQL Server Management Studio.

 

SQLCMD option

Note: using the –A switch

 

 sqlcmd -A –S <SQL_Server_Instance>  –E

 Note: using the –A switch

 

SSMS Option

Note: Using the ADMIN: prefix on the Server Name

 

Read More

Monitoring a Rollback and sys.dm_exec_requests

SQL Server – default trace FAQ