SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Monitoring for Non Existent Events

I was catching up on work recently, reading the third installment of The 5 Worst Days in a DBA's life, starring The DBA Team. Someone had asked me if I enjoyed having Paul Randal (b | t) of SQLskills join them team. The piece had been edited and published while I was gone, and I hadn't had a chance to immerse myself in the adventure. I was anxious too read how Paul helped save the day. 

It was a fun read, but one quote in the piece struck me. "A job that runs long or doesn't run at all can sting just as bad as one that fails." That's a quote from my character showcasing a situation that few people actually think about. However jobs that don't run or don't finish are situations that DBAs should be monitoring for.

So many of us adopt a set-it-and-forget-it mentality with our jobs. We assume that things will work, or fail, as we set them up. However it's easy to forget that there are other states we might find ourselves or our systems in that can cause issues.

Monitoring is critical to any well run system, but monitoring needs to be set up well. If we require that certain jobs run, we need to not only check for success or failure, but if the job has actually run and completed. It's easy to accidentally disable the wrong job and not notice. It's also entirely possible that a job gets stuck and doesn't complete.

If you're not watching for those other states, you might find yourself in a situation where you don't have backups and your job is on the line. However you probably won't have The DBA Team to call on.

Steve Jones from SQLServerCentral.com

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


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

The Voice of the DBA 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.

Everyday Jones

Follow Steve Jones on Twitter to find links and database related items and announcements.

ADVERTISEMENT
WHEN DISASTER STRIKES
man 1 Nooooo
noonono
I don't believe it.


- What's up with Frank?
Lisa and Ron talking
- Pygmy goats trampled his data center.
- Well that's not funny at all *snicker*.
Frank, very upset WHY *hic*. Someone tell me why!
- Wait. Wasn't your data there too? lisa and ron talking
- Yeah, but I have a disaster recovery plan. His was in the head of a lone DBA he fired last month.
Frank, prostrate with grief I'll never get back what I've lost!
- Rough. What was in there? Customer data? lisa and ron talking
- Nope. All his fantasy football stats from the last 8 years.
Don't let it happen to your data.

Download SQL Server Backup and Restore free and learn how to really protect your data.

Featured Contents

 

Spackle: Making sure you can connect to the DAC

Kenneth Fisher from SQLServerCentral.com

The DAC is an important tool and several things can go wrong when trying to connect to it. More »


 

A TDD Journey: 1-Trials and Tribulations

Additional Articles from SimpleTalk

Test-Driven Development (TDD) has a misleading name, because the objective is to design and specify that the system you are developing behaves in the ways that the customer expects, and to prove that it does so for the lifetime of the system. Michael Sorens starts an introduction to TDD that is more of a journey in six parts. More »


 

From the SQLServerCentral Blogs - How To Get The Boss To Pay For Training

Andy Warren from SQLServerCentral Blogs

How do you get the boss to pay for training? In my experience you have to: Ask. The boss isn’t sitting... More »


 

From the SQLServerCentral Blogs - Power View report migration from SharePoint 2010 to 2013

Rayis Imayev from SQLServerCentral Blogs

With the latest changes to the Microsoft Power BI and ability to create Power View reports within an Excel file... More »

Question of the Day

Today's Question (by Shiva N (AxSys)):

Which of these queries will return a list of table names that have constraints?

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


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

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

ADVERTISEMENT

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.

Yesterday's Question of the Day

Yesterday's Question (by Sreepathi):

DECLARE @i INT = 1
WHILE @i < 5
BEGIN
    DECLARE @j AS TABLE
    (
        i INT
    )
    SET @i = @i + 1
    INSERT INTO @j
        SELECT @i   
END

SELECT * FROM  @j

If I run the above query, what will be the result set using SQL Server 2012?

Answer: This will return 4 rows with the values (2,3,4,5)

Explanation:

SQL Server variable scope is per batch or the entire function/procedure/trigger, not per black/nested construct

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


