December 20, 2015 at 4:22 am
I use the emptyfile technique to move DB files to other drives or simply to reorganize DB files. After starting the dbcc shrinkile command I am used to see a phase of "SpaceReclaim" and afterwards a phase of "FilesCompact". During this phase the content of the file to be emptied is moved. During both phases the dm_exec_requests shows the percent_complete value. So far the normal way as I know.
There is however a database in my set that will no longer react on dbcc shrinkfile at all. Not on emptyfile, not on shrinking. Once the dbcc shrinkfile is in "FilesCompact" phase the percent_complete column in dm_exec_requests shows 0 all the time and nothing happens regarding the space used in the files. It does not seem to react slowly. No reaction after more than 12 hours. Not one MB of data transfered, percent_complete still equals 0, no errors and running until cancel.
The database is one of a set of databases all of similar size and exact structure and settings, all on the very same mount points. But his particular database behaves strange in the way described above.
On top of this users report that this database takes twice the time to run a certain process than the other databases of that set. I am looking for reasons since weeks but could not find any differences, except that there is a huge amount of blocking situations during the process mentioned above with this particular database but not whith the other databases of that set. I am not sure whether those blockings are cause or effect of the problem. The statements are blocked when they try to do simple deletes on a certain table while other deletes on the same table are running with exactly the same parameterized statement. Something like "Delete top (@P0) from table where id1=@P1 and id2<@P2". The waitstates are always out of PAGEIOLATCH_EX, PAGEIOLATCH_SH and LCK_M_RS_U with the headblockers tending to wait for IO and the blocked ones often waiting on release of LCK_M_RS_U.
A DBCC checkdb ('x', repair_rebuild) with all_errormsgs, no_infomsgs) took 90 minutes and came back with nothing but "completed successfully" and no change of the strange behaviour.
Any ideas and hints are appreciated.
Thanks a lot in advance.
December 20, 2015 at 8:36 am
Any time a file is "shrunk" it does so at the expense of extreme fragmentation because of a lousy "non-Norton" method of doing the shrink that they never let Paul Randal's team actually fix. Have you rebuilt the indexes in the databases for these "shrunk/moved" files?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2015 at 11:50 am
Thank you for your hint, which I confirm. However I am not at all interested in shrinking files but in emptying and removing them in order to setup big and contiguous files rather. And yes, the indexes are defragmented and rebuild on a semi regular basis. My problem is that dbcc shrinkfile (x, emptyfile) does not show any effect and I have no clue why and how to get this DB back to "normal" behaviour or which Lion I am actually chasing for.
Besides this a restore to a test Database transports the problem. As far as I know a restore from backup will generate rarely fragmented objects depending on the state of the IO subsystem, doesn't it? I did the backup this morning and the copy reacts exactly like the original. So I guess there is something wrong within my database but as told before dbcc checkdb does not show any errors.
December 20, 2015 at 3:29 pm
I have to admit that my knowledge is a bit limited in this area and so must apologize for not being able to help. Hopefully, my response will act as a "bump" to your post to get some of the heavy hitters in this area interested.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply