Last night I had the pleasure of presenting “The What, Why, and How of Filegroups” for the East Iowa I-380 SQL Server user group. Thanks for having me!
My presentation materials are available [here][2].
During the presentation, I show the DBCC CHECKFILEGROUP command. An attendee asked if running this command instead of a full DBCC CHECKDB would reduce the size of the snapshot created. I asked my good friend Erin Stellato ([blog][3] | [twitter][4]), and she gave me this excellent information:
"When you run any CHECK, it has to create a consistent view of the database, hence the snapshot.� Even if you're just checking a filegroup (or one table), it needs the entire database to be transactionally consistent, not just a table or filegroup, so the snapshot of the database gets created.
Now, a couple things to remember about the snapshot.� DBCC creates an NTFS alternate stream for each file in a database.� If you have 4 filegroups with 2 files each, you get 8 alternate streams, one for each file.� But of course, the snapshot is empty initially, it starts to grow as changes are made to pages while DBCC runs.� So...the size of the snapshot is ultimately dependent on how many pages are being changed while DBCC is running, not what kind of DBCC you're running."
Thanks for the info, Erin!
[2]: /media/users/grrlgeek/East Iowa 201212.zip?mtime=1355347802 [3]: http://erinstellato.com/ [4]: https://twitter.com/erinstellato
Jes Borland is a Premier Field Engineer - SQL Server for Microsoft, where she is a trusted technical adviser, deep technical support, and teacher for her customers. Her experience as a DBA, consultant, and 5-time Data Platform MVP allow her to help the business and the IT teams reach their goals. She has worked with SQL Server as a developer, report writer, DBA, and consultant. Her favorite topics are administration, automation, and performance. She frequently presents at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef.
