•
What is a log file?
–
guarantees data integrity of the database and
for data recovery
–
it is what makes ACID, in part, possible
•
atomicity
•
concurrency
•
isolation level
•
durability
•
A database has at least one transaction log file
•
Each transaction log file is comprised of VLFs
–
no fixed size
–
no fixed number of files
•
Make sure initial size and auto-grow is set
correctly
–
Try to minimize the number of times a files
grows
•
Some growth is of course ok
•
To determine number of VLFs, run
DBCC LogInfo(DatabaseName)
•
50 or less is ok
•
over 50:
use SHRINKFILE to reduce the number of virtual log files.
BACKUP LOG databasename TO
devicename
DBCC SHRINKFILE(TransactionLogLogicalFilename, TRUNCATEONLY)
ALTER DATABASE databasename
MODIFY FILE
(
NAME =
TransactionLogLogicalFilename
, SIZE = NewTotalSize
)
Now, for the real power of using code to help with database
administration. Create a procedure that
will store the VLF info for all databases. You can schedule this to run daily, and even
set up alerts to be notified if the number of VLFs exceeds a threshold value,
say 50 or 100.
See next post for a handy view which will show how many VLFs per database.
See next post for a handy view which will show how many VLFs per database.
USE Admin
GO
CREATE PROC dbo.PopLogInfo
@ShowSql bit= 0
AS
/* DATE AUTHOR REMARKS
10/16/12 PPaiva Initial
creation.
DESCRIPTION
Truncates and populates
LogInfo using DBCC LogInfo.
DEBUG
SELECT *
FROM LogInfo
-- Number of VLFs per
database
SELECT Server, DB,
COUNT(*) Qty
FROM LogInfo
GROUP BY Server, DB
ORDER BY 3 desc, 1, 2
SELECT status, COUNT(*)
FROM LogInfo
GROUP BY status;
SELECT TOP 5 *
FROM vwServerDBFile
DBCC LogInfo ('AmRept')
USAGE
PopLogInfo 1
PopLogInfo
*/
SET NOCOUNT ON
-- DROP TABLE dbo.LogInfo
IF OBJECT_ID('dbo.LogInfo') Is Null
CREATE TABLE LogInfo(
ID int
NOT NULL IDENTITY(1, 1) CONSTRAINT pk_LogInfo PRIMARY
KEY CLUSTERED,
Server
varchar(30) NULL,
DB sysname
NULL,
FileID int
NOT NULL ,
MBytes As
Convert(decimal(8, 2), (SizeBytes/1000000.)),
StartOffsetBytes bigint NOT NULL,
FSeqNo bigint
NOT NULL,
Status
int NOT NULL, -- can be 0 or 2. 0
= reusable, 2 = not reusable
StatusDesc AS (case when Status=(0) then 'Reusable' when Status=(2) then 'Not Reusable' else '(undefined)' end),
Parity int
NOT NULL, -- can be 64 or
128. Value switches upon reuse.
CreateLSN Numeric(25, 0) NOT NULL, -- 0 indicates an original file, when database was created.
SizeBytes bigint
NOT NULL,
dbID
int NULL,
InsertDate datetime
CONSTRAINT def_LogInfoInsertDate DEFAULT GetDate()
)
SELECT name, database_id
INTO #DBs
FROM sys.databases
DECLARE @MaxDB sysname,
@DB sysname,
@dbID int,
@s varchar(500)
SELECT @MaxDB = MAX(Name),
@DB =
''
FROM #DBs
IF @ShowSql = 0
TRUNCATE TABLE LogInfo
WHILE @DB < @MaxDB
BEGIN
-- Get
next DB
SELECT
@DB = Min(Name)
FROM
#DBs
WHERE
Name > @DB
-- Get
corresponding dbID
SELECT
@dbID = database_id
FROM
#DBS
WHERE
Name = @DB
SET
@s = 'INSERT INTO
LogInfo (FileID, SizeBytes, StartOffsetBytes, FSeqNo, Status, Parity,
CreateLSN)
Exec (''DBCC LogInfo(''''' + @DB + ''''')'')
UPDATE
LogInfo
SET
DB = ''' + @DB + ''',
dbID = ' +
Convert(varchar, @dbID) + '
WHERE
DB Is Null
'
IF
@ShowSql = 1
Print
@s
ELSE
Exec(@s)
END
UPDATE LogInfo
SET Server = Convert(varchar(30), SERVERPROPERTY('ServerName'))
WHERE Server Is Null
