Never update systems tables directly - a study in Agent job scheduling

Nakul Vachhrajani from SQLServerCentral.com

It is often recommended that system tables should not be updated directly. Presenting a case in point built around nightly job configuration in order to demonstrate the possible issues with updating system tables directly. More »

TSQL Challenge 82 - Find elements that uniquely make up a group

Jacob Sebastian from SQLServerCentral.com

The challenge is to find the Tax Code based on Price. Each sales amount is uniquely made up by the sum of one or more prices. Based on this you should assign the tax code to each price.  More »

Distributed Computing - Hybrid Systems Considerations

Additional Articles from SimpleTalk

When the Cloud was new, it was often presented as an 'all or nothing' solution. Nowadays, the canny Systems Architect will exploit the best advantages of 'cloud' distributed computing in the right place, and use in-house services where most appropriate. So what are the issues that govern these architectural decisions? More »

SQL in the City - New York 2012

Press Release from SQLServerCentral.com

Come join Grant Fritchey, Steve Jones and others for a free day of training in New York City on Sept 28, 2012. More »

From the SQLServerCentral Blogs - SQL Server : Usage of OVER Clause

nelsonaloor from SQLServerCentral.com

Over  clause can be used in association with aggregate function and ranking function. The over clause determine the partitioning and... More »

I've always followed a few maxims over the years that have guided me well. One of those is that we must all agree on how we are going to perform any process and then all work that way. We can define whatever standards, rules, frameworks, processes, etc. that the group wants, but once we decide, we all need to work along in the manner we've prescribed. That's worked well for me, and when everyone follow the rules we've agreed on, things run smoother.

I ran across this post on three rules for database work from Scott Allen, a developer that has a lot of experience writing software. The three rules are never use a shared database server for development, always have an authoritative source for your schema, and always version your database. The last two rules are things I've always followed in the past since I think these are extremely important for maintaining control over the environment. Not control as a way to ensure developers are hamstrung, but control in that at any point in time you are aware of the state of the database.

The first rule, never using a shared database, isn't something I've usually done. Most of the time I've worked with small enough teams that people are working on separate sections of the database, and they almost never run into any conflicts with each other. A shared database server means changes from one person appear almost immediately to others is valuable. It allows development that is dependent on other changes to take place right away. That was in the past. These days with a tool like SQL Source Control (or custom scripting), you could simulate this on separate databases for separate developers.

As with most rules in the database world, I think the answer to whether you follow these particular rules in your environment depends. It depends on what your resources are, your needs, your team structure and more. You can choose the framework that works best, whatever it is. You just need to make sure that everyone works within whatever you decide.

Steve Jones from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

• Watch the Windows Media Podcast - 22.5MB WMV
• Watch the iPod Video Podcast - 19.9MB MP4
• Listen to the MP3 Audio Podcast - 4.0MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

» To submit an article, rant or editorial, log in to the Contribution Center

Today's Question:

We are executing below commands to take the backup of two databases:

BACKUP DATABASE TestDatabase_1 
  TO DISK = 'C:\TestDatabaseBackup.BAK'
  WITH COMPRESSION
GO
BACKUP DATABASE TestDatabase_2 
  TO DISK = 'C:\TestDatabaseBackup.BAK'
  WITH NO_COMPRESSION, INIT

What will be the result?

By Divine Flame

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: Backups. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today

Let’s suppose that you have disconnected all database connections from your application so that the application cannot access the database. While taking that database offline, you noticed that it’s taking a long time. It could be because some external resources are accessing that database. How you can take the database offline?

By Naseer Ahmad

Answer: Alter database [database name] set offline with rollback immediate

Explanation: When you will face this issue, first of all check the if anybody is using the database by runing sp_who2 command. If yes, then just use the "alter database [database name] set offline command with rollback immediate" option. It will rollback all active transactions and take the database offline.

Ref: ALTER DATABASE - http://msdn.microsoft.com/en-us/library/ms174269.aspx

» Discuss this question and answer on the forums

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.

Show all Objects used by Linked Server

KILEY MILAKOVIC from SQLServerCentral.com

Stored Procedure will list all objects, object type, and linked server name. The stored procedure can either be passed in a linked server name as string or null. More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

SQL2005 client components (SSMS, MS Visual Studio 2005,...) not compatible with windows 64bit??!! - Can anyone please confirm that installation of SQL2005 Client Components is not possible on windows 64bit? I tried to install SQL2005...

is it required to run reindex on a table soon after a column is dropped or added?if yes then why? - Hi, Should reindexind be done on a table after a new column is added to the table..does it depend on if...

Deadlocks on IIS Server - We have an IIS server and a SQL 2005 server on 2 separate boxes. Sometime we got deadlocks recorded in...

Unable to shrink Datafile - I had a table which was 100 gb and whole database size was 150 GB. I have archived that table to...

SQL Server 2005 : Business Intelligence

