SQL SERVER – Target Recovery Time of a Database – Advance Option in SQL Server 2012

Recently I was going over few advanced options of SQL Server 2012 in database properties and I found a new option in the property screen.

Properties screen of SQL Server 2008 R2

Properties screen of SQL Server 2012

I got little curious and decided to learn what does this new feature indicates. When I started to learn more about this subject, I had excellent learning experience.

The default value of this option is 0. This value is directly related to Checkpoint. When it is set to greater than 0 (zero) it uses indirect-checkpoints and establishes an upper-bound on recovery time for this database.

NOTE: Do not attempt this right away on your production database. Make sure that you try this out on your development server and validate before making it any changes on your production server. Make sure that you do not change this option unless you are 100% sure about what you are doing and understand the risk as well implications.

Indirect Checkpoints are alternative to automatic checkpoints (which should be good for most of the system and should be left alone if you are not sure about implications). If system crashes accidentally, indirect checkpoints provides potentially faster recovery time than automatic checkpoints.

Reasons for enabling indirect checkpoint

  • It may improve database recovery time
  • It may reduce checkpoint I/O as it writes continuously pages to the disk in the background

Reasons for NOT enabling indirect checkpoint

  • In OLTP workload it can increase overall writes on server by writing continuously pages to the disk in the background which may reduce the performance.

I have now learned the pros and cons of this feature and I am going to test this feature out on various workload oriented servers and see what is the effect of this feature. You can read further on MSDN about this feature.

Reference : Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

Last day I had excellent fun asking puzzle on SQL Server Login SQL SERVER – Merry Christmas and Happy Holidays – Database Properties – Number of Users. One of the user sent me email asking urgent question about how to resolve following error. Reader was trying to remove the login from database but every single time he was getting error and was not able to remove the user.

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

As per him it was very urgent and he was not able to solve the same. I totally understand his situation and here is the quick workaround to the issue. The reason for error is quite clear from the error message as there were schema associated with the user and that needs to be transferred to another user.

Workaround / Resolution / Fix:

Let us assume that user was trying to delete user which is named as ‘pinaladmin’ and it exists in the database ‘AdventureWorks’.

Now run following script with the context of the database where user belongs.

USE AdventureWorks;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('pinaladmin');

In my query I get following two schema as a result.

Now let us run following query where I will take my schema and and alter authorization on schema. In our case we have two schema so we will execute it two times.

ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_denydatawriter TO dbo;

Now if you drop the database owner it will not throw any error.

Here is generic script for resolving the error:

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('YourUserID');

Now replace the result name in following script:

ALTER AUTHORIZATION ON SCHEMA::YourSchemaName TO dbo;

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Merry Christmas and Happy Holidays – Database Properties – Number of Users

First of all Merry Christmas and Happy Holidays to everybody. I wish you best holiday season.

In today’s blog post – I am sharing very small question received by one of the reader. Though simple sometime a small question make people think. He sent me very similar to following image and asked few questions. As his image represented his server’s information, I am reproducing very similar image using AdventureWorks database.

Question: What does the number of users signifies in database properties? Does this mean current connected users or total active users or total enabled users or what exactly?”

Answer: Database Properties >> Number of Users indicates the how many users exists in the database.

In my current example you can see that there are 5 users and that is displayed in the above properties screen. Though, it is very simple question, now I am going to ask you question back.

Question to readers: If you notice there is small arrow besides GUEST or SYS login. It is RED arrow on south. What does this arrow means?

Please leave your answer in comments area.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – A Simple Puzzle and Simple Solution of Datatype and Computed Column

Christmas is just near and happy holidays to all of you. Today is Christmas eve and I decided to share something very simple but interesting with you. Recently some one reading my SQL Server Interview Questions and Answers book asked me following question.

“Pinal,

Instead of puzzle, or difficult interview question, I was asked following riddle in my interview. I could not answer it, do you have any idea.

Riddle: Create a table with two columns but you are allowed to specify datatypes only once. Additionally, write a mechanism that your data is copied from first column to second column without using any update statement. Also the column name should not contain any alphanumeric character.

How do I do this?”

For a moment I was confused too. As the requirement were very interesting, coping data without using update statement, no use of alphanumeric statement etc.

Finally I came up with following small and quick script.

CREATE TABLE MyTabs ([!] INT, [!!] AS [!]);
INSERT INTO MyTabs VALUES (1);
SELECT * FROM MyTabs;

If you are free, can you come up with further optimize script?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – A Quick Script for Point in Time Recovery – Back Up and Restore

Blogging is like writing a big novel in parts. It has its own mood and it has its own colors. Someday I feel like writing philosophy and some day I like writing theory and some day just a script. Today is one of the day when I just feel like providing working script for user requested frequently. Here is one of the script which I refer whenever I faced situation about restoring the database at point in time.

In this demo we will see three step operations:

  1. Set up script and backup database
  2. Restore the database in point in time
  3. Clean up database

Let us see each step with simple script:

1. Set up Script and backup database

------------------------------------------------
-- Creating environment
------------------------------------------------
-- Create Database
CREATE DATABASE SQLAuthority
GO
-- Make sure database is in full recovery
ALTER DATABASE SQLAuthority
SET RECOVERY FULL
GO
USE SQLAuthority
GO
-- Create Table
CREATE TABLE TestTable (ID INT)
GO
-- Taking full backup
BACKUP DATABASE [SQLAuthority]
TO DISK = N'D:\SQLAuthority.bak'
GO
INSERT INTO TestTable (ID)
VALUES (1)
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (2)
GO
-- Taking log backup
BACKUP LOG [SQLAuthority] TO
DISK =
N'D:\SQLAuthority1.trn'
GO
INSERT INTO TestTable (ID)
VALUES (3)
GO
INSERT INTO TestTable (ID)
VALUES (4)
GO
BACKUP LOG [SQLAuthority] TO
DISK =
N'D:\SQLAuthority2.trn'
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (5)
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (6)
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (7)
GO
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (8)
GO
-- Marking Time Stamp
SELECT GETDATE() BeforeTruncateTime;
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
-- Quick Delay before Truncate
WAITFOR DELAY '00:00:01'
GO
TRUNCATE TABLE TestTable
GO
-- Quick Delay after Truncate
WAITFOR DELAY '00:00:01'
GO
-- Marking Time Stamp
SELECT GETDATE() AfterTruncateTime;
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (9)
GO
-- Taking log backup
BACKUP LOG [SQLAuthority] TO
DISK =
N'D:\SQLAuthority3.trn'
GO
-- Marking Time Stamp
SELECT GETDATE() CurrentTime;
-- Selecting the data from TestTable
SELECT *
FROM TestTable
GO
USE MASTER
GO

2. Restore the database in point in time

-----------------------------------------------
-- Restoring Database
------------------------------------------------
USE [master]
GO
-- Taking tail log
BACKUP LOG [SQLAuthority] TO
DISK =
N'D:\SQLAuthority5.trn'
WITH NORECOVERY
GO
-- Restore full backup
RESTORE DATABASE [SQLAuthority]
FROM DISK = N'D:\SQLAuthority.bak'
WITH
STANDBY = N'D:\SQLAuthority11.bak'
GO
-- Restore transaction backup
RESTORE LOG [SQLAuthority]
FROM DISK = N'D:\SQLAuthority1.trn'
WITH STANDBY = N'D:\SQLAuthority11.trn'
GO
-- Selecting the data from TestTable
SELECT *
FROM SQLAuthority.dbo.TestTable
GO
-- Restore transaction backup
RESTORE LOG [SQLAuthority]
FROM DISK = N'D:\SQLAuthority2.trn'
WITH STANDBY = N'D:\SQLAuthority21.trn'
GO
-- Selecting the data from TestTable
SELECT *
FROM SQLAuthority.dbo.TestTable
GO
-- Restore transaction backup
RESTORE LOG [SQLAuthority]
FROM DISK = N'D:\SQLAuthority3.trn'
WITH STOPAT = '2011-12-21 11:12:18.797', -- Insert Your Time
STANDBY = N'D:\SQLAuthority33.trn'
GO
-- Rolling database forward
RESTORE LOG [SQLAuthority]
WITH RECOVERY
GO
-- Selecting the data from TestTable
SELECT *
FROM SQLAuthority.dbo.TestTable
GO

3. Clean up database

------------------------------------------------
-- Clean up
------------------------------------------------
USE MASTER
GO
ALTER DATABASE [SQLAuthority]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [SQLAuthority]
GO

Few quick points to note: Database has to be either in full recovery or bulk recovery mode. While restoring the database it should be done either WITH NORECOVERY (Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored) or WITH STANDBY (Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted).

Watch a 60 second video on this subject

Lots of scripts today but if you are in need of Point in Time Recovery script, this script is for you. I am open for suggestion, enhancements etc. Let me know your comments.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Mastering the Basics – Igniting Learning – A Unique Learning Experience

It has been my lifelong dream to be an author, and recently I have seen that goal realized three times.  One of the books I am most proud of is SQL Server Interview Questions and Answers. When I wrote SQL Server Interview Questions and Answers – I had very clear idea what my goals were for the book.

My goals were simple:

  1. Mastering the Basics
  2. Igniting Learning

I am very fortunate that my co-author shares the same love and passion for the SQL Server community. Vinod Kumar always believed in my same two goals. Our combined passion for the subject made writing this book a pleasant, even invigorating experience. We always felt this urge to help community and those who are eager to learn more. Years of learning the basics and practicing advanced technology led us to believe that learning the basics is sometimes the most difficult task of all. Once someone masters the basics, advanced concepts come easy. We have kept this in mind as we wrote this book.

A Book for Everyone

This book is not just for people who are looking to get their first job in the information technology field.  If you do not have an upcoming interview, if you already work in the field, but have questions about SQL Server or know that there are topics you should brush up on, then this is the book for you.

