In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Search How do you search your database schema?
"I use SQL Search regularly and think it's great." Gregor Suttie, Senior Software Engineer, Pulsion Technology. Download Red Gate SQL Search while it's free.
 
SQL Backup Hosted New! Safeguard your SQL backups
Protect your backups from onsite disaster with SQL Backup Pro and a Hosted Storage account from Red Gate. Learn more.
 
SQL Compare Compare and sync databases with SQL Compare
“SQL Compare is fast, extremely easy to use, full-featured and affordable. I wouldn't bother messing around with anything else.” Adam Machanic, SQL Server MVP. Download a 14-day free trial.

In This Issue

Automating SQL Traces

Capturing the activity on a server when a problem occurs can be a challenge for many DBAs. This article shows how you can automate the capture of information when a CPU spike occurs. More »


Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets

There are plenty of occasions when it makes a lot of sense to do backup and restore scripts in PowerShell. Microsoft have put effort into making it much easier, as Allen White demonstrates. More »


Transaction Log Stairway Series available as a book

Red Gate Books has released SQL Server Transaction Log Management, a free eBook on maintaining and troubleshooting the enigmatic transaction log based on the Stairway Series. More »


From the SQLServerCentral Blogs - Azure does Powershell too

Or, I guess it might be more appropriate to say that Powershell does Azure. Regardless, there are a set of... More »


From the SQLServerCentral Blogs - The many uses of CROSS APPLY

Over the last few years of studying SQL I’ve noticed 4 different uses for the command CROSS APPLY. In the first... More »


Editorial - Trusting Systems

Computer technology has become more and more integrated into all sorts of businesses. These days when I look at the ways in which automation and technology is embedded in business, I'm amazed to think that I worked in businesses that didn't have any personal computer systems early in my life. Unfortunately this integration comes at the a price: dependence. We depend more and more on our systems working as expected for businesses to continue to operate.

Most businesses can tolerate some issues and failures in their systems for a short period of time. As much as I want to maintain 5 9s of uptime, I've never been in a business that really required that level of stability. I've had systems go down that management claimed were critical. They've been down for a couple days, and the companies didn't go out of business. We have a lot more tolerance than management would like to admit. If you work with a 24x7 environment like Amazon's online store or a banking application that is generating revenue every second, that might not be true, but for most of us, in most organizations, for most systems, we can tolerate some downtime.

However the effects of some failures can't easily be measured. American Airlines had computer issues recently and had to delay or cancel 400 flights. I'm sure there were some costs in compensating customers that took other airlines, or received vouchers for services. However, most of the flights were moved to later in the day, and I doubt there were many refunds. The short term costs of this computer issue were probably relatively small. Given the fact that some customers might have skipped or moved flights, with a change fee, there might not have been any absolute short term loss.

However it's impossible to know how many other businesses were affected. Sales calls that were missed and not rescheduled, people that missed events that were important to them and they'll never be able to attend, or even penalties paid on contracts for the failure to deliver something in person. What's also impossible to know is how many people will reconsider booking flights with American in the future because they lost their trust in that company.

That's really the bottom line for most of our management and our businesses. If we can't provide reliable computer systems, management won't trust us, and their customers won't trust them. If that happens too often, we won't be employed, and at some point in the future, we might find ourselves unemployable if our reputation is such that we can't keep systems running.

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

Everyday Jones

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


Question of the Day

Today's Question:

What is returned from this query?

SELECT
   REPLICATE('12',1)
,  REPLICATE('12',0)
,  REPLICATE('12',-1)
,  REPLICATE('12',1.5)

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.

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.


Yesterday's Question of the Day

You manage a 2008 R2 instance with a PROD_DB. You want all login events to be stored in a separate database called AUDIT_DB containing just one table for that purpose. You create the AUDIT_DB, enable broker on it, create a QUEUE, create a SERVICE on that queue and ROUTE on that service. Then you create EVENT NOTIFICATION ON SERVER FOR AUDIT_LOGIN, AUDIT_LOGOUT, AUDIT_LOGIN_FAILED and define a stored procedure in your AUDIT_DB that receives any login events from the QUEUE and inserts the login data into the AUDIT_DB.

All works fine and you forget about it until a few months later, when you find out that someone must have dropped the AUDIT_DB, because it is no longer there. In addition, you notice that one of the remaining databases grew unusually large (over 500%) and keeps growing every day. Which database is it?

Answer: msdb

Explanation: If the destination service is unreachable server level event notifications get stored in msdb.sys.transmission_queue. Ref: http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/e89ed1c8-14a3-4ebf-9516-e90687d15dda

» Discuss this question and answer on the forums

SQL Server Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.


Featured Script