Looking for good books/resources for SQL 2k8 data warehouse - All, ** I think this is the appropriate forum but please let me know if not and I'll move it ** My...

SQL Server 2005 : SQL Server 2005 General Discussion

out of memory - hello, we are on: Microsoft SQL Server 2005 - Ent Ed 32-bit SP4 mutltiple dbs running used for web applications On Jun-9: - SP4...

SQL Server 2005 : SQL Server 2005 Integration Services

error - hi, i am getting this error: Error: 0xC02020A1 at Data Flow Task 1, Flat File Source [1]: Data conversion failed. The data...

SSIS error from flat file to table - hi, i am inserting data from flat file to sql table usig SSIS. my flat file is empid,empname,salary 1,david,1000 2,krik,3456 3,van,4532 4,venus,345 my table in sql is create...

SSIS 2012 package hanging without any message - I am using SSIS 2012, to import an oracle table to Oracle, i am working with an 8,00,000+ lacs rows...

SQL Server 2008 : SQL Server 2008 - General

DELETE statement won't complete - This is an odd one. A DELETE statement with a single filter, deletes around 1.5 million records from a table...

Implicit Transaction On - Snapshot Isolation - Ever Increasing Version Store - We have a 3rd party application: - where the target database has "snapshot isolation on" - where some connections from the application...

Help me make my mind up- sql or access - I have a client to whom I am writing a database program for in vb.net 2010 express. They currently have...

select query - I am using SQL 2008 and I need help to write a select query for the following output I need list...

Log information in to a Table! - Hi, I have created a SP which is used for Backup/Restore. Now the problem is I need to create a table...

new to DBA an dtechnology - Hi all, I am new to technology world, been in customer support, planing to move to technology, been thinking of doing...

Audit using Profiler ... - Hello, I'm a new member and it's my first post When I use a Profiler to monitor Log in/out (Audit Login + Audit...

NOLOCK/Isoloatin Level for READONLY database - Hello, We have a sole readonly database which is updated monthly at night during maintenance window. The db has several stored...

Replication Issue. - When I try to run sanpshot job is also failing. When I replication monitor, I see below error. Help needed. Error messages: The...

sqlserver database administration mirroring - how to change portnumbers in mirroring when business running?

Can we Prioritize which index to be used first in a Select query ? - Can we Prioritize which index to be used first in a Select query? if so how can this be done.

Query plan - largest cost - This query takes about 45 seconds to complete (down from 10+ minutes by adding clustered index to table). Looking at...

Insert record in DATABASE SNAPSHOT seems to block server - In my server I have a few 10's of databases and on some of them I created a snapshot to...

Searching the time data type - I have two fields of type time. StartTime and EndTime. I want to see if Now is between these two...

SQL Server 2008 : T-SQL (SS2K8)

Adjust how Data is Returned in Table - Hi, Messing around withn SQL queries on a Sunday, so I have another question (posted my fist one this morning). This...

create database - How do you create a database from a data and log file?

Combining a Query to Include an Additional Column - Hi, I am trying to work with data from the ProjectServer_Reporting database, which is a part of the Microsoft Project Server...

how to find duplicate indexes in all the tables in a database - how to find duplicate indexes in a table ,example a nonclustered index names FX_LOCATION_ID is created on columns a,b and...

UPDATE when the values are the same - Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating...

SQL Server 2008 : SQL Server Newbies

Lastname, Firstname switch - I dont know if this is any help but this is the code I started with: SELECT HOST9006.DESCRIPTION, HOST0110.ROOMNAME, HOST0140.NAME, dateadd(mi,...

Multiple values in some columns single value in another - Hi, I'm a first time poster long time reader of SSC (so perhaps I should already know this!). But... I'm having trouble...

Script to check database blocking - send auto email - Below is my script to check database blocking then will send automated email. I am getting this error: Msg 156,...

Multi database to SQL Server at record level - Hi All I have created a touchscreen data collection system that stores its information on local Access 2007 databases. The reason...

Landing The First SQL Job.... - Hello All! I currently am a plumber by trade, been in that industry courtesy of my father in law since graduating...

SQL Server 2008 : SQL Server 2008 High Availability

Backups & Restore.. - I know how to take Full backuo & differential backup.. But the problem here is how do i go and automate the...

Programming : XML

The argument 1 of XML datatype method nodes must be string literal.plz help - declare @starttag varchar(100) declare @root varchar(100) set @starttag ='XML' set @root ='XML' Declare @xml xml Set @xml = '<XML> <Provider> <providerID>1</providerID> <Address>address1</Address> </Provider> <Provider> <providerID>2</providerID>...

SQLServerCentral.com : Anything that is NOT about SQL!

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Data Warehousing : Integration Services

Integration Issue - Goal: Filtrate the different integration issues. My selection is: Description - Customer Name - IBM Encoding - Sex - M = Male and F= Female Units - Cable Length Inches Key...

Get Value of Variable in VB.NET Script Task - I have a SQL Server Script Task. This task queries a table and checks for the existence of a record from...