是否可以使用查询清除SQL Server 2008中的日志? [英] Is it possible to clear logs in SQL Server 2008 using a query?

问题描述

我注意到数据库服务器上有一些可疑活动.我使用以下T-SQL语句检查服务器上最近执行的最近300条查询:

I noticed some suspicious activities on my database server. I inspect last 300 recent queries executed on my server using this T-SQL statement:

SELECT TOP 50 * 
FROM
    (SELECT 
        COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
        execution_count, s2.objectid,
        (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
           ( (CASE WHEN statement_end_offset = -1
                    THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
                    ELSE statement_end_offset 
              END)- statement_start_offset) / 2+1)) AS sql_statement,
    last_execution_time
FROM 
    sys.dm_exec_query_stats AS s1
CROSS APPLY 
    sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE 
    sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
--and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
ORDER BY 
    last_execution_time DESC 

我相信昨天有人从我的服务器上删除了日志.

I believe yesterday someone delete logs from my server.

我想知道是否可以通过查询或使用SQL Server Management Studio远程访问数据库来删除最近的查询日志.如果可能的话，怎么办?

I want to know if this possible to delete recent queries logs with a query or remote access to database with SQL Server Management Studio. If possible, how ?

推荐答案

DBCC FREEPROCCACHE 清除过程缓存.

sys.dm_exec_query_stats 仅报告缓存中的查询.重新启动计算机或由于内存压力而刷新的计划或由于索引或统计信息维护使它们失效的原因是DMV不会包含较早查询的其他可能原因.

sys.dm_exec_query_stats only reports on queries in the cache. Restarting the machine or plans being flushed due to memory pressure or because they have been invalidated due to index or statistics maintenance are other possible reasons why the DMV would not contain older queries.

这篇关于是否可以使用查询清除SQL Server 2008中的日志?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！