Home Database Mysql Tutorial MS SQL 日志记录管理

MS SQL 日志记录管理

Jun 07, 2016 pm 05:44 PM
log manage Record

MS SQL的日志信息/日志记录,可能对你来说,既熟悉又陌生,熟悉是因为你可能一直都在使用,查看、关注一些日志信息/记录,例如,作业历史记录;陌生是因为你可能从不关注日志信息/记录的管理,这里我一直用日志信息/记录这个词,而没有用日志文件这个词来阐

    MS SQL的日志信息/日志记录,可能对你来说,既熟悉又陌生,熟悉是因为你可能一直都在使用,查看、关注一些日志信息/记录,例如,作业历史记录;陌生是因为你可能从不关注日志信息/记录的管理,这里我一直用日志信息/记录这个词,而没有用日志文件这个词来阐述,是想让大家把它和事务日志文件(ldf)区分开来,香港服务器租用,网上你用日志文件做搜索关键词,可能搜出来的都是事务日志相关的信息。其实它真的也叫日志文件,这篇文章我大概从日志记录分类、如何查看日志记录、日志记录的位置、日志记录的设置、为什么错误日志会暴增、如何清除日志记录等方面来讲述。

日志记录分类

按日志文件查看器,习惯将错误日志归为SQL SERVER、 SQL SERVER 代理, Windows应用程序日志,数据库邮件等四类错误日志记录。如果还考虑维护计划、远程维护计划、作业历史记录日志信息,总共是7类日志信息文件。

clip_image002

其中Windows应用程序日志类型又分为系统日志(System)、安全日志(Security)、应用程序日志(Application), PatchLink日志等几种,我在服务器(Windows Server  2008 R2 Standard)上打开SSMS,居然发现又多了HardwareEvents, Internet Explorer、Windows PowerShell等日志文件。这些都是系统的日志文件。你不必太纠结有多少种。

clip_image004

日志记录位置

 

SQL SERVER日志记录、 SQL SERVER代理记录的位置如下所示, SQL SERVER日志记录一般存储在ERRORLOG.n(n为数字)文件里, SQL SERVER代理日志记录位于SQLAGENT.n这类的文件里。当然这跟数据库的版本也有关系:

版本

路径

SQL SERVER 2005

Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG

SQL SERVER 2008

Program Files\Microsoft SQL Server\MSSQL10.实例名\MSSQL\LOG

SQL SERVER 2008 R2

Program Files\Microsoft SQL Server\MSSQL10_50.实例名\MSSQL\LOG

    SQL SERVER 2005,默认情况下,错误日志位于 Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG 和 ERRORLOG.n 文件中。其中MSSQL.n的区分为:

        MSSQL.1:SQLSERVER

        MSSQL.2:SSAS

        MSSQL.3:SQLExpress

        MSSQL.4:SSRS

所以,一般情况下,你只需要关注MSSSQL.1目录下的日志文件

clip_image006

那么,数据库邮件日志记录位于哪里呢?作业历史记录日志信息、Windows应用程序日志又位于哪里呢?是不是从没考虑过这些?

数据库邮件日志记录信息可以从视图msdb.dbo.sysmail_event_log查询得到,实质保存在[dbo].[sysmail_log]表里面。

sysmail_event_log

作业历史记录日志信息都保存在msdb.dbo.sysjobhistory的表里面,其中run_status字段代表作业执行状态

0 = 失败

1 = 成功

2 = 重试

3 = 已取消

4= 正在进行

所有Windows应用程序日志其实都位于同一位置%SystemRoot%\System32\Winevt\Log。像Application日志文件位于%SystemRoot%\System32\Winevt\Logs\Application.evtx,如下所示,

clip_image008

查看日志记录

查看日志记录可以确保进程(例如,备份和还原操作、批处理命令或其他脚本和进程)成功完成。此功能可用于帮助检测任何当前或潜在的问题领域,包括自动恢复消息(尤其是在 SQL Server 实例已停止并重新启动时)、内核消息或其他服务器级错误消息。

方式1: 查看错误日志文件

