Ed Leighton-Dick

SQL Server consultant, PASS user group leader, Microsoft Data Platform MVP, speaker, husband, father, and runner - in no particular order

[Ed Leighton-Dick @eleightondick]

@AngryPets @_randolph_west Thank you both!

[Ed Leighton-Dick @eleightondick]

#sqlhelp Has anyone encountered error 8705 on SQL 2019: "A DML statement encountered a missing entry in index ID 1 of table ID 421576540, in the database xxxxx due to an interaction with another transaction"? Looking for insight on meaning and severity.

[Ed Leighton-Dick @eleightondick]

@_randolph_west It's a table. I'm not sure on isolation level - I haven't been able to trace it back to the query the user was running at the time. It's a third-party app that uses NOLOCK heavily, so I suspect that's involved.

[Ed Leighton-Dick @eleightondick]

@sqlstudent144 Congratulations!

[Ed Leighton-Dick @eleightondick]

@jqmtweets I’d consider transactional replication for DR if I couldn’t do AG or log shipping, but not for HA. It’s too easy for an inexperienced dev/DBA to break it.

[Ed Leighton-Dick @eleightondick]

THANK YOU to @Microsoft for recognizing me as a Microsoft MVP for the Data Platform again this year. It's truly an honor to be a part of this community! #MVPBuzz

[Ed Leighton-Dick @eleightondick]

@randy_knight Looks like we aren't connected on Twitter. Can you DM me about the session Dan mentioned?

[Ed Leighton-Dick @eleightondick]

@IrishSQL OMG. Thanks for pointing that out. My general rule is don’t feed the trolls, but I can’t let a 19th century attitude like that go. Responded.

[Ed Leighton-Dick @eleightondick]

@MattWalshBlog Oh, and I was still working since I didn’t get more than a week of vacation off. That’s why men want paternity leave. I’m glad your attitude worked for your marriage, but don’t condemn those of who think there might be a better way.

[Ed Leighton-Dick @eleightondick]

@MattWalshBlog Seriously? When my kids were born, my wife had two jobs during her leave: recovering, and doing the parts of the care of the baby that no one else can. My job? LITERALLY EVERYTHING ELSE, including getting up with the baby part of the time so she could get some sleep. 1/2

[Ed Leighton-Dick @eleightondick]

@SQLBek Good luck, sir!

[Ed Leighton-Dick @eleightondick]

@Hugo_Kornelis @cl @sqldbawithbeard Thanks for trying, Hugo!

[Ed Leighton-Dick @eleightondick]

We used to be able to use DBCC SHRINKFILE(1, EMPTYFILE) to move all data out of a large .mdf file into a group of .ndf files, but that appears to be blocked under SQL 2019. Other than rebuilding each individual index, does anyone have a method of doing this efficiently? #sqlhelp

[Ed Leighton-Dick @eleightondick]

@cl @Hugo_Kornelis @sqldbawithbeard Interesting idea. I hadn't thought about it possibly being related to compat level. It's a long shot, but I'll try it in test. Thanks!

[Ed Leighton-Dick @eleightondick]

@Hugo_Kornelis @cl @sqldbawithbeard Correct. EMPTYFILE used to be able to do that - it's an old trick that worked because it moved the user data before throwing the error about the system tables. Convenient b/c I need to keep the tables in PRIMARY if possible (vendor app).

[Ed Leighton-Dick @eleightondick]

@NedOtter Sorry, poor word choice. It seemed like they changed something in SQL that now does additional validation at the beginning of the run that prevented the command from being used on .mdf. Hugo’s test suggests it may be specific to this db.

[Ed Leighton-Dick @eleightondick]

@Hugo_Kornelis Interesting.

This is a 1.3TB database, recently upgraded from SQL 2012. Receiving error 2555 about 30 sec after issuing EMPTYFILE command. Reason given is system tables cannot be moved - I expected that at the end of the run, not the start.

[Ed Leighton-Dick @eleightondick]

@DanTheSQLMan @leemarkum Agreed. SQL Audit is especially good at that type of monitoring. Let me know if you have any questions on it.

[Ed Leighton-Dick @eleightondick]

For anyone who attended my Security Dashboard session at PASS Summit on Friday: I've uploaded my scripts to my GitHub account at https://t.co/VxTRgrbw1p.

#sqlpass #passsummit2020

[Ed Leighton-Dick @eleightondick]

@EM_SQL Thanks! I’ll try to get the code posted this weekend.