Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 2 December 2014

How to Change SQL Server Database Auto Growth Settings

How to Change SQL Server Database Auto Growth Settings



In this article we will go through the steps to change SQL Server Database Auto Growth Settings. It is always a best practice to set an appropriate auto growth setting for all Production database to a handle unexpected database growth which can be due to unexpected data load or due to the disk space requirements to perform maintenance tasks. The steps mentioned in this article are same across SQL Server 2005 and higher versions.

Different ways to Change SQL Server Database Auto Growth Settings

  • Change SQL Server Database Auto Growth Settings Using SQL Server Management Studio (SSMS)
  • Change SQL Server Database Auto Growth Settings Using TSQL Script
MyTechMantra Recommendation

It is a best practice to Configuring Database Instant File Initialization Feature on windows for SQL Server to reduce the time required to growth database file.
Let us go through each of the above mentioned options in detail.

How to Change SQL Server Database Auto Growth Settings Using SQL Server Management Studio (SSMS)

1. Connect to SQL Server Instance Using SQL Server Management Studio
2. Expand Databases; right click the database and select Properties from the drop down list to open up Database Properties to change the Autogrowth settings for a database as shown in the snippet below.
Check SQL Server Database Properties 

3. In Database Properties; Select Files Page on the left side panel as highlighted and then click on “” button to open up Change Autogrowth for Database dialog box.
SQL Server Database Properties Dialog Box to Change Database Autogrowth Settings 

4. In Change Autogrowth for Database dialog box you will see that the default File Growth Autogrowth setting is 1 MB. You can change the Autogrowth settings by changing the value either in Mega Bytes or in Percentage. However, it is better to change the value in Megabytes are this will have better control on the database file growth. The Autogrowth value should be change for both Data and Log files. In this demo I have set the data file growth as 512 MB and Log File growth as 256 MB. Once you change Autogrowth setting click OK to save the changes and return to Database Properties window.
How to Change Database Autogrowth Settings of an SQL Server Database 

5. In the Database Properties Window you could see that new values for Data and Log file Autogrowth is reflected. Click OK to make the changes to the Autogrowth settings of the database.
Updated Database AutoGrowth Settings in SQL Server 

How to Change SQL Server Database Auto Growth Settings Using TSQL Script

Using the below script you can change the database Autogrowth settings to grow data file at 512 MB and Log file at 256 MB.
USE [master]


  (NAME = N'MyTechMantra_log', FILEGROWTH = 256MB )

To get Database size in MB for SQL

Weekly Server Report for SQL


IF OBJECT_ID('tempdb..#T','U') IS NOT NULL

CREATE TABLE #T (DBName nvarchar(500),SizeinMB nvarchar(100),FreeSpaceinMB nvarchar(100))

EXEC sp_MSforeachdb 'USE ? SELECT ''?'' DBName,
sum(size/128.0) [Size in MB],sum(size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)[Free Space in MB]
FROM sys.master_files
where DB_NAME(database_id)=db_name()
group by DB_NAME(database_id)'


Thursday, 27 November 2014

How to get Count from CHARINDEX Function

How to use the CHARINDEX Function
The CHARINDEX function returns the starting position of a character, or a string of characters within another character string. The CHARINDEX function is called using the following format:
CHARINDEX expression1 , expression2 [ , start_location )
Where expression1 is the string of characters to be found in expression2, and start_location is the position where the CHARINDEX function will start looking for expression1 in expression2.
The CHARINDEX function returns an integer. The integer value returned is the position where the characters being search for are located within the string being searched. If the CHARINDEX does not find the characters you are searching for then the function returns a zero integer value. Let say we execute the following CHARINDEX function call:
 CHARINDEX('SQL', 'Microsoft SQL Server')
This function call will return the starting location of the character string "SQL", in the string "Microsoft SQL Server". In this case the CHARINDEX function will return the number 11, which as you can see is the starting position of "S" in string "Microsoft SQL Server".
Now say we have the following CHARINDEX Command:
 CHARINDEX('7.0', 'Microsoft SQL Server 2000')
In this example the CHARINDEX function will return zero, since the character string "7.0" cannot be found in the string "Microsoft SQL Server 2000". Let go through a couple of examples of how you might be able to use the CHARINDEX function to solve some actual T-SQL problems.
For the first example say you would like to display only the last name of the ContactName column, for the first 5 records in the Northwind database Customer table. Here are the first 5 records.
 Maria Anders
 Ana Trujillo
 Antonio Moreno
 Thomas Hardy
 Christina Berglund
