You are hereSQL 2008 DBA
SQL 2008 DBA
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.
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
