如何使用SQLDiag工具来追踪死锁错误(ZT)

SQLDiag最初是在SQL Server 7.0中引入的，它是一个非常方便的工具可以用来监控数据库服务器中出现的问题。这个命令行工具可以为微软的客户提供必需的信息，在运行 时，SQLDiag可以将不同的SQL Server工具提供的数据输出到一个文本文件中，以便于您阅览，该文本文件包含了配置、数据库和错误日志的信息。
在SQL Server 2005中，您可以将 SQLDiag作为一个服务运行，也可以将它作为一个命令行工具。在以下的讨论中，我将假定您的机器上运行的是非集群的SQL Server 2000，而我将通过命令行提示符来发出命令。[b] [/b]
[b]实战[/b][b]SQLDiag[/b]
我觉得实际操作比阅读更容易吸收技术信息，让我们通过一个实例来看看SQLDiag是如何工作的，以及怎样用它让数据库管理员的日子好过些。在这个例子中，我将展示如何捕获造成数据库服务器死锁的语句，并进行研究，然后使用一些技巧性的调整来避免死锁的出现。
如果您对死锁不太熟悉，也不了解它们是怎样对数据库系统造成严重破坏的，可以参考我以前的文章，我可以对死锁做一些简单的解释，以及如何在SQL Server 2005中捕获死锁并重试。
在 服务器上捕获死锁信息之前，我必需开启服务器上的一些追踪标记，一个追踪标记是设定在SQL Server 中的一种状态，它可以监测到服务器对非法操作的响应。在这种情况下，我要开启可以监测死锁错误的追踪标记，并在错误发生时将这些信息写入错误日志。为了开 启这些追踪标记，请执行以下的命令：(您必需具备SQL Server系统管理员权限来运行DBCC命令)
DBCC TRACEON(1204, -1)
DBCC TRACEON(1205, -1)
DBCC TRACEON(3605, -1)
1204 标记将返回死锁时遇到的锁的类型；1205标记则会返回更加详细的信息，它可以记录出现死锁时正在运行的语句；3605标记将把追踪信息发送到错误日 志，SQLDiag工具可以将这些信息输出到一个文本文件中。命令中的“-1”参数表示对所有的数据库连接应用追踪标记。
现在系统已经具备捕获死锁的能力了，现在让我们来创造一个死锁的情况，在列表A中，我创建了一些表格来制造死锁。
IF EXISTS(SELECT [name] FROM sysobjects WHERE type = 'U' AND name = 'DeadlockTable1')
      DROP TABLE DeadlockTable1
GO
IF EXISTS(SELECT [name] FROM sysobjects WHERE type = 'U' AND name = 'DeadlockTable2')
      DROP TABLE DeadlockTable2
GO
CREATE TABLE DeadlockTable1
(
      IDCol TINYINT IDENTITY (1,1) PRIMARY KEY,
      DeadlockValue1 VARCHAR(20)
)
GO
CREATE TABLE DeadlockTable2
(
      IDCol TINYINT IDENTITY (1,1) PRIMARY KEY,
      DeadlockValue2 VARCHAR(20)
)
GO
列表A
现在表格已经创建好了，我将在每个表格中插入一行，这样我们就有一个值来更新了，我将使用两条临近的更新语句来造成死锁，如列表B所示：
INSERT INTO DeadlockTable1 (DeadlockValue1)
SELECT 'Deadlock1'
INSERT INTO DeadlockTable2 (DeadlockValue2)
SELECT 'Deadlock2'
GO
列表B
这 部分很有意思，在以上的代码中，我在每一个表格中都添加了一行更新语句，但是这两条语句又都在互相争夺表资源，最终会造成死锁情况的发生。在此，我将使用 两个单独的数据库连接，剪切并粘贴这部分SQL Server代码到一个连接中，我将它命名为连接A，参见列表C，然后，剪切并粘贴这部分SQL Server代码到另外一个连接，我将它命名为连接B，参见列表D。
BEGIN TRANSACTION
      UPDATE DeadlockTable2
      SET DeadlockValue2 = 'Connection1'
      WAITFOR DELAY '0:0:3'
      UPDATE DeadlockTable1
      SET DeadlockValue1 = 'Connection1'
COMMIT TRANSACTION
列表C
BEGIN TRANSACTION
      UPDATE DeadlockTable1
      SET DeadlockValue1 = 'Connection2'
      WAITFOR DELAY '0:0:6'
      UPDATE DeadlockTable2
      SET DeadlockValue2 = 'Connection2'
