SQL Server Log Analysis

4.6 view server running status through Logs

SQL
Server 2005 records some system events and custom events to SQL Server error logs and Windows application logs. Both types of logs are automatically marked with time.

4.6.1 view the server running status in Event Viewer

SQL
Server
2005 an event record is generated when the server is started, shut down, and paused. This record is recorded in the Windows event viewer. In section 4.5.4
Server
2005 If logon review is set in the security settings of the server, as long as the event records that meet the logon review conditions are recorded in Windows [Event Viewer], the following describes how to view
SQL
Server 2005 Event Recording Method.

(1) In the "Start" menu of the computer, "Management Tools", and "Event Viewer", some readers may have different menus than those of the author, you only need to find the [Event Viewer] program.

(2) In the "Event Viewer" dialog box shown in 4.49, select "Event Viewer (local)"> "application". All event records are listed in the list box on the right.

Figure 4.49 Event Viewer dialog box

(3) double-click an event. The "event properties" dialog box shown in 4.50 is displayed. The event details are displayed here. In this example, the review succeeded. This item shows whether a hacker has successfully intruded into the system.

Figure 4.50 event Properties dialog box

(4) Events of different applications may be recorded in the event viewer.
If you only want to view and SQL
For server-related event records, right-click Application and choose View> filter from the shortcut menu ]. The Application Properties dialog box shown in 4.51. Here
You can filter event types, event sources, categories, and event time. The specific filtering examples will not be described in detail.

Figure 4.51 Application Properties dialog box

Note: The event records related to SQL Server 2005 are also recorded in [security] and [system] In the event viewer. Do not forget to view them. Windows application logs not only record the database startup stop and identity audit information, but also completely record Windows operating system events and SQL
Events in the server and SQL Server proxies.

4.6.2 2005 new feature: View SQL server logs in Log Viewer

To view SQL server logs in SQL Server Management studio, follow these steps:

(1) start SQL Server Management studio and connect to the SQL Server server.

(2) In object Resource Manager, choose Instance name> Manage> SQL server log, as shown in Figure 4.52.

Figure 4.52 view SQL server logs

(3) double-click a log archive. In the "Log File Viewer" dialog box shown in 4.53, you can view the specific log Content.

Figure 4.53 Log File Viewer window

4.6.3 view the SQL statement in the log folder
Server Error Log

SQL
Server 2005 also saves the SQL server error log to the "D:/Program" of the system disk.
Files/Microsoft SQL Server/MSSQL. X/MSSQL/log. The file names are "errorlog" and "errorlog. X", where "X" is a number. You can use NotePad to open it.

View SQL Server transaction logs:
In SQL Server 7.0 and 2000, run the following command:

DBCC log ({dbid | dbname}, [, type = {0 | 1 | 2 | 3 | 4}])

Parameters:
Dbid or dbname-ID or name of any database

Type-type of the output result:

0-minimum information (operation, context, transaction ID)

1-more information (plus flags, tags, row length)

2-very detailed information (plus Object Name, index name, page ID, slot ID)

3-all information about each operation

4-Add the hexadecimal information of the transaction to all information of each operation.

Default type = 0

To viewZhozdb DatabaseYou can use the following command to log the transaction:
DBCC log (zhozdb)

SQL Server compresses logs and database file size

1. Clear logs

Dump transaction zhozdb with no_log

2. truncate transaction logs

Backup log zhozdb with no_log

3. Compress database files (if not compressed, the database files will not be reduced)

Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file

-- Select log file -- select to shrink to xxm in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.

-- Select data file -- select to shrink to xxm in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.

You can also use SQL statements:

-- Shrink Database

DBCC shrinkdatabase (customer profile)

-- Contract the specified data file. 1 indicates the file number. You can use this statement to query:

Select * From sysfiles DBCC shrinkfile (1)

4. To minimize log files (for SQL 7.0, this step can only be performed in the query analyzer)

A. Separate the database:

Enterprise Manager -- server -- database -- Right-click -- detach Database

B. Delete log files in my computer

C. Additional database:

Enterprise Manager -- server -- database -- Right-click -- attach Database

This method generates a new log with a size of more than 500 K.

Or use the code:

The following example separates pubs and attaches a file in pubs to the current server.

A. Separation

Exec sp_detach_db @ dbname = 'pubs'

B. Delete log files

C. append:

Exec sp_attach_single_file_db @ dbname = 'pubs', @ physname = 'C:/program files/Microsoft SQL Server/MSSQL/data/pubs. MDF'

5. In order to automatically contract in the future, make the following settings:

Enterprise Manager -- server -- Right-click Database -- Property -- option -- select "auto contract"

-- SQL statement setting method:

Exec sp_dboption 'zhozdb', 'autoshrink', 'true'

6. If you want to prevent the log from increasing too much in the future.

Enterprise Manager -- server -- Right-click Database -- properties -- transaction log

-- Limit file growth to xm (X is the maximum data file size you allow)

-- SQL statement settings:

Alter database zhozdb Modify file (name = logical file name, maxsize = 20)

Many of my friends saw problems in the Event Viewer, but most of them did not know how to solve the actual problems based on the information. The following is my Discussion and Practice on the Internet, we will share some of our experiences. If the moderator finds it helpful, he hopes to add the essence. If it is not good, please do not use eggs or bricks or other objects. Thank you!

The following error logs are continuously displayed in the event viewer.

Event Type: Error
Event Source: termservdevices
Event Type: None
Event ID: 1106
Date: 2004-7-8
Event: 14: 06: 37
User: N/
COMPUTER: UBO-WS
Description:
The printer cannot be installed.
Data:
0000: BA 06 00 00 32 04 00 o... 2...
======================================
To solve this problem, we obtain the event source and event ID: termservdevices and 1106

Visit http://eventid.net/search.asp (this is a query event inside the source and ID information of a good site, free of charge services are available, good English friends can directly read) enter the event source and event ID in the search keyword to search for the error:
====================
Event ID: 1106 // error event ID
Source termservdevices // error event Source
Type Error // Error Type
Description the printer cocould not be installed. // simple description (unable to install the printer)
Details comments and links for event ID 1106 from source termservdevices // here is a detailed link to the specific discussion. Click here
======================================
 
Next, click the detail link to get a detailed discussion. Comments shows that some foreign IT friends have discussed this issue and it is of great reference value. Then there is a micro-
For more information about the soft knowledge base, see q239088 and q294429.


Microsoft Knowledge Base links: http://support.microsoft.com/default.aspx? SCID = KB; ZH-CN; + ID of the Knowledge Base

Finally, we use the following link to get the final answer and correct the error.

Windows 2000 Terminal Service server records events 1111, 1105, and 1106
Http://support.microsoft.com/default.aspx? SCID = KB; ZH-CN; 239088

Printer redirection architecture in Windows Server Terminal Service
Http://support.microsoft.com/default.aspx? SCID = KB; ZH-CN; 294429

In SQL Enterprise Manager, choose "manage"> "SQL server log.

In SQL 2000, you can use the system function to find it.

Select * From: fn_dblog (default, default)