You are hereSQL 2005 DBA
SQL 2005 DBA
SQLSaturday #43 Talk on Replication Performance Tuning and Troubleshooting
This past Saturday, I gave a talk on SQL Server Replication Performance Tuning and Troubleshooting. The talk went great and everyone seemed to have learned alot. The attendance was great even though Buck Woody was talking next door. Tough competition!
I have attached my powerpoint deck of my talk to this post. Other good links for my custom replication scripts are:
http://www.sqlwebpedia.com/content/sql-replication-undelivered-command-count
SQL Replication Undelivered Command Count
Ever wanted to get an email summary of subscriptions that are behind a certain amount? This script can do it! Just setup as a job on your distributor and it will send you a summary email with how many commands are yet to be delivered in a table format for easy reading.
SQL Replication Error Summary
We had a script that would try to get all the information about replication's current condition, but I felt it was doing too much and didn't do a good enough job. So, I re-wrote it in to two scripts. This script will tell you all the errors that have happened in past hour. Schedule it as a job on your distributor box and it will email you only when there were errors.
The Ultimate MS SQL Backup Script
I am finally getting around to publishing my ultimate Microsoft SQL Server Backup scripts. All told, it is about 1600 lines of code and it can do just about anything you would need for Native or Quest Litespeed backups.
http://www.sqlwebpedia.com/files/Scripts/DBOPSBackupScripts.zip
Here are some examples so you get idea of its power.
PowerShell Script to Export SQL Schema Definition
Ganesh Kaliaperumal created a script to export schema to files for us to easily compare with WinMerge. Check out the options and modify as needed.
Call from powershell command line like so:
.\GenerateScript.ps1 "Servername" "Adventureworks" "c:\scripts\powershell\"
Reasons why not to shrink DB data files
Paul Randal gives an excellent explanation of why not to shrink data files if you don't have to. http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx You can turn a <1% fragmented index to 99% fragmented index with one shrink operation.
His recommendation, if you have the extra space, is to shrink by moving data to new filegroups:
I am finalist for 2009 Exceptional DBA Award
Since the Red-Gate software sponsored award website doesn't post much information about us, I thought I would post my application letter so you get a better feel for what I do at work. Thanks for voting - click Vote Now image on this site: http://www.exceptionaldba.com/vote.htm
Improving delete operations with SQL Server
The SQLCAT team blog has a great tip on deleting rows efficiently to help preventing blocking and minimize log file growth http://sqlcat.com/msdnmirror/archive/2009/05/20/fast-ordered-delete.aspx.
Preserving SQL Job History
I am often migrating servers and like to keep the SQL job history more accessible than a backup I need to restore. So, I created a script for saving this information into a table for easy access later:
How do you manage database log files?
There seems to be a lot of mis-understanding of log file management, potentially enough to cause data loss in a disaster recovery scenario. Paul Randal has an excellent blog post on the subject and survey results of what the public does: http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-proper-transaction-log-size-management.aspx
