I guess someone tries to logon to our sql server and error log is getting bigger. I am running out of space on hdd. What should be the solution?
我想有人试图登录我们的SQL服务器,错误日志越来越大。我在硬盘上用完了空间。应该是什么解决方案?
- Cleaning up error log regularly? Howto?
- Disabling access to SQL server? For attacker IPs? For local use only? Howto?
- Any other?
定期清理错误日志?如何?
禁用对SQL Server的访问?对于攻击者IP?仅供本地使用?如何?
Regards, Burak
3 个解决方案
#1
Are you sure logins are the cause of the error-log growth? If so, you could disable remote logins:
您确定登录是导致错误日志增长的原因吗?如果是这样,您可以禁用远程登录:
Goto START --> Microsoft SQL server 2005 --> Configuration Tools --> SQL server surface area configuration
转到开始 - > Microsoft SQL Server 2005 - >配置工具 - > SQL服务器表面区域配置
Select Surface area con figuration for services.. Select Databas engine --> Remote connections and choose 'local only'
选择服务的表面区域配置。选择数据库引擎 - >远程连接并选择“仅本地”
Note, this will disable all remote connections to the database, so only change this if your application connects locally!
注意,这将禁用与数据库的所有远程连接,因此只有在应用程序本地连接时才更改此设置!
#2
we had a similar problem here, constant attempts to guess the systems password filled up the log to epic proportions.
我们在这里遇到了类似的问题,不断尝试猜测系统密码将日志填满了史诗般的比例。
you could disable external access via the windows firewall (if you're using windows SBS 2003) its fairly trivial but the end solution we opted for (because we still required external access ourselfs to the database server) was to change the default access port to SQL server; it seemed to stop alot of problems.
您可以通过Windows防火墙禁用外部访问(如果您使用的是Windows SBS 2003),这是相当简单的,但我们选择的最终解决方案(因为我们仍需要对数据库服务器进行外部访问)是将默认访问端口更改为SQL服务器;它似乎阻止了很多问题。
although if possible, I would also considering changing the architecture of your network slightly (sometimes this isn't possible if you have purchased some virtual machine from a service provider); moving your database server and disconnecting it from your hub/switch and plugging it into the back of your web server (if this is the ultimate use for your databases) so the web server acts as a type of proxy, prevent all external internet access.
虽然如果可能的话,我也会考虑稍微改变你的网络架构(如果你从服务提供商那里购买了一些虚拟机,有时这是不可能的);移动数据库服务器并将其从集线器/交换机断开并将其插入Web服务器的后端(如果这是数据库的最终用途),因此Web服务器充当代理类型,阻止所有外部Internet访问。
#3
For emergency you execute sp_cycle_errorlog to start a new one, so you can delete the old one w/o restarting the server.
对于紧急情况,您执行sp_cycle_errorlog以启动一个新的,因此您可以删除旧的没有重新启动服务器。
But the million dollar question is, of course, what is filling up the errorlog? What message shows up again and again? If you tell us that, perhaps we can help you fix the problem and eliminate the errorlog growth.
但是,百万美元的问题当然是什么填补了错误日志?什么消息一次又一次出现?如果您告诉我们,也许我们可以帮助您解决问题并消除错误日志增长。
#1
Are you sure logins are the cause of the error-log growth? If so, you could disable remote logins:
您确定登录是导致错误日志增长的原因吗?如果是这样,您可以禁用远程登录:
Goto START --> Microsoft SQL server 2005 --> Configuration Tools --> SQL server surface area configuration
转到开始 - > Microsoft SQL Server 2005 - >配置工具 - > SQL服务器表面区域配置
Select Surface area con figuration for services.. Select Databas engine --> Remote connections and choose 'local only'
选择服务的表面区域配置。选择数据库引擎 - >远程连接并选择“仅本地”
Note, this will disable all remote connections to the database, so only change this if your application connects locally!
注意,这将禁用与数据库的所有远程连接,因此只有在应用程序本地连接时才更改此设置!
#2
we had a similar problem here, constant attempts to guess the systems password filled up the log to epic proportions.
我们在这里遇到了类似的问题,不断尝试猜测系统密码将日志填满了史诗般的比例。
you could disable external access via the windows firewall (if you're using windows SBS 2003) its fairly trivial but the end solution we opted for (because we still required external access ourselfs to the database server) was to change the default access port to SQL server; it seemed to stop alot of problems.
您可以通过Windows防火墙禁用外部访问(如果您使用的是Windows SBS 2003),这是相当简单的,但我们选择的最终解决方案(因为我们仍需要对数据库服务器进行外部访问)是将默认访问端口更改为SQL服务器;它似乎阻止了很多问题。
although if possible, I would also considering changing the architecture of your network slightly (sometimes this isn't possible if you have purchased some virtual machine from a service provider); moving your database server and disconnecting it from your hub/switch and plugging it into the back of your web server (if this is the ultimate use for your databases) so the web server acts as a type of proxy, prevent all external internet access.
虽然如果可能的话,我也会考虑稍微改变你的网络架构(如果你从服务提供商那里购买了一些虚拟机,有时这是不可能的);移动数据库服务器并将其从集线器/交换机断开并将其插入Web服务器的后端(如果这是数据库的最终用途),因此Web服务器充当代理类型,阻止所有外部Internet访问。
#3
For emergency you execute sp_cycle_errorlog to start a new one, so you can delete the old one w/o restarting the server.
对于紧急情况,您执行sp_cycle_errorlog以启动一个新的,因此您可以删除旧的没有重新启动服务器。
But the million dollar question is, of course, what is filling up the errorlog? What message shows up again and again? If you tell us that, perhaps we can help you fix the problem and eliminate the errorlog growth.
但是,百万美元的问题当然是什么填补了错误日志?什么消息一次又一次出现?如果您告诉我们,也许我们可以帮助您解决问题并消除错误日志增长。