/*
02 - New Structures.sql, to setup and investigate FILESTREAM tables
Remark: Learned from sqlskills.com
*/
-- Setup FILESTREAM at OS level
-- (This has already been done)
-- Check the FILESTREAM configuration level
EXEC sp_configure 'filestream_access_level';
GO
/*
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
filestream access level 0 2 2 2
*/
-- if it isn't enabled, enable FILESTREAM at the
-- instance level
EXEC sp_configure 'filestream_access_level', 2;
RECONFIGURE
GO
-- Create a database. Note the FILESTREAM filegroup
/*
C:\>mkdir C:\SQLskills
C:\>mkdir C:\SQLskills\FileStreamTestDB
*/
USE master
GO
IF DATABASEPROPERTY('FileStreamTestDB','Version') > 0
DROP DATABASE FileStreamTestDB;
CREATE DATABASE FileStreamTestDB ON PRIMARY
( NAME = FileStreamTestDB_Data,
FileName = N'C:\SQLskills\FileStreamTestDB\FSTestDB_data.mdf'),
FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
( NAME= FileStreamTestDBDocuments,
FILENAME = N'C:\SQLskills\FileStreamTestDB\Documents')
LOG ON
( NAME = FileStreamTestDB_log,
FILENAME = N'C:\SQLskills\FileStreamTestDB\FSTestDB_log.ldf');
GO
-- Create two tables with FILESTREAM columns
USE FileStreamTestDB
GO
CREATE TABLE FileStreamTest1 (
DocId UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
DocName VARCHAR(25),
Document VARBINARY(MAX) FILESTREAM);
GO
CREATE TABLE FileStreamTest2 (
DocId UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
DocName VARCHAR(25),
Document1 VARBINARY(MAX) FILESTREAM,
Document2 VARBINARY(MAX) FILESTREAM);
GO
-- Now look at the file system again
-- New directories for the tables just created, with a
-- subdirectory for each FILESTREAM column
/*
C:\SQLskills\FileStreamTestDB\Documents\7dd5a1b7-a824-458d-b0f8-0354517c18be\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777
C:\SQLskills\FileStreamTestDB\Documents\282c7a64-2d4d-4f39-adb7-c11a8a37bf82\5f857a24-0f32-4a33-8956-c3cd9e499921
C:\SQLskills\FileStreamTestDB\Documents\282c7a64-2d4d-4f39-adb7-c11a8a37bf82\de639d81-62c5-4c4e-b1ac-8a4c60e9c3d7
*/
INSERT INTO FileStreamTest1 VALUES (
NEWID (), 'Donghua Luo',
CAST ('SQLskills.com' AS VARBINARY(MAX)));
INSERT INTO FileStreamTest1 VALUES (
NEWID (), 'KaitLyn Luo',
CAST ('dbaglobe.com' AS VARBINARY(MAX)));
GO
SELECT *, CAST (Document AS VARCHAR(MAX))
FROM FileStreamTest1;
GO
/*
DocId,DocName,Document,
DBF70A90-979E-4366-9D86-D956AF6EE6C1,Donghua Luo,0x53514C736B696C6C732E636F6D,SQLskills.com
E11BC00C-7994-4CA5-9650-78C2B9382018,KaitLyn Luo,0x646261676C6F62652E636F6D,dbaglobe.com
(2 row(s) affected)
*/
--Note the tow files in the FILESTREAM folder
/*
Directory of C:\SQLskills\FileStreamTestDB\Documents\7dd5a1b7-a824-458d-b0f8-0354517c18be\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777
01/14/2011 03:02 PM 13 00000013-00000147-0002
01/14/2011 03:02 PM 12 00000013-00000150-0002
*/
-- Now what happens when we update a FILESTREAM value
UPDATE FileStreamTest1
SET Document = CAST (REPLICATE('a',8000) AS VARBINARY(MAX))
WHERE DocName LIKE '%Donghua%';
/*
Directory of C:\SQLskills\FileStreamTestDB\Documents\7dd5a1b7-a824-458d-b0f8-0354517c18be\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777
01/14/2011 03:02 PM 13 00000013-00000147-0002
01/14/2011 03:02 PM 12 00000013-00000150-0002
01/14/2011 03:08 PM 8,000 00000013-00000159-0005
*/
-- Look again and see that the original file hasn't been
-- deleted, there are three files now, representing two
-- values. It will be garbaged collected later.
-- open the second file in notepad to demostrate that
-- someone with privileges can access the files. In
-- this case, it's plain text.
-- Now delete the file and try selecting from the table
-- again.
SELECT *, CAST (Document AS VARCHAR(MAX))
FROM FileStreamTest1
GO
/*
DocId,DocName,Document,
DBF70A90-979E-4366-9D86-D956AF6EE6C1,Donghua Luo,0x616161616161 (...) 6161,aaaaaa (...) aa
Msg 5552, Level 22, State 1, Line 1
FILESTREAM file named with GUID 'e11bc00c-7994-4ca5-9650-78c2b9382018' that belongs to FILESTREAM data file ID 0x0 does not exist or cannot be opened.
*/
-- Error 5552 and connection broken
-- try DBCC CHECKDB
DBCC CHECKDB (FileStreamTestDB)
WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
/*
Msg 7904, Level 16, State 2, Line 1
Table error: Cannot find the FILESTREAM file "00000013-00000150-0002" for column ID 3 (column directory ID bfeaaaa1-59dd-4d67-999d-c8e70b4bd777) in object ID 2105058535, index ID 0, partition ID 72057594038779904, page ID (1:161), slot ID 1.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'FileStreamTest1' (object ID 2105058535).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'FileStreamTestDB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (FileStreamTestDB).
*/
-- DBCC CHECKDB does an extensive check.
-- Try creating a ramdom file in the FILESTREAM directory
-- and running DBCC CHECKDB again
DBCC CHECKDB (FileStreamTestDB)
WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
/*
C:\>echo "A" > C:\SQLskills\FileStreamTestDB\Documents\7dd5a1b7-a824-458d-b0f8-0354517c18be\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777\test.txt
*/
/*
Msg 7908, Level 16, State 1, Line 1
Table error: The file "\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777\test.txt" in the rowset directory ID 7dd5a1b7-a824-458d-b0f8-0354517c18be is not a valid FILESTREAM file.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
Msg 7904, Level 16, State 2, Line 1
Table error: Cannot find the FILESTREAM file "00000013-00000150-0002" for column ID 3 (column directory ID bfeaaaa1-59dd-4d67-999d-c8e70b4bd777) in object ID 2105058535, index ID 0, partition ID 72057594038779904, page ID (1:161), slot ID 1.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'FileStreamTest1' (object ID 2105058535).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'FileStreamTestDB'.
*/
-- Try to fix it
-- For FILESTREAM, it can be restored from backup
/*
C:\>del C:\SQLskills\FileStreamTestDB\Documents\7dd5a1b7-a824-458d-b0f8-0354517c18be\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777\test.txt
*/
ALTER DATABASE FileStreamTestDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB (FileStreamTestDB, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE FileStreamTestDB
SET MULTI_USER;
GO
SELECT *, CAST (Document AS VARCHAR(MAX))
FROM FileStreamTest1
GO
/*
DocId,DocName,Document,
DBF70A90-979E-4366-9D86-D956AF6EE6C1,Donghua Luo,0x616161616161 (...) 6161,aaaaaa (...) aa
(1 row(s) affected)
*/
INSERT INTO FileStreamTest1 VALUES (
NEWID (), 'KaitLyn Luo',
CAST ('dbaglobe.com' AS VARBINARY(MAX)));
GO
/*
Directory of C:\SQLskills\FileStreamTestDB\Documents\7dd5a1b7-a824-458d-b0f8-0354517c18be\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777
01/14/2011 03:08 PM 8,000 00000013-00000159-0005
01/14/2011 03:51 PM 12 00000014-00000050-0002
*/
-- Map filenames back to records
DBCC IND ('FileStreamTestDB','FileStreamTest1',-1);
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 162 NULL NULL 2105058535 0 1 72057594038779904 In-row data 10 NULL 0 0 0 0
1 161 1 162 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 166 NULL NULL 2105058535 2 1 72057594039042048 In-row data 10 NULL 0 0 0 0
1 165 1 166 2105058535 2 1 72057594039042048 In-row data 2 0 0 0 0 0
(4 row(s) affected)
*/
--
DBCC TRACEON (3604);
DBCC PAGE ('FileStreamTestDB',1,161,3);
GO
/*
PAGE: (1:161)
BUFFER:
BUF @0x04365F8C
bpage = 0x116B2000 bhash = 0x00000000 bpageno = (1:161)
bdbid = 15 breferences = 0 bUse1 = 23525
bstat = 0xc00109 blog = 0xbb797979 bnext = 0x00000000
PAGE HEADER:
Page @0x116B2000
m_pageId = (1:161) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8208
m_objId (AllocUnitId.idObj) = 27 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039697408
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 20 m_slotCnt = 2 m_freeCnt = 7916
m_freeData = 360 m_reservedCnt = 0 m_lsn = (20:81:7)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = -636224135
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 88
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 88
Memory Dump @0x6467C060
00000000: 30001400 900af7db 9e976643 9d86d956 †0.....÷Ûž—fC.†ÙV
00000010: af6ee6c1 03000002 00280058 80446f6e †¯næÁ.....(.X.Don
00000020: 67687561 204c756f 03000000 00000080 †ghua Luo........
00000030: 900af7db 9e976643 9d86d956 af6ee6c1 †..÷Ûž—fC.†ÙV¯næÁ
00000040: 02000000 14020000 00000000 13000000 †................
00000050: 59010000 05000000 †††††††††††††††††††Y.......
Slot 0 Column 1 Offset 0x4 Length 16 Length (physical) 16
DocId = dbf70a90-979e-4366-9d86-d956af6ee6c1
Slot 0 Column 2 Offset 0x1d Length 11 Length (physical) 11
DocName = Donghua Luo
Document = [Filestream column] Slot 0 Column 3 Offset 0x28 Length 48
ColType = 3 FileId = -2147483648 UpdateSeq = 2
CreateLSN = 00000013:00000159:0005 (19:345:5) TxFMiniVer = 0
XdesId = (0:532)
Slot 1 Offset 0x110 Length 88
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 88
Memory Dump @0x6467C110
00000000: 30001400 1ddfdc57 00ff5749 935a3c32 †0....ßÜW.ÿWI“Z<2
00000010: 2d9f7ebd 03000002 00280058 804b6169 †-Ÿ~½.....(.X.Kai
00000020: 744c796e 204c756f 03000000 00000080 †tLyn Luo........
00000030: 1ddfdc57 00ff5749 935a3c32 2d9f7ebd †.ßÜW.ÿWI“Z<2-Ÿ~½
00000040: 01000000 20020000 00000000 14000000 †.... ...........
00000050: 50000000 02000000 †††††††††††††††††††P.......
Slot 1 Column 1 Offset 0x4 Length 16 Length (physical) 16
DocId = 57dcdf1d-ff00-4957-935a-3c322d9f7ebd
Slot 1 Column 2 Offset 0x1d Length 11 Length (physical) 11
DocName = KaitLyn Luo
Document = [Filestream column] Slot 1 Column 3 Offset 0x28 Length 48
ColType = 3 FileId = -2147483648 UpdateSeq = 1
CreateLSN = 00000014:00000050:0002 (20:80:2) TxFMiniVer = 0
XdesId = (0:544)
*/
Friday, January 14, 2011
SQL 2008 Core Studies - File Streams
Subscribe to:
Post Comments (Atom)
Awesome...
ReplyDeletethanks.. :) :)