-- if the table is created in the master database and starts with the three characters ‘sp_’,
-- it can be accessed and modified in any database:
USE master
GO
IF EXISTS (SELECT 1 FROM sys.tables
WHERE name = 'sp_LOGINFO')
DROP TABLE sp_loginfo;
GO
CREATE TABLE sp_LOGINFO
(FileId tinyint,
FileSize bigint,
StartOffset bigint,
FSeqNo int,
Status tinyint,
Parity tinyint,
CreateLSN numeric(25,0) );
GO
-- The following code creates a new database call TWO_LOGS and then copies a large table from
-- the AdventureWorks sample database into that TWO_LOGS, causing the log to grow:
USE Master
GO
IF EXISTS (SELECT * FROM sys.databases
WHERE name = 'TWO_LOGS')
DROP DATABASE TWO_LOGS;
GO
CREATE DATABASE TWO_LOGS
ON PRIMARY
(NAME = Data ,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TWO_LOGS.mdf'
, SIZE = 100 MB)
LOG ON
(NAME = TWO_LOGS1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TWO_LOGS1.ldf'
, SIZE = 5 MB
, MAXSIZE = 2 GB),
(NAME = TWO_LOGS2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATATWO_LOGS2.ldf'
, SIZE = 5 MB);
GO
-- If you run DBCC LOGINFO, you’ll notice that it returns VLFs sorted by FileID, and initially,
-- the file sequential number values (FSeqNo) are also in order:
USE TWO_LOGS
GO
DBCC LOGINFO;
GO
-- Now we can insert some rows into the database, by copying from another table:
SELECT * INTO Orders
FROM AdventureWorks.Sales.SalesOrderDetail;
GO
-- Instead, we can save the output of DBCC LOGINFO in the sp_loginfo table, and sort by FSeqNo:
TRUNCATE TABLE sp_LOGINFO;
INSERT INTO sp_LOGINFO
EXEC ('DBCC LOGINFO');
GO
-- Unused VLFs have a Status of 0, so the CASE forces those to the end
SELECT * FROM sp_LOGINFO
ORDER BY CASE FSeqNo WHEN 0 THEN 9999999 ELSE FSeqNo END;
GO
-- Log records are being generated, and as each VLF is reused, it gets a new FSeqNo value.
USE pubs;
-- First look at the VLFs for the pubs database
DBCC LOGINFO;
-- Now verify that pubs is in auto truncate mode
SELECT last_log_backup_lsn
FROM master.sys.database_recovery_status
WHERE database_id = db_id('pubs');
GO
CREATE TABLE newtable (a int);
GO
INSERT INTO newtable VALUES (10);
INSERT INTO newtable VALUES (20);
INSERT INTO newtable VALUES (30);
GO
SET NOCOUNT ON
DECLARE @counter int;
SET @counter = 1 ;
WHILE @counter < 1000 BEGIN
UPDATE newtable SET a = a + 1;
SET @counter = @counter + 1;
END;
-- I’ll discuss recovery models later in this chapter, but for now, you can just make sure that pubs
-- is in the appropriate recovery mode by executing the following command:
ALTER DATABASE pubs SET RECOVERY FULL;
-- You can use the following statement to make the backup, substituting the path shown with the path
-- to your SQL Server installation, or the path to any backup location
BACKUP DATABASE pubs to disk =
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backup\pubs.bak';
-- As soon as you take the full backup, you can verify that the database is not in autotruncate mode,
-- again by looking at the database_recovery_status view:
SELECT last_log_backup_lsn
FROM master.sys.database_recovery_status
WHERE database_id = db_id('pubs');
-- Now you can try to shrink the log back down again. The first thing you need to do is truncate the log,
-- which you can force by setting the recovery model to SIMPLE:
ALTER DATABASE pubs SET RECOVERY SIMPLE;
-- If you then issue the following command, or if you issue the DBCC SHRINKDATABASE command for the
-- pubs database, SQL Server will shrink the log file.
DBCC SHRINKFILE (2);
-- Instead, you can use the dynamic management view sys.dm_os_performance_counters and retrieve
-- the percentage full for each database’s log:
SELECT instance_name as [Database],
cntr_value as "LogFullPct"
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Percent Log Used%'
AND instance_name not in ('_Total', 'mssqlsystemresource')
AND cntr_value > 0;
-- For example, this query returns the recovery mode and the state of the AdventureWorks database:
SELECT name, database_id, suser_sname(owner_sid) as owner ,
state_desc, recovery_model_desc
FROM sys.databases
WHERE name = 'AdventureWorks'
