Expand your SQL Server horizons with a Kindle loaded with 5 eBooks
Read these related tips:
* Click here for related tips
* Still need your problem solved?
* Let us know what you think about the site
Problem
One crucial aspect of all databases is the transaction log. The transaction log is used to write all transactions prior to committing the data to the data file. In some circumstances the transaction logs can get quite large and not knowing what is in the transaction log or how much space is being used can become a problem. So how to you determine how much of the transaction log is being used and what portions are being used?
Solution
In most databases the transaction log is generally just one (ldf) file, but inside the overall transaction log is a series of virtual log files as depicted below.
source (SQL Server 2005 Books Online)
The way the transaction log is used is that each virtual log file is written to and when the data is committed and a checkpoint occurs the space becomes useable again. Although this does depend on your database recovery model, whether you are using replication and your backup processing. If there are no additional virtual logs available, SQL Server will grow the transaction log, based on your database settings, to accommodate the additional space that is required.
source (SQL Server 2005 Books Online)
The use of the file and the virtual logs all depends on how the database is used and other settings you have enabled in your database. If you are publishing data from this database or if the database is set to the the Full or Bulk-Logged recovery mode, this will also affect whether the process loops back to the beginning of the file, if it uses the next available virtual log or it if needs to grow the transaction log and create additional virtual logs.
DBCC SQLPERF(logspace)
One command that is extremely helpful in understanding how much of the transaction log is being used is DBCC SQLPERF(logspace). This one command will give you details about the current size of all of your database transaction logs as well as the percent currently in use. Running this command on a periodic basis will give you a good idea of how the transaction logs are being used and also give you an idea on how large they should really be. This is a question that is often asked by a lot of people that use SQL Server and as you run this you will find out there is no perfect answer it all depends on a lot of criteria such as:
*
recovery model
*
size of the transactions
*
how large your tables are and therefore how much space is needed for index maintenance
*
how frequently you run transaction log backups
*
whether the database is published or not
*
etc...
To run this command issue the following in a query window:
DBCC SQLPERF(logspace)
This is sample output:
From here we can see the size of the transaction logs as well as how much space is being used. The current log space used will tell you how much of the transaction log is being used. If this percentage is high and the size of the log is quite big it is probably due to one of the items listed above.
DBCC LOGINFO
The next command to look at is DBCC LOGINFO. This will give you information about your virtual logs inside your transaction log. The primary thing to look at here is the Status column. Since this file is written sequentially and then looped back to the beginning, you want to take a look at where the value of "2" is in the output. This will tell you what portions of the log are in use and which are not in use Status = 0. Another thing to keep an eye on is the FSeqNo column. This is the virtual log sequence number and the latest is the last log. If you keep running this command as you are issuing transactions you will see these numbers keep changing.
To run this command issue the following in a query window:
DBCC LOGINFO
This is sample output:
If we now run a transaction log backup such as the following:
BACKUP LOG DBUtil WITH NO_LOG
or
BACKUP LOG DBUtil TO DISK = 'C:\Backup\DBUtil.trn'
and then rerun the command you will see how the Status=2 has changed in the file. The last entry is still marked as in use, but the previous entries have been reset to 0.
One thing to note, if you do run BACKUP LOG...WITH NO_LOG you will need to run another full backup, otherwise SQL Server will just reuse the space in the transaction log because there is no way to restore the next transaction log backup since you did not do a real transaction log backup and therefore the settings in the log file were reset. Also, if you don't have a full backup of your database the space in the transaction log also gets reused. This is because there is no full backup to restore first and therefore you can not issue a transaction log restore.
DBCC OPENTRAN
Another command to look at is DBCC OPENTRAN. This will show you if you have any open transactions in your transaction log that have not completed or have not been committed. These may be active transactions or transactions that for some reason never completed. This can provide additional information as to why your transaction log is so big or why you may not be able to shrink the transaction log file. This will show you both open transactions as well any un-replicated transactions if the database is published.
To run this command issue the following in a query window:
DBCC OPENTRAN
This is sample output:
Now that you have an idea how much of your transaction log is being used and what is being used you can start to make some decisions on how large the transaction log should be. One thing you should try to do is find that optimum size in order to eliminate having to shrink and grow the transaction log on a constant basis. As with all database and server activity it is best to minimize the overhead as much as you can and this is one of those areas that you can somewhat manage by creating and maintaining the optimum transaction log size.
Next Steps
* Make sure you are using the correct backup strategy based on your recovery model
* If you are going to use BACKUP LOG...WITH NO_LOG you should just look at changing your database to the Simple recovery model
* If you do need to shrink your transaction log file take a look at DBCC SHRINKFILE.
* Here are some other tips regarding transaction log space.
o Monitoring transaction log space
o Managing SQL Server 2000 Transaction Log Growth
checking syslogs
The total number of data page in the table is 1.
NOTICE:Space used on the log segment is 0.20 Mbytes, 0.13%.
NOTICE:Space free on the log segment is 153.4Mbytes,99.87%.
DBCC execution Completed.If dbcc printed error messages,
Contact a user with SA role.
若日志不在它自己的设备上,则会显示下列信息:
NOTICE:Notification of log space used/free.
Can not be reported because the log segment is not on its own device.
例2. dbcc checktable (titles)
The total number of data page in this table is 3.
Table has 18 data rows.
DBCC execution Completed. If DBCC printed error messages. contact a user with SA role.
dbcc checkalloc (pubs2)
.
.
.
alloc page 0 (#of extent=32 used pages=68 ref pages=6
alloc page 256 (# of extent=32 used pages=154 ref pages=154)
alloc page 512 (# of extent=28 used pages=184 ref pages=184)
alloc page 768 (# of extent=1 used pages=1 ref pages=1)
total (# of extent=93 used pages=407 ref pages=407) in this database.
DBCC execution completed.If dbcc printed error message,
Contact a user with System Adminstrator (SA) role.
dbcc tablealloc(titles)
显示信息如下:
The default report option of OPTIMIZED is used for this run. The default fix option of FIX.is used for this run.
.
.
.
Total #of extent=3
Alloc page 256 (# of extent=1 used pages=2 ref pages=2).
Alloc page 256(# of extent=1 used pages=2 ref pages=2)
Alloc page 256 (# of extent=1 used pages=2 ref pages=2)
Total (# of extent=3 used pages=8 ref pages= in this database.
use adventureworks
go
create table a (a int)
go
checkpoint
go
backup log adventureworks WITH NO_LOG
--2008中需改为 DBCC SHRINKFILE (N'adventureworks_Log' , 1)
go
dbcc log(adventureworks,3)
go
--接着,我们在表格里插入一条记录。
insert into a values (1)
go
dbcc log(adventureworks,3)
go
结果中(见图1-26)可以看到三条关于这个Insert的记录。
--sql2008中增加了20几条记录
图1-26 DBCC LOG结果中3条和INSERT动作相关的记录
我们再插一条记录。
1. insert into a values (100)
2. go
3. dbcc log(5,3)
4. go
每个 SQL Server 数据库都有一个恢复模式属性,(the Recovery Model), 它指示事务日志如何记录,如:事务日志是否可以被备份,以及恢复操作的许可类型。默认情况下,一个新的数据库从 Model 数据库继承了一个恢复模式。当然,你也可以修改默认设置为其它模式。
你可以配置一个 SQL Server 数据库的恢复模式为以下几种之一:
简单模式(Simple): 在这种模式下,事务日志的备份是不安全的,这意味着你不能对备份之后的事务日志进行管理。这种模式也会自动的扩展日志空间,所以几乎不需要去管理事务日志的空间。然而,这种模式也是风险最大的一种模式,数据库只能被恢复到最后一次备份的时间点,而在最后一次备份之后执行的事务将会丢失。这种模式通常用于系统数据库、或者用于测试和开发阶段。或者是几乎仅有只读情况的数据仓库数据库。这种情况下,一些操作只是尽可能少的被记录。
完整模式(Full): 由于这种模式可以提示指定时间点的恢复,因此它可以备份并且也应当进行备份。这种模式比简单模式的风险要小。但是,在完整模式下,所有的操作都被完整的记录,包括大数据量操作。这种模式适用于生产环境。
大数据量记录模式(Bulk Logged): 这种模式可以看作是完整模式的补充,因为在这种模式下,大数量操作只是被最小化的记录。例如,你可能要大量的加载数据但你不希望这些事务日志被记录,因为你只是希望加载数据而已。在这种情况下,你可以在导入数据时,将模式由完整模式切换到大数据量模式,执行完后,再恢复到完整模式。(需要注意的是:在切换回完整模式后,你应当做一次完整备份)
你可以在数据库上通过执行 ALTER DATABASE 语句,和指定 Set Recovery 来切换这些模式,例子如下:
USE master;
ALTER DATABASE EmployeeDB
SET RECOVERY FULL;
在上面的代码中,我修改了 EmployeeDB 数据库,并将恢复模式设置为完整模式 FULL。注意:由于默认的 model 数据库是被配置为完整模式 Full ,这也意味着 EmployeeDB 数据库被自动配置为完整模式,因为它是继承自 model 数据库的。所以,如果在你的服务器上, model 数据库的默认设置没有被更改的话,上面的的例子中 ALTER DATABASE 并不会改变什么,但是你要注意,当你将数据从 简单模式 切换到完整模式时,有时候必须执行一些其它步骤,例如进行一个完整备份。在SQL Server 在线教程中主题 "Considerations for Switching from the Simple Recovery Model" 描述了将数据库的恢复模式从简单模式转换为完整模式或大容量模式时,有哪些步骤要执行。
你也可以在 SQL Server Management Studio中设置恢复模式。在对象浏览器中右键单击数据库名称,并选择“属性”,在数据库属性对话框中,单击选项页,并设置恢复模式属性。
SELECT name,
size, -- in 8-KB pages
max_size, -- in 8-KB pages
growth,
is_percent_growth
FROM sys.database_files
WHERE type_desc = 'LOG'
这条语句返回了当前的文件大小(按8-KB的页面大小),文件可增长到的最大大小 (同样按 8_KB 的页面大小),增长率和是否按百分比增长标记。该标记指示数据库文件的大小按何种方式增长。如果标记被设置为0,那么增长率就是 8-KB ,如果设置为 1,则按百分比增长。
上述代码返回的结果大致如下:
Name size max_size growth is_percent_growth
EmployeeDB_log 128 268435456 10 1
如结果中所示,这条语句只返回一行记录。这是因为 EmployeeDB 只配置了一个日志文件。上面的结果还反映了 EmployeeDB_log 的当前文件大小是 128 个8-KB 的页面大小,它可以增长到 268,435,456 个8-KB 的页面大小,增长率是按 10%的速率。
你还可以使用 DBCC SQLPERF 语句返回一个 SQL Server 实例的每个数据库的事务日志信息,要获取日志数据,你必须在参数中使用 LOGSPACE 关键字,如下所示:
DBCC SQLPERF(LOGSPACE);
这条语句返回以 MB 计算的日志大小,日志空间使用的百分比,以及你的 SQL Server 实例中每个数据库的日志状态。 EmployeeDB 数据库的信息如下:
Database Name Log Size (MB) Log Space Used (%) Status
EmployeeDB 0.9921875 40.05906 0
这个例子中 EmployeeDB 日志大约是 1 MB 大小,并且使用了 40% 的日志空间。
你也可以在 SQL Server Management Studio 中生成一个图形化的报表,结果类似于执行 DBCC SQLPERF 语句。方法是:在对象浏览器中,右键单击数据库名称,选择报表,再选择标准报表,最后点击磁盘利用率。
备份日志文件
如果你将数据库的恢复模式配置为完全模式或大容量模式,你就应当有规律的备份事务日志,这样你就可以截断日志并释放不活动的日志空间。备份也可以用于恢复数据库(通常与数据库备份一起使用)。
在事务日志备份之前,必须先执行过一个数据库的完整备份。通常,在我使用本文中的日志备份前,一般都先执行下面的数据库备份语句:
BACKUP DATABASE EmployeeDB
TO DISK = 'E:\DbBackup\EmployeeDB_dat.bak';
注意:执行这段代码时,确认指定路径存在或指定一个另外的路径。
执行完数据库备份后,我一般运行下面的数据修改语句,以使当前日志不包含已备份的内容:
USE EmployeeDB;
UPDATE Employees
SET JobTitle = 'To be determined';
UPDATE Employees
SET CountryRegionName = 'US'
WHERE CountryRegionName = 'United States';
DELETE Employees
WHERE BusinessEntityID > 5;
然后我再运行 DBCC SQLPERF 查看日志空间的统计信息,该语句返回下面的结果:
Database Name Log Size (MB) Log Space Used (%) Status
EmployeeDB 0.9921875 64.41929 0
你可以看到,日志空间的使用率已从40%提升到接近65%。
备份完数据库,你就可以备份事务日志了。执行事务日志的备份,使用 BACKUP LOG 语句,并指定备份位置,如下:
-- back up transaction log
BACKUP LOG EmployeeDB
TO DISK = 'E:\LogBackup\EmployeeDB_log.bak';
同样要注意路径的问题。
这里我指定了备份路径,然而, BACKUP 还支持其它选项,可以在SQL Server Books Online查看 “BACKUP (Transact-SQL)” 主题以获得更多信息。
执行完事务日志的备份以后, SQL Server 数据库引擎会自动截断不活动的日志空间。(注意:截断事务日志只是移除了不活动的虚拟日志空间,并不减小文件大小)要减小日志文件,你应当对文件进行收缩。要检查是否截断了日志,请再次运行 DBCC SQLPERF 语句。现在的结果应当如下面所示:
Database Name Log Size (MB) Log Space Used (%) Status
EmployeeDB 0.9921875 44.88189 0
现在日志空间已下降到45%。