-- Code and scripts from Chapter 5: Logging and Recovery


-- The following script creates a new table in the 
--   pubs database, inserts three records, 
--   and then updates those records 1000 times. 
--   Each update is an individual transaction, and each 
--   one is written to the transaction log. However, 
--   note that the log does not grow at all and the 
--   number of VLFs does not increase even after 
--   3000 update records are written. 
--   (If you’ve already taken a backup of pubs, you might 
--   want to re-create the database before trying this example. 
--   You can do that by running the script instpubs.sql script 
--   from the companion CD, which I told you about in Chapter 4.) 

--   However, even though the number of VLFs does not change, 
--    you will see that the FSeqNo values changes. Log records are 
--    being generated, and as each VLF is reused, 
--    it gets a new FSeqNo value. 

--   The active portion of the log circling back to the beginning of the 
--     physical log file
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
DROP TABLE newtable
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;
GO
--  Now make a backup of the pubs database after making sure that the 
--    database is not in the SIMPLE recovery mode. Make sure that pubs 
--    is in the appropriate recovery mode by executing the following command:
ALTER DATABASE pubs SET RECOVERY FULL;
GO
--   You can use the following statement to make the backup, 
--   substituting the path shown with the path to your SQL Server installation:
BACKUP DATABASE pubs to disk = 
    'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backup\pubs.bak';
GO
-- 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');
GO


--  The following code, supplied by Cliff Dibble from Microsoft, 
--   returns the same information as DBCC SQLPERF('logspace') in a 
--   tabular format that can be further filtered or easily embedded 
--   into a stored procedure, table-valued function, or view:
   SELECT rtrim(pc1.instance_name) AS [Database Name]
    ,      pc1.cntr_value/1024.0  AS [Log Size (MB)]
    ,      cast(pc2.cntr_value*100.0/pc1.cntr_value as dec(5,2))  
            as [Log Space Used (%)]
    FROM   sys.dm_os_performance_counters as pc1
    JOIN   sys.dm_os_performance_counters as pc2
    ON     pc1.instance_name = pc2.instance_name
    WHERE  pc1.object_name LIKE '%Databases%'
    AND    pc2.object_name LIKE '%Databases%'
    AND    pc1.counter_name = 'Log File(s) Size (KB)'
    AND    pc2.counter_name = 'Log File(s) Used Size (KB)'
    AND    pc1.instance_name not in ('_Total', 'mssqlsystemresource')
	AND    pc1.cntr_value > 0
GO


--   To see what mode your database is in, you can inspect the sys.databases view. 
--   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'
GO