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.
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 | twitter), 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!