Search
SQL Server Expert Blogs
SQL 2008 DBA
SQL Saturday #68 Transactional Replication Inside and Out
This past weekend, I gave a talk at the Olympia, WA SQL Saturday conference on Transaction Replication. I am posting up my slide deck and script files for monitoring replication performance and last hours replication errors. Both of these procs need to be run on your distributor server and the prc_internalsendmail can be replaced with your mail proc of choice, including sp_send_dbmail.
http://sqlsaturday.com/viewsession.aspx?sat=68&sessionid=3979
Automated Restore Database from Last Full Backup
In my environment, I backup to many files (for faster backups), and need to restore to a custom location. This query gets latest full backup info and restores to the path you specify. Tested on SQL 2005/2008.
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:
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.
SQLCAT team whitepaper on SQL 2008 Clustering
SQLCAT team has come out with another very detailed whitepaper (175 pages) on SQL 2008 Clustering.
SQL 2008 Central Management Server
SQL 2008 has some new DBA focused features, one of which is the Central Management Server for issuing multi-queries (queries against many servers at once, even down level clients) and Policy Based Management. Connections use your Windows Authentication only.
