Error - Instant File Initialization Failed

Hi Friends,

Here comes the first post of the wonderful year 2016 ahead. Last year, we have ended with the post on Introduction to Hadoop. Lets start our first post with an Error in SQL Server.

Description:

One of the error which I faced very frequently now a days as you can see the below snapshot: "File initialization failed" because of which my Restoration activity was failed.

This error occurred when I was in the middle of a Migration activity, were I was suppose to Backup and Restore a Database from one Server to another one. After executing the Restoration command with stats=1, I was waiting for 1 percent to complete (after that I can have a nap because the backup file was huge and the activity was at mid night) but I was awaiting awaiting and awaiting for that 1 percent. It was getting suspicious because it should not take too long to complete even a percent.

So, I decided to stop the restoration. Once the session was stopped I found the below error message:

Instant File Initialization Failed

Basically, I will try to explain the behind the scene what exactly SQL Server does then we create or Restore a backup file in different post. In this post just lets look for the solution for the error.

Solution:

1. Run => Secpol.msc;

Open Local Security Policy => Local Policies => User Rights Assignment => "Perform Volume Maintenance Task" => Right click => Add the user through which SQL Server services are running. Like you see in the below snapshot.

2. No need to restart the Server.

3. Now, start the restoration process and this time it will work well.

Perform Volume Maintenance Task

So from now SQL Server will skip the Zero Initialization whenever we create or restore a Database. Later we will see what exactly does this mean in probably in different post.

Hope this will save your time and this will help you. Don't forget to drop a comment below. Also do vote below if it is Interesting, Informative or Boring.

Facing trouble while switching the Database from Single User Mode to Multi Mode check here for solution.

(It's been so long, more than couple of weeks I was away from my blog. I'm afraid this could further continue for few more, due to multiple projects on weekdays as well over the weekends. Due to this, my Blogging might also affected. So, stay tuned soon we will learn many things on SQL Server as well as Hadoop Administrator.)

Thanks,
Vikas B Sahu
Keep Learning and Enjoy Learning!!!

Error - While Putting Database from Single to Multi User Mode

Dear Friends,

Click here to check how to start the SQL Server without TempDB Database. Let's learn what to do or how simple it can be to change the Database Mode from Multi User to Single User Mode or vice-versa?

Indeed! it is simple with the following command:

a. Alter Database Out set Single_User 
b. Alter Database Out set Multi_User

If we don't mention any termination clause like above it will run until the statements get completed.

Suppose there are n numbers of users connected to the Database and you executed the above command it will take hell lot of time to complete.
So rather, you can force disconnect the users to put the Database in Single User mode you have to fire the below command:

Alter Database out set Single_user with Rollback After 30 -- After 30 Seconds it will cancel and Rollback the Query

Alter Database out set Single_user with Rollback  Immediate -- It will immediately cancel and Rollback the Query

Alter Database out set Single_user with No_Wait -- If  there is any incomplete transaction No_Wait will Error

But again it might get horror if the Databases is in Single User and you cannot access the Database because only one connection can be made at a time and just think that connection is taken by the system i.e. SQL Server.

In this situation you are locked out, reason you cannot access the Database. Like you can see in the snapshot the Database Out is used by the system i.e. it is used by the Background process.

If you try to bring back the Database again in Multi User mode system will throw the following error:

Another possibility you can try would be detaching the Database. But when I tried detaching the Database, SQL Server will first kill the connections to the Database. Rather I should frame it as SQL Server will kill only the User connect and not the system connection.

After loads of struggle we were back to square one, that our Database was not getting back to Multi User mode. Seems it was like a deadlock between System SPID with Out Database. So we enabled the trace flag and checked the Error Log file. So following snapshot confirms that there was an deadlock:

DBCC TRACEON (1204,1222,-1)

Deadlock Graph

If we try to Alter the Database to put it in Multi User mode. We will get a deadlock and since our Alter Statement is having low priority it will fail with the error message:

After random tries we tried the following command and it saved us. We have to set the deadlock priority high and then execute the Multi User mode query like below:

Set Deadlock_Priority High
Go
Alter Database Out Set Multi_User

