SQL SERVER – Change Fill Factor – SQL in Sixty Seconds #069

“I want to change fill factor to another value – how do I do that?”

The other day I received this question in via Facebook. Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made by  Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left on the page, when the fill factor is 100.

I decided to build a quick video which explains how to change fill factor with the help of T-SQL as well as SSMS.

The code which I have used in this code is over here:

EXEC sys.sp_configure N'fill factor (%)', N'80'
GO
RECONFIGURE WITH OVERRIDE
GO

Action Item

Here are the blog posts I have previously written on the subject of Fillfactor. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

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

About these ads

SQL SERVER – Attach or Detach Database – SQL in Sixty Seconds #068

When we have to move a database from one server to another server or when we have to move a database from one file to another file, we commonly use Database Attach or Detach process. I have been doing this for quite a while as well. Recently, when I was visiting an organization I found that in this organization lots of developers are still using an older version of the code to attach the database. I quickly pointed that out to them the new method to attach the database, however it was really interesting to find out that they really did not know that sp_attach_db is now a deprecated method to attach the database. This really made me to do today’s SQL in Sixty Seconds. I demonstrate in this SQL in Sixty Seconds how to attach or detach the database using a new method of attaching database.

The code which I have used in this code is over here:

-- Detach Database
USE [master]
GO
EXEC MASTER.dbo.sp_detach_db @dbname = N'AdventureWorks2014_new'
GO
-- Deprecated Way to Attach Database
USE [master]
GO
EXEC MASTER.dbo.sp_attach_db 'AdventureWorks2014_new',
'E:\AdventureWorks2012_Data_new.mdf',
'E:\AdventureWorks2012_log_new.ldf'
GO
-- Correct Way to Attach Database
USE [master]
GO
CREATE DATABASE [AdventureWorks2014_new] ON
( FILENAME = 'E:\AdventureWorks2012_Data_new.mdf'),
(
FILENAME = 'E:\AdventureWorks2012_log_new.ldf')
FOR ATTACH
GO

Here is the question back to you – Do you still use old methods to attach database? If yes, I suggest that you start using the new method onwards.

SQL in Sixty Seconds Video

I have attempted to explain the same subject in simple words over in following video.

Action Item

Here are the blog posts I have previously written on the subject of SA password. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

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

SQL SERVER – Copy Database – SQL in Sixty Seconds #067

There are multiple reasons why a user may want to make a copy of the database. Sometimes a user wants to copy the database to the same server and sometime wants to copy the database on a different server. The important point is that DBA and Developer may want copies of their database for various purposes. I copy my database for backup purpose. However, when we hear coping database – the very first thought which comes to our mind is – Backup and Restore or Attach and Detach. Both of these processes have their own advantage and disadvantages. The matter of the fact, those methods is much efficient and recommended methods.

However, if you just want to copy your database as it is and do not want to go for advanced feature. You can just use the copy feature of the SQL Server. Here are the settings, which you can use to copy the database.

SQL in Sixty Seconds Video

I have attempted to explain the same subject in simple words over in following video.

Action Item

Here are the blog posts I have previously written on the subject of SA password. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

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

SQL SERVER – Reset SA Password – SQL in Sixty Seconds #066

It has been quite a long time since I posted SQL in Sixty Seconds Video. Here we are back with the new video.

One of the most popular question, I often receive is very similar to following:

“How do I reset the password for SA username?”

“I have forgotten the SA password, what should I do?”

“Our DBA just left the job and now noone knows how to reset the SA password. Please help.”

Solution

Honestly, it is pretty simple to reset the password for SA username. You just have to login to the system where your SQL Server is installed. Open SQL Server Management Studio and login with the help of Windows Authentication. As local system is the admin of the SQL Server, you will get access with administration privileges. Now, open the database, expand Security >> Login folder and right click over SA username. Now, it will bring up the screen where you can change the password for SA.

SQL in Sixty Seconds Video

I have attempted to explain the same subject in simple words over in following video.

Action Item

