In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
 
SQL Backup SQL Backup Pro - easy DBCC CHECKDB for your backups
Try SQL Backup Pro's new verification options to check the integrity of your backups in one automated process. Get faster, smaller, secure backups today. Download a free trial.
 
Red Gate Deployment Manager NEW! Automate your .NET deployments
Deploy ASP.NET applications and SQL Server changes fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.

In This Issue

Stairway to Transaction Log Management in SQL Server, Level 6: Managing the Log in BULK_LOGGED Recovery Model

A DBA may consider switching a database to the BULK_LOGGED recovery model in the short term during, for example, bulk load operations. When a database is operating in the BULK_LOGGED model these, and a few other operations such as index rebuilds, can be minimally logged and will therefore use much less space in the log More »


SQL Server 2012: Installing Data Quality Services

Greg Larsen shows you how to install Data Quality Services and the companion client tool called Data Quality Client for SQL Server 2012. More »


The OLAP Sprint

The OLAP Challenge at the PASS Summit. Read about the plan and preparations. More »


From the SQLServerCentral Blogs - Solid State Storage: Enterprise State Of Affairs

Here In A Flash! Its been a crazy last few years in the flash storage space. Things really started taking off... More »


Editorial - Savvy Managers

data qualityToday's editorial was originally released on Nov 27, 2007. It is being re-run as Steve is at the PASS Summit.

My guess is that most of you out there wouldn't necessarily classify your managers as "savvy", especially as it relates to data. I know that when I hear managers talking about "data quality", "data integrity", "ETL", or any other acronyms, I tend to cringe, expecting more work, with poor requirements, and likely unreasonable estimates.

But not all managers are that bad and when I attended the Micosoft BI Conference earlier this year, I was surprised to see so many business people, especially managers, there and talking about how much value they got from BI because it gave then more insights into their data.

And they almost universally talked about how important a strong data warehouse with high data quality is to the success of a project.

Data quality is important, but it takes an effort to ensure that you can achieve a high level of quality in your data, meaning that the data is accurate and represents what you think it represents. I caught this interesting article about 10 data quality habits for successful managers. It probably should be for successful organizations and not just managers, but it's a good guide for managers to be aware of. Without reading the article, I'd bet you could guess at what some of the items should be. They're mainly common sense, but they bear repeating and it's good to see them listed in together in a short article.

Data quality takes effort and just like programming, the earlier you can introduce checks and catch errors, the less expensive it is to maintain. However that doesn't necessarily mean that you should go all the way to the source. Putting in a huge amount of checks and filters in the input client might not be in your best interest.

Consider a salesman, trying to make a sale, entering data and constantly getting pop-ups and errors that force data entry to be exact. Can you imagine how frustrating this would be? And possibly how this might impact data quality? Can you guess at what percentage of people might get names entered as initials instead of misspelled names? Does someone need to be slowed down because they typed "Bbo?"

Enforcing data quality at the source might be better served with suggestions or filters that try to fix common mistakes or even batch up confirmations of suspected errors for someone to examine later. There are any number of ways to make this an easier process and still ensure data quality.

My advice is that you should tackle data quality as an ongoing part of your job. Make constant, continuous, and small improvements, build in checks and balances, and be sure you work with other groups and users to ensure the load is shared, and more importantly, easily integrated into the way they already do business.

And maybe I'll see you featured at one of the next BI events as a SQL Server success!

Steve Jones


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

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

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

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

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


Question of the Day

Today's Question:

What stored procedure is used to enable Database Mail in SQL Server 2012?

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

This question is worth 1 point in this category: Database Mail. 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 Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate


Yesterday's Question of the Day

In SQL Server 2008 R2 and earlier versions, the auto updating of statistics rules are:

  • Minimum threshold of 500 rows in the table.
  • Fixed rate of change with a 20 percent threshold.
  • Uses a default dynamic sample rate based on the # of rows.