» Discuss this question and answer on the forums

Featured Script

Report overlapping jobs

Wilfred van Dijk from SQLServerCentral.com

This script reports jobs which are running when another job is also running. This could be a reason for performance degradations.

Copy and past this script and run it as a query. Study the outcome to make decisions for shifting the timeframe for a job.

More »

Database Pros Who Need Your Help

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

SQL Server 2014 : Administration - SQL Server 2014

Help Re-Generating MS Agent Jobs from dead Server - This morning one of our DB servers died. They restored backups of the DB's to another server but these didn't include...

Licensing Nightmare - Hi all, I’m having nightmare with SQL Server licensing and I hope someone can help. We have an Enterprise Agreement with Microsoft...

Disable Unicode output - I am running SQL Server 2012. I have been producing files as output for the Unix folks at work. We...

Can SSIS 2012 run against SQL 2014 database? - We are considering installing SQL 2014 to take advantage of columnstore updateable indexes for an upcoming ETL process but am...

Restore database without backup history in msdb - What is the best way to restore a database from a folder of backups (including full, diff and log backups)...

Downgrading from SQL Server 2014 to SQL Server 2012 - Hi, I need to know how I can downgrade to a SQL Server 2014 installation to SQL Server 2012. Thanks

Error: The OLE DB provider "SQLNCLI11" for linked server "HELIXDB" supplied inconsistent metadata for a column. The name was changed at execution time. - Hi While trying to run the sql job folowing error is displaying sometimes, but the job is successfully executing some times. JOB: Step...


SQL Server 2014 : Development - SQL Server 2014

Run same script for multiple clients - Hi, kind of new to sql so help would be much appreciated. I have a script that needs to be...

Bulk Insert - Hello, I need to load the following data into a SQL table. This is how the vendor is able to...

HI...Sample INSERT statement script needed to insert 500 million rows into in-memory table sql server 2014 - Hi, I am doing a performance testing for In-memory option is sql server 2014. As a part I want to...

need repetition of a table when joined with another table - table1 id value 1 11 2 12 3 13 4 14 table2 id1 value1 1 21 2 22 1 31 2 32 in need output as follows id value id1 value1 1 11 1...


SQL Server 2012 : SQL 2012 - General

SQL query - Hello, I need advice on how to do SQL queries that I returned the following: I have 2 tables: customer and the...

Today's Humor.. - A vendor's help desk provided us with this stellar support. “You can let your DBA know that our databases must...

Memory Dump - Does anyone have a query that will tell me what query was running when I experienced a memory dump?

AlwaysOn AG failed over changed sql user passwords??? - I had a failover occur last night on my AlwaysOn AG, the SQL accounts had to have the passwords re-entered...

