A community of more than 1,500,000 database professionals and growing

Disaster Recovery Week

Steve Jones from SQLServerCentral.com

This week we highlight the practice of being prepared for disasters at SQLServerCentral. More »

Preparing for the Unthinkable - a Disaster/Recovery Implementation

Marios Philippopoulos from SQLServerCentral.com

Functionality to help maintain a Disaster/Recovery (D/R) Environment synchronized with production is introduced and the associated challenges are discussed. More »

Unteachable Disaster Recovery Techniques

Additional Articles from SimpleTalk

There are some skills which are extensions of your instincts, and which you can only learn though years of experience. Matt Simmons has this brought home by the fact that he was recently minutes away from a data-loss disaster, and he doesn't quite know how he prevented it. More »

When I get feedback about cloud computing from DBAs, the main concern seems to be data security, or the perception of problems with security from a cloud provider. That's natural as sending your data to the cloud doesn't necessarily remove the responsibility for security from the purview of the DBA, yet you don't have complete control over the way the data is managed. Various providers are working on their security and passing certifications, though this doesn't necessarily make many DBAs feel comfortable with databases in the cloud.

However security may not be your number one concern. Outages and business continuity should be your primary concern. As noted in this article, cloud service outages have been more common than lost data. It's much more likely that the service will go down, and if it does, what do you do? Contacting a cloud services company, and receiving fast answers aren't likely when a cloud outage usually affects a large number of customers.

However business continuity is important. If your database, or application server goes down, what would you do? In most cases you would sit in your office waiting for the cloud provider to come back online. That's not much different from in-house failures where you may wait on the network or OS administrators to recover a host server. In a few of the disasters I've experienced, I only had slightly more control than I'd guess a cloud provider gives me.

In either case, having good backups and a strategy to bring your system online in the event of an extended outage is important. I suspect that will mean hybrid public/private cloud applications that can function from an alternative location will need to be an architectural requirement for many businesses.

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 - 12.1MB WMV
• Watch the iPod Video Podcast - 18.2MB MP4
• Listen to the MP3 Audio Podcast - 3.6MB 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:

What is the count returned by last two SELECT statements respectively?

CREATE TABLE TblA1
  (
     col1 INT IDENTITY,
     col2 VARCHAR(10)
  );
GO

CREATE TABLE TblA2
  (
     col1 INT IDENTITY,
     col2 VARCHAR(10)
  );
GO

INSERT INTO TblA1(col2)
VALUES      ('A'),('B'),('C'),('D'),('E'),('F');
GO

INSERT INTO TblA2(col2)
VALUES      ('A'),('B'),('C'),('D'),('E'),('F');
GO

SET IDENTITY_INSERT TblA1 ON;
GO
SET IDENTITY_INSERT TblA2 ON;
GO

INSERT INTO TblA1
            (col1,
             col2)
VALUES      (7,
             'G');
GO

INSERT INTO TblA2
            (col1,
             col2)
VALUES      (7,
             'G');
GO

SET IDENTITY_INSERT TblA1 OFF;
GO
SET IDENTITY_INSERT TblA2 OFF;
GO           

SELECT COUNT(*) from TblA1;
SELECT COUNT(*) from TblA2;

By Lokesh Vij

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

This question is worth 1 point in this category: T-SQL. 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.

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Suppose there is a procedure defined as:

create proc rename_test
 as
begin
 select *
  from mytable
end

Now change the procedure name using sp_rename:

exec sp_rename rename_test,rename_stored_proc

Now run the following statements.

1. sp_helptext rename_stored_proc
2. select OBJECT_DEFINITION(OBJECT_ID('rename_stored_proc'))

Now create a new procedure as

create proc rename_test2
 as
begin
 select *
  from mytable
end

Now right click on the procedure and select 'modify', get the alter proc script and change the procedure name to 'rename_by_alter'. Execute the script.

Now run the following statements

3. sp_helptext rename_by_alter

4. select OBJECT_DEFINITION(OBJECT_ID('rename_by_alter'))

The question is what procedure name the statements 1,2,3 and 4 show respectively when executed

By swaran kalair

Answer: rename_test,rename_test, rename_by_alter, rename_by_alter

Explanation: Renaming a stored procedure, function, trigger or view does not change the corresponding object name in the definition column of sys.sqlmodules catalog view.

Ref: http://msdn.microsoft.com/en-us/library/ms188351.aspx

» Discuss this question and answer on the forums

Performance Tuning with SQL Server Dynamic Management Views

This is the book that will de-mystify the process of using Dynamic Management Views to collect the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own requirements. Grab your copy today from Amazon!

Find SQL Statements Consuming tempdb Space

Rahul Singh from SQLServerCentral.com