With the above rules a table containing 1,000,000 rows requires 200,000 changes before the auto updating of statistics is invoked. As a DBA you have determined that this is unacceptable in your situation.  Thus you created and schedued a job to determine the number of changes in a table and if they exceeded your defined limit you could/would invoke an update of the pertinent statistics.  Due to changes in business conditions, data entry staff attendance, etc., etc, you find yourself continually having to alter the schedule of when your job runs. 

In SQL Server 2008 R2 SP1, which one of these is true:

  1. Microsoft has altered the 20% threshold for auto updating of statistics. 
  2. Microsoft has NOT altered the 20% threshold for auto updating of statistics
  3. Microsoft released a trace flag which will change the fixed rate of the 20% threshold for update statistics into a dynamic percentage rate.

Answer: Micrisoft released a trace flag which will change the fixed rate of the 20% threshold for update statistics into a dynamic percentage rate

Explanation: In order to activate this new logic, you need to enable traceflag 2371. As a result the threshold to trigger update statistics will be calculated based on the number of rows in the table. However, the table still needs to have a minimum of 500 rows as the minimum row threshold did not change. As before the update of the statistics will apply the default sample rate which is dynamic and based on the # of rows.

Ref: http://www.microsoft.com/casestudies/Microsoft-SQL-Server-2012-Enterprise/Microsoft-IT/Microsoft-Ensures-Smooth-Operation-of-ERP-System-and-Cuts-Disaster-Recovery-Time/710000000493
http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Featured Script

Search for String in all columns

Find all columns containing a string value across all tables and schemas. 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

Daily Differential Backup Job Failing - Good afternoon, I have a SQL Job which performs nightly differential backups of all databases on one of my production servers....

Primary Key datatype Bigint vs uniqueidentifier: - Hi Friends! Can any one suggest me for each tables primary key should be Bigint as PrimaryKey or [uniqueidentifier] as...

difference between session and task - sys.dm_db_session_space_usage/sys.dm_db_task_space_usage - can someone please tell me what is the difference between session and task ?

trigger - Hi guys, I have created this trigger: [quote]create trigger TRG_CONTRIBUINTES_VERIFCA_RF_E_NIF on contribuintes for update as if (contribuintes.NUMEROPOSTO) ='CENTRAL' BEGIN if contribuintes.cod_rep_fiscal not in...

Full-Text index in an unusable state - I'm trying to backup a database (1 of over 40 on the system) but it tells me: The backup of full-text...

shrinking logfile in SQL 2008 - I cannot manage to shrink my logfile and datafile, the command used before in SQL 2000 doesn't work anymore, Urgent...

SQL Server 2005 : Business Intelligence

SSRS Language Report Properties - Where??? - Hi I have created a simple report which does not access any database or even display anything for that matter. I just...

SQL Server 2005 : Development

How to copy production DB into New Developmant Database? - Hi, We have Sql server 2008 R2 and i would like to set up my new test server and copy production...

SQL Server 2005 : SQL Server 2005 General Discussion

Is it "better" to specify the definition of a temporary table, or just define it on the fly? - The subject line says it all, but just to make it clearer... I could use a [b]CREATE TABLE #TempTable[/b] to define...

Using the "IF" function in a column - Is it possible to use the "IF" function in a column at design time as the value for that field?...

Invoked Schedule Id of the Job : Urgent! - Hello: When a job step is being executed from SQL Server Agent, I need to determine the "schedule_id" that invoked the...

SQL Server 2005 : SS2K5 Replication

Could not find object 'sp_MSrepl_startup' - Hi, This is the first time i am working with Replication. Can anybody tell me where the Problem is in my...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Concurrency Testing Stored Procedures - I have an request from a client to test a stored procedure that may run up to 500 times concurrently...

SQL Server 2005 : SQL Server 2005 Integration Services

Extracting Oracle data and loading to SQL server destination using SSIS - Hi, I was going through extraction of oracle data using OLEDB source/connection. I could see we cannot extract oracle data having data...

Issue with TransactionOption of SSIS - Hi All, I created a SSIS package which contains two "Execute SQL Tasks" components - one inserts/updates the records in Table1 of...

SQL Server 2005 : T-SQL (SS2K5)

Group by vendor & rate in po where vendor or rate changes - --Table CREATE TABLE #PUR_HIS1 ( SL_NUM INT, VENDOR_CODE CHAR(18), VENDOR_DESC VARCHAR(55), PO_NO CHAR(18), --PO_SERIAL INT, RATE NUMERIC(28,8), ORD_QTY NUMERIC(28,8), RECV_QTY NUMERIC(28,8), CR_DATE...

Item grouping for packing slips - Hi Everybody :-) We ship books - no more than 10 books per box. And Other items (toys, etc.) – no more than...

Msg 241 Level 16 (Conversion failed when converting datetime from character string) - Hello everyone, I'm stumped right now. Here is the deal, I need to replicate data from one table to another...

Dealing with Daylight Savings Time - Hi all, I'm having an issue regarding dates that are stored in one format vs another, as far as Daylight Savings...

SQL Server 7,2000 : T-SQL

Group by with MAX then other columns - how to control what is returned if there are multiple results - Hi all, I know this is a 'normal' issue that I see littered about the forums alot, trying to get unique...

SQL Server 2008 : SQL Server 2008 - General

Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. - I'm posting this to hopefully help someone else who runs across this problem. We have an application (Websense) running locally on...

SQL 2012 SSRS and AO - Is anyone using SQL 2012 SSRS with AO groups, if so have you had any problems with it. Is it...

Variable Parameter list for for procedure - Dear All I need to create procedure with variable parmaeter list. I am trying to achive it through table value parameter. Is...

Please explain the difference between "Parameter Sniffing and With Recompile" - [font="Verdana"][size="5"][/size][/font]

Need to run several maintenance scripts - Hello there. I need to run a number of scripts (which will take a number of hours) that involve data purging...

Setting database to read-only - Hi all, What will the impact of setting the database to read-only be? Will performance improve on reading? If so why...

SSIS question - I have three column values (startdate, completedate, recordcount) that i get in txt audit file. I need to use those...

Simple Performance testing questions - Ok so im working on a query and this will be the first time im really testing any thing so...

Autonumber on a field that doesn' have increlent or identity - Hello all, Quick question I got a table with a varchar(20) field as primary key the result has to be W0001 W0002 .... The data is...

Having a SQL 2008 R2 Database in Memory - Hi All, I have looked around and not found enough to answer my questions. And under some pressure to review this...

how to insert image in sql server 2005 - hi, i want to insert image into sql server using sql query. please help. thanks in advance sanjay

Auto Shrink In SQL Server 2008 - Dear, 1. I like to auto shrink my database. But I dont know how to do it. 2. My current DB size...

ssis package taking ages to open - Hi An ssis package i have been working on has started to take ages to open. It also takes ages to...

Point in time restore, restoring past the point in time - I've done a search and couldn't find any postings for this. I recently did a point in time restore to a...

duplicate backup log resore - I have full backup job on 5.10 and log backup at every 15 minutes If I restore the full backup from...

How can I get join not duplicate? - I get join 2 table but use 3 table. i need not duplicate value. but i try join have duplicate. [b]Table...

Add minus symbol in sql query - As SQL is not my main job what might seem easy to many here has me stumped.:blush: I have written some...

Updating 566787 Records takes forever - Hi, I am writing a script to update bad data in a column of a table. The Table has 566787 rows...

Error in max(substring) - can we add startid, endid SplitLayout startid, endid 1,1 2,1 3,1 4,2 6,3 9,1 10,5 15,4 19,3 22,4 Startid = will start from 1 and will add endpoint for second row 1+1...

Best practices to create databases for different applications - Hi, We are in a process of developing 4 different applications and they are all not related. All 4 application developers created...

Recent experiences with SQL 2008 Administration training providers in CA - Wanted to see if there were any recommendations for classroom training, SQL 2008 R2 administration in California - global knowledge, new...

Connot connect to server via name but IP works, not a SQL Browser service issue :) - Ok, here is the issue and my steps to troubleshoot: I can connect to my SQl server using SSMS by IP...

Money datatype and decimal in .net c# - I am returning a money datatype for sql to a .net app. When I retrieve the value from the output parameter...

SQL Server 2008 : T-SQL (SS2K8)

CONVERT BEHAVIOUR - hi, when I ran query- SELECT CONVERT(FLOAT,'-') It gives me an error- Error converting data type varchar to float. While when I ran...

Check and transfer data between two databases in same server using complex conditions - Source DB1.TB1 [ID, EAN, ASIN, NAME, CONDITION] 1,1001,20001,ps2,COD2,new 2,1002,20002,xbox,HOLA,new 3,1003,20003,xbox,Spider Man,used and DB1.TB2 [ID, PRICE] 1,50 2,51 3,61 Destination DB2.TB1 [ID, KID,...

Error When converting Nvarchar to XML - Hi all, I'm trying to convert a string to XML, I'm not sure why I'm getting the error:XML parsing: line...

building the query for the below output - Hi, CREATE TABLE [T_Master_lookup]( [F_lookup_id] [int] NOT NULL, [F_data_id] [int] NOT NULL, [F_looup_desc] [nchar](10) NOT NULL ) ON [PRIMARY] GO INSERT INTO [T_Master_lookup] ([F_lookup_id],[F_data_id],[F_looup_desc]) VALUES (1001,1,'A'); INSERT INTO...

Near Friday Date - Our Acounts Payable changed the way they pay vendors, from now on they will cut checks only on fridays, I...

Finding min and max date within a sub-group - My task is to find the minimum and maximum date for the Code contained in the latest row. A picture...

How to correlate data between two tables - Hey guys! Need your valuable help again, please! :-D I have various objects within one Salseforce system, which were migrated into another...

Information_Schema.Columns on All Database - Hi, When I run the following query: [code="sql"]Select * From INFORMATION_SCHEMA.COLUMNS [/code] This only returns results for the Databse it is on. Is there script...

Need to populate new column in the result set - Hi I have a table which has 3 unique values as below table. SNo. Date Cat Value 1 11/01 A 100 2 11/02...

[MYSTERY] Execution-plan bug? “Schema changed after the target table was created. Rerun the Select Into query.” : Every 6th run! - Hiyah! Before I get to the problem I want to say that we've worked around it by splitting the SELECT INTO...

LIKE operator with INNER JOIN - Hi all, I have to join two tables with the common field being the [SONumber] Well the problem I am facing...

SQL Server 2008 : SQL Server Newbies

Naming multiple results of one column different name. - Hi, I have a table that, among other columns, there are three in particular; qualifier, segment, and value. the combination of data...

Modifying a SQL 2000 maintenance plan -- and shrinking - I've got a SQL 2000 DB with a plan that was created long ago. On the Optimizations tab it's got...

Backup plan help required - Good afternoon, Please consider the following: [img]http://s17.postimage.org/ggq5fn4xr/Backup_Plan.jpg[/img] I have 18 servers at two different sites. The number of DBs on each server varies...

SQL Server 2008 : SQL Server 2008 High Availability

ReBuilt Index - I wanna confirm that some user are manually executing below query on each databases one by one This may be the...

SQL Server 2008 : SQL Server 2008 Administration

I/O requests taking longer than 15 seconds to complete - This is a Win2008R2 SQL2008R2 SP2 active/passive cluster. I am getting these messages when I run an UPDATE STATS on a...

Scalability of SQL Query - Hi, Can you help me understand how I can check the scalability of an SQL query. - Nidhi

Backup/mirroring - Our sql server databases are being mirrored to another server. We are also taking regular end of day backups. Question: Is it necessary...

Backup & Restore - I have read articles on backup and restore i.e. how to restore to a particular time. i.e. Grant fritchey's articles for...

ReportServer mirror - Hi, The sql server has ReportServer and ReportServerTempDB databases as a result of having SSRS on the sql server. Question: Can the ReportServerTempDB...

Blocked Queries - Hello All I'm trying to find the cause of blocking on my system I've found the following script to check for...

Dumps Required - Require Sql Server 2008 dumps for Certification

Security question - Is it possible to restrict from adding new members to db_datareader role?

ATTACHING SQL SERVER_2000 DATABASE TO SQLSERVER 2008 - I TRIED TO ATTACH A DATABASE CREATED IN SQLSERVER_2000 INTO SQLSERVER_2008. BUT THE FOLLOWING ERROR MESSAGE APPEARED. ---------------------------------------------------------------- Msg 5120, Level 16,...

Plan Cache - Query Stats - Usecounts - Execution Count - Hello All First of all, I'm using the script at the bottom to investigate my Plan Cache I've noticed that there are...

SQL Server Agent service failed to start, windows 7 - Hello, Please help me to fix the issue. Log file pasted below. 2012-10-20 20:46:17 - ? [100] Microsoft SQLServerAgent version 10.0.1442.32 (x86 unicode retail...

Service Broker - I am a newbie as far as service broker is concerned. The problem is with message forwarders. Forwarders are receiving...

Forced parameterization - Hi one of our instances was constantly at 100% CPU. We noticed a steady few hundred compilations/s and drilling down we...

Career : Certification

want to Study for MCTS - Hi All, I`m gearing up to study for my first cert and i`m looking at the 70-432 exam. I want to...

SQL Server reporting services - Hi, I have created some reports and deploy on report server.I want that every user on LAN can access reports. When I...

Microsoft Certified Master - There's been a lot of debate recently about whether certifications are worth doing and whether they provide you or your...

Programming : XML

Returning multiple rows from XML - Greetings, I am new to XML and am struggling with shredding an XML column into a tabular format. Here is what I...

SQLServerCentral.com : Anything that is NOT about SQL!

How's this for a Job Description - So I recently got this job description in the mail and at first glance it looks pretty good, but at...

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

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

Passing variables between reports?! - Hello, I'm creating new reports. I come from a Crystal Reports background. I want to use one detail report. I want to...

Reporting Services : Reporting Services 2005 Development

Multi row records in reports - Hi Guys, Despite searching, I haven't been able to find any info on how to go about displaying records in multiple...

How to Create Dashboard using SSRS 2005 Reports.?? - Hi All, After all attempt I finally Suceeded to deploy SSRS reports outside Local Host...Next thing I am trying to create...

display dataset query in report - Hi, 1.How to display report dataset query in main report.pls any one give right information. Thanks, Vivek.

Data Warehousing : Integration Services

Data Source - Stored Proc calls VIEW - Hi All, I am working on a project where developers are considering using a Stored Proceedure as data source in SSIS...

Single vs Multiple Data Flows - I have used SSIS a bit in the past (2005), but I am embarking on a new BI project and...

Importing from DBF - Hi, I'm pretty new to integration services and I'm trying import data from a DBF database. What's the best way...

Data Warehousing : Strategies and Ideas

Best book to start off MSBI - I have some idea about Integration services and Reporting services. I couldnt get a chance to work on SSAS practically. I...

Unknown dimension values or - In your DW do you always create an "-1 Unknown" record for each dimension? For example in DimEmployee: [code="plain"]EmployeeKey -1 EmployeeName "Unknown" ect.[/code] Or...

Data Warehousing : Analysis Services

applying filter in MDX - Hi, i have one MDX query which i am using for reporting, i have around 30 different fields which have different...

MDX not eqaual to condition - Hi i have following single MDX query. SELECT NON EMPTY { [Measures].[Appointments Count] } ON COLUMNS, NON EMPTY { ([Garage].[Company Id].[Company Id].ALLMEMBERS * [Garage].[Garage Id].[Garage...

Best book to start off MSBI - I have some idea about Integration services and Reporting services. I couldnt get a chance to work on SSAS practically. I...