对SQL SERVER、SQL SERVER AGENT日志记录信息,你可以直接去log目录下找到ERRORLOG、SQLAGENT日志文件,直接打开查看;而像Windows应用程序日志记录,去到%SystemRoot%\System32\Winevt\Log目录,找到对应的日志文件,直接打开查看。

 

方式2:通过SSMS来查看日志记录

查看与常规 SQL Server 活动相关的日志

  • 在对象资源管理器中,依次展开“管理”和“SQL Server 日志”,再双击“当前”,将显示 SQL Server、“SQL 代理”和“Windows 事件”日志。
  • 查看与作业相关的日志

  • 在对象资源管理器中,展开“SQL Server 代理”,右键单击“作业”,再单击“查看历史记录”,此时将显示“作业历史记录”和“SQL 代理”日志。
  • 查看与维护计划相关的日志

  • 在对象资源管理器中,展开“管理”,右键单击“维护计划”,再单击“查看历史记录”,此时将显示“维护计划”、“作业历史记录”和“SQL 代理”日志。
  •  

    方式3:用脚本查看

    3.1 对于SQL SERVER日志文件,可以通过下面脚本查看:

       --查看日志文件的存档号

        EXEC master.dbo.sp_enumerrorlogs

       用这个命令可以查看日志文件的大小,这个非常有用,你可以把大小异常的文件给排查出来。

     

       --根据存档号查看该档日志内容

       EXEC master.dbo.xp_readerrorlog 1

       --根据job_id查看SQL SERVER日志记录

      SELECT * FROM  msdb.dbo.sysjobhistory WHERE job_id='36E9232B-CD5B-4646-9BED-B8242090FFF9'

     

    3.2 对于作业历史记录日志信息,你既可以通过下面存储过程查看,也可以直接查询对应的表。

    例如,服务器空间,我要查看作业“ServerDiskCapacityCheck”的历史记录

    Code Snippet

     

    3.3数据库邮件记录查看

    SELECT * FROM  msdb.dbo.sysmail_event_log;

     

    日志记录管理

     

    设置最大错误日志文件数

        1:在对象资源管理器中,连接到 SQL Server 数据库引擎实例,再展开该实例。

    Statement of this Website
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

    Hot AI Tools

    Undresser.AI Undress

    Undresser.AI Undress

    AI-powered app for creating realistic nude photos

    AI Clothes Remover

    AI Clothes Remover

    Online AI tool for removing clothes from photos.

    Undress AI Tool

    Undress AI Tool

    Undress images for free

    Clothoff.io

    Clothoff.io

    AI clothes remover

    AI Hentai Generator

    AI Hentai Generator

    Generate AI Hentai for free.

    Hot Article

    R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
    2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
    Repo: How To Revive Teammates
    4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
    Hello Kitty Island Adventure: How To Get Giant Seeds
    3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

    Hot Tools

    Notepad++7.3.1

    Notepad++7.3.1

    Easy-to-use and free code editor

    SublimeText3 Chinese version

    SublimeText3 Chinese version

    Chinese version, very easy to use

    Zend Studio 13.0.1

    Zend Studio 13.0.1

    Powerful PHP integrated development environment

    Dreamweaver CS6

    Dreamweaver CS6

    Visual web development tools

    SublimeText3 Mac version

    SublimeText3 Mac version

    God-level code editing software (SublimeText3)

    What is event ID 6013 in win10? What is event ID 6013 in win10? Jan 09, 2024 am 10:09 AM

    The logs of win10 can help users understand the system usage in detail. Many users must have encountered log 6013 when looking for their own management logs. So what does this code mean? Let’s introduce it below. What is win10 log 6013: 1. This is a normal log. The information in this log does not mean that your computer has been restarted, but it indicates how long the system has been running since the last startup. This log will appear once every day at 12 o'clock sharp. How to check how long the system has been running? You can enter systeminfo in cmd. There is one line in it.

    Where can I view the records of things I have purchased on Pinduoduo? How to view the records of purchased products? Where can I view the records of things I have purchased on Pinduoduo? How to view the records of purchased products? Mar 12, 2024 pm 07:20 PM

    Pinduoduo software provides a lot of good products, you can buy them anytime and anywhere, and the quality of each product is strictly controlled, every product is genuine, and there are many preferential shopping discounts, allowing everyone to shop online Simply can not stop. Enter your mobile phone number to log in online, add multiple delivery addresses and contact information online, and check the latest logistics trends at any time. Product sections of different categories are open, search and swipe up and down to purchase and place orders, and experience convenience without leaving home. With the online shopping service, you can also view all purchase records, including the goods you have purchased, and receive dozens of shopping red envelopes and coupons for free. Now the editor has provided Pinduoduo users with a detailed online way to view purchased product records. method. 1. Open your phone and click on the Pinduoduo icon.

    Troubleshooting Event 7034 Error Log Issues in Win10 Troubleshooting Event 7034 Error Log Issues in Win10 Jan 11, 2024 pm 02:06 PM

    The logs of win10 can help users understand the system usage in detail. Many users must have seen a lot of error logs when looking for their own management logs. So how to solve them? Let’s take a look below. . How to solve win10 log event 7034: 1. Click "Start" to open "Control Panel" 2. Find "Administrative Tools" 3. Click "Services" 4. Find HDZBCommServiceForV2.0, right-click "Stop Service" and change it to "Manual Start "

    How to use Redis to implement distributed transaction management How to use Redis to implement distributed transaction management Nov 07, 2023 pm 12:07 PM

    How to use Redis to implement distributed transaction management Introduction: With the rapid development of the Internet, the use of distributed systems is becoming more and more widespread. In distributed systems, transaction management is an important challenge. Traditional transaction management methods are difficult to implement in distributed systems and are inefficient. Using the characteristics of Redis, we can easily implement distributed transaction management and improve the performance and reliability of the system. 1. Introduction to Redis Redis is a memory-based data storage system with efficient read and write performance and rich data

    Detailed explanation of log viewing command in Linux system! Detailed explanation of log viewing command in Linux system! Mar 06, 2024 pm 03:55 PM

    In Linux systems, you can use the following command to view the contents of the log file: tail command: The tail command is used to display the content at the end of the log file. It is a common command to view the latest log information. tail [option] [file name] Commonly used options include: -n: Specify the number of lines to be displayed, the default is 10 lines. -f: Monitor the file content in real time and automatically display the new content when the file is updated. Example: tail-n20logfile.txt#Display the last 20 lines of the logfile.txt file tail-flogfile.txt#Monitor the updated content of the logfile.txt file in real time head command: The head command is used to display the beginning of the log file

    What to do if the right-click menu management cannot be opened in Windows 10 What to do if the right-click menu management cannot be opened in Windows 10 Jan 04, 2024 pm 07:07 PM

    When we use the win10 system, when we use the mouse to right-click the desktop or the right-click menu, we find that the menu cannot be opened and we cannot use the computer normally. At this time, we need to restore the system to solve the problem. Win10 right-click menu management cannot be opened: 1. First open our control panel, and then click. 2. Then click under Security and Maintenance. 3. Click on the right to restore the system. 4. If it still cannot be used, check whether there is something wrong with the mouse itself. 5. If you are sure there is no problem with the mouse, press + and enter. 6. After the execution is completed, restart the computer.

    C# Development Advice: Logging and Monitoring Systems C# Development Advice: Logging and Monitoring Systems Nov 22, 2023 pm 08:30 PM

    C# Development Suggestions: Logging and Monitoring System Summary: In the software development process, logging and monitoring systems are crucial tools. This article will introduce the role and implementation suggestions of logging and monitoring systems in C# development. Introduction: Logging and monitoring are essential tools in large-scale software development projects. They can help us understand the running status of the program in real time and quickly discover and solve problems. This article will discuss how to use logging and monitoring systems in C# development to improve software quality and development efficiency. The role of logging system

    Understand the meaning of event ID455 in win10 logs Understand the meaning of event ID455 in win10 logs Jan 12, 2024 pm 09:45 PM

    The logs of win10 have a lot of rich content. Many users must have seen the event ID455 display error when looking for their own management logs. So what does it mean? Let’s take a look below. What is event ID455 in the win10 log: 1. ID455 is the error <error> that occurred in <file> when the information store opened the log file.

    See all articles