Articles having tag: GENERAL DBA

Contained Databases in SQL Server 2012

Contained Databases have been an oft requested feature which has finally arrived in SQL Server 2012. In prior SQL Server versions, database were not 100% portable. In case you need to move a database from one server to another using backup and restore, the...

Practical Introduction to XML Manipulation Using XQuery in SQL Server

By Seth Delconte

XQuery was developed by W3C as a query language designed to programmatically interact with and manipulate Extensible Markup Language (XML).\xa0 XQuery uses XPath methods and expressions to search and retrieve XML data.\xa0 In this article, I’d like to...

New T-SQL Features in SQL Server 2012

By Dinesh Asanka

Paging Data Paging is a big issue for developers as it is required for many applications but entails many performance problems.\xa0 Developers have used different workarounds to support paging. For example, assume we need to display several pages of the HumanResource.Employee...

Finding Replication Publications For A Table

By Dinesh Asanka

If you want to drop or rename a table, you will need to make ensure the table is not an article of a publication. If it is article for any publication, you need to find out what are those publications. If you have several publications and if one table is an...

A Primer on Log Shrinking and Truncation in SQL Server

By Seth Delconte

In the event that a transaction log grows to an out-of-control size, shrinking it can be the only solution – but don’t rush to do this until you are aware of the consequences. Shrinking the log could ‘break the log chain’ – rendering...

Using Stored A Procedure with Temp Tables for SSIS Packages

By Dinesh Asanka

Using stored procedure with temp tables for SSIS packages. Some stored procedures are complex and so require temporary tables or table variables. Let us say we have the following procedure to extract data. CREATE PROC ProcWithTableVariable AS DECLARE @Table...

Verify Whether a SQL Server Agent Job is Running

By admin

When I posted an faq about Finding Running SQL Server Agent Jobs I had several few emails and queries on how verify whether a job is running by using only a TSQL script. The below script will verify if the job exists and whether or not it is running. DECLARE...

Create a Deadlock and Set the Deadlock Victim

By Dinesh Asanka

To create a deadlock for testing purposes you can execute the below TSQL script: -- 1) Create Objects for Deadlock Example USE TEMPDB CREATE TABLE dbo.foo (col1 INT) INSERT dbo.foo SELECT 1 CREATE TABLE dbo.bar (col1 INT) INSERT dbo.bar SELECT 1 -- 2) Run in...

Replication Without Creating a Snapshot

By Dinesh Asanka

In replication, the standard way of creating a publisher is to create a publication with a snapshot (which is what all the documentation recommends). However there are a few problems with this method: Creating the snapshots and then applying them is time consuming....

Transactional Replication – Unable to Replicate Data onto the Subscriber – A Practical Example

By satnam singh

Scenario: A couple of days back, I got a call from one of my users saying that the Data is not being replicated properly onto the Production Reporting Server from the Production OLTP server. We have Transactional Replication scheduled for every 15 minutes between...