So what this will do is it will set the Dead Lock priority High and Alter the Database to Multi User mode.

Guys do share the feedback about this article and of course about the blog too.

Want to start learning SQL Server Clustering?? Check here the three part series on SQL Server Clustering.

Do you know MS SQL Server 2016 is ready to launch?? Check here the two part series on New Features of SQL Server 2016.

Keep Learning and Enjoy Learning!!!

How to Start SQL Server Instance when TempDB is unavaliable

Hi Guys,

Here is my last post on error while starting the SQL Server Analysis Services (SSAS) and here I've shared earlier on a issue, I request you please read that before proceeding.

Today we will learn a fact on the internal of SQL Server Tempdb (which was little unusual for me before I actually faced it. So I must say it was a learning experience).

Following are some questions that comes to my mind when I think about TempDB:

1. Where and how exactly the TempDB Database is used?

2. What exactly happened when your TempDB is corrupted?

3. Have you ever though of restarting the SQL Server Services without TempDB? Is it possible to do that?

4. Is it possible to Backups (Full, Differential or Log) and Restore the TempDB Database?

Let's see the possible answers to these questions:

Q 1. Where and how exactly the TempDB Database is used?

Ans. i. It is a System Database. Database ID for TempDB is 4. 
        ii. It is use to store temporary Data. Whenever we create a hash table (#abc) it gets created in TempDB.  
        iii. It also used for Sorting of Data (for e.g. If we use Order by clause in a query it uses TempDB to sort).
        iv. Also used for Row-Versioning.
        v. Recovery model of the TempDB is "Simple". 
        vi. We cannot run DBCC CheckDB on TempDB. 
        vii. We cannot detach the TempDB files. (Rather I must say one cannot detach the system Database files) 
        viii. Most important it is re-created every time whenever SQL Server is restarted. By checking the "Last Creation Date" we can see when was the last SQL Server was restarted (can expect as an interview question).

2. What exactly happened when your TempDB is corrupted?

Ans. i. Since we cannot run DBCC CheckDB; we cannot identify if there is any corruption on TempDB Database.
        ii. The only way to get rid of  corruption is to restart the SQL Server Services. Since it will recreate the Database files again.
        iii. Without TempDB Database SQL Server Services cannot be started. 
        iv. Model Database acts as the template for all the user created Database as well as for TempDB. So if model Database is unavailable TempDB will not get create and hence SQL Server  Service cannot be started.

TempDB Cycle

3. Is it possible to Backups (Full, Differential or Log) and Restore the TempDB Database?

Ans. i. Since TempDB is recreated every time when ever we restart the instance. We cannot Backup this Database.

4. Have you ever though of restarting the SQL Server Services without TempDB? Is it possible to do that?

Ans. (Now here is the question what made to write this post)

i. As said above, TempDB is restarted every time when a SQL Server is restarted. (This will create both the mdf and ldf files on the location present for TempDB in the Master Database). 

ii. But what if there is a disk level corruption or the defined location is not present due to xyz reason.

iii. Now here comes my question: Will the SQL Server Services will start??? So, answer for this is YES!!! it is possible (Till now it was NO for me). 

iv. If  you will start the SQL Services normally (assuming there is a disk level corruption on the disk were the TempDB was placed) the disk is unavailable you will get the below error message:

TempDB Error Log

v. So basically starting the SQL Services normally will not help you. Therefore we have to start the SQL Server Instance in Single User Mode as well as Minimally Configured. 

Adding Parameter in Startup parameter

vi. You can do that by adding -m (Single User Mode) and -f (Minimal Configuration) parameter in the startup parameter in the SQL Server Instance.

vii. This will start your SQL Server in minimal configuration (of course TempDB files is created) placing the TempDB files i.e. Data file on the User Database Data file location and the Log file on the User Log file location.

viii. And by this way your TempDB files are placed on different location other than the actual location for TempDB files.

ix. But this different location is temporary and only if you will start your SQL Server Instance in minimal configuration. So it is important to change the TempDB location to another drive by using the following command:


USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.ldf');
GO

SELECT name, physical_name AS Location
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

Hope this post will give you a different angle on concept of TempDB. Further we will see so more findings on TempDB. Share your comment on this to discuss more on it. You might be aware on SQL Server 2016 check here for the new features in it.

Thank You !!!

Error - Analysis Services Failed To Start in SQL Server 2012

Hi Guys,

Read my last blog on Error while configuring SQL Server Cluster here. As a DBA, we cannot or say we should not limit our self to only Database Engine related stuff. Today let's learn something related to Analysis Service i.e. SSAS.

In one of the migration project, I wanted to migrate an SSAS Database which is called as "Cube" to another Server.

So firstly, I needed the backup of the Cube. See the following command to take the backup of the Cube.

SSAS Cube Backup Code

Where Test is the Database Name and Test.abf is the Backup Name (Which will take the Backup in the default location).

Now, I wanted to restore this backup to my new Server. But when I tried to connect the SSAS, it got  failed due to SSAS was disabled. So tried to start the SSAS and it gave the following error:

And when I checked Windows Event Viewer it captured the below error message:

Windows Event Viewer

From the Error in the Event Viewer it is confirmed that it is related to something with permission issue. So what we commonly do is "Going to that particular drive\ Folder and give full right to the account which runs the SQL Server Analysis Services". But unfortunately it din't helped.

I started searching more in google related to this error and in most of the sites they will provide the same solution which we saw above. And the search continued and continued till I landed to the next solution. And solution was to check the msmdsrv.ini file (It is an Configuration file for AS).

Basically when we configure AS, we have to pass a location where Data and Log file will create, but in my case no such files where created. So what did was as follows:

1. Created two Folder's Data & Log on E:\ and F:\ drive respectively and gave full permission to it.

2. Open the msmdsrc.ini file (in notepad) from this location (default):

C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config

msmdsrv.ini File

3. As we can see DataDir and LogDir tags which contains the location for the files; Change the path save it.

4. Then tried to re-start the AS and it was successfully started.

So the resolution for this seems simple. Want to know what is SQL Server Azure read my introduction post on Azure. Do comment if you like of dislike the page.

Thank You !!!
Keep Learning and Enjoy Learning !!!

Dropping a SQL Server Database

Hi Guys,

Last post I have shared an Error that I was unable to start my SQL Server Database Engine. Today here I will share a small finding which I thought of worth sharing it.

In one of my migration project what I observed was "Even after dropping a Database the Data files and Log files were still exist". Ideally this should not happen because if we drop a Database the data file and log file associated with it should be dropped as well. But lets see what happened exactly that the database files are not dropped.

So what are the reasons for this?

Following are various ways to remove or Offline the Database from the SQL Server Database Engine:

a. Drop a Database
b. Detach \ Attach
c. Offline \ Online

General syntax for dropping a Database is: Drop Database Test

Let's see the scenario what exactly happened:

For the production migration what we were suppose to do was, remove then Test migrated Database and the replace it with the new Production migration Database. Following is the sequence which I followed:

Current Production Server : SQLServer-Current
New Production Server : SQLServer-New

Database Name : Test

1. The test migration for the Database "Test" was done on "SQLServer-New" from "SQLServer-Current" Server.
2. After proper testing on "SQLServer-New" the Databases was put Offline.
3. On the day of actual Production Migration, we dropped the database so that we can create a fresh copy of the Database.
4. Once it was dropped we started restoring the Database on the location were we have kept the data and log file.
5. It popped out an error message that the mdf and ldf files are exist. Either I should rename by new file name or I need to change  the path.
6. Since I cannot change the Database path, I was forced to change the file names.
7. After that I was able to restore the Database.
8. But I was wondering how can the files are still exist if I have dropped the Database. So once the migration activity was completed successfully I started troubleshooting it and here I found something new to learn.

"If you put the Database in Offline state and then drop a Database; you will always find the file associated with the Database present in the defined location"

Share you comments or any queries below to discuss more.

New Features in SQL Server 2016 - Part 2

Good Morning Guys,

As we have seen the top 3 new features in SQL Server 2016, here is the continuation of that post. We will see few other enhancements in it.

Following are the list extending to these top 3 features:

4. Enhancements to AlwaysOn:

As everyone knows the concept of AlwaysON was introduced by Microsoft in SQL Server 2012. This was introduced due to many limitations in the previous versions of SQL Server on High Availability and Disaster Recovery AlwaysON was introduced for mission critical Enterprise Application.

It was released for only Enterprise Edition but in SQL Server 2016 it will be released in Standard Edition of course with limited functionality. AlwaysON has also seen enhancement in SQL Server 2014 as well.

There is 1 Primary replica, 8 Secondary replicas and upto 3 replicas can be synchronized compared to 2 secondary replica can be synchronized in 2014.

Distributed transaction or MSDTC was not supported between Databases on AG in 2012 and 2014; which is now supported in SQL Server 2016.

We can now perform Full, File, File group or Log backups on the secondary replica Databases, but can't perform Differential Backup.

For more detail please refer to this blog on AlwaysON.

5. Native JSON Support:

JSON stands for JavaScript Object Notation. This is mainly provided for the developers, who will use this function for organizing the data in more logical manner. It is much more similar to XML already available by SQL Server.

It format the SQL result set as JSON by adding FOR JSON clause in the From statement. It is very much similar to XML in terms of types as well:

• RAW
• AUTO
• Explicit

Following is the syntax for using the JSON:

SELECT column, expression, column as alias
 FROM table1, table2, table3
 FOR JSON [AUTO | PATH]

Figure 1 shows the demo how the JSON script be written from a SQL Table:

Figure 1

Please refer the msdn blog here for more detail on JSON.


6. Enhancement In-Memory OLTP:

The feature of In-memory OLTP was introduced in SQL Server 2014 with the name "Hekaton" which is derived from a Greek which means 'one hundred' i.e. the goal of the project was to see 100x performance appraisal.

It has a separate query processor engine which is lock free design but at the same time it also maintain the data transnational integrity. Due to which it is different from Pin Table in SQL Server 6.5 release or putting Databases in SSD's (Solid State Disks). Refer this link to get an idea about the architecture of In-memory OLTP.

When it has done the debut it came with lots of drawback. And now from these demerits many has been converted to merit. lets see some of them below:

    i.  The max table size 256 GB which has changed to 2 TB.

    ii. TDE feature which is the greatest security feature in SQL Server was not supported in 2014 version, but now in 2016 it is supported.

    iii. Another major drawback clause such as IN, EXISTS, DISTINCT,OR,NOT,OUTER JOIN was not supported for query. Now these clauses are supported in SQL Server 2016.

    iv. LOB data type such as Varbinary(MAX) or Varchar(MAX) were not supported by the released version but from SQL Server 2016 it is supported.

    v. Similar way Foreign Key was not supported in older version which is not supported in SQL Server 2016.

There are many other new properties which has been enhanced in the new product of SQL Server 2016 for In-Memory OLTP check this msdn link for more detail.

These were the few new and some enhancement done in SQL Serve 2016. Check out this link which has some series of SQL unplugged videos for more details. So guys get ready to work with these exciting new features of SQL Server 2016 for Administrators.

Also along with features for DBA many more are waiting for BI and Developers guys as well.

Msg 22049-Error executing extended stored procedure: Invalid Parameter

Hi Guys,

It's time to learn from the error which I went today. If you remember I have posted here a backup script. The purpose of the script is to take Database backup along with few options. The script has undergone through few changes now.

But here I want to discuss an issue which I faced during implementing this code on one of new environment.

Scenario:

1. We wanted to configure this Database Backup Script in a new Server. There where few Databases on the Server. What we noticed was one of the Database name in the that Server was having 60 character (It was an Share Point Application Database).
2. And due to that we were getting this (Msg 8152, Level 16, State 2, Line 1 String or binary data would be truncated) error message.
3. For sake, we had changed the variable to Nvarchar(Max).
4. After changing this variable there was another error popped up.Error was (Msg 22049, Level 15, State 0, Line 0 Error executing extended stored procedure: Invalid Parameter).
5. Please see the Figure 1 snapshot.
6. But in between Step 3 and 4; I was executing the SP from a Job and was unable to see this error message. As some of the error message were truncated. So I ran the SP manually from SSMS and I got to know about this error.
7. Looking into the error, I came to know there was a problem in my last portion of the code.That means First portion 'To take DB Backup' was running fine and the Second part 'To delete the old Backup' was creating issue as I was using Extended Stored Procedure (i.e dbo.xp_delete_file)
8. Not sure what was the issue after doing little research found an partial solution and then tested the code with some changes in variable and it was successful.
9. Please see the demo code in Figure 2.  

Figure 1

Figure 2

Can you find any difference in Figure 1 and Figure 2?? (Of course apart from the Error)

The solution for the error was @path variable in the above code has datatype Nvarchar(Max) and if that is called from the extended Stored Procedure like in our case from dbo.xp_delete_file it throws the above error.

But if we pass the datatype as Nvarchar() like in Figure 2 and passed to Extended Stored Procedure the error is solved. 

So do remember if we are passing any variable to Extended Stored Procedure do defined a fixed value to the variable.

Keep Learning and Enjoy Learning!!!

New Features in SQL Server 2016 - Part 1

Hello Friends,

I've given a short introduction to you on SQL Server 2016 here now, it's time to learn some of the much awaited new features for DBA's. You can consider this a qualified interview question as well.
I'll try to explain these features one by one here:

1. Stretch Database:  

This is the most interesting feature introduced by Microsoft in SQL 2016. The word "Stretch" means expand, which means you can expand your local (on-premise) Database to Azure (Cloud). So here we will see the combination of on-premise + Azure in one Database.

Stretch Database

By default, this feature is disabled. We need to enable through SP_Configure 'Remote Data Archive'. 

Once it is enabled, you can configure this option under; 

Right Click on Database; Next Tasks; Next 'Enable Database for Stretch...' 

You need to supply the Azure Credential and select the table which you want to move.  

The whole purpose of Stretch Database is, we can move the old historic data to Azure by remote query processing. 

No changes is required from Application side. If a query is executed from an Application it will hit the on-premise Database and this on-premise Database will fetch the data from Azure and return the result set. (Only if that particular table data is moved to Azure)

Backup and Restore procedure of these Database would be little different from regular one. If you follow the regular 'Full' backup it will only consider the on-premise Database.

This would be consider as a good option to your environment because you can save the Hardware cost, Backup time would be less, Since historic data are moved out query processing for the current data will be improved. Also most important it's not a pre-requisite to have knowledge on SQL Azure  so any DBA who din't had luck to worked on SQL Azure should not worry.

There would be little Performance impact because it will fetch the data from on-premise as well as Azure. This will be the major drawback of this feature.

This feature must be only available in Enterprise Edition (Not sure yet). 

This was just a brief note about this Database. More information about Stretch Database are available here on msdn. You can also refer this site, it provides each and every details with GUI about Stretch Database.

2. Always Encrypted: 

Moving ahead with this interesting feature of Stretch Database, you might be definitely having question about data security, when there is any data movement from on-premise to Azure or vice-versa.

With this let me introduce to the next exciting feature of SQL 2016 which is Always Encrypted.
You might be well aware of TDE (Transparent Data Encryption) which was introduced by SQL Server 2008 in Enterprise Edition. It's use is to encrypt the data at rest with the help of certain keys (Master Key and Certificate). But as the data in Database is encrypted at the rest, the data is very much transparent; when the data is on fly (i.e. when data is moved from Application to Database or vice-versa). So man in middle attack is easily possible.This was the main drawback for TDE.

To overcome this drawback Microsoft has introduced new or can say extended security feature of TDE i.e "Always Encrypted" feature in SQL Server 2016.

Always Encrypted

Basically there is an enhancement in ADO.NET library which will protect the data in rest as well as motion.

So basically, the keys and certificate which we have create in SQL Server has to be deployed during application creation. This deployment of the Keys and Certificated will be handled by the ADO.NET library.

The figure on right will give an fair idea about the same. The data during fly will be encrypted and decryption will take place on the client side with the help of keys present in ADO.NET library.

This was just an overview on Always Encrypted, you can find more details here in msdn blog which is explained in depth with GUI. This is a very important feature for any organisation which handles critical data in there Database w.r.t to Auditing and Compliance. 

3. PloyBase: 

This one is the most important and exciting feature Microsoft has introduced ever and also the unexpected. Before this version any RDBMS product has the ability to interact with any other RDBMS (Through Linked Server).
But for the first time in SQL Server history it will interact with Non-RDBMS product as well. If you have gone through my previous post here, I already introduced to you about this Non-RDBMS product i.e. Hadoop.  

PloyBase

Looking into the future of Database world, Microsoft has also considered to introduce this feature in there upcoming product.
Earlier Apache Sqoop (It is a tool for data transfer between Hadoop Cluster and Relational Database) has the ability to interact with other RDBMS application but it has few limitations.
These limitations can be overcome with this new feature in SQL Server 2016.

If you have both SQL Server and Hadoop Cluster in your environment now it will not be necessary to learn Hadoop query to fetch data it can be easily just like Linked Server in SQL Server 2016.

How it will work?

• Microsoft has introduced two new SQL Services which will be installed during installation of SQL Server. (Uncheck the box if there is no need of this Service)
• We need to start by configuring SP_Configure parameter 'Hadoop Connectivity',0; by passing an appropriate value.
• You can use the following configuration values:

0	no Hadoop connectivity (default)
3	Enable connectivity to Cloudera CDH for Linux
4	Enable connectivity to Hortonworks Data Platform for Windows Server (HDP), HDInsight on Analytics Platform System, or HDInsight’s Microsoft Azure blob storage
5	Enable connectivity to Hortonworks Data Platform (HDP)for Linux

Kindly refer to this link you will get a complete details about this feature.

These are just an overview of three new feature which is introduced by Microsoft in there new version of SQL Server 2016. We will see the another few features in our next post.

Till then stay tuned for more updates and feel free to share your comments here. Also do like and share if you liked the post.

Check this link for Part 2 of this post.

Keep Learning and Enjoy Learning!!!

Auto Growth Option in SQL Server Database Part-2

Good Morning Friends,

This post is the continuation of Auto Growth Part 1.

Part 1 we have seen the options available of Auto Growth in SQL Database and the parameters available. Now here we will see how to select Parameters.

For "Parameter 2" easily we can set to "Unlimited" because no one will stop the growth of there Database. So it is set to "Unlimited" (It will grow until your disk get full or in simple terms you can say limited to your Drive size).

For "Parameter 1" best I will select "Growth in MB". Following are the cases I'll try to explain while selecting this option:

Case 1:

1. Suppose I've a Database Clis3, which has one Data and one Log File with size is 50 and 10 GB   respectively.
2. Growth for Log file is set to 100 MB. Recovery model in Full recovery mode.
3. Suppose there is a Bulk transaction in the Clis3 Database. Obviously the Log file will capture each and every transaction. SO the log file will start growing.
4. First it will start utilizing all the available VLF (Virtual Log File) up to 10 GB. Once it is full it will add 100 MB to Log file to write the transactions.
5. Now total Log size is 10.10 GB.
6. Once this 100 MB is also full it will add 100 MB more and so on. It will be done until the transaction is completed.
7. So now total Log size is 10.20 GB.

Here SQL Server is adding a defined amount of space (i.e. 100 MB) to the log file. This will indirectly control the growth of Log file.

Case 2:

1. Suppose for same Database Clis3, which has one Data and one Log File with size is 50 & 10   GB respectively.
2. Growth for Log file is set to 10 percent. Recovery model in Full recovery mode.
3. Suppose there is a Bulk transaction in the Clis3 Database. Obviously the Log file will capture each and every transaction. SO the log file will start growing.
4. First it will start utilizing all the available VLF up to 10 GB. Once it get full it will add 1024 MB (10% of 10 GB) to Log file to write the transactions.
5. Now total Log size is 11 GB.
6. Once this 1 GB is also full it will add 1127 MB more (10% of 11 GB) and so on. It will be done until the transaction is completed.
7. So now total Log size is 12.1 GB.

Here SQL Server is adding 10 percent of the current Log Size. Which means the size will vary depending upon the current Log File.

Ideal Setting for Auto Growth

From the above image ideally for large Databases we can set for Auto Growth.

Case study for the above two cases:

1. Log File in Case 1 will have 10.20 GB at the end whereas;
2. Case 2 will have 12.10 GB at the end.
3. That means Log file will utilize 1.9 GB of unnecessary space from the disk which is unwanted.
4. Hence proves for larger Database it is good to define "Parameter 1" to "Growth in MB's" as it will avoid unnecessary utilization of disk space.

Keep in touch... and Happy Learning....

I appreciate and thank you for reading this post. :) 

