-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathbrokenPageTest.sql
112 lines (80 loc) · 2.46 KB
/
brokenPageTest.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
CREATE TABLE Test1
(
Filler CHAR(8000)
)
on FG01
-- Insert 4 records
INSERT INTO Test1 VALUES (REPLICATE('A', 8000))
INSERT INTO Test1 VALUES (REPLICATE('B', 8000))
INSERT INTO Test1 VALUES (REPLICATE('C', 8000))
INSERT INTO Test1 VALUES (REPLICATE('D', 8000))
GO
-- Retrieve the selected records
SELECT * FROM Test1
GO
CREATE TABLE Test2
(
Filler CHAR(8000)
)
on FG02
-- Insert 4 records
INSERT INTO Test2 VALUES (REPLICATE('A', 8000))
INSERT INTO Test2 VALUES (REPLICATE('B', 8000))
INSERT INTO Test2 VALUES (REPLICATE('C', 8000))
INSERT INTO Test2 VALUES (REPLICATE('D', 8000))
GO
-- Retrieve the selected records
SELECT * FROM Test2
GO
CREATE TABLE Test3
(
Filler CHAR(8000)
)
on FG03
-- Insert 4 records
INSERT INTO Test3 VALUES (REPLICATE('A', 8000))
INSERT INTO Test3 VALUES (REPLICATE('B', 8000))
INSERT INTO Test3 VALUES (REPLICATE('C', 8000))
INSERT INTO Test3 VALUES (REPLICATE('D', 8000))
GO
-- Retrieve the selected records
SELECT * FROM Test3
GO
CREATE TABLE Test4
(
Filler CHAR(8000)
)
on FG04
-- Insert 4 records
INSERT INTO Test4 VALUES (REPLICATE('A', 8000))
INSERT INTO Test4 VALUES (REPLICATE('B', 8000))
INSERT INTO Test4 VALUES (REPLICATE('C', 8000))
INSERT INTO Test4 VALUES (REPLICATE('D', 8000))
GO
-- Retrieve the selected records
SELECT * FROM Test4
GO
--==============
--backup db
backup database SueFGTest to disk = 'c:\data\data1\sql_db\SueFGTest.bak'
dbcc ind(sueFGTest, Test1, -1)
--
alter database sueFGTest set single_user with rollback immediate
--corrupt page 8
dbcc writepage('sueFGtest', 3, 8, 4000, 1, 0x45, 1);
go
select * from Test1
--error!
/*
Msg 824, Level 24, State 2, Line 90
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x81bb6e92; actual: 0x81bb6a92). It occurred during a read of page (3:8) in database ID 29 at offset 0x00000000010000 in file 'C:\data\data2\sql_db\sueFG01.ndf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
*/
alter database sueFGtest set multi_user
--now run checkfilegroup?
EXECUTE master.dbo.DatabaseIntegrityCheck
@Databases = 'sueFGTest',
@CheckCommands = 'CHECKFILEGROUP',
@FileGroups = 'ALL_FILEGROUPS',
@PhysicalOnly = 'Y',
@EXECUTE = 'Y',
@LogToTable = 'Y'