You can use the following query to find the currently active requests, their associated T-SQL statement, and the corresponding query plan that is allocating most space resources in tempdb. More »

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

SQL Server 2005 : Administering

How to notify when SQL Server Agent stopped automatically? - Hello all, I have many jobs running and for some reason sql server agents stops automatically not sure why..Can someone tell...

URGENT: CPU 100% SQLSERVR.EXE but no high CPU process - This morning on our production SQL 2005 9.0.3353 box it is running at 100% CPU. I have looked at sysprocessses but...

Database list from multiple SQL servers and instances??? - Hi I'm currently looking for a script to get a list of all databases from our SQL 2005 and above...

Find out why a maint plan failed - Hi all, a maintence plan failed last night and im trying to find out why. now apart from the fact that...

SQL server cluster resource deleted - Good afternoon I've given a problem to resolve and at present I can't see a resoulution other than a re-install...

SQL Server 2005 : Backups

Log backup Fails - I have full backup scheduled daily at evening 5 PM. Differential occurs daily after every 3 hours throughout the day. Log backups...

Create a policy to avoid backups withou copy_only option - Hi. We have several databases in full recovery model. Daily we make full backups, but sometimes we need take a...

Netapp SnapManager for SQL - Hello, Is anyone using Netapp's Snapmanager for SQL to do backups/restores? Have you had any issues with it? All comments are...

SQL Server 2005 : Business Intelligence

SSIS to get external data, Security risk? - Hi All, I want to use a SSIS package to grab data from an external suppliers mySQL database. I have read...

SQL Server 2005 : Data Corruption

Stand by/read only database on SUSPECT - Hi all, I had the issue twice back to June, and Oct this year. Our environment uses log shipping between two...

SQL Server 2005 : SQL Server 2005 General Discussion

Need help on creating SQL statement for deriving Consecutive worked days. - Hi all, Anyone can help me in deriving the sql statement to achieve the new column CONSECUTIVE_D? Consecutive days should be solely...

Failed to notify ''operator name'' via email? - Following command sends email fine: EXECUTE msdb.dbo.sp_notify_operator @name=N'operator name',@body=N'test message' However, SQL Job with a notification to above operator doesn't work. Job history...

SQL Server 2005 : SQL Server 2005 Strategies

How to notify through DB MAIL when sql server agent stopped automatically? - Hello all, Can someone tell me how can we send email when my sql sevrer agent stops automatically, i have my...

SQL Server 2005 : SQL Server 2005 Integration Services

Demanding and difficult logic qns. Sorry but i need help. - Hi pros out there, I am introduced to SSIS to do ETL for repporting in SSRS very recently. I am given...

Confused about FlatFile Connection Manager Error - I'm getting this error on my package "A valid file name must be selected". Here is why this confusing to...

SQL Server 7,2000 : SQL Server Newbies

Microsoft Certified Solutions Associate (MCSA) - Hi, I want to do MCSA exam. Do any dump available for this? Please let me know. Thanks, Anandh

SQL Server 7,2000 : Performance Tuning

How to Improve the perforamce of large tables ? - Hi All, Please give your valuable suggestions on how can i improve the performance of tables which having 2 crores of...

SQL Server 7,2000 : T-SQL