Auto Growth Option in SQL Server Database Part-1

Hi Guys,

Thanks a lot friends for your wishes for my certification completion Blog here.

There was a discussion couple of days back with one of my colleague DBA about the "Auto growth" option in SQL Server Database.

This topic came into consideration when one of my re-indexing job got failed due to less disk space on the log drive. As we are aware of rebuild requires  sufficient amount of disk space for the log file growth (We will discuss on separate post "Behind the scene- while Re-indexing").This will be a two part series.

We can check the "Auto growth" option under 

Right Click on Database, Next Properties ,Next Files, Next Autogrowth / Maxsize Column. 

Auto- Growth Properties

Here you will see depending upon total number of Data and Log Files you have in your Database. 

By default, it will inherited the value from Model Database but the Log growth will limit up to 2,097,152 MB (2 TB). So now the question is should we limit this to by default value? or we should change the setting? 

The answer is "It Depends" (Just like most of the answers in SQL Server). Well, it depends up to scenario to scenario. There are many factors come into consideration before changing the value in Production environment.

1.  How frequently is my log growing?

2.  What are the operations are happening in my Database?

3.  I have multiple Log files in different Drive. Still my log file will grow?

4.  Do we take Log backups?

What triggers to grow the Log file rapidly:

a. If your recovery model is on "Full" and there is a bulk operation such as BCP  it will capture each       and  every transaction in log. 