Here are the blog posts I have previously written on the subject of SA password. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

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

Developers – Drive by Daniel Pink – Book Review

I have been an avid reader of books. I read pretty much one or two books every week. I believe reading helps me a lot in my day job as well as well writing blog post. I am an evangelist and my primary responsibility is to spread the love for the technology. When I read more, I learn more and that effectively leads me to communicate better with my audience. Here is one of the recent book which I read and I loved it. This book is about what motivates us and what is the best job for us. I have read this book a second time and I think finally I have digested the meaning of this book. I love every bit of it and if you do not have it, you should get it.

I am going to give 10 Drive physical books to 10 people who register for my newsletter over here.

If you want to own the book, you can get it from Amazon |Flipkart.


Here are essential few quotes from the book.

The joy of the task was its own reward.

When money is used as an external reward for some activity, the subjects lose intrinsic interest for the activity.

Rewards can deliver a short-term boost – just as a  jolt of caffeine can keep you cranking for a few more hours. But the effects wears off – and, worse, can reduce a person’s longer term motivation to continue the project.

Enjoyment-based intrinsic motivation, namely how creative a person feels when working on the project, is the strongest and most pervasive driver.

Intrinsic motivation is of great importance for all economic activities. It is inconceivable that people are motivated solely or even mainly by external incentives.

Intrinsic motivation is conductive to creativity; controlling extrinsic motivation is detrimental to creativity.

It is those who are least motivated to pursue extrinsic rewards who eventually receive them.

Greatness and nearsightedness are incompatible. Meaningful achievement depends on lifting one’s sights and pushing toward the horizon.

Any extrinsic reward should be unexpected and offered only after the task is complete.

Type Intrisic behavior emerges when people have autonomy over the four T’s: their task, their time, their technique, and their team.

If you want to work with more type Intresic’s the strategy is to become one yourself. Automony, it turns out, can be contagious.

There is complexity, autonomy, and a relationship between effort and reward in doing creative work, and that’s worth more to most of us than money.

Carrots & sticks are so last century. Drive says for 21st century work, we need to upgrade to autonomy, mastery & purpose.

This new approach has three essential elements: (1) Autonomy—the desire to direct our own lives; (2) Mastery—the urge to get better and better at something that matters; and (3) Purpose—the yearning to do what we do in the service of something larger than ourselves.

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

SQL SERVER – Three Questions – Do You Know Your Servers? – Book Gift

It has been almost 2 years since we had a poll on this blog so here a poll which ask you three simple questions. Trust me these questions, I have carefully picked after doing a beta testing on a small group of people. It was very intriguing to see that not everyone knows the answer to these simple questions. When I asked the users to answer these questions and later on asked to find out the real number they were extremely surprised to see that their estimate and answer were way off to the reality. Let us do this exercise together.

The contest has two parts. You need to take part in the both the contest.

Part 1:Answer following questions

Click here to take survey

Part 2: Subscribe to our infrequent newsletter

Click here to subscribe to the newsletter.

If you are already subscribed to the newsletter, you have already completed this second part.

The winner will be announced on Monday 21st April via Newsletter only.

The winner will get a very inspiring book which I have been reading recently – Drive: The Surprising Truth about What Motivates us by Daniel Pink (Amazon | Flipkart).

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

SQL SERVER – SQLPASS 2013, Charlotte Memories – Book and Book Signing – Part 2

Yesterday I wrote about my SQLPASS 2013, Charlotte Memories Friends. This is second and last part of the same series. As you know I have so far written 10 different books on SQL Server and I am very much proud of that fact. Every time at SQLPASS, our books are available on display and lots of users get hold of those books. For last few years we are also doing book signing where we give away hundreds of the books to attendees for free. We had done similar experiment this year as well. We gave away hundreds of the books during this event.

Here are few of the photographs of the event.

Various books on display

Book signing event

Long line for book signing at SQLPASS 2013

Long line for book signing at SQLPASS 2013

Rick Morelan, Tim Radney, Pinal Dave and Greg Lynch

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