Monitor SQL Replication Log Reader Agent Latency
In this tip we look at how to tell when there is log reader replication latency.
Kun is a database administrator in Washington, DC. His areas of interest are database administration, architecture, data modeling and development. He loves technologies of all kinds.
Script to Check and Auto Generate SPNs for SQL Server
Read this tip to learn how to check and auto-generate SPNs for your SQL Servers.
SQL Server does not use all assigned CPUs on VM
Learn how to properly configure CPUs when installing SQL Server standard edition on a virtual machine.
Getting more details with an enhanced xp_fixeddrives for SQL Server
I manage a large number of SQL servers and I don't want to waste SAN space, so I want to control SQL server drive space very closely. Because of that, when there is a space problem, I use xp_fixeddrives to see how much free space the drives have but this
Manual cleanup Change Data Capture for a SQL Server database
I had a database where the log file kept growing and used 99.99% of the available space. The database recovery model was set to SIMPLE and there was no replication setup for this database, but the transaction log kept growing. The next thing I looked
Customized SQL Agent Job Notifications
We have quite a lot of SQL Server jobs and as a production DBA I only want to get notifications when jobs fail. I get enough emails already and getting more emails will not help me work on the real problems. However, sometimes business owners want to get
Providing SQL Agent Job Log Data for Developers
As a production Database Administrator, I do not want to give Developers direct access to the SQL Server Agent job log especially for servers in the DMZ. Another problem we have is that when there is a lot of log data for a job, the default job log doesn'
Monitoring Jobs on Multiple Servers by Using Multi Server Query
There are often SQL Agent jobs that need to run on multiple servers in a coordinated effort, such as refreshing Development and Test environments from a Production server. This requires that many jobs run on different servers and it requires many differe
Steps to clean up orphaned replication settings in SQL Server
As with many companies, we are trying to make our Test environment as close to our Production environment as possible. Our production environment is very replication intensive and we want to have our Test environment mimic Production as much as possible.
Tracking Down SQL Server Replication Distribution Subsystem Agent (null) Failed Errors
We have a very complicated replication environment and we have a lot of subscribers as well as distributors to support the environment. Once in a while, we get replication errors for failed jobs from our distributor server via replication alerts when we d
Maintain SQL Server Replication using Source Control
My company relies on replication intensively and we have many different SQL Server versions and configurations to support. For example, some publications are replicating data every 1 minutes, some continuous and other on demand. In addition, we have sever
Managing SQL Server Agent Job History Log and SQL Server Error Log
When you install SQL Server, it has a few default configuration settings for SQL Server to manage logs. In some instances, where servers generate too much log info and you need to look at the log it takes a long time to pull up. Also there maybe cases w
Custom sp_start_job to delay next task until SQL Agent Job has completed
The system stored procedure sp_start_job is a very useful T-SQL command that starts a SQL Agent job. The problem with this is that it starts the job and it doesn't wait until the job is finished and sometimes I want to start a job and wait until the job i
Replication Schema Option Across Different Versions of SQL Server
My company uses replication quite extensively across SQL 2000, SQL 2005 and SQL 2008 servers. The problem with using different versions of SQL Server is that the replication options do not always behave the same way. Because of these differences we have
Maintain Custom Indexes on Replication Subscriber Tables via Pre and Post Scripts
We are using replicated Subscriber databases for reporting and other purposes in our environment. As such, we have differing index needs based on the user activity on the publisher tables vs. the subscriber tables. Unfortunately, whenever we reinitialize
Enable and Disable SQL Server Agent Jobs for Maintenance Mode
Sometimes when we conduct SQL Server Maintenance we need to shutdown the SQL Server Service and the SQL Server Agent once the maintenance is complete. In these circumstances, I typically manage (shut down and start) the SQL Server service first then star
Customized Alerts for SQL Server Transactional Replication
My company is heavily dependant on SQL Server transactional replication and once in awhile the default alerts are not sufficient. In this tip I will show you a few scripts I have implemented to allow me to better manage our transactional replication.
SQL Server Security Audit Report
If your company needs to go through security audit this tip shows you how you can easily pull a report for all elevated security accounts within SQL Server
Troubleshoot Slow SQL Server Replication due to Distributor Database
My company has a very replication intensive environment (mostly transactional replication) and recently I started to see that replicating data was taking longer and longer. In this tip I will show you some of the techniques that I used to troubleshoot th
SQL Server Replication Configuration Scripts
My company uses SQL Server replication for moving data between servers and we have several publications with many articles being replicated. Therefore, I get questions all of the time about what tables are being replicated, what databases are published, w
SQL Server Data Mining Add ins for Office 2007 to Predict Database Growth Part 2
Explore the SQL Server Data Mining Add In to easily predict database growth using Excel's Get External Data feature.
SQL Server Data Mining with Office 2007 to Predict Database Growth Part 1
As part of your DBA duties, you should be analyzing the size of your database so you can prepare for growth. This may be for new purchases of disk sub-systems, consolidation, pre-allocating enough space for your database to minimize fragmentation or mayb
Execute SQL Server query on multiple servers at the same time
Managing multiple SQL Servers has its challenges. One of the big challenges is having to collect data from each of the servers you manage and figure out which servers need attention. You can setup a process to connect to each server and collect the data
SQL Server Database Mail and SQL Agent Mail setup by using a script
I was in the middle of upgrading many servers to SQL Server 2005 and SQL Server 2008. As I was setting up Database Mail and the SQL Server Agent Alert System I wanted to create a script that I could use to do this instead of having to do this manually th