You don’t have to have a certain level of experience to get a lot from this book, either.  If you are a beginner you will not be totally confused by the topics – in fact, we tried to lay out the book for beginners.  However, experts will also find a lot to learn in these pages. Too.

Philosophy of the Book

We believe that this book will give advanced users a refresher and beginners a good learning point. We believe that after reading this book, our readers would be ignited to learn more. The goal of this book was not to tell you everything but to tell you enough so that you can learn yourself. Here is the hidden philosophy behind the book:

“Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.” – Source Unknown

Our goal in writing this book was to give our readers a SQL Server fishing pole.  They can then learn how to “fish out” the good answers and solutions to their problems.  This book does not list the most common problems (and interview questions) and then give a long list about exactly what to do.  The point of this book was to teach the readers to think for themselves – and feed themselves for a lifetime.

Mastering the Basics

Recently someone asked me if they could pass an interview just by reading this book. The answer is – interviews are a complex process. One’s goal should not be to merely pass the interview. The goal should be to demonstrate your knowledge and find the right appropriate way to apply your learning. Think of it this way, before attempting to pass an interview just by remembering the right answers – ask yourself if you will be able to perform the real world tasks – the answer can be complex. How about this – learn all you can about the real work job before you go to the interview, and master the subject. Now when you go to the interview or to the real world job, you will always demonstrate superior quality and the ability to perform the necessary tasks.

Igniting Learning

Well, our book is exactly the same. It tells you what you should learn in depth, what you should know in the real world, what kind of information you should collect and master before you go on that interview. Do not use this book as a tool to pass the interview – it will not work that way – consider the book as ‘learning path’. Take each topic covered in the book and dwell on it, think about it and digest it slowly and masterfully. The book is designed in such a way that every important topic has enough reference material listed right below it. You can use the references listed there or do your own search on the subject. Just make sure that the learning experience is complete.

Real World Experience

In a tough job market, it can be easy to look at a job advertisement and think to yourself, “I could do that job.”  You might know just a little bit about a subject, and you think that if you study hard enough you will sound smart enough to get the job, and then later you can learn all you can about the subject.  However, while our book will certainly help with the “sounding smart” part, you will also find that it encourages you to really learn SQL Server before that interview – with lots of real world examples and pointers advanced learning.  We encourage everyone to think about the job they want and love, and go for it.  Don’t look at book as just a way to get a job.

YOU!

We are constantly flooded with numerous emails, positive comments and constructive feedback. We are overwhelmed that almost everybody has understood the philosophy behind the book. SQL Server experts have told us that this book has helped them remember the basics and many have sent us questions to incorporate into the next versions. We believe the success of this concept is the positive energy which we have received from all of YOU!

Here are few blog posts about the book:

1) Review by Nakul VachhrajaniGood, portable book with easy to understand language on SQL Server Interview Questions & Answers

2) Review by Paras DoshiBook Review: SQL server Interview Questions and Answers by Pinal Dave and Vinod Kumar.

 3) Review by Madhivanan SQL Server MVP  – Book Review – SQL Server Interview Questions and Answers

4) Review by Sandeep KalraPinal Dave and Vinod Kumar‘s SQL Server Interview Questions and Answers: Book review

This book is available worldwide, here is the information about book – SQL Server Interview Questions and Answers

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – A Quick Trick about SQL Server 2012 CONCAT Function – PRINT

Yesterday I posted A Quick Trick about SQL Server 2012 CONCAT function and the very first comment in few minutes of Vinod Kumar. He suggested that this function should be also used with the PRINT statement as well. While I was having conversation with him – Jacob Sebastian sent me message suggesting the same. As I got feedback in first 10 minutes of publishing the blog post – I decided to update the blog post. While I started to write there was an email from Rick Morelan suggesting that this function can be used along with PRINT statement. Alright – 3 SQL experts suggesting the same thing to me in less than 15 minutes. Here is the dedicated post addressing the same idea.

Here is the question how many of you had the same suggestion as Vinod, Jacob and Rick?

Here is the updated version of the same article. If you use PRINT statement and directly appending string and datetime datatype you will get following error.

PRINT 'Current Time ' + GETDATE()

The normal reaction to the same is to modify the script and convert datetime datatype to varchar datatype. This will not only remove the error but also a very common practice.

PRINT 'Current Time ' + CAST(GETDATE() AS VARCHAR(20))

Current Time Dec 21 2011  7:00PM

Just like earlier blog post you can now use SQL Server 2012 function CONCAT to resolve the error.

PRINT CONCAT('Current Time ', GETDATE())

I have previously written in detail article about SQL Server 2012 CONCAT function over here additionally, I have written quick guide on 14 new functions of SQL Server Denali.

I am sure Vinod, Jacob and Rick are smiling at looking at this blog post.

Watch a quick video relevent to this subject:

Reference: Pinal Dave (http://blog.SQLAuthority.com)