SQL Server General DBA

Report SQL Server Percentage Backup Completion and Time Completion

When executing a backup from a t-sql command, it is difficult to know what percentage has completed and when the backup will be complete. When you are taking a backup, you can specified STATS = 10 like following. BACKUP DATABASE [AdventureWorks2012] TO DISK = N’D:\MYBackup\AdventureWorks2012.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2012-Full Database Backup’, SKIP, NOREWIND, […]

Controlling Growth of a Distribution Database

I was recently asked to set up Transactional Replication comprising of 29 publishers as well as same number of subscribers. One interesting challenge was that the size of the distribution database suddenly increased from few MB’s to around 62 GB and as a result the disk on which the production database was hosted quickly filled […]

Physical Join Operators in SQL Server – Nested Loops

SQL Server implements three different physical operators to perform joins. In this article series we will examine  how each  operators works, its advantages and challenges. We will try to understand the logic behind the optimizer’s decisions on which operator to use for various joins using (semi) real life examples and how to avoid common pitfalls. […]

Copy Only Backups for Adhoc Backups

Introduction In most organizations backup plans are implemented using full differential and transactional log backups. The normal scenario would be take a full backup on Sunday (off peak hours), differential backup daily at mid-night and transactional log backups on hourly basis. What if there is a requirement to refresh on the test/development environment with production […]

Refresh the Staging Server with Production Data – A Real World Scenario.

Recently I had to restore the Production Reporting Database Server onto our Staging environment for testing. This database was around 280 GB in size with around 120 GB of data in the Primary Data File (.mdf), 130 GB of data in the Secondary Data File (.ndf) and around 30 GB of Transactional Log File (.ldf). […]

Implementing Transactions in SQL Server – Part II

In Implementing Transactions Part I I briefly described the role of Transactions in SQL Server and outlined a very basic implementation. In this second part, I will explain how a DBA can best implement Transactions in scripts that are to be deployed on production databases. One of the regular tasks of a DBA is to […]

Troubleshoot Database Concurrency in SQL Server with sp_locks

General Database concurrency can be defined as the number of users that can work at a given database at the same time while not effecting or interfering with eachother’s work. The greater the number of users working at the same time the higher concurrency is. SQL Server holds Locks on user data to protect data […]

SQL Server 2014: Encryption for Backups

The next version of Microsoft’s main stream relational database management system (RDBMS), SQL Server 2014, has the ability to encrypt data in the database while a backup is created. The pleasant surprise is that this encryption feature for native database backups is available in Standard, Enterprise, and Business Intelligence editions of SQL Server 2014. Moreover, […]

Default Database Issues and Best Practices

What is Default Database? For every user, there is an option of setting a default database. So when that user connects to the SQL Server, they will be taken to the set default database. So in the above case, when dba_testuser logs in, they will be taken to the DBLOG database. Configuring Default Database When […]

Protecting Against SQL Injection

Despite being so well understood, SQL Injection remains one of the most common vulnerabilities in web applications. What is SQL Injection Any SQL which is dynamically created has the potential for having malicious SQL injected into it. For example, the below code receives a querystring and adds it to a SQL select string which will […]

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |