1、点SQL SERVER错误日志,右键,配置,限定错误日志的数目,比如6个
2、然后运行命令:
EXEC sp_cycle_errorlog ;
这个命令的作用是将当前日志归档,然后新建一个日志。因为日志数量被限制为6,那么只要运行这个步骤若干次,那么老的日志就会被回收了。SQL服务重启一次,也会将日志归档新建,但这个命令不用重启。
结论:
1、SQL的默认端口一定要改
2、sa的密码一定要设的够强,最好不要用sa,改一个用户名。
遇到的问题:
遇到SQL Server 无法启动服务
把SQLEXPRESS协议下的TCP/IP协议终的 已启用改为否
用到的资料:
查看监听端口的方法
exec sys.sp_readerrorlog 0, 1, 'listening'
查询SQL Server错误日志信息
The parameter values can be as follows:
Parameter | Values |
<LogNumber> | Log number 0, 1, 2 … For example 0 returns current log. 2 returns logs from ERRORLOG.2 |
<LogType> | 1 – Reads SQL Server error logs, 2 – Reads SQL Server Agent error logs |
<SearchTerm1> | Search Term for Text Column |
<SearchTerm2> | Search Term for Text Column * When both search terms are specified, it only returns lines containing both terms |
<StartDate> | Start reading logs from specified date |
<EndDate> | Reads logs till this date |
<SortOrder> | ASC – Ascending or DESC – Descending |
You can use the stored procedure as:
EXEC xp_ReadErrorLog
- Reads current SQL Server error log
EXEC xp_ReadErrorLog 1
- Reads SQL Server error log from ERRORLOG.1 file
EXEC xp_ReadErrorLog 0, 1
- Reads current SQL Server error log
EXEC xp_ReadErrorLog 0, 2
- Reads current SQL Server Agent error log
EXEC xp_ReadErrorLog 0, 1, 'Failed'
- Reads current SQL Server error log with text 'Failed'
EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login'
- Reads current SQL Server error log with text ‘Failed’ AND 'Login'