/*
13 - Backup Internal.sql
Remark: Learned from sqlskills.com
*/
USE master;
GO
IF DATABASEPROPERTY('MCM2008', 'Version') > 0
DROP DATABASE MCM2008;
GO
CREATE DATABASE MCM2008;
GO
USE MCM2008;
GO
-- Create a table and insert 8MB
CREATE TABLE BigTable (
c1 INT IDENTITY,
c2 CHAR(8000) DEFAULT 'a');
GO
CREATE CLUSTERED INDEX BigTable_CL
ON BigTable (c1);
GO
SET NOCOUNT ON;
GO
INSERT INTO BigTable DEFAULT VALUES;
GO 1000
-- Put the database into FULL recovery
-- model and clear out the log
ALTER DATABASE MCM2008 SET RECOVERY FULL;
GO
BACKUP DATABASE MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Full_0.bak'
WITH INIT, STATS;
GO
BACKUP LOG MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Log_0_Initial.bak'
WITH INIT, STATS;
GO
-- Now Rebuild the clustered index to
-- generate a bunch of log
ALTER INDEX BigTable_CL ON BigTable REBUILD;
GO
-- Backup the log to get a baseline size
BACKUP LOG MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Log_1_Baseline.bak'
WITH INIT, STATS;
GO
-- Test 1
-- Now rebuilds the clustered index again
-- to generate more log
ALTER INDEX BigTable_CL ON BigTable REBUILD;
GO
-- Now try a full backup to see if it clears
-- the log
BACKUP DATABASE MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Full_1.bak'
WITH INIT, STATS;
GO
-- If it did, this next log backup should be
-- very small
BACKUP LOG MCM2008 TO
DISK ='C:\SQLskills\MCM2008_Log_2_FullTest.bak'
WITH INIT, STATS;
GO
-- Test 2
-- Now rebuild the clustered index again
-- to generate more log
ALTER INDEX BigTable_CL ON BigTable REBUILD;
GO
-- Now try a checkpoint and see if it clears
-- the log
CHECKPOINT;
GO
-- If it did, this next log backup should be
-- very small
BACKUP LOG MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Log_3_CheckTest.bak'
WITH INIT, STATS;
GO
-- Now let's try a rebild in BULK_LOGGED
-- recovery model. Does that change the log
-- backup size
ALTER DATABASE MCM2008
SET RECOVERY BULK_LOGGED;
GO
ALTER INDEX BigTable_CL ON BigTable REBUILD;
GO
ALTER DATABASE MCM2008
SET RECOVERY FULL;
GO
BACKUP LOG MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Log_4_BulkTest.bak'
WITH INIT, STATS;
GO
-- Test 5
-- Now the case where there's a long-running
-- transaction and the log can't be cleared
-- by the backup. when does it get cleared?
-- In another window, do a long-running
-- transaction
/*
USE master
GO
SET NOCOUNT ON
GO
-- Start the long-running transaction
BEGIN TRAN;
GO
INSERT INTO MCM2008..BigTable DEFAULT VALUES;
GO 1000
*/
-- How much log is being used?
DBCC SQLPERF (LOGSPACE);
GO
/*
Database Name Log Size (MB) Log Space Used (%) Status
------------------------------- ------------- ------------------ -----------
master 1.742188 44.61884 0
tempdb 0.4921875 71.62698 0
model 0.4921875 98.4127 0
msdb 5.054688 16.15147 0
ReportServer$SQL08 6.742188 17.04085 0
ReportServer$SQL08TempDB 0.7421875 55.06579 0
AdventureWorks2008 1.992188 23.43137 0
AdventureWorksDW2008 1.992188 20.58824 0
AdventureWorksLT2008 1.992188 23.72549 0
AdventureWorks 1.992188 15.44118 0
AdventureWorksDW 1.992188 30 0
AdventureWorksLT 1.992188 25.09804 0
TestDb 0.7421875 90.13158 0
MCM2008 12.05469 91.61131 0
FileStreamTestDB 0.9921875 65.15748 0
SalesDB 933.9922 0.8197339 0
IndexInternals 99.99219 5.169349 0
Credit 399.9922 0.4311608 0
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
-- Now let's take a log backup
BACKUP LOG MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Log_5_LongTest.bak'
WITH INIT, STATS;
GO
-- Did the percentage used go down?
DBCC SQLPERF(LOGSPACE)
GO
/*
Database Name Log Size (MB) Log Space Used (%) Status
---------------------------------- ------------- ------------------ -----------
master 1.742188 44.61884 0
tempdb 0.4921875 71.62698 0
model 0.4921875 98.4127 0
msdb 5.054688 16.22875 0
ReportServer$SQL08 6.742188 17.04085 0
ReportServer$SQL08TempDB 0.7421875 55.06579 0
AdventureWorks2008 1.992188 23.43137 0
AdventureWorksDW2008 1.992188 20.58824 0
AdventureWorksLT2008 1.992188 23.72549 0
AdventureWorks 1.992188 15.44118 0
AdventureWorksDW 1.992188 30 0
AdventureWorksLT 1.992188 25.09804 0
TestDb 0.7421875 90.13158 0
MCM2008 12.05469 91.61131 0
FileStreamTestDB 0.9921875 65.15748 0
SalesDB 933.9922 0.8197339 0
IndexInternals 99.99219 5.169349 0
Credit 399.9922 0.4311608 0
*/
-- Commit transaction in another window
/*
COMMIT TRAN
GO
*/
-- Did the percentage used go down?
DBCC SQLPERF(LOGSPACE)
GO
-- This is because of SQL Server release these spaces reserved for rollback
/*
Database Name Log Size (MB) Log Space Used (%) Status
------------------------------------ ------------- ------------------ -----------
master 1.742188 44.61884 0
tempdb 0.4921875 71.62698 0
model 0.4921875 98.4127 0
msdb 5.054688 16.22875 0
ReportServer$SQL08 6.742188 17.04085 0
ReportServer$SQL08TempDB 0.7421875 55.06579 0
AdventureWorks2008 1.992188 23.43137 0
AdventureWorksDW2008 1.992188 20.58824 0
AdventureWorksLT2008 1.992188 23.72549 0
AdventureWorks 1.992188 15.44118 0
AdventureWorksDW 1.992188 30 0
AdventureWorksLT 1.992188 25.09804 0
TestDb 0.7421875 90.13158 0
MCM2008 12.05469 78.01361 0
FileStreamTestDB 0.9921875 65.15748 0
SalesDB 933.9922 0.8197339 0
IndexInternals 99.99219 5.169349 0
Credit 399.9922 0.4311608 0
*/
-- How about a checkpoint?
CHECKPOINT
GO
DBCC SQLPERF(LOGSPACE)
GO
-- How about a log backup
BACKUP LOG MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Log_LongTest2.bak'
WITH INIT, STATS;
GO
/*
100 percent processed.
Processed 5 pages for database 'MCM2008', file 'MCM2008_log' on file 1.
BACKUP LOG successfully processed 5 pages in 0.038 seconds (0.886 MB/sec).
*/
DBCC SQLPERF(LOGSPACE)
GO
/*
Database Name Log Size (MB) Log Space Used (%) Status
------------------------------ ------------- ------------------ -----------
master 1.742188 44.61884 0
tempdb 0.4921875 71.62698 0
model 0.4921875 98.4127 0
msdb 5.054688 16.30603 0
ReportServer$SQL08 6.742188 17.04085 0
ReportServer$SQL08TempDB 0.7421875 55.06579 0
AdventureWorks2008 1.992188 23.43137 0
AdventureWorksDW2008 1.992188 20.58824 0
AdventureWorksLT2008 1.992188 23.72549 0
AdventureWorks 1.992188 15.44118 0
AdventureWorksDW 1.992188 30 0
AdventureWorksLT 1.992188 25.09804 0
TestDb 0.7421875 90.13158 0
MCM2008 12.05469 12.69443 0
FileStreamTestDB 0.9921875 65.15748 0
SalesDB 933.9922 0.8197339 0
IndexInternals 99.99219 5.169349 0
Credit 399.9922 0.4311608 0
*/
/*
C:\Users\Administrator>dir c:\SQLskills\MCM*
Volume in drive C has no label.
Volume Serial Number is F0ED-4B30
Directory of c:\SQLskills
01/30/2011 09:25 AM 9,719,296 MCM2008_Full_0.bak
01/30/2011 09:31 AM 10,604,032 MCM2008_Full_1.bak
01/30/2011 09:26 AM 83,456 MCM2008_Log_0_Initial.bak
01/30/2011 09:29 AM 9,388,544 MCM2008_Log_1_Baseline.bak
01/30/2011 09:33 AM 9,124,352 MCM2008_Log_2_FullTest.bak
01/30/2011 09:35 AM 9,257,472 MCM2008_Log_3_CheckTest.bak
01/30/2011 09:40 AM 9,753,088 MCM2008_Log_4_BulkTest.bak
01/30/2011 09:51 AM 9,658,880 MCM2008_Log_5_LongTest.bak
01/30/2011 09:59 AM 124,416 MCM2008_Log_LongTest2.bak
9 File(s) 67,713,536 bytes
0 Dir(s) 12,096,598,016 bytes free
*/
Sunday, January 30, 2011
SQL 2008 MCM Studies - Backup Internals
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment