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

The Voice of the DBA

Quality Provides Traction/Velocity

Today we have a guest editorial as Steve is out of the office and traveling.

The thing with agile is that it is often abused. Agile is a set of principles to encourage rapid, uninhibited progress with the ability to readily change direction in order to meet actual needs when they differ from perceived requirements. In reality it is often executed as an excuse for shortcuts. No documentation. Lack of control. And poor quality. Whilst not unique to the agile movement, I believe that quality is an enabler for productivity. I believe that without a reasonable level of quality you cannot be as productive.

What do I mean by quality? In this instance I mean designs peer reviewed, coding standards followed, automated unit tests, code peer reviewed, automated performance tests, tested scripted deployments and documentation. Which of these or even how much of any of these is down to the specific of the project.

I am probably saying this to an audience that, if you believe the cliches, is most likely to agree. DBAs like the safe bet. In direct opposition to the mantra of "if its going to be late it might as well never turn up", many lean more towards "late is better than not getting there". Although I am not a DBA but a pesky developer, I tend to lean towards the latter. Quality improves productivity.

Gary Varga from SQLServerCentral.com

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

SQL Clone – create database copies fast!

Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. Download the beta.

Featured Contents

The Power of the Data Model

David Poole from SQLServerCentral.com

A data model is far more powerful than simply a set of build instructions for DBAs. It can be used to shape business thought and actions for the benefit of all. More »

Quick Testing with SQL Clone Images

Steve Jones from SQLServerCentral.com

Steve Jones explains how SQL Clone can be used to create multiple copies of a database for different testing scenarios, while using just a fraction of the disk space. For example for developers to experiment in sandbox environments, with full copies of large databases, without the hassle and delays of restoring backups or copying database files. More »

Office Hours Webcast 2017/02/01

Additional Articles from Brent Ozar Unlimited Blog

In this edition of Office Hours, Brent, Erik, and Richie discuss AlwaysOn Availability Groups, execution plans, speeding up log shipping, applying wrong service packs, their favourite high availability solutions, and more. More »

From the SQLServerCentral Blogs - Does Query Store Pre-Allocate Space

Grant Fritchey from SQLServerCentral Blogs

I love the questions I get while I’m presenting because they force me to think and learn. The question in... More »

From the SQLServerCentral Blogs - Creating SQL Containers from a Dockerfile

Andrew Pruski from SQLServerCentral Blogs

I’ve been playing around with SQL containers on Windows Sever 2016 a lot recently and well, building empty SQL containers... More »

Question of the Day

Today's Question (by Steve Jones):

The Database class in SMO represents a SQL Server database. Which of these things is not possible with this class?

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

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

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Igor Micev):

MIXED_PAGE_ALLOCATION is a new database option introduced in SQL Server 2016. What is true for this option?

Answer:

It’s set ON for the master, model and msdb databases and cannot be set OFF
It’s set OFF for the tempdb and cannot be set ON
The user databases are by default created with the setting set OFF regardless of its value in the model database

Explanation:

The option MIXED_PAGE_ALLOCATION is a new one coming with SQL Server 2016. You can find it in the sys.databases view and see how it's set up for the databases

 SELECT [name], is_mixed_page_allocation_on FROM sys.databases

It can't be changed for any of the system databases. It’s OFF only for the tempdb, and for the master, model and msdb it’s ON.

This ALTER database statement will result with the an error message.

 ALTER DATABASE TempDB SET MIXED_PAGE_ALLOCATION ON

 Msg 5058, Level 16, State 9, Line 3 Option 'MIXED_PAGE_ALLOCATION' cannot be set in database 'TempDB'.

The user databases are by default created with MIXED_PAGE_ALLOCATION = OFF. The option for them can be changed with the ALTER database statement.

Refs: https://msdn.microsoft.com/en-US/library/bb522682.aspx

https://blogs.msdn.microsoft.com/psssql/2016/03/15/sql-2016-it-just-runs-faster-t1117-and-t1118-changes-for-tempdb-and-user-databases/

» Discuss this question and answer on the forums

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 2016 : SQL Server 2016 - Administration

xp_cmdshell File Copy Failing - "Access Is Denied" - 2 New Windows Servers 2016 running SQL 2016 Standard. No Active Directory. Logged on as my local account (in Windows Administrator...

KILLED/ROLLBACK query doesn't stop running - I've tried to kill a query but it has the status killed/rollback ! the query is SELECT Id FROM dbo.Month WITH...

Hardening SQL Server - Hello everyone! Does anyone here hardening project of sql server? What major parts of this project and what is your recommendation? If you...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Query never completes with CXPACKET waits - Hi, I have a query that runs in under 40 minutes on our test server but never completes on the production...

SQL Server 2014 : Administration - SQL Server 2014

Unexplained jump in Backup File - I hope somebody here can help with this one, it's driving me mad. Up until 1 week ago, our full daily...

Flipping between SIMPLE and FULL recovery mode - Hi, DBA's out there in the big wide world just need some insight I am a SQL Developer (DBA light) and...

Performance improves after reboot - I am an ETL Developer who was asked to look at SQL Server performance problem. We have two DBA's but they're...

SQL Server DBA training. - Hello everyone, I have a question about the SQL Server training. I have been working as a IT helpdesk for...

SQL Server 2014 : Development - SQL Server 2014

Help with Instead of Trigger - I wanted to Validate the code againt the look up table before i insert into a Base table. I have two...

trouble wit a poor performing query - I've got a poor performing query and not sure the best approach to fixing it. Here's the statement: UPDATE @t SET...

SQL Server 2012 : SQL 2012 - General

Why is restoring a system database cross server a bad idea? - Hi all, I made a post a while ago in regards to an update to our DR process we currently have....

What is perfect way to declare variable data type in asp.net to match SQL data type? - From asp.net project I need to insert data into one table in SQL server which has a lot of data...

SQL Server 2012 : SQL Server 2012 - T-SQL

Read multiple .sql files having issues with .cmd script :( - Hello, this is part 2/2 of my goal, so i have created a write part and it writes to a share...

Count Difference for Every 15 Min - Hi, When I run the query using mulitple joins on Fact table and Dimension Table, my Output would look like Execution :1 When...

SQL Server 2008 : SQL Server 2008 - General

How to break down time span in Categories - Basically I have list of the tickets (records) in the db table ticket, and I need to find how long...

High Memory Usage - In our production server , memory usage is around 95% even tho it has been capped (8 GB ) . Total memory 12...

Rename instance name in SQL Server 2008 R2 - How to rename a instance name, and let me know do we need to restart the server to be affected

SQL Server 2008 : SQL Server 2008 Administration

Restore Database Question - Hello, My predecessor left this bit of code in an SSIS package and I can't understand how one restores a database...

Database Design : Relational Theory

Is having a lookup table that is for multiple entities a common practice? - I've been programming and designing databases of various sorts for several years. And I've been on teams who have designed...

SQLServerCentral.com : SQLServerCentral.com Announcements

FAQ: Using the new forum Software (Jan 2017) - After we completed the forum upgrade on Jan 12, 2017, quite a few things changed, so this thread is a...

This email has been sent to newsletter@newslettercollector.com. 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.