Select GROUP BY QUERY - I have a table like: CREATE TABLE [dbo].[tbl_FaceBookScore]( [ROWGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [Quiz_No] [bigint] NULL, [Mobile_No] [varchar](20) NULL, [Score] [nchar](10) NULL, [TotalTime] [bigint] NULL, [TotalQuestion] [int]...

Combine overlapping datetime to return single overlapping range record - For e.g. -- I have these records in a table. Eg. Table: Overlapping Days Has following data – ID StartDate EndDate ----------------------------------------------------------------- 1 01-01-2009 10:00:00...

SQL Server 2008 : SQL Server 2008 - General

Column restriction - I came acroos a problem shared by a developer in my firm, He cretaes a SP & generates a report, now while...

How to find Transaction - Hi All, I need to know,How many transaction happening for a table in sql server 2008 R2 for a period of...

Report to have servername as parameter display data - Hi all, Iam designing report to display the index stat of the server we choose from the dropdown list box. I...

Calculate the position for a combination of columns - Hi guys, I have the following situation: A table with receipts key and item key. One receipt has one or more items....

null value - is there many type off null value? i have table that have null value on 1 of my field. ex: id | Name | Info ---------------- 01...

Advanced backup solution - how do we make sure we dont delete the wrond backup file? - Basic : We take a full backup (BAK) once a week, a differential backup every days (not the day we make...

Query optimization - Hello, I have two tables #InnerTable which have around 7000 rows DCl.CompanyToken which have around 20000000 rows when i am firing below query,...

log file truncate only not working - i m trying to release the log from one of the database, using backup log with truncate_only, but it is...

port - how to check and open the port 1433 for sql in windows 2008 .

How to insert text in another text in Sql Server 2012 - Hi, I want to Insert Some Text in Another Text in Sql Server 2008. How to do this. For Eg, I Have a...

loop in trigger - hello all. I have two tables.when insert into one ot them with trigger insert into another and with another trigger insert...

Help to optimize SP - ALTER PROCEDURE [dbo].[PMLline_sp] ( @Requestno varchar(10), @GroupName varchar(50), -- BO governor role @UserId varchar(10), @DteTime datetime, @BOName varchar(10), -- current bo group DTRGRPOPT @Remarks varchar(2000), @MDTType varchar(10), -- type of request @StartDate datetime, -- previous history date @Assignby varchar(10), -- current user @RequestStatus varchar

DB with SIMPLE Recovery model not releasing log space - Hi, I have a database in SIMPLE recovery model and the log file size is approx 30GB (99% free). I...

SQL Server Index and Statistics Maintenance - Just showing an example in our environment. BCC SHOWCONTIG scanning 'Table_Name' table... Table: 'Table_Name' (274100017); index ID: 1, database ID: 13 TABLE level...

SQL 2008 R2 tempdb growth/MAXDOP - We have a certain query that now seems to result in tempdb growing until the disk runs out of space....

Minidump getting created on every minute 2008 Standard Edition - Hello Friends, I am getting below issue. Minidump is getting cretaed in every minute on my SQL 2008 instance (Standard Edition...

delete top(8000) takes 1 second, delete top(9000) takes 42 seconds !! - Hi, Delete top(8000) takes 1 second, but delete top(9000) takes 42 seconds, any ideas why? In management studio, if I do the...

Sql Query to display records in all tables - Hi Team, I need a query to display all the records from all tables in a database or export records tables wise...

Find Failed Jobs - Hi all, Does anyone have any scripts to find a list of all the failed jobs in the last 10 hours. I...

Optimization advice for huge tables - Hello, I need tuning advices for large tables query performance. I have 3 big tables and I need to join these 3...

Divide by zero from implicate conversion - I have a select statement where I am doing some basic math with a datediff function. I have looked at...

Problem in Configuring Mirroring - Dear, I am trying to configure mirroring. Principal server is [b]Serv1[/b] and Mirror server is [b]Serv2[/b]. Both servers are running SQL...

SSIS packege run good on BIDS, but when set up to run in SQL Agent job, always fail duw to access - Hello: Here is my problem, Create a new project in BIDS, new package, go into Control Flow, Create an execute SQL...

Alternative to xp_cmdshell - i need to delete some backup files as per daily operation through SQL ,currently i am using xp_cmdshell ,is there...

SSIS Sharepoint List Source Timeout Exception - Hello All: I have an SSIS package with SharePoint List adapter as data source connection. I'm experiencing the timeout issue very...

SQL Server 2008 : T-SQL (SS2K8)

FULL text search - Do we get any benefit if we choose to use full text search over like operator. see the below queries...

Help Needed please - Hi. I have a table with figures below. 003223 003225 003227 003227A 003236 003236A 003241 003273 003273A I need to do a search to find all items where the id...

Simular to PIVOT Table, but not - Hello Everyone I am having a real time with this one. [code="sql"] --===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#LabValues','U')...

Query for a plus sign - Hello Everyone I am attempting to query a column that the data has a plus sign. I know there is data,...

ORDER BY Should be same as my input in IN() - DECLARE @T TABLE(ID INT,NAME VARCHAR(10)) INSERT INTO @T VALUES(1,'PPP') INSERT INTO @T VALUES(2,'AAA') INSERT INTO @T VALUES(2,'ZZZ') INSERT INTO @T VALUES(3,'XYZ') INSERT INTO @T...

How To Generate XML File....From the Table... - Hi, I Successfully Imported the Following XML File ... Again I Want to Export (Generate) the Same XML File Format... Using The Output...

How To Export The XML File From The T-SQL Table.... - Hi, I Successfully Imported the Following XML File ... ------------------------------------------------------------------------------------- [code="sql"] DECLARE @idoc int DECLARE @doc xml DECLARE @xmldata Table (FirstName varchar(110), LastName varchar(210), Email ...

Delete records in group - but not all - Hello all, I think that my question is quit simple, but hope someone can help me. I have a table that looks...

Dynamic SQL - Hi, has anyone got an example of a Dynamic SQL and what is it and why it would benefit using this? Thanks

merge statement - to get a row count - I use a merge statement to do incremental load to the table. I use it in a stored procedure that is...

Update Query Fails Table Gets Locked By Select Query - Hi, My update query fails as table gets locked by select query. When i am selecting data it comes around 500 something. But...

SQL Server 2008 : SQL Server Newbies

Need help on an if statement - Hello all. Im still learning in t-sql trying to get my certification thats why I am not to familiar wich functions...

Migrating databases to new server, need advice - If you haven't read my posts before, I'll introduce myself: I'm a botanist by training but I got roped into...

Precendence Constraint Logical AND/OR Confusion - Hi All, I am wet behind the ears with all things SQL and I'm currently just beginning to study for my...

Reduce database size - Have been asked by a customer to reduce the database size to latest 10% of its data. Not sure how...

SQL Server 2008 : SQL Server 2008 High Availability

SQL MIrroring - I recently configured on database in mirroring with automatic failover.I want to push 10 GB of data into primay server...

Vertical Partitioning with transactional replication - I have a publisher and a subscriber database under transactional replication. The schemas are identical. One of the tables has...

High availability best practice? - I have a vendor supplied database driven application, I need to place a copy in another city for DR purposes,...

Mirror Failover - Hello my Gurus friends, I'm facing a problem for which I need your expertise and advise. I have 2 servers that are...

Dear All this post is about logshipping DC- DR Drill [switch over and switch back (or)failover and failback ] - Iam a starter in sql server dba handling 3.5 to 4 yrs wrk i too googled and taken feedback from so...

SQL Server 2008 : SQL Server 2008 Administration

Plan Cache Memory Allocations - Hi all I've done my research, I've done my testing. I just want to confirm something Given that any plan in cache with...

SQL versions and editions are supported on Windows server 2012 platform. - Can some one provide the list for what SQL versions and editions are supported on Windows server 2012 platform.

Slow restore performance - Hello All, Any thoughts and comments appreciated. I am restoring a 2005 database to 2008 R2. I think the performance of the...

Failed to map 8388608 bytes of contiguous memory - My customer is running a small e-commerce site on a SQL Server 2008 R2 SP2 Workgroup Edition x64. The SQL...

DB Mail issues with DLL - Dear All, I have been facing the problem when I am sending mail , in example in Test mail I am getting...

Grant (permission) on database:: ... where can I see these? - If run the following statement: [code] use someDB; grant select on database::someDB to someuser; [/code] Is there anywhere I can see this permission using the...

Slow Full backup in SQL SERVER 2008 R2 - Hi every one, We have database(4.6TB) in SQL SERVER 2008 R2.On weekly bases we take full backup from this database.It used...

network monitor - Hi, Is there a third party tool (Free or paid) which allows me to monitor the activity of the network at...

SQL Server index fragmentation is high after rebuilt - I have few indexes in my db, which report high fragmentation even after we rebuild indexes. The page count on...

Programming : Connecting

SQL Server Managament Studio, Windows Firewall and ports - Hello, We have a network with different subnets at work. PCs are on the client subnets and some servers are on...

Programming : General

Sending Emails From SQL Server - Okay, so i have a query that i run several times a day. After running the query i then analyse...

SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

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...

Reporting Services : Reporting Services

How to create date range parameter in RS2005? - Hi folks, I have created to parameter. @StartDate and @EndDate withe this statement: Select * From table where ENTRY_D BETWEEN @StartDate and @EndDate. Firstly...

no calendar icon on RS 2008 date parameters - Hi, I'm having a problem on one of our clients PC. Seems like the calendar icon on the date parameter is [b]not...

Barcode in RDLC Programmatically - How to generate RDLC Barcodes Programmatically........

last day of the month parameter in ssrs - I occasionally need to schedule reports that run for an entire month and automatically email them. A monthly report, of...

Database Design : Hardware

Lots of Memory or Fusion I/O - My first post so hello to everyone for the first time :) I work for a company who make a large...

Data Warehousing : Integration Services

Can I use the container name in an expression for a control flow task - Hi guys, I have an SSIS package that currently bulk inserts 21 files using SQL Tasks that all call a stored...

Read and Write the same variable in a DFT script component - Hi guys, It would be nice if you could help me with the following issue: I need to read a DFT level...

error with send mail task - Hi, Pls guide me on below error. [Send Mail Task] Error: An error occurred with the following error message: "Error in processing....

Enumerator cannot enumerate multiple Excel files/worksheets - G'day, We received a number of Excel files from a customer containing sales data I need to load into a database....

Data Warehousing : Analysis Services

Help: creating named calculation for sorting - please help me i need to create an Employee attribute (named calculation) that displays employees’ first and last names, but sorts...

Loading change data from AS400 (or other source systems) - Greetings all! So I will keep this concise as I can; I have a source as400 system I am extracting data...