To find errors refracted productivity issues from analyzing SQL Server errorlog

To find errors refracted productivity issues from analyzing SQL Server errorlog

A few days ago, when backing up one of the libraries on a server encountered a problem, the database 80g+, when the full backup, SQL Server error

News3271Level -State1The theline in the file "E:\DataBaseUnrecoverable I on \XXXXXX\FG_XXXXX_CLASSID_05_DATA.NDF "/O Error:2(the system cannot find the file specified.) )。 Message3013Level -State1The theLineBACKUP DATABASEis terminating abnormally.

The server is hung with 20+ database, all databases can be fully backed up, but this library has a problem, database name:9115

There are two possible problems here:

1. The database is damaged due to some reasons

2, the disk has a problem caused the database corruption

In one night I checkdb the database for about 3 hours of repair time.

He always said you can restart the server, because he has tried to restart the server, the database corruption problem is normal

But I don't dare to restart the server, afraid of restarting the

Everyone may feel checkdb, repair can be normal backup, backup is finished, can not be normal backup I'm not sure, because disk space is not enough

But as a DBA, it's at least a reason to trace.

There are three questions that need to be traced:

(1) Since when the database has been damaged???

(2) The same error occurred in other libraries (unrecoverable I/O error: 2 (the system cannot find the file specified)???

(3) This library is not moved from other servers, if it is moved over before the damage, or moved over after the damage???

Tracking depends on SQL Server errorlog

Say this for so long, seems to be not with efficiency???

Efficiency issues

First we manage thousands of databases , daily work, from work to work, work to sleep, the system so much

How can you quickly find out the answers to these two questions?

Puzzle: SQL Server errorlog log file is large and is loaded directly with SQL Server's Log viewer to report "Out ofmemory" or "Multiple errors" or "SSMs crashes directly"

With UE?? Don't even think about it, just crash.

Step 1: Although it is possible to view it directly on the server, I am worried that

1, can affect server performance

2. If you restart SQL or machine, the oldest log will be gone.

First compress these several errorlog, then copy to the local, actually copy to the local as a backup role, and how to check how to check

Step 2: How can I view such a large log file? Install a SQL Server on this computer, and then replace

Installation path \microsoft SQL Server\mssql.1\mssql\log The following log file

When replacing, be careful not to replace each other, only the 5th and 6th log files need to be replaced, because these two files are on the GB level

Step 3: After replacing, open ssms, we need to find the word "system cannot find the specified file" in the 6th file.

Logging, determining the time, the 6th file modification time is 2012-11-3, we will start from 2012-1-1 to find

If there is a record before 2012-1-1, we will revise the time, and the end time we will choose 2015-10-10

Ensure that the entire log file can be overwritten

Enter the following SQL statement

EXEC 6,1,'9115',' system cannot find the specified file ',' 2012-01-01 ','2015-10-10','DESC'

It took 9 minutes to find no records.

Step 4: continue to find the 6th log file, and look at 9115 when this library is in existence on this server

Use the following SQL statement

EXEC 6,1,'9115',NULL,'2011-05-09', ' 2015-10-10 ','DESC'

You can see that this library already exists on this server in 2012-06-11.

Step 5: continue to find the 5th log file

Found, took 42 seconds, found 5 rows of records, the earliest problem was on 2013-11-18 00:35:48

ARead  ofThefile 'E:\DATABASE\XXXX\FG_XXXXX_CLASSID_05_DATA.NDF'At offset0x00000009668000Succeeded after failing1Time (s) withError2(The system cannot find the file specified.) ). Additional messagesinchThe SQL Server errorLog  andSystem EventLogmay provide more detail. This error condition threatensDatabaseIntegrity andMust be corrected. Complete a Full DatabaseConsistencyCheck(DBCCCHECKDB). This error can be caused byMany factors; forMore information, see SQL Server Books Online.

Problem one and problem three are untied.

(1) Since when the database has been damaged???

The first occurrence (the system could not find the file specified) was on 2013-11-18 00:35:48

(3) This library is not moved from other servers, if it is moved over before the damage, or moved over after the damage???

It is not clear to move, but it is certain that this library has been running on this server for some time before this problem, it can be explained that the database is not a problem at the beginning

Step 6: continue to the second question

Use the following SQL statement to view the 5th and 6th log files, and to troubleshoot any other libraries that have the same error

EXECXp_readerrorlog6,1,NULL,'The system cannot find the file specified','2011-12-12','2015-10-10','DESC'EXECXp_readerrorlog5,1,NULL,'The system cannot find the file specified','2011-12-12','2015-10-10','DESC'

6th log file does not have any records

The 5th log file is 9115 This database, and 9457 This database should be restored when the Bak file is not found

Step 7: At this point, you're going to want to continue using SQL statements to continue looking for errors

However, because the first log file is not replaceable, even if the SQL is stopped, the SQL will be re-generated after the start, you may replace to replace, change the file name

Although these methods can be used to change the file names using SQL statements, the two words highlighted in this article are " efficiency "

Look at the size of each log file, errorlog.4 only 21MB

We can use SQL Server's own log viewer to view

Why use SQL Server's own log viewer instead of text editing tools such as UE, because SQL Server has a much better filtering capability than UE for the log Viewer

And the results of the screening is very intuitive, very easy to use, the efficiency is not a difference how much

1. Renaming

2. Enter "System cannot find the specified file" in the message containing text box

The 4th log file doesn't have a single record.

Similarly, in the 3rd, 2, a log file is also a record is not

Attention:

As soon as you have the filter applied, the log Viewer will automatically filter out the required records for you when you load the next errorlog file.

Instead of showing all the records!!

Found 39 records in Errorlog, all of which are 9115 of this library.

The second problem is solved, only 9115 this library appears (an unrecoverable I/O error occurred: 2 (the system cannot find the specified file) error

Summarize

Every day of work, I will record the work done every day, also encounter the same problem can quickly find a solution

In the case of so much work, how to reduce their workload, constantly reflect on to improve work efficiency ~

A small sum, I hope you have a lot of support O (∩_∩) o

Reference article:

Hear the wind blow the Rain

SQL Server error log filtering (errorlog)

If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o