b. Even in rebuilding operations the log files will grow rapidly.

c. Recovery model is "Full" and there is no Log Backups taken regularly.   

So as you can see and as I said it depends upon many factors. So before changing anything in Production environment you should be well aware of all the scenarios.

Following are the parameters we need to set in "Auto Growth" for a Database?

1. We can set this option for both Data and Log File by two types:

a. In Percent

b. In Megabytes

2. We can set the Maximum File Size to

a. Limited

b. Unlimited

With the help of below query we can find out this setting for all the Databases:

--auto growth percentage for data and log files

Select DB_NAME(files.database_id) database_name, files.name logical_name, 

CONVERT (numeric (15,2) , (convert(numeric, size) * 8192)/1048576) [file_size (MB)],

[next_auto_growth_size (MB)] = case is_percent_growth

    when 1 then CONVERT(numeric(18,2), (((convert(numeric, size)*growth)/100)*8)/1024)

    when 0 then CONVERT(numeric(18,2), (convert(numeric, growth)*8)/1024)

end,

is_read_only = case is_read_only 

    when 1 then 'Yes'

    when 0 then 'No'

end,    

is_percent_growth = case is_percent_growth 

    when 1 then 'Yes'

    when 0 then 'No'

end, 

physical_name

from sys.master_files files

where files.type in (0,1)

and files.growth != 0

Now as a DBA needs to set these parameters after knowing the behavior of the Database in given environment. By default it will inherit the values from your Model Database.

We will continue in the next post Part 2 the cases which best suits for parameter 1. 

Just to share with you last year on 22nd June'14 I started blogging and it's now 1 Year. Special Thanks to Akhilesh Humbe for the guidance and Thank you friends for reading my post. 

If you have any suggestion to improve this blog feel free to comment. Suggestion are most and always welcome. 

Keep in touch... And Happy Learning....

I appreciate and thank you for reading this post.