如何删除MS SQL Server 的数据库日志(*.ldf)信息?(50分)

我有一MS SQL SERVER数据库，其存放的日志test.ldf已经很大了，
该如何删除该日志信息，使其减小呢?

???sql6.5没有这个文件, 是sql7???

对，是SQl SERVER 7!其日志文件为*.ldf,怎么将其缩小呢?

使用DBCC SHRINKFILE语句:
DBCC SHRINKFILE (T-SQL)
Shrinks the size of the specified data file or log file for the related database.
Syntax
DBCC SHRINKFILE
( {file_name | file_id }
{ [, target_size]
| [, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY}]
}
)

Examples
This example shrinks the size of a file named DataFil1 in the UserDB user database to 7 MB.
USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
GO

具体用法参见Sqlserver7.0 BooksOnline.
<font color=red size="6"> 注意: <font></font></pre>
做完之后备份数据库,以供以后恢复.

Charles:
我的数据库日志test.ldf 大小为100MB,运行
DBCC SHRINKFILE(TESTLOG,100)
之后大小没有任何反映，这是为何?

Physical Database Files and Filegroups
Microsoft&amp;reg;
SQL Server&amp;#8482;
version 7.0 maps a database over a set of operating system files. Data and log information are never mixed on the same file, and individual files are used only by one database.
SQL Server 7.0 databases have three types of files:
Primary data files
The primary data file is the starting point of the database and points to the rest of the files in the database. Every database has one primary data file. The recommended file extension for primary data files is .mdf.
Secondary data files
Secondary data files comprise all of the data files other than the primary data file. Some databases may not have any secondary data files, while others have multiple secondary data files. The recommended file extension for secondary data files is .ndf.
Log files
Log files hold all of the log information used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file extension for log files is .ldf.
SQL Server 7.0do
es not enforce the .mdf, .ndf, and .ldf file extensions, but these extensions are recommended to help identify the use of the file.
SQL Server 7.0 files have two names:
logical_file_name is a name used to refer to the file in all Transact-SQL statements.
The logical file name must conform to the rules for SQL Server identifiers and must be unique to the database.
os_file_name is the name of the physical file.
It must follow the rules for Microsoft Windows NT&amp;reg;
or Microsoft Windows&amp;reg;
95/98 file names.

SQL Server data and log files can be placed on either FAT or NTFS file systems, but cannot be placed on compressed file systems.
Pages in a SQL Server 7.0 file are numbered sequentially starting with 0 for the first page in the file. Each file has a file ID number. Uniquely identifying a page in a database requires both the file ID and page number. The following example shows the page numbers in a database that has a 4-MB primary data file and a 1-MB secondary data file.