SQL Server Clustered availability group - Trying to get my head around a scenario I have 2, 2 node clusters PROD1(cluster 1) Clustered SQL instance1 PROD2(cluster 1) DR 1 (cluster...

VIEW SERVER STATE permssion. - What will VIEW SERVER STATE permission do? will it have the permission to restart the sql server?

Use SQL Server Agent to run a SSIS Project - New with SSIS 2012 is the ability to define a project consisting of many packages. I've created one like that...

SQL Trace file sizes - Hi there I am in the middle of capturing a workload to try and tune a SQL instance and was wondering...

Tables on seperate filegroups. - Hi All We have a large Datawarehouse and the size is 50TB.. The tables are placed in filegroups based on the...

Maintenance Jobs Calendar - There has been contention on infrastructure level and quick checks found that maintenance plans across multiple instances are running at...

Re-establish one database in an Availability group - I've got an availability group with multiple databases, replicating to multiple secondary servers. On one of the secondary servers, some...

2012 Mirroring Alert - Sorry, but i am being lazy and not doing much research myself I am getting the below SCOM alerts regarding...

Replication - I've a database that comprises of about 50 tables. Some have PKs and some don't. Source is 2005 and Target...

Selecting a Clustered Index - HI Guy The question is a bit allover - What are your thoughts on adding clustered index on datetime (createdDate , native GUID)...

Restore Master Database for a new dba - Sometime during the night last night some user account permissions were "lost". Am I right to think that restoring the...

RAISE ERROR to THROW conversion - Hi, I have an old proc like below which I want to update to use THROW. Looks like THROW can save...

How useful are saved configuration .ini files for automating installs? - I nabbed a couple from some recent installs I did, and, assuming I can get my SAN guy to always...


SQL Server 2012 : SQL Server 2012 - T-SQL

sp_send_dbmail question - I have this: EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA_Mail', @recipients = 'paresh.motiwala@gmail.com', @body = 'The count of the unprocessed records.', @subject ='Number of unprocessed records...

Exporting Data from MDS 2012 entity to SQL 2012 database table - Hi All, How to export data from MDS 2012 entity to SQL 2012 user database table ? Inputs are welcome! Thanks Karthik M

Convert Bigint time to datetime - Hi, How to convert bigint time to datetime (CST) Declare @MyBigIntTime BIGINT =1397750400000 Thanks, PSB


SQL Server 2008 : SQL Server 2008 - General

Navision Performance and sp_executesql - Hi all, Hopefully you can help me out here. We are running Navision 2009r2 with a sql 2008r2 (RTM) backend. Every...

TinyInt to Unique Identifer - Hello friends, I have a select statement with a WHERE clause filter. The column in the WHERE statement is a uniqueidentifier. The...

Instance: table column with default value - Hello, During the installation of SQL Server or even after installed, it is possible to define some types of fields with...

Distributed Transaction Help - Please assist with any ideas beyond the fixes I have already tried, listed below: [u]The Error[/u] [code="plain"][OLE/DB provider returned message: New transaction...

DATEADD with aliases? - Hello, I want to subtract 3 months from a date in an alias field but I cannot get it to work....

Name table - Hello, My search skills are poor at best, so I was not able to get very far. But I have...

How to create a job targeting multiple servers? - I need to create a job that would run like this: Step1 -- server A Step2 -- server B Step3 -- server A Step4 -- server B In Job...

Running a stored proc remotely - Hey all! I've been trying to look for a solution to this but haven't really been successful. Basically, I have a stored...

Linked server with MS access mdb database - Error 7416? - Hi MS ACCESS mdb database located to another server. SQL 2K8R2 - database another server. I have created & working fine linked at SQL 2K8R2...

Merging XML Variables Like a Table Join - Hi All, I have several stored procedures that will query tables and return xml data. I would like to join these...

OS cache in Sql Server - Does Sql server use OS cache (outside buffer cache) for its operations ?

How to optimize this query? - Hi All, I have a simple query as below Select A,B,C from Table1 How to create a good index on that...

Do recursive call without a ParentID column - I have the following tables [Order] ID [Schedule] OrderID Start End I have the following query that gets the schedule details for a specific OrderID. It also...

Multiple joins to the same table - Hi all I've got the following stored procedure (this is part of the from clause) and it looks horrendous. Here's the...

Unspecified error using ado command to update a varchar max field - Hello... I posted this a couple of days ago on the "anything that is not SQL" forum but had no...

Tuning a query that takes 60 minutes to run, - Hello, I have a SQL query that takes 60 minutes to complete, the query is selecting data from 4 tables, out...

Restore of db fails - We have a SQL2008R2 db backup given to us by a vendor that is 127Gig in size which is using...

DB transaction log file on a shared hosting service - Hi, Thanks to this forum. It's great. I have a SQL DB on a shared hosting service (NewTek/The SBA) with 400Mb disk...


SQL Server 2008 : T-SQL (SS2K8)

what do rows were affected mean - I have an address table and a log table that stores historical records. So the address table have an after update...

T SQL script improvements - Hi everyone Hope someone can offer some advice. Been stuck on this one for a while. Trying to use the TSQL below...

Report - Hi Team, For pre-paring report I need this format report on instance. Database Name Database User Name Database User Permission Request to...

One to many problem - I have three tables I want to match two of them based on the third. [code="sql"]CREATE TABLE #Check1 (IDX INT, C1FirstName...

INSERT/UPDATE getting blocked immediately? - Hi, For resolving blocking issues, I have mention SET TRANSACTION ISOLATION LEVEL SNAPSHOT in that SP. Pls. confirm & suggest , Is it...

T-SQL to add 7 days to ship date - Hello All, A quick question to all regarding my query. Below statement adds 7 days to the shipment date if we...

Cross Join Help - I have a massive table (company, plant, resource, part number) that I need to create a from part number, to...

Problem with Round - I have the following problem with ROUND. When doing the calculation for each value by a percentage using round, the...

Trigger is not working as expected - Hi , 1) I have two tables , chat and country tables , chat table has 3 columns(chat_id,language,chat_info) and media table has 2...


SQL Server 2008 : Working with Oracle

Zendesk Expansion - [url=http://www.youtube.com/watch?v=oLi1zoPuvkw]Crusaders vs Waratahs Live stream[/url] [url=http://www.youtube.com/watch?v=4_hl2pQeh9A]Super Rugby Final Live stream[/url] [url=http://www.youtube.com/watch?v=cgW3slgEJbk]Waratahs vs Crusaders Live stream[/url]


SQL Server 2008 : SQL Server Newbies

Trace user with sql server authentication - Hi All, How to trace user if database has SQL SERVER Authentication. Please help me on this.. Thanks in Advance..

Parsing a String in SSRS - What up Guys, First time posting a question here. I'm not entirely sure how to solve. I have the following string...


SQL Server 2008 : SQL Server 2008 High Availability

Mirroring + Log Shipping together - HI, I'm setting up a database with mirroring for HA and log shipping for DR. The mirroring works fine. But I...


SQL Server 2008 : SQL Server 2008 Administration

how to automate dbcc checkdb - Hey Folks, I'm new at this SQL Server stuff and all I'm trying to do is run a 'dbcc checkdb <some...

How to Reduce the Table Size... - Hi All, Am using 2008 Sql server. In my database one of the table containing 20gb of data.I need to check that...

Attach DBs from a restored file on a disk - I had SQL Server on a Server and the OS went bad and I had to reinstall the OS. In...

Indexs dont work - I have a weird issue.. There were a few tables i had replaced with new data. the table structure is same just...

Live P2V SQL Server - Hello, Has anyone ever done a live physical to virtual conversion? If so, were there any special considerations or planning steps...

Shrinking DB not working - I have a 3 TB database, in which recently I relocated and dropped a huge table, probably about 1.2 TB...


SQL Server 2005 : Administering

Will the application work as usual if we create alias with existing cluster name for a new sql server instance and break cluster ? - Hi Experts, One of the SQL Server 2005 cluster database is being moved to a new standalone VM instance. Application team has...

Restore Database Failed: 'Exclusive access could not be obtained because the database is in use.' - Hi, While trying to restore a database (with replace option), I am getting the below error - Exclusive access could not be...


SQL Server 2005 : Backups

Confirmation with Restoration - I have Restored the Database and we can see that it showing old created date. I have followed below procedure....


SQL Server 2005 : Business Intelligence

ssis Training - i am new to ssis .how to prepare ssis ? plz help me

SSRS Export whole underlying dataset to excel. - Basically my reports are formatted displays of underlying datasets. How do I make the full (preferably not just the fields actually...


SQL Server 2005 : SQL Server 2005 General Discussion

Delete statement not delting rows? - Has anyone come across a situation where a delete statement doesn't give an error, but also doesn't delete the rows...


SQL Server 2005 : SQL Server 2005 Performance Tuning

find % CPU from dm_os_performance_counters - Hi , Iam collecting a baseline report for one of the sql server instance, where i have only instance level access,...


SQL Server 2005 : SQL Server 2005 Integration Services

Removing certain text from a column - Hi i have been asked to do the following b. If Asset Type = ‘IRS’ or ‘IFS’ i. Remove any leading text ‘IRS: Flo/Fix: ‘ or ‘IRS:...

Importing Poorly Formatted Text File - I am trying to import a text file to a SQL server table using SISS. I use SSIS but this...


SQL Server 2005 : T-SQL (SS2K5)

How to find who installed the sql server? - Can some one please provide me the script to find who installed the sql server and if it is a...


Reporting Services : Reporting Services

Using a parameter value as a field name - Hi. Two multi-value parameters come in. For example : ColumnLabel & ColumnField Columnlabel denotes the column header label & ColumnField denotes the field that should...

Group total expression - [img]http://imgur.com/8mEL4fe.png[/img] I want to add a column after Total lets say..'Progress' where Progress = sum(Actual)/sum(Budget) The only filter on Progress is to consider...

A subreport as a parameter - Is it possible to define a subreport as a parameter? My situation is I have 6-7 subreports with identical parameters. I'd...

help with SSRS performance - Hello Our SSRS report times is getting worse. We process about 5000 report subscriptions on a daily basis. They start at...

Display negative seconds in HH:MM:SS format - I'm calculating the difference between two text boxes which record the time in seconds. When I get negative results EG...


Reporting Services : Reporting Services 2008 Development

Paging A Stacked Bar Chart - What our users want, sorted largest - smallest on the x axis for y axis value. I tried the put chart in...

Merge Vertically - How do I merge cells vertically, I am able to do it horizontally, but not vertically. I don't want to...

while printing records - WhilePrintingRecords; PageNumber > 1 The above one is the code in the crystal, which is used to suppress the details in the...


Reporting Services : SSRS 2012

SSRS 2012 have Issue with Preview - Hello Experts I have problem with preview reports on vs2012 using framework 4.5 and run the VS by RunAs command. I added...


Data Warehousing : Integration Services

Single file excel file upload to sql destination - Hi, I have a file which changes every week @C:\TEMP(data and filename both) schema remains the same all the time. I...

MySQL client ran out of memory issue in ssis package. - Hi All i have small doubt in ssis please tell me how to solve error. I have 80 databases from...

Execute Package Task failing even though dtsx package it calls had a failure that was corrected - I have an ssis package called 'master scheduler' which has an Execute Package Task that kicks off a child dtsx...

Common encountered errors in SSIS - Hi, I am currently writing my Bachelor Thesis about error handling in SSIS and generally increasing the robustness of ETL processes. One...

Extracting VarBinary(MAX) column to Flat File - Facing Issue. - I’m extracting data from the table to flat file using SSIS,during this process I’m facing the data conversion issue in...


Database Design : Design Ideas and Questions

Cocktail Recipe Database Schema - Tables Question - Normalization - I'm fairly new to database design. I have done a few in the past, however, I am trying to normalize...


SQLServerCentral.com : Anything that is NOT about SQL!

What does a masters degree in database administration give you - Is there any justification for pursuing a masters degree in database administration? what would be the positives and the negatives?

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


SQLServerCentral.com : Articles Requested

XML Export to individual files - Looking for an article that takes data from an XML column in a table and exports each field as a...


Career : Certification

I'm totally new. Need some guide on SQL 2012 - Hi folks! I'll put it briefly.. I want to work with DB, but I know nothing about it because I have...

How to break into data warehouse management - Hi All, I'm hoping I can get some valuable input from people in the field. My current and past jobs have...


Career : Employers and Employees

Do you usually go to agencies? - Hi all database professionals. Want to ask you some questions about agencies and dealing with them. When you are looking for...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com