COMMIT TRANSACTION
列表D
为 了制造死锁，您需要首先运行连接A中的代码，紧接着运行连接B中的代码，正如连接A中WAITFOR DELAY语句所示的那样，您大约有3秒钟的时间来切换连接并执行连接B中的代码。如果您正确执行了以上步骤，那将会在一个查询窗口中收到和下面这条信息 非常相似的出错信息：
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 53) was deadlocked on
resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.
发 生死锁是因为两个连接之间发生了资源争夺，连接A锁定了DeadlockTable2并试图更新DeadlockTable1，而这是无法实现的，因为与 此同时，连接B锁定了DeadlockTable1，而且需要在DeadlockTable2上运行更新语句，这个场景是一个非常经典的例子，这也是为什 么在设计事务的时候，您要尽可能按照相同的使用顺序来更新表格，这一点非常重要。
如果您按照这种方式来设计事务，那么出现这种令人头疼的情况的可能性就会降低很多。SQL Server数据库引擎有专门的算法来通知您有这种情况出现，它还会系统性地关闭一个进程并允许另外一个顺利完成。
现 在我们已经体验了死锁，我们可以使用SQLDiag工具将错误信息输出到一个文本文件了。您可能会感到奇怪，既然在出现死锁错误的情况下可以从屏幕上看到 错误信息，为什么还要将这些信息输出到文本文件中呢？开启追踪标记可以捕获在您的系统上发生的所有死锁，这对于有大量数据更新的系统来说，这些信息是非常 有价值的。在文本文件中查找并观察何时发生错误、哪些对象牵连在错误中，可以节省您大量的研究时间。
为了执行SQLDiag工具，打开一个命令提示符窗口并浏览到SQL Server安装目录中的Binn文件夹。一个可能出现该工具的文件夹是C:Program FilesMicrosoft SQL ServerMSSQLBinn。
在使用这个工具的时候，您可能会用到一些参数，我会用到－E和－O开关，－E的含义是在执行是使用集成的安全属性；而－O开关可以指定您要输出的文件路径，以下是使用该工具参数的一个例子：
C:Program FilesMicrosoft SQL ServerMSSQLBinn
SQLDiag –E –O C:SQLDiagOutput.txt
[b]Conclusion[/b]
死 锁，尤其在2005版之前的SQL Server中，常常是数据库管理员的致命伤，如果没有好的方法来捕获并重现死锁事件，数据库管理员和开发者不得不依赖SQL Profiler和其它的工具来捕获死锁，并用于进一步的研究工作。SQLDiag是一个非常有效的追踪工具，不仅可以用来对付这些令人尴尬的死锁错误， 还可以用来捕获SQL Server环境中很多其它的诊断信息。我建议您要花些时间来研究这个强大的工具，而不仅仅是尝试本文中的例子，因为SQLDiag可以作为预防性的工具 来避免未来可能出现的令人头痛的问题。

解决/优化SQL Server查询速度慢的方法

1、把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL2000不在支持。数据量（尺寸）越大，提高I/O越重要。
2、纵向、横向分割表，减少表的尺寸(sp_spaceuse)
3、升级硬件
4、根据SQL Server查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建）,不要对有限的几个值的字段建单一索引如性别字段。
5、提高网速。
6、扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。
配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server? 2000时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的1.5倍。如果另外安装了全文检索功能，并打算运行Microsoft搜索服务以便执行 全文索引和SQL Server查询。
可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的3倍。将SQL Server max server memory服务器配置选项配置为物理内存的1.5倍（虚拟内存大小设置的一半）。
7、增加服务器CPU个数;但是必须 明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询 的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作UPDATE,INSERT， DELETE还不能并行处理。
8、如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like ''a%'' 使用索引 like ''%a'' 不使用索引用 like ''%a%'' 查询时，SQL Server查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。
9、DB Server 和APPLication Server 分离；OLTP和OLAP分离
10、分布式分区视图可用于实现数据库服务器联合体。
联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。（参照SQL帮助文件''分区视图''）
a、在实现分区视图之前，必须先水平分区表
b、 在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上 运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。
11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。
在T-sql的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理SQL Server查询计划的过程是这样的：
1、 查询语句的词法、语法检查
2、 将语句提交给DBMS的查询优化器
3、 优化器做代数优化和存取路径的优化
4、 由预编译模块生成SQL Server查询规划
5、 然后在合适的时间提交给系统处理执行
6、 最后将执行结果返回给用户。
其次，看一下SQL SERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。