The first page in each file is a file header page containing information about the attributes of the file. The ninth page in a primary data file is a database boot page containing information about the attributes of the database.
SQL Server 7.0 files can grow automatically from their originally specified size. When you define a file, you can specify a growth increment. Each time the file fills, it increases its size by the growth increment. If there are multiple files in a filegroup, theydo
not autogrow until all the files are full. Growth then
occurs using a round-robin algorithm.
Each file can also have a maximum size specified. If a maximum size is not specified, the file can continue to grow until it has used all available space on the disk. This feature is especially useful when SQL Server is used as a database embedded in an application where the userdo
es not have ready access to a system administrator. The user can let the files autogrow as needed to lessen the administrative burden of monitoring the amount of free space in the database and allocating additional space manually.
Database Filegroups
Database files can be grouped together in filegroups for allocation and administration purposes. Some systems can improve their performance by controlling the placement of data and indexes onto specific disk drives. File groups can aid this process. The system administrator can create filegroups for each disk drive, then
assign specific tables, indexes, or the text, ntext, or image data from a table, to specific filegroups.
No file can be a member of more than one filegroup. Tables, indexes, and text, ntext, and image data can be associated with a filegroup, in which case all their pages will be allocated in that filegroup.
Log files are never a part of a filegroup. Log space is managed separately from data space.
Files in a filegroup will not autogrow unless there is no space available on any of the files in the filegroup.
There are three types of filegroups:
Primary
The primary filegroup contains the primary data file and any other files not put into another filegroup. All pages for the system tables are allocated in the primary filegroup.
User-defined
User-defined filegroups are any filegroups specified using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.
Default
The default filegroup contains the pages for all tables and indexes thatdo
not have a filegroup specified when they are created. In each database, only one filegroup at a time can be the default filegroup. Members of the db_owner fixed database role can switch the default filegroup from one filegroup to another. If no default filegroup was specified, it defaults to the primary filegroup.
SQL Server 7.0 can work quite effectively without filegroups, so many systems will not need to specify user-defined filegroups. In this case, all files are included in the primary filegroup and SQL Server 7.0 can effectively allocate data within the database. Filegroups are not the only method that can be used to distribute I/O across multiple drives.
Members of the db_owner fixed database role can back up and restore individual files or filegroups instead of backing up or restoring an entire database.
The following example creates a database with a primary data file, a user-defined filegroup, and a log file. The primary data file is in the primary filegroup and the user-defined filegroup has two secondary data files. An ALTER DATABASE statement makes the user-defined filegroup the default. A table is then
created specifying the user-defined filegroup.
USE master
GO
-- Create the database with the default data
-- filegroup and the log file. Specify the
-- growth increment and the max size for the
-- primary data file.
CREATE DATABASE MyDB
ON PRIMARY
( NAME='MyDB_Primary',
FILENAME='c:/mssql7/data/MyDB_Prm.mdf',
SIZE=4,
MAXSIZE=10,
FILEGROWTH=1),
FILEGROUP MyDB_FG1
( NAME = 'MyDB_FG1_Dat1',
FILENAME = 'c:/mssql7/data/MyDB_FG1_1.ndf',
SIZE = 1MB,
MAXSIZE=10,
FILEGROWTH=1),
( NAME = 'MyDB_FG1_Dat2',
FILENAME = 'c:/mssql7/data/MyDB_FG1_2.ndf',
SIZE = 1MB,
MAXSIZE=10,
FILEGROWTH=1)
LOG ON
( NAME='MyDB_log',
FILENAME='c:/mssql7/data/MyDB.ldf',
SIZE=1,
MAXSIZE=10,
FILEGROWTH=1)
GO
ALTER DATABASE MyDB
MODIFY FILEGROUP MyDB_FG1 DEFAULT
GO

-- Create a table in the user-defined filegroup.
USE MyDB
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )
ON MyDB_FG1
GO

User filegroups can be made read-only. The data cannot be altered, but the catalog can still be modified to allow work such as permissions management.
SQL Server 7.0 databases can be detached from a server and reattached to either another server or the same server. This is especially useful in making databases that are distributed for use on a customer’s local SQL Server installation. For example, a company could create a database containing their current product catalog. The company could create this database on a writable compact disc drive and make the database read-only. They could then
copy the compact disc and send copies to all of their field sales representatives equipped with a catalog application and SQL Server on Windows 95 laptops. The sales representatives would then
have the latest catalog information.

dsp:
我看了你的说明，但还是不知道该如何缩小我的SQl SERVER7的日志大小！
能否详细举例说明!

Sqlserver 7.0 Books Online 中 讲到:
"Unlike data files, the shrinking of log files is not immediate. Each log file is marked with the target size of the shrink operation. "

各位大侠:
这个问题该如何解决，我还在等着各位的指教呢!

用DUMP TRANSACTION databasename WITH NO_LOG
我记得6.5里是这么写的,
7.0的也试过,但说不准
如果不行,先查文档
还是不行,再来找我

bethouvnlue:
我刚试过你的方法，好象不行，*.ldf的文件还是原来的大小,
这该怎么办?

不是让你先查文档么?
偏要让我来查
试试这个
DUMP TRANSACTION databasename WITH TRUNCATE_ONLY
如果还不行,再告诉你个笨办法

bethouvnlue:
我已经查过文档，采用的命令为：
BACKUP LOG database with no_log
但该方法还是不能删除日志，你有何高招，这里先谢谢!

多人接受答案了。