The development team was updating a native xml column in batches of 200 or so and attempted to speed things up by increasing the batch size. Once they hit about 500 they broke the log reader agent and we saw these errors:
I had the bright idea to upgrade SQL 2000 to 2005 by detaching and uninstalling SQL 2000 and resintalling SQL 2005 to get a "clean install". The database had both a publication and a subscription (transactional replications). This was a couple of hour mistake. But was fixed easily enough with one post I found in newsgroups.
I tried to run this command:
If you need to select a column of data (vertical selection as apposed to row based selection) in the results window of Management Studio (SSMS) you can by holding down the Alt key first before you select the data. You can copy, cut, paste in the window to get the order you want. This works in code windows also.
You have to set the Options in SSMS to get this to work properly. Under Tools|Options|Query Results|SQL Server|Results to Text, select drop down option for Output Format to be Column Aligned and check box next to Include column headers in the result set.
I downloaded the ISO from MSDN and extracted to a single layer DVD which contained all the editions. Upon booting from disk I selected the Windows 2008 Standard 32bit install with Server Core option. This will install basically a command prompt only, and quickly found difficulty adding any feature, so abandoned quickly and went to the full install which still gives you a base install set, but not many features enabled.
MS SQL Community Worldwide has a great summary article on best practices on troubleshooting performance with sql queries including links to other great articles on the subject.
SQLServerCentral author Jeff Moden has a great introductory write up on the uses of Nums/Numbers/Tally tables for ultimate performance out of SQL queries. There are more uses than just replacing cursors and while loops, but his use of cross joins was new to me and very cool.
Paul Randall of SQLSkills created a nice script that will help you decide when to do a Diff backup vs a Full backup. This is great for those large DBs that sometimes change large amounts of data, but often don't and you don't want to waste the disk space for backups when you don't need to.