12、 Commit和rollback的区别 Rollback:回滚所有的事物。 Commit:提交当前的事物. 没有必要在动态SQL里写事物，如果要写请写在外面如： begin tran exec(@s) commit trans 或者将动态SQL 写成函数或者存储过程。
13、在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。
14、SQL的注释申明对执行没有任何影响

分析方法：
第一、先优化一下索引，索引对数据查询速度影响很大的。
第二、将日志文件与数据文件分开存放。
第三、优化系统中存储过程的查询条件，这个是提高查询速度的最基本，曾在一个大型公司的数据库中写存储过程，取数是从北京，上海，广州三地取数，数据量超过50万，整个查询不超过 10秒。就是因为索引与存储过程的优化。

–首先从两个方面:
1\分析是硬件的问题,比如内存经常在90%,CPU经常在80%以上,建议加硬件了.提升硬件速度.加内存,内存够的话对整个磁盘的读写能力会减轻很多.我们公司以前的内存是4G,现在升级为64位,可以用16G,速度大幅度提高,但有时也会出现慢,只是有时锁的问题…
  硬盘建议用RAID0+1,现在的硬盘又不贵.数据库一般都建议用RAID10.一是安全,二是速度.都比较有保障.
2\软件方面:
  检测耗用内存,CPU最多的语句或过程,进行跟踪优化,SQL2005中有一个表sys.dm_exec_query_stats可以看到.当然也可以根据Read80Trace程序进行统计跟踪.(比如一个小时的跟踪查看耗用资源最多的语句).
根据这些信息再来优化数据,如:加索引,分区表,….优化语句啊.
总结:以前听过一句话, 数据库慢的80%的因素是SQL语句.所以把SQL语句优化好对速度提高很快的当然加硬件也可以的

A. 按平均 CPU 时间排在前五个的查询
此方案提供有关 CPU 时间、IO 读写以及按平均 CPU 时间排在前五个查询的执行次数的信息。SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],

(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_textFROM sys.dm_exec_query_statsORDER BY [Avg CPU Time] DESC

还是建议做个SQLDIAG的诊断报告，获得整个系统的性能信息，然后才能更有针对性的做出调整。

1、把系统换成64位，然后加大内存。
2、数据库性能的优化。相信很多查询都是有很大的优化空间的，也不清楚你们有没有专门的数据库维护人员，看一下索引碎片之类的情况。 

Important: ASP.NET Security Vulnerability

http://weblogs.asp.net/scottgu/archive/2010/09/18/important-asp-net-security-vulnerability.aspx

Win2008/ R2 IIS7.x Classic Asp upload 200K limit solution

During the process of migrating a site to IIS 7 we came across an issue with Classic ASP file uploads throwing 500 errors when larger than 200kb. There is an EASY fix for this.
In IIS 7, click your site and expand it then click the ASP icon.
Expand the Limits Properties icon, and change the value in the “Maximum Requesting Entity Body Limit” to a value larger than 200000 (which is about 200kb). 2000000 would be roughly 2mb, 20000000 would be 20mb.
Click the APPLY button. That’s it!

IIS7.0的修改方法如下：
打开IIS管理器--双击“IIS”中的“ASP”-- 打开“配置 ASP 应该程序的属性”--展开“限制属性”；
修改“最大请求实体主体限制”的值，默认值为200000（即不到200KB）；
把它修改为你想修改的大小，如：52000000（50MB）。
修改完成点击“应用”OK！

IIS6.0的修改方法如下：
在服务里关闭iis admin service服务
找到windows\system32\inetsrv\下的metabase.xml,
打开，找到aspmaxrequestentityallowed 把他修改为需要的值，默认为204800，即200k
把它修改为51200000（50MB）
然后重启iis admin service服务

Tips for Classic ASP developers on IIS7.x

There are a few changes in IIS7 which Classic ASP developers should be aware of.

ASP not installed by default

First things first!  If you're moving from XP to Windows Vista / Longhorn Server, you may be getting this error:
--------------------------------------------------------------------------------------------------------------------
HTTP Error 404.3 - Not Found
Description: The page you are requesting cannot be served because of the Multipurpose Internet Mail Extensions (MIME) map policy that is configured on the Web server. The page you requested has a file name extension that is not recognized, and is not allowed.

--------------------------------------------------------------------------------------------------------------------

this is usually the case when you haven't installed the ASP component.  Go to where you installed IIS and look under IIS/WWW Services/Application Development/ASP and install it.  :)

Access and Classic ASP

A lot of people use Access as a database - because it is small, can be copied around, and is easy to manage.  One of the changes we made in IIS7 in Vista broke using ASP and Access by default.  I described this change in more detail in this post, but essentially it has to do with the fact that Application Pools now use the Application Pool identity's profile and temporary directory, rather than \windows\temp by default.  And since the only one that can write to Network Service's temp directory is the Network Service, anonymous or authenticated ASP applications break, since ASP uses the impersonated identity to access the database.  If you use ASP and Access on IIS7, you've probably seen this error, or a variation of it: 
--------------------------------------------------------------------------------------------------------------------
Microsoft JET Database Engine error '80004005'
Unspecified error

--------------------------------------------------------------------------------------------------------------------

The answer is pretty straight forward:  turn off loadUserProfile, or ACL the temp directory to allow writes.  As a result of this and other compatibility issues, we're considering reverting this change in Longhorn Server / Vista SP1.  In the mean time, you can work around it by doing either of the following:
This appcmd command will turn off loadUserProfile for the Default Application Pool.  if your application runs in a different AppPool, make the corresponding change:
%windir%\system32\inetsrv\appcmd set config /section:applicationPools /[name='DefaultAppPool'].processModel.loadUserProfile:false
This command will ACL the Network Service temp directory to allow creator write / read privledges.  If you run your Application Pool under a different identity, you'll need to ACL that owner's temp directory:
icacls %windir%\serviceprofiles\networkservice\AppData\Local\Temp /grant Users:(CI)(S,WD,AD,X)
icacls %windir%\serviceprofiles\networkservice\AppData\Local\Temp /grant "CREATOR OWNER":(OI)(CI)(IO)(F)

**Update 2/19/2009** if you are having issues with Access and ASP you might want to read this terrific guide recently posted on IIS.NET: http://learn.iis.net/page.aspx/563/using-classic-asp-with-microsoft-access-databases-on-iis-70-and-iis-75/ 

 

Script errors no longer shown in browser by default

As a result of our security paranoia, we turned off ASP's default behavior of sending script errors (including line number and code snippet to the browser.  So instead of seeing the typical error you would see ASP throw, you will now see this:
--------------------------------------------------------------------------------------------------------------------
An error occurred on the server when processing the URL. Please contact the system administrator
--------------------------------------------------------------------------------------------------------------------
To revert back to IIS6- behavior, simply run the following command:
%windir%\system32\inetsrv\appcmd set config -section:asp -scriptErrorSentToBrowser:true
Or you can find it in the UI here:


then you'll be back to seeing this style of error instead:
--------------------------------------------------------------------------------------------------------------------
Microsoft VBScript compilation error '800a03ea'
Syntax error
/test.asp, line 4

Response.Write("I love classic ASP" && foo)
-------------------------------------^

--------------------------------------------------------------------------------------------------------------------

 

Parents paths disabled by default (redux)

We disabled parent paths by default with IIS6, but I've seen this hit people on Vista coming from XP, where it is still enabled by default in IIS5.1  The enableParentPaths setting determines where ASP "includes" should be allowed to escape the parent directory (eg. ../../../includeFile.inc).   You'll see this error by default if you try to escape the current directory:
--------------------------------------------------------------------------------------------------------------------

Active Server Pages error 'ASP 0131'
Disallowed Parent Path
/test.asp, line 1
The Include file '../bad.inc' cannot contain '..' to indicate the parent directory.
--------------------------------------------------------------------------------------------------------------------

or you may see this error if you are using a path with ../ in it and your ADODB code

--------------------------------------------------------------------------------------------------------------------

Server.MapPath() error 'ASP 0175 : 80004005'
Disallowed Path Characters
/testdir/test.asp, line 9
The '..' characters are not allowed in the Path parameter for the MapPath method.
--------------------------------------------------------------------------------------------------------------------
To revert back to IIS 5.x behavior, simply run the following command:
%windir%\system32\inetsrv\appcmd set config -section:asp -enableParentPaths:true
or you can find the UI setting here:

APPL_PHYSICAL_PATH no longer returns "\" with path

If you use Request.ServerVariables("APPL_PHYSICAL_PATH") to get at the physical path for your application, you may notice that the physical path no longer returns with a trailing slash.  In previous releases of IIS, we returned this value as stored in the metabase.  In IIS7, we calculate this value based on the configuration store, and we never return a trailing slash.  You'll need to account for this especially if you are the return value with some other part of the path in your application.

 

 

Session_OnEnd not firing

If you find that Session_onEnd event in your global.asa is not firing, check out this blog post from Lou on the issue and the fix.

Classic ASP on IIS 7 (setting, tips...)

It's not really difficult to run classic ASP on Internet Information Server 7, but there are some bumps down the road. Here's a little help to get you started:
First of all, check whether you have the Windows feature for ASP installed. Go to Programs and Features in Control Panel and click Turn Windows features on and off. Navigate down the tree of features and make sure ASP is checked.

Next, go to Internet Information Server (IIS) Manager (run inetmgr from the Start Search box to get there fast) and create a new application pool for ASP.

This should be a "No managed code" and "Classic" app pool if you do not intend to mix and match the .NET Framework and ASP.NET in the same pool. The Integrated pipeline would not make sense, because it only applies to integrating HTTP modules that are either native or managed .NET implementations.
I had some difficulties setting the identity of the application pool to the new built-in IUSR account. This account replaces the former computer account called IUSR_machinename. Same goes for the IIS_WPG group for application pools that is replaced by the builtin group IIS_IUSRS. Read more about it here. Under the Advanced Settings of the "ASP" application pool you will find the Identity property under Process Model. The default value is NetworkService. I found no way to set this to the BUILTIN\IUSR by choosing SpecificUser and setting BUILTIN\USR under Identity SpecificUser Credentials. I guess that you shouldn't be running ASP sites under IUSR anymore. It is used for anonymous users automatically.

To easy the administration effort, there is a way to turn off the IUSR account without turning off anonymous identification:
appcmd set config -section:anonymousAuthentication -userName:"" --password
And lastly, create your new web application. For example, copy the ASP site folder under C:\inetpub\wwwroot, where the default installation location of "Default Web Site" of IIS. Convert the folder (or virtual directory) to an application and choose "ASP" as the application pool. Check whether the .asp extension is mapped to the correct handler and whether it is enabled. Also make sure that the identity of your application pool has sufficient rights (and no more than that) to access the files in the web site's folder.

One final note: whenever there is a error in the ASP website, and you get this error message: "An error occurred on the server when processing the URL. Please contact the system administrator",

it's probably because by default no error messages are sent to the client in IIS7. Change the setting "Send Errors To Browser" of your web application under the ASP icon to reveal such errors.

Side note: if you get the nice Internet Explorer 500 error message and no particular details at all, remember to uncheck the "Show friendly HTTP error messages" checkbox under Internet Options, Advanced of IE.

Best Max Memory Settings for SQL Server 2005/2008

It is pretty important to make sure you set the Max Server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the operating system that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure. 
This setting controls how much memory can be used by the SQL Server Buffer Pool.  If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems. It is even more important to set this correctly if you have “Lock Pages in Memory” enabled for the SQL Server service account (which I always do for x64 systems with more than 4GB of memory).
These settings are for x64, on a dedicated database server, only running the DB engine, (which is the ideal situation).
Physical RAM                        MaxServerMem Setting
2GB                                           1500
4GB                                           3200
6GB                                           4800
8GB                                           6400
12GB                                         10000
16GB                                         13500
24GB                                         21500
32GB                                         29000
48GB                                         44000
64GB                                         60000
72GB                                         68000
96GB                                         92000
128GB                                       124000
If you are running other SQL Server components, such as SSIS or Full Text Search, you will want to allocate less memory for the SQL Server Buffer Pool. You also want to pay close attention to how much memory is still available in Task Manager. This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003):
Physical RAM            Target Avail RAM in Task Manager
< 4GB                               512MB – 1GB
4-32GB                              1GB – 2GB
32-128GB                            2GB – 4GB
> 128GB                              > 4GB
You can use T-SQL to set your MaxServerMemory setting. The sample below sets it to 3500, which is the equivalent of 3.5GB. This setting is dynamic in SQL Server 2005/2008, which means that you can change it and it goes into effect immediately, without restarting SQL Server.
-- Turn on advanced optionsEXEC  sp_configure'Show Advanced Options',1;GO
RECONFIGURE;GO
-- Set max server memory = 3500MB for the serverEXEC  sp_configure'max server memory (MB)',3500;GO
RECONFIGURE;GO

-- See what the current values areEXEC sp_configure;

You can also change this setting in the SSMS GUI, as you see below:

Finally, I have learned that it is a good idea to temporarily adjust your MaxServerMemory setting downward by a few GB if you know you will be doing a large file copy on your database server (such as copying a large database backup file).

http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx