Replication Failed with Log Reader Agent Failed to Construct Command

3.875
Your rating: None Average: 3.9 (8 votes)

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:

sp_removedbreplication fails after SQL upgrade

0
Your rating: None

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:

Move Text Results in SSMS with Column Based Selection

0
Your rating: None

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.

Delete duplicates from a table using TSQL

3.333335
Your rating: None Average: 3.3 (3 votes)

Even if you have variable number of duplicates, this script will delete them all. It uses SQL 2005's new TOP feature in DELETE clause. The () are required now, although still works without them in SELECT statements.

Windows Server 2008 Install as My Workstation

0
Your rating: None

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.

Get filename and path into two different variables

3.666665
Your rating: None Average: 3.7 (3 votes)

Best practices for Troubleshooting Slow Running Queries

5
Your rating: None Average: 5 (1 vote)

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.

Using a Numbers or Tally Table for Query Performance

4
Your rating: None Average: 4 (3 votes)

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.

When to do a Diff backup vs Full backup

0
Your rating: None

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. 

Data Platform Team Announces SQL 2005 SP3

0
Your rating: None

The Data Platform team announced in their blog that Microsoft will do SP3, but start working on it after SQL 2008 ships. Personnally, I am just going to test Cumulative 7 patch and take my time for SP3.