Find Blocked SQL and Blocked By Information.

This is a stored procedure which can be used to find which processes are been blocked by each other. This Procedure will also provide SQL information associated with the blocked SPID.  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 2005 : Administering

Link SQL Server (x64) to MySQL with ODBC - Hi Guys, Was wondering if you can please help, I am connecting our x64 2008r2 SQL Server to MySQL using ODBC. I...

Deleting backups on OS -- problem? - Hi guys, Here we have a 2005 XE instance (XE has no maintenance plans). There is a script that backs up...

Very large table - performance issues - We have a tall table that contains 2.6 billion rows Table structure: [img]http://i.imgur.com/Z02zDqk.jpg[/img] The application which uses this table has been running slow...

sysJobActivity showing jobs from years ago that never ended - I've noticed that sysJobActivity shows a number of jobs where the stop_execution_date is NULL, but the run_requested_date is up to...

Does SQLSERVER2008 offer Object level recovery - Friends, I have been using a third party toll that offers excellent object level recovery from the compressed backup file, whereby...

SQL Server 2005 : Backups

INDEX REBULID - HI, ANY ONE GIVE ME SCRIPT FOR INDEX REBULID AND UPDATE STATISTICS IN SQL 2008 R2

SnapManager for SQL job - Hi....I am new to Snapmanager for SQL jobs trying to find a solution for my issue. I run the SnapManager...

SQL Server 2005 : SQL Server 2005 General Discussion

Date reflect in profiler show different for 2 PC - Dear All, Pardon me, if im not sure how to title my topic correct, i hope i can explain it clearly...

BCP Utility to output contents into CSV - I have a stored procedure that gets a SQL query, file path (to output) and file name as parameter which...

Retrive parent child relation - Hi all below is my table and I want query for parent child relation. CREATE TABLE [dbo].[PRDST]( [PARENT] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS...

SQL Package downgrade from VS 2010 to 2008r2 - Hi, I have created packages in VS2010 in development but my production env is 2008r2. how do i downgrad my packages? Thanks...

Deploying ssis package - Hi All, Am trying to deploy a package. which is created in VS2010 into sql server 2012. but am getting below...

SQL Server 2005 : SS2K5 Replication

ALTER TABLE breaks replication - I have a table-article being replicated as part of a publication that replicates DDL commands. Historically, executing ALTER statements on...

How to alter a primary key on a Replicated Table in SQL Server 2008 - We have a table in Sql Server 2008 R2, which has been (Transactional Replication) replicated to two subscriber. We need...

Replication Action Message - Hi Experts, We have set up a brand new replication it was working fine till yesterday, today all of sudden logs...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS Package Optimization - Folks, We are taking Oracle Source data using Data Flow Task in which Data Reader as Source [We have Separate DSN...

Downgrade SSIS 2008 package to 2005 - I developed and tested 3 .dtsx packages in my development environment using Visual Studio 2008 v9.0.30729.4462 QFE, not realising that...

SQL Server 7,2000 : Administration

Decyphering waitresource - I have a spid that shows a wait resource in the form n:n:nnnnnnn . The first number is dbid and the...

SQL Server 7,2000 : Performance Tuning

Shrink large database - I have been asked to look a a sql 2000 database that has been neglected for some time. It has an...

SQL Server 2008 : SQL Server 2008 - General

Read mail using SQL Server 2008 r2 - Hi, I have configured SQL Server database mail. Now i want to read mail of ID that i have configured in SQL...

SQl Server 2008 Query Performance - Hi Guys, The below one is my query. It's taking 12 seconds for the execution process. The count(distinct()) and SUM() functions...

Memory is not releasing in sql server 2008 - Dear All, We have sql server 2008 with two node cluster. during operation memory is reaching up to 90% but its not...

SQLCMD Issue - I am facing error with SQLCMD(sql 2005 EE) SQLCMD -E -S Sqlcmd: Error: Microsoft SQL Native Client : Unable to complete login process...

Time out from App but not SSMS - Hi In a web app (linq to sql),I have a query with some parameters I took the actual query with specific...

Need to be alerted to disallowed email addresses being entered - Admin cleaning up after App programmers! :-) - Hi all I think I may be on the right track when considering creating a scheduled job. I need to capture...

Import of huge XML file - Hi All! I have an xml file of 44 Gb (Not Meg, its really GB) Delivered by the Danish custom authorities. My problem...

Normalization (DB Design) - [img][/img] Is my table schema normalized? Please Suggest.

How to set PacketSize in JDBC Driver? - I am using, Java, Jboss, Hibernate, SqlServer 2008 R2, getting following exception WARNING [com.microsoft.sqlserver.jdbc.internals.TDS.Reader] (pool-26-thread-7) ConnectionID:4 TDS header contained invalid packet...

How to use View Result in Insert trigger in sql server 2008 ? - i am having two tables A,B On Insertion in A, i insert data in B table(using Insert trigger) but i need...

Guideline question - I am looking for your advice. We have a calendar for each organization where the total count of the org events...

Initial data dumps - We have a fairly large production database for few hundred customers. When we add a new customer, we need to...

SQL Server Corporate Standards - Automated Compliance Checking - Does anyone know of a flexible automated SQL Syntax checker? I want to be able to analyse scripts before they are...

Why is my subtree cost so high?! - I really need help with optimizing this code. The subtree cost is really high, but it may just be a...

Convert data to HL7 message - Hello everyone. I have be trying to figure out how to convert patient records into HL7 format for about a...

For your amusement - [code] USE [collect2000] GO /****** Object: UserDefinedFunction [dbo].[tvf_ScrubData] Script Date: 05/21/2013 12:54:28 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tvf_ScrubData]') AND type in...

Dynamic Pivot Using sp_executeSQL with Parameters - Hello, I am having an issue with a dynamic pivot query(SQL 2008). I have read that using sp_executesql with parameters...

syntax difference between create index and create clustered index - Is there any difference if I say Create index and create nonclustred index? or both means same? ex: [i]CREATE INDEX[/i] IX_ProductVendor_VendorID ON...

Shrink helping performance - I have a situation that i thought was impossible and cant really explain to the users. I was asked to...

SQL Server Native Cleint and PB - Dear Gurus, How to the apply the ‘Auto Translate’ property of the SQL Native client (OLE DB) connection string in Power...

Running updation in SQL - Dear Team , Kindly advise , How to create query in sqlserver for running updations, Qustions : 1 . 1st row Total comes to...

How to restore database from network place... - Hi. My requirement is Restoring database from network place. when i am trying the restore the drive is not display...

Kindly advise , How to create query in sqlserver for below issue, - Dear Team , Kindly advise , How to create query in sqlserver for below issue, I Have Table with below details Id Name...

How to write a select query that shows upcoming birthdays - This will be an easy challenge. Select date_of_birth from person --This gives values in the YYYYMMDD format like 19700101 I want a...

SQL Server Security Issue - Hello all, There are accounts that were created well before my time in which the users (developers) know the passwords. These...

Attaching a database in SSMS without sysadmin role gives error - I am accessing my sql server 2008 r2 express server using SSMS. I have a user which has got db_owner...

Find date gaps - Hello everyone, I need to find a way to fill gaps in case a week is missing, example each record has...

SQL Server 2008 : T-SQL (SS2K8)

Need Alternate Solution For Cumulative Sum - Hi Friends... I Having Table For The Following Structure..( Sample Data ) [code="sql"]Create Table EmpProcessDetail ( EmpId varchar(25), EmpName varchar(50),ProcessName varchar(30),GroupCode varchar(5),PlannedDays int,ActualDays...

SQL Query Help - Create Table Test1 ( ID INT, VALUE VARCHAR(20), MAXVAL VARCHAR(20), VALUETYPE INT, OutCome int ) --select * from Test1 INSERT INTO Test1 VALUES ('1','FirstValue','140/90','1','4') INSERT...

SELECT INTO ignoring IDENTITY column - Hello, I want to know if anyone knows of an easy way to do a SELECT INTO from a table with...

Dynamic ranking - Thanks in advance if you can offer any assistance. I have a table of ranked cities-#Cities I have a table called #CityGroups...

List out rows from a duplicate record set that has one or more odd values - I have a table with data that looks as below... [img]http://www.sqlservercentral.com/Forums/Attachment13718.aspx[/img] Each CaseID has multiple records in the table. Each of these...

Very difficult query, not sure how to handle without a cursor. - I have a scenario here I have been wrestling over off and on for weeks. We have to tables, one lists...

Datetime function help - Hi, I m trying to get a datetime function for the start day of current week (Sunday). I m using...

SQL Server 2008 : SQL Server Newbies

windows updates / security patches in a clustered environment? - Hi all I´me new to the DBA role and this weekend we are having our first "maintenance weekend" in a while,...

SQL 2008 R2 performance monitoring and troubleshooting - Hi folks, I'm a Network Admin that has been assigned the task of evaluating our SQL server for performance issues...and when...

Database Diagram - Hi Guys. After creating a new database diagram,i don't see any relationships.It's only showing the tables.I tried selecting the show relationships...

user to run server agent job from their client pc - how do i setup a user on a windows 7 client pc to run a sql server agent job on...

SIGN - Why does this return more then one row? The book I'm reading doesn't say why and I'm used to select...

Counting duplicates - newbie question I am trying to count the number of diabetic patients who have had their blood pressure checked twice or...

How to delete a record with different column values? - I have a table where the unique identifier is studentId. Now this table contains decision column If decision has been made, then...

Sql server relationship using between 2 values - I have a database that contains 2 tables (sql server 2008): [b]LenderCommission[/b] ID int Commission decimal [b]CommissionTier[/b] ID int MinCommission decimal MaxCommission decimal I want to create a...

Adding LinkedServer in Uppercase and Lowercase - I am trying to find out how could I add two linkedserver with the same name, one in uppercase and...

linked tables - I have a SQL server express that I'm using to learn how SQL server works. Up till this point I've...

Trying to copy database from my laptop to my desktop pc - Hi, as you can tell i am a nube here. I am trying to copy SQL database established locally on...

Backup and recovery for the accidental DBA - Hello - I am not a DBA, let's start with that...but I have acquired the task of backup up a db...

Random Number Generator - I am trying to create a random number generator that would populate a number between 8-48 to 9 different lanes...

SQL Server 2008 : Security (SS2K8)

AD group has dbowner access to DB. How can I stop users from accessing from outside the application. - What am I missing. An application uses the users AD ID to execute against the database (read, write, create temp...

Linked server error - Hello, I'm trying to setup a Linked server between SS 2005 and SS2012 but keep getting "Login failed for user 'NT...

Best practices for SQL Server Agent service account - Hello, Can someone suggest what is the best security for SQL Server Agent service account? Or in other words which account...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Server 2008 Merge Replication issue - Hi, i am getting the below mentioned error for one of my database which is in replication, please help what needs...

Alwayson failover deleted logs and database file - We have a Alwayson availibility group setup on two clustered Windows 2008R2 machines. There is no shared storage setup, the...

One Way Transactional Replication: Multiple subscribers for single Publisher - Hi Friends, I have a scenario where there are two servers A and B. A act as a publisher and B...

SQL 2008 cluster does not failover to another node - Hi guys, I have this issue when I installed a SQL 2008 cluster instance on the Windows 2012 OS. I have...

Instance not selectable when trying to remove node from a cluster - Hi All, I have installed SQL on one of the nodes of a 2 node cluster but it failed, this...

SQL Server 2008 : SQL Server 2008 Administration

SSRS report Memory limit - I want limit report server for cpu and memory. Thanks in Advance.

Restoring Backup - Hello, I would like to know that what will be the best way to restore from Full backup, Differential and Transaction...

cluster - Can someone help me understand cluster? we have two nodes for the cluster. each cluster we installed two sql instances, one...

SP on cluster - We have an Active-Passive SQL Server 2008 Failover Cluster installed. Front end is sharepoint. They have two instances on each cluster,...

Added node to clustered sql prior to installing SQL on it, what to do :) - Hi - I am suppose to setup an active/active clustered server (none of servers had any sql on it, these are...

Backup Job Incomplete - Hi, I have problem My backup script runs on every sunday for full backup and should have to take 41 database...

client was unable to reuse a session? - Hi. Error messages, The client was unable to reuse a session with SPID 246, which had been reset for connection pooling....

restoring partial partitioned database - Hi all, I'm rather inexperienced in the Data Warehouse side of SQL, but we have rather small sized data warehouse db...

Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. - I'm setting up SCOM 2012 with the SQL Server Management Pack and I'm getting these errors: [quote]Could not allocate a new...

AUDIT LOG DISPLAYS WRONG TIME ...? - i have implemented the sql audit for the sql server 2008 sp1.When view the audit logs I am wondering why...

dbmail problems on SQL 2008 /SBS 2008 64bit - Hi, I'm neither a DBA nor a Sys Admin person, More a coder but I've been asked to move a...

Career : Certification

70-461 - SQL Server 2012 Querying a Datbase - Just started to look at the certs for 2012 and it looks like this needs to be taken before you...

SQLServerCentral.com : Anything that is NOT about SQL!

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

Audit reporting on report changes, etc.? - Hi. I need to track changes made to reports; when they were created, changed, deleted, moved to another folder in...

Data Warehousing : Integration Services

call sproc in oracle - We would like to do this in SSIS package: 1. pull a list of employeeID, with their address from a SQL...

Recording File Name etc into a SQL Table using a loop for each directory required - Hi, Im starting a new project today. What I have is an area with 10 folders where services drop their files...

SSIS Package to remove leading 0's - Hey Everyone, :-) I am creating an SSIS package and having some issues removing leading 0's from a field. I cannot...

Data Warehousing : Strategies and Ideas

Single DW multiple sources - Hey all, We currently have multiple systems that we report on. They all contain information on the same topic - but they...