As you can see, the CustomerName contains both the first and last name of the customer, where first and last name are separated by a single space. I will use the CHARINDEX function to identify the position of the space between the two names. This way we can used the position of the space to parse the ContactName so we can display only the last name portion of the column. Here is some T-SQL code to display only the last name for the first 5 records in the Northwind Customer table.
 select top 5 substring(ContactName,
                       charindex(' ',ContactName)+1 ,
                       len(ContactName)) as [Last Name]
       from Northwind.dbo.customers
Here is the output from this command:
 Last Name
The CHARINDEX function found the space between the First and Last Name, so that the substring function could split the ContactName, thus only the Last Name was displayed. I added 1 to the integer value that CHARINDEX returned, so the Last Name displayed did not start with a space.
For the second example, say you want to count all the records from a table where a given column contains a particular character string. The CHARINDEX function could be used to satisfy your request. To count of the Addresses in the Northwind.dbo.Customer table where the Address column contains either the word 'Road' or an abbreviation for road ('Rd'), your SELECT statement would look like this:

you can't do count(charindex(string)) would give syntax error

But to resolve error see this

 select count(*) from Northwind.dbo.Customers 
   where CHARINDEX('Rd',Address) > 0 or CHARINDEX('Road',Address)
 > 1

Monday, 24 November 2014

Create Database on Diffrent Drive in SQL Server Using T-SQL

Creates a new database and the files used to store the database, a database snapshot, or attaches a database from the detached files of a previously created database.


( NAME = testdb_dat,
    FILENAME = 'E:\Databases\testdb.mdf' )
( NAME = testdb_log,
    FILENAME = 'G:\Logs\testdb_log.ldf')

Creating a database without specifying files

The following example creates the database mytest and creates a corresponding primary and transaction log file. Because the statement has no <filespec> items, the primary database file is the size of the model database primary file. The transaction log is set to the larger of these values: 512KB or 25% the size of the primary data file. Because MAXSIZE is not specified, the files can grow to fill all available disk space. This example also demonstrates how to drop the database named mytest if it exists, before creating the mytest database.
USE master;
-- Verify the database files and sizes
SELECT name, size, size*1.0/128 AS [Size in MBs] 
FROM sys.master_files
WHERE name = N'mytest';
USE master;
( NAME = Sales_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\saledat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
( NAME = Sales_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\salelog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;

Attaching a database

The following example detaches the database Archive created in example D, and then attaches it by using the FOR ATTACH clause. Archive was defined to have multiple data and log files. However, because the location of the files has not changed since they were created, only the primary file has to be specified in the FOR ATTACH clause. Beginning with SQL Server 2005, any full-text files that are part of the database that is being attached will be attached with the database.
USE master;
sp_detach_db Archive;
      ON (FILENAME = 'D:\SalesData\archdat1.mdf') 
      FOR ATTACH ;

Wednesday, 19 November 2014

Why VIEW takes long time to Execute

Why VIEW takes long time to Execute

Here in this article we are discussing related to the performance of the views. Why the performance of the view is slow. Please note that I am not taking about the indexed views, it's another thing and out of the scope of this article.
Like stored procedure, the optimizers cache the execution plan in the case for further use.
Let's take an example:
We have a simple base table contains records and we just create a view from this base table objects and then execute both (Base table and View separately)

       (empid    INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
        empname  VARCHAR(50) NOT NULL,
        empsal   DECIMAL(20,2))

CREATE VIEW vw_empView
SELECT empid, empname, empsal
FROM   emp_Table

-- Example-1
SELECT * FROM emp_Table

-- Example-2
SELECT * FROM vw_empView


Here in this example which one is executed faster?
It's Example-1. That means direct query from Table objects. To get the better understanding related to execution we just look at the execution plan.

Both execution plans are same. So, why the execution of views take long time?
This is because it takes SQL Server extra work such as looking up data in the system tables before it can execute the view.
This extra work is not part of the execution plan, so it appears that the two SELECT statements should run at the same speed, which they don't, because some of the work SQL Server is doing is hidden.

Monday, 17 November 2014

Avoiding 'Failed to notify via email' errors

If a picture is worth a thousand words, then the words associated with the following picture would probably involve a decent amount of muttering and cursing about the the DREADED ‘Failed to notify <operator name> via email’ error message that you might see crop up in your environment every once in a while – on new or recently provisioned servers:

Email Alerts Good. Configuration Options – Not so Intuitive

As I’ve posted in the past, using Database Mail can be a great way to be notified of problems or issues that might occur with your server (such as corruption or IO problems) or with your jobs – provided, of course, that you’ve got some sort of backup or ‘watchdog’ for your alerting system to make sure that it doesn’t just fail silently without any warnings.
Only, as great as it is to set up a Notification for an operator (I highly recommend using an alias instead of binding actual/individual email-addresses to so-called operators) to be notified of when a job fails or runs into problems, there’s an extra configuration step that you’ll need to jump through to get this to work.
And the problem is that this extra/additional configuration step is obscenely easy to forgot. In fact, I’ve forgotten about it enough times now that being burned by this issue with new servers that I’ve provisioned (or recently inherited) has caused me to blog about this step – in the hopes that doing so will ‘fuse’ this step into my brain. That, and this extra step is so easy to forget about that I’m sure that posting it here will help someone else.

Letting the SQL Server Agent Talk to Database Mail

As you might guess, getting Database Mail to play nicely with the SQL Server Agent is sadly a question of security. Or, stated differently, you’ll get the dreaded ‘Failed to notify … via email’ errors IF you haven’t explicitly allowed the SQL Server Agent to interact with Database Mail.
That said, it’s happily a trivial operation to allow the SQL Server Agent and Database Mail to ‘get giggy’ with each other and throw wild little parties for one another – whenever they want or need to. To do so, you just need to right click on the SQL Server Agent node in SQL Server Management Studio, and select Properties. Then switch to the Alert System node, and enable the Mail profile you’d like to allow the SQL Server Agent to use, restart the SQL Server Agent Service (NOT the SQL Server or the box – just the SQL Server Agent Service – which you can pretty much safely do on MOST systems at ANY time without any big worries or concerns), and you’re good to go.


Move Database Files MDF and LDF to Another Location

 Move Database Files MDF and LDF to Another Location


When a novice DBA or Developer create a database they use SQL Server Management Studio to create new database. Additionally, the T-SQL script to create a database is very easy as well. You can just write CREATE DATABASE DatabaseName and it will create new database for you. The point to remember here is that it will create the database at the default location specified for SQL Server Instance (this default instance can be changed and we will see that in future blog posts). Now, once the database goes in production it will start to grow.
It is not common to keep the Database on the same location where OS is installed. Usually Database files are on SAN, Separate Disk Array or on SSDs. This is done usually for performance reason and manageability perspective. Now the challenges comes up when database which was installed at not preferred default location and needs to move to a different location. Here is the quick tutorial how you can do it.
Let us assume we have two folders loc1 and loc2. We want to move database files from loc1 to loc2.
USE MASTER;GO-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database
Now move the files from loc1 to loc2. You can now reattach the files with new locations.
-- Move MDF File from Loc1 to Loc 2
-- Re-Attached DB
FILENAME N'F:\loc2\TestDB_log.ldf' )FOR ATTACH
Well, we are done. There is little warning here for you: If you do ROLLBACK IMMEDIATE you may terminate your active transactions so do not use it randomly. Do it if you are confident that they are not needed or due to any reason there is a connection to the database which you are not able to kill manually after review.

Copy Database from SQL 2005 to SQL 2008 ( Lower to Higher Version)

We cant able to copy higher version(SQL 2008 Secondbase) to lower version(SQL 2005 Secondbasedev)

Using attach/detach or backup/restore Method.

But We can Achieve using 

Generate Scripts(with Data) / SQL Server Import AND Export Wizard available in 

SQL 2008 on Small Databases.

before doing this we have to disable all constraints and make it copy then enable constraints

-- Disable all the constraint in databaseEXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
Generate Scripts(with Data):

save to an script window or text file and make it easy.
-- Enable all the constraint in databaseEXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

SQL Server Import AND Export Wizard:

before doing this we have to disable all constraints and make it copy then enable constraints

-- Disable all the constraint in databaseEXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

  • Then SQL Server Import AND Export Wizard window will be opened. Press Next
  • Choose a Data source (in your case from SQL Server 2008). Choose a Destination( in your case from SQL Server 2005).
  • Select Copy data from one or more tables or view
  • Select the source's tables and destination's tables
  • Click Next & Finish
-- Enable all the constraint in databaseEXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Thursday, 13 November 2014

Enabling Distributed Transactions in Remote Servers

Enabling Distributed Transactions in Remote Servers

When we run a distributed transaction against an instance of SQL Server, we may receive an error message. This problem can be resolved by following the below statements:
      • Make sure that the MSDTC (Microsoft Distributed Transaction Coordinator) is enabled on both the servers.
      • Go to Start –> Run –> Type as ‘Services.msc

      • ~Right click on the above selected service and click on start. This starts the MSDTC service in the server (do the same on the remote servers also)
      • Once the Service is started, the next step is to enable the Network Transaction.
      • Go to Start –> Run –> Type as ‘dcomcnfg.exe’ or Goto Control Panel –> Administrative Tools –> Component Services
      • In windows 7, Expand Console Root –> Compoent Services –> Computers –> My Computer –> Distributed Transaction Coordinator –> Local DTC –> Right Click Properties
      • In windows XP, Expand Console Root –> Compoent Services –> Computers –> My Computer –> Right Click Properties
    • Goto ‘Security’ Tab and check the checkbox ‘Network DTC Access’
    • To allow the distributed transaction to run on the current server from a remote server, select the ‘Allow Inbound’ check box.
    • To allow the distributed transaction to run on a remote server from the current server, select the ‘Allow Outbound’ check box.
    • Under the Transaction Manager Communication group, select the ‘No Authentication Required’ option.
    • Click on OK in Security Dialog Box
    • If the settings are not affected once restart the server where the changes were done
    We can check the status of service state by executing the below command in the query window (this need the corresponding permissions for executing):
    EXEC xp_servicecontrol N'querystate',N'msdtc'

Thursday, 30 October 2014

How Do We know Restoration date & time for Database?

How Do We know Restoration date & time for Database?

Use msdb
Select restore_date, destination_database_name, user_name  from restorehistory

Tuesday, 28 October 2014

Setting Up Alerts for Long-Running Transactions

Setting Up Alerts for Long-Running Transactions

The code below is something you can use to easily set up an alert for long-running transactions:
/* NOTE: You have to configure/set the following 3 variables */
DECLARE @AlertingThresholdMinutes int = 10;
DECLARE @MailProfileToSendVia sysname = 'General';
DECLARE @OperatorName sysname = 'Alerts';


DECLARE @LongestRunningTransaction int;
        @LongestRunningTransaction =
                MAX(DATEDIFF(n, dtat.transaction_begin_time, GETDATE()))
        sys.dm_tran_active_transactions dtat
        INNER JOIN sys.dm_tran_session_transactions dtst
                ON dtat.transaction_id = dtst.transaction_id;

IF ISNULL(@LongestRunningTransaction,0) > @AlertingThresholdMinutes BEGIN

        DECLARE @Warning nvarchar(800);
        DECLARE @Subject nvarchar(100);

        SET @subject = '[Warning] Long Running Transaction On ' + @@SERVERNAME;
        SET @Warning = 'Check SSMS > Server > Reports > Top Transactions By Age.';
        EXEC msdb..sp_notify_operator
                @profile_name = @MailProfileToSendVia,
                @name = @OperatorName,
                @subject = @subject,
                @body = @warning;
Where you’ll just need to specify the name of the operator you’d like to notify (along with the Mail Profile to use to do so—all of which you can learn about via Books Online and from my previous post on favoring notification of operators over sending emails directly). You’ll also want to specify how many minutes constitute a 'long-running transaction.' This'll obviously vary from one server/workload to the next and from environment to environment. My recommendation though (with this and with all forms of alerting) is to make sure you don’t set this value so low that you’re constantly getting alerts or notifications—alerts and notifications are useless if you train yourself to ignore them because they occur to frequently.
Otherwise, once you’ve specified all of the parameters as needed you’ll then want to create a new SQL Server Agent job that runs every few minutes (where the actual frequency will depend upon your @AlertingThresholdMinutes value) so that you’ll be notified if/when something goes over your specified threshold.

Why Would I Want to Set Up Alerts for Long-Running Transactions

Of course, knowing how to set up alerts for long running transactions (and I’m SURE there are multiple ways to tackle this need) doesn’t exactly explain why you’d want to do so—or what the benefits are.
Simply put, there are a number of benefits you can receive by setting up alerts for long running transactions—simply because long-running transactions can either cause so many potential (locking/blocking) problems in many cases and/or because the presence of long-running transactions on many severs can be an indication of problems or issues. For example, I had a similar alert set up on a client’s box—which recently started sending alerts (during the middle of the night).
A bit of investigation revealed that the culprit was a batch-processing job being run late at night to get rid of non-valid users on an e-commerce site. The job in question typically ran for about 2-3 minutes most nights (and had done so for a very long time). But, recent changes to the logic to determine non-valid users had included a JOIN against a site-log tracking table to review which pages each user had visited as part of establishing their validity. That table, in turn, was missing some key indexes, had been experiencing serious growth, and had—in turn—caused this job to start taking over 3 hours because of the missing index and large amount of data. All of which was made ‘visible’ by virtue of a long-running transaction alert. (Consequently, and with a bit of index tuning, the job was back down to running at a more respectable time of 5-7 minutes every night.)
The point, however, is that the process and server in question wasn’t a huge priority or concern—but aspects of these related operations HAD managed to start bloating to the point where they were impacting other operations. And a simple alert for long-running transactions made this all visible.