截断 SQL Server 2005 的日志文件
一个有助于截断 SQL 日志文件的工具。
引言
开始使用 SQL 2005 及更高版本的人员有时可能会遇到日志文件大小增加的问题。
日志文件是数据库中所有事务的存储，因此它包含有关每个操作的详细信息。
许多不太熟悉数据库管理的开发人员可能会对如何管理这个巨大的大小感到困惑，并且可能想知道这会对他们的生产环境产生什么影响。
在这里，我编写了一个小工具，可用于修剪（将日志文件缩小到最小级别），我希望它有所帮助。
日志文件的用途
当 SQL Server 正在运行和操作时，数据库引擎通过在事务日志中进行条目来跟踪数据库中发生的几乎每个更改，以便稍后在需要时使用。
SQL Server 事务日志的位置在创建数据库的同时配置。创建数据库时，会指定 SQL Server 事务日志的位置以及与事务日志关联的其他选项。
什么是恢复模式
恢复模式是您希望将事务注册到日志文件的方式。由于日志文件包含数据库中的所有事务，因此它们可用于恢复。有 3 种类型的恢复，它们如下，以及它们的作用。
简单恢复模式
简单恢复模式就是如此：简单。在这种方法中，SQL Server 仅在事务日志中维护最少的信息。SQL Server 每次数据库达到事务检查点时都会截断事务日志，从而不留下用于灾难恢复目的的日志条目。
在使用简单恢复模式的数据库中，您只能恢复完整或差异备份。
完整恢复模式
如果发生数据库故障，您在使用完整恢复模式时具有恢复数据库的最大灵活性。除了保留存储在事务日志中的数据修改之外，完整恢复模式还允许您将数据库恢复到特定时间点。
大容量日志恢复模式是一种特殊用途的模型，其工作方式与完整恢复模式类似。唯一的区别在于它处理批量数据修改操作的方式。批量日志模型使用一种称为最小日志记录的技术将这些操作记录在事务日志中。这可以显着节省处理时间，但会阻止您使用时间点恢复选项。
代码
该工具的核心在于以下函数中。
正如您所看到的，此函数采用 3 个参数，分别是相关的数据库名称、服务器名称以及数据库的新的理想大小，我假设该大小为 0（最小为 128KB）。
- 在这里，它连接到数据库
- 获取日志文件的大小
- 将恢复模式更改为简单
- 针对数据库发出检查点，以将记录从事务日志写入数据库
- 清空日志文件，但不清空文件的大小（这意味着它会删除文件中的数据，从而增加文件中的空白空间），因为您可能知道，日志文件和数据文件的大小并不能完全表达数据的大小，通常它们具有带有递增策略的空白空间。
- 减小文件的大小。
- 获取新的大小并显示有关结果的小报告。
private static void ShrinkDatabase(string Database, string ServerName, int NewSize)
{
int OldSize;
SqlConnection Cnn = null;
try
{
Cnn =
new SqlConnection(string.Format
("trusted_connection=true; database={0};server={1}", Database,
ServerName));
Cnn.Open();
SqlCommand Cmm = new SqlCommand("", Cnn);
Cmm.CommandType = CommandType.Text;
Cmm.CommandText = string.Format("SELECT [size]
FROM sysfiles WHERE name LIKE '%LOG%'");
OldSize = (int) Cmm.ExecuteScalar();
Cmm.CommandText = string.Format("ALTER DATABASE {0}
SET RECOVERY SIMPLE", Database);
Cmm.ExecuteNonQuery();
Cmm.CommandText = string.Format("CHECKPOINT");
Cmm.ExecuteNonQuery();//issue a checkpoint against the database to
//write the records from the transaction log to the database.
Cmm.CommandText = string.Format("BACKUP LOG [{0}] WITH NO_LOG", Database);
Cmm.ExecuteNonQuery(); //This empties the log file but does not
//reduce its size.
Cmm.CommandText = string.Format("SELECT Name FROM sysfiles
WHERE name LIKE '%LOG%'");
Database = (string) Cmm.ExecuteScalar();
Cmm.CommandText = string.Format("DBCC SHRINKFILE ({0}, {1})",
Database, NewSize);
Cmm.ExecuteNonQuery();//reduce the size of the individual LDF file(s).
Cmm.CommandType = System.Data.CommandType.Text;
Cmm.CommandText = "SELECT [size] FROM sysfiles WHERE name LIKE '%LOG%'";
NewSize = (int) Cmm.ExecuteScalar();
Console.WriteLine(" The Old Size was {0}KB \n
The New Size is {1}KB \n The Logfile has shrinked with
{2}KB \n and you gained {3}% of the file size",OldSize ,
NewSize , OldSize - NewSize,
(100-(NewSize *100.00/ OldSize)).ToString("0.00"));
}
catch(Exception Ex)
{
Console.WriteLine(Ex.ToString () );
}
finally
{
if (Cnn != null)
{
Cnn.Close();
Cnn.Dispose();
}
}
}
一个有用的函数是下面的函数，它可以帮助您显示数据库及其大小，这可以通过简单地调用系统存储过程“sp_databases”来完成。但是，我们关心的是日志文件，而不是数据文件，因此我们需要查询每个数据库的日志大小。
private static SortedList<int, string> DisplayDatabases(string Servername)
{
SqlConnection Cnn = null; SqlCommand Cmm= null;
SortedList<int, string> Result = new SortedList<int, string>();
try
{
Cnn = new SqlConnection( string.Format
( "trusted_connection=true; server={0}", Servername));
SqlConnection CnnLogSize=new SqlConnection( string.Format
( "trusted_connection=true; server={0}", Servername));
Cmm = new SqlCommand("sp_databases", Cnn);
Cmm.CommandType = CommandType.StoredProcedure;
Cnn.Open();
SqlDataReader sqlDataReader = Cmm.ExecuteReader
(CommandBehavior.CloseConnection);
int Index=0;
Console.Write("#".PadRight(4));
Console.Write("Name".PadRight(40));
Console.Write("\t");
Console.Write("Size".PadRight(20));
Console.WriteLine("Log Size");
Console.WriteLine
("--------------------------------------------------------------------");
string sqlQueryLogSize = null;
SqlCommand cmdLogSize = new SqlCommand();
if (sqlDataReader != null)
while (sqlDataReader.Read ())
{
sqlQueryLogSize = string.Format("use {0}
SELECT [size] FROM sysfiles where name like
'%{0}%' and name like '%log%'", sqlDataReader.GetString(0));
cmdLogSize.CommandText = sqlQueryLogSize;
cmdLogSize.Connection = CnnLogSize ;
CnnLogSize.Open();
var LogFileSize = cmdLogSize.ExecuteScalar();
CnnLogSize.Close();
Console.Write(Index.ToString ().PadRight (4) );
Console.Write(sqlDataReader.GetString(0).PadRight (40));
Console.Write("\t");
Console.Write(sqlDataReader.GetInt32 (1).ToString ().PadRight (20));
if (LogFileSize != null) Console.Write(LogFileSize.ToString());
Console.WriteLine();
Result.Add(Index++, sqlDataReader.GetString(0));
}
if (sqlDataReader != null)
{
sqlDataReader.Close();
sqlDataReader.Dispose();
}
cmdLogSize.Dispose();
CnnLogSize.Dispose();
}
catch (Exception Ex)
{
Console.WriteLine(Ex.ToString());
}
finally
{
if (Cnn != null)
{
Cnn.Close();
Cnn.Dispose();
}
}
Console.WriteLine
("--------------------------------------------------------------------");
Console.WriteLine("Please Choose a number from 0 to {0},
Hit Ctrl+C to exit", Result.Count - 1);
return Result;
}
此处的main函数将仅调用上述函数并显示一些消息，供用户决定要截断哪个数据库的日志文件。
static void Main(string[] args)
{
string ServerName;
if (args.Length == 0 )ServerName = "Localhost";
else ServerName = args[0] ;
A:
SortedList<int, string> L = DisplayDatabases(ServerName);
string Input = Console.ReadLine();
int IntInput=0;
bool isInt= int.TryParse(Input, out IntInput);
while (!(IntInput > -1 && IntInput < L.Count && isInt))
{
L = DisplayDatabases(ServerName );
Input = Console.ReadLine();
isInt = int.TryParse(Input, out IntInput);
}
ShrinkDatabase(L[IntInput], ServerName, 0);
Console.WriteLine("------------------------------");
Console.WriteLine("Do you want to Shrink another? hit Y if Yes, Otherwise N");
Input = Console.ReadLine();
if (Input != null) if (Input.ToLower() =="y" ) goto A;
}
本文主要基于
管理 SQL Server 2000 事务日志增长
作者：Jeremy Kadlec -- 2006 年 7 月 10 日
http://www.mssqltips.com/tip.asp?tip=950