Fix Your SQL Server

I wish that there is a switch which I can press when I am panicking and suddenly all the problems around me goes away. Well, there is no such switch available but there is indeed very similar solution. If you are facing problems with your SQL – continue to read on.

Here are the few of the questions I received every day and if you are facing the same problems than you are in indeed lucky as I know how to resolve them efficiently and quickly.

  • Your SQL queries are running slow.
  • Your database is corrupted and now you want to recover your data.
  • You just want to check that if your server is secure and protect it from intrusion.
  • You accidentally deleted your data and now have no idea how to recover it back.
  • You want to make sure that your server can handle your soon to be exploding business growth.
  • You want to setup your database backup and recovery strategy so your data is always safe and secure.
  • You want to set up your new SQL Server and you want to make sure that you have it setup right from the day 1.
  • You have plenty of data and have no clue how to gain new insight from it as well as understand business performance.
  • You want your data to bend, twist, fold and take the shape that satisfies your business needs and accelerate your business growth.
  • Your SQL Server was earlier running fine and now suddenly you it is very slow, unresponsive or just does not behave as it used earlier.
  • You are managing database designed few years ago and now want to create road map to to efficiently use all the goodness released in the latest version of SQL Server.

If you have faced any of the situation above and if you need help, I have a solution for you. My friends at Linchpin People are expert at Anything and Everything about SQL. Linchpin People are database coaches and wellness experts for a data driven world. Founders of Linchpin People have been focusing on the Microsoft SQL Server ecosystem for decades, helping companies succeed with data through technology, process, leadership, and most of all people.

You can reach out to me at pinal @ sqlauthority.com and I will hold your hand till you find the right expert at Linchpin People, who will see through that your database problem is solved. Alternatively you can directly reach out to them, if you prefer, just mention that you know me and Linchpin People will make sure that your problem is resolved on priority. Do not forget to check out their famous AnythingSQL and WellDBA service.

About these ads

44 thoughts on “Fix Your SQL Server

  1. Pingback: SQL SERVER – Transaction Log Full – Transaction Log Larger than Data File – Notes from the Field #001 | Journey to SQL Authority with Pinal Dave

  2. Pingback: SQL SERVER – Root Cause to Performance Problems – Notes from the Field #002 | Journey to SQL Authority with Pinal Dave

  3. Pingback: SQL SERVER – vCPUs – How Many Are Too Many CPU for SQL Server Virtualization ? – Notes from the Field #003 | Journey to SQL Authority with Pinal Dave

  4. Pingback: SQL SERVER – SQL Server Configuration Checking – A Must Do for Every DBA – Notes from the Field #004 | Journey to SQL Authority with Pinal Dave

  5. Hello,
    I facing sp long execution time getting records SkuSearch table having 2 crore records
    It takes 30 sec to execute bellow SP but when I write 3 query given bellow on separate panel with hard coded value it is executed within 1 sec.
    tell me what was the problem?

    ALTER PROCEDURE [dbo].[usp_getRelatedPartNumber] –exec usp_getRelatedPartNumber ’8971A001′,’canon’,’13803031416′,’44103105′
    @strkey varchar(50),
    @mfr varchar(150),
    @strnsn varchar(50),
    @unspsc varchar(50)
    AS
    BEGIN
    set nocount on
    declare @strSubkey varchar(50)
    if(@strkey!=”)
    set @strSubkey=SUBSTRING(@strkey,1,((LEN(@strkey)/3)*2 ))
    –print @strSubkey
    –==========Start Retrive top 30 part number,Mfr Name =============–
    select top(30) SkuName,ProdName,ProdId,UPC,UNSPSC from dbo.SkuSearch
    where SkuName like ”+@strSubkey+’%’ and ProdName!=@mfr –order by NEWID()
    –==========End Retrive top 30 part number randomlly=============–
    –==========Start Retrive Valid Mfr Name based on input string=============–
    select top 1 ProdName,ProdId from dbo.Product
    where ProdName = @mfr
    –==========End Retrive Valid Mfr Name based on input string=============–
    –==========Start Retrive Valid part number desc based on input strings=============–
    select Top 1 SkuDesc from dbo.SkuSearch where ProdName=@mfr and SkuName=@strkey and UPC=ISNULL(@strnsn,’0′) and UNSPSC=ISNULL(@unspsc,’0′) order by SkuDesc desc
    –==========End Retrive Valid part number desc based on input strings=============–
    set nocount off
    END

  6. Pingback: SQL SERVER – Finding Frequently Running Query and Elapsed Time – Notes from the Field #005 | Journey to SQL Authority with Pinal Dave

  7. Pingback: SQL SERVER – Optimal Memory Settings for SQL Server – Notes from the Field #006 | Journey to SQL Authority with Pinal Dave

  8. Pingback: SQL SERVER – SQL Server Alerts and How to Use Them – Notes from the Field #007 | Journey to SQL Authority with Pinal Dave

  9. Create Person table
    PersonID (PK) INT NOT NULL
    FirstName VARCHAR(25) NOT NULL
    LastName VARCHAR(25) NOT NULL
    DateOfBirth SMALLDATETIME NOT NULL
    IsActive BIT NOT NULL

    Given the above table, write SQL statements that return:

    A. PersonID, FirstName and LastName of currently ACTIVE people sorted by LastName [A-Z] and FirstName [Z-A]

    B. DateOfBirth of the oldest person

    C. The number of people born in each month (NOTE: you need not worry about the year, only the month)

    • —————————————
      Create the table in SQL like so:
      —————————————
      CREATE TABLE [dbo].[Person]
      (
      [PersonID] [int] IDENTITY(1,1) NOT NULL,
      [FirstName] [varchar](25) NOT NULL,
      [LastName] [varchar](25) NOT NULL,
      [DateOfBirth] [smalldatetime] NOT NULL,
      [IsActive] [bit] NOT NULL,
      CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
      (
      [PersonID] ASC
      )
      WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY];

      —————————————
      Insert some data like so:
      —————————————
      INSERT INTO Person VALUES(‘Fred’,’Flintstone’,’1900-04-25′,1);
      INSERT INTO Person VALUES(‘Wilma’,’Flintstone’,’1903-06-12′,1);
      INSERT INTO Person VALUES(‘BamBam’,’Flintstone’,’1925-09-13′,0);
      INSERT INTO Person VALUES(‘Barney’,’Rubble’,’1901-06-23′,1);

      —————————————
      Run the requested queries like so:
      —————————————
      A)
      SELECT PersonID, FirstName, LastName
      FROM Person
      WHERE isActive = 1
      ORDER BY LastName ASC, FirstName DESC;

      B)
      SELECT TOP 1 * FROM Person ORDER BY DateOfBirth ASC;

      C)
      SELECT Month(DateOfBirth), COUNT(*) AS ‘Nbr People Born’
      FROM Person
      GROUP BY Month(DateOfBirth)
      ORDER BY Month(DateOfBirth) ASC;

      -or-

      SELECT DATENAME(month,DateOfBirth), COUNT(*) AS ‘Nbr People Born’
      FROM Person
      GROUP BY DATENAME(month,DateOfBirth)
      ORDER By 2 DESC;

  10. Pingback: SQL SERVER – Overall SQL Server Performance – Not Just Query Performance – Notes from the Field #008 | Journey to SQL Authority with Pinal Dave

  11. Pingback: SQL SERVER – Tools for Proactive DBAs – Central Management Server – Notes from the Field #009 | Journey to SQL Authority with Pinal Dave

  12. Hello Sir,
    I need to shrink or reduce my database of 180gb. But when i try to shrink my database with 5Gb increments it is taking 4 hours for each iteration. What is the alternative to reduce the space used in db? Please advice. I am a developer with knowledge of sql server

  13. Pingback: SQL SERVER – Auditors, Passwords and Exceptions – Notes from the Field #010 | Journey to SQL Authority with Pinal Dave

  14. Dear Sir,

    I have installed SQL Server 2007 on windows 7 with following credentials
    Server Name: server
    Authentication: SQL Servef Authentication
    Username: sa
    Password:

    My problem is that the sa password is changed automatically. This occurs frequently. I do not know how to handle this. My applications won’t work if the password is changed.

    I don;t want to use any password for SQL server.

    Kindly, fix this issue.

    -Regards,
    Ravi Dethiya

    • Firstly, never use the SA account for applications. The SA account should always be disabled and not used. Second, there is no SQL Server 2007 :)

      Using no passwords makes no sense, this is not secure and as far as I am aware, impossible to do anyway. You should always have STRONG passwords for all accounts.

  15. Pingback: SQL SERVER – Check for Database Integrity – Notes from the Field #011 | Journey to SQL Authority with Pinal Dave

  16. Pingback: SQL SERVER – Tools for Proactive DBAs – Policy Based Management – Notes from the Field #012 | Journey to SQL Authority with Pinal Dave

  17. Hi,

    I have a query, in management studio we are giving a connection string (host name for default instance) it just gets connected, how it is happening, is this instance name saved in file, where it goes and read this file or what actually happens in background when we press connect? can some one please explain?

  18. Pingback: SQL SERVER – Virtualized SQL Server Performance and Storage System – Notes from the Field #013 | Journey to SQL Authority with Pinal Dave

  19. Pingback: SQL SERVER – Learning SSIS – Where Do I Start? – Notes from the Field #014 | Journey to SQL Authority with Pinal Dave

  20. Log shipping has been set up new and is running fine. All on a sudden during the after hours, it starts failing. What could be the possible reasons?

  21. Pingback: SQL SERVER – Expanding Your Skills – Notes from the Field #015 | Journey to SQL Authority with Pinal Dave

  22. Pingback: SQL SERVER – Finding the Last Backup for All Databases – Notes from the Field #016 | Journey to SQL Authority with Pinal Dave

  23. Pingback: SQL SERVER – Dude, Where is the SQL Agent Job History? – Notes from the Field #017 | Journey to SQL Authority with Pinal Dave

  24. Hello Pinal,

    I am regular reader of your post and I am getting too much help from your blogs but right now
    I am really stuck to understand of use execute plan in ms sql server 2008. I want to compare two sql queries or statement for better performance is there any further way to know which one is best a sql statement which I have to put in my product.

  25. Pingback: SQL SERVER – Performance Statistic Collection – Notes from the Field #018 | Journey to SQL Authority with Pinal Dave

  26. Pingback: SQL SERVER – SSIS Data Flow Troubleshooting – Part1 – Notes from the Field #019 | Journey to SQL Authority with Pinal Dave

  27. Hi to Every one,i stocked with one query in which i need to insert a Arabic string in table for which i am writing trigger i am using instead of trigger on a table “Emp” when i am reeterview value i am unable to get arabic string instead i am getting “?????” .. when i try to append with N its working fine. so now my probelm is how can i append N for string for example
    set @str=’عربي/عربى’ now i am trying to assign another variable i cannot set @str1=n’عربي/عربى’ which is not possible i need a solution . thanx in advance

  28. Hi,
    I have a scenario where i am currently working Please find below the details.

    > I’m calling a function from stored procedure.

    I would want the below function to be modified as per the new logic mentioned at last.

    //** Function **//

    ALTER FUNCTION [dbo].[fn_XYZ]
    (
    @EndpointName VARCHAR(200)
    )
    RETURNS VARCHAR(200)
    AS
    BEGIN
    DECLARE @Participant VARCHAR(200)
    DECLARE @FirstPart VARCHAR(200)
    –Eliminating -, .,space if exists in begining of string
    IF LEFT(@EndpointName,1) = ‘-’
    SET @EndpointName = REPLACE(@EndpointName,’-’,”)
    IF LEFT(@EndpointName,1) = ‘.’
    SET @EndpointName = REPLACE(@EndpointName,’.’,”)
    IF LEFT(@EndpointName,1) = ‘ ‘
    SET @EndpointName = SUBSTRING(@EndpointName,2,LEN(@EndpointName)-1)

    —Conditions–

    IF @EndpointName LIKE ‘%STC99%’ OR @EndpointName LIKE ‘%STG%’
    BEGIN
    SET @Participant=’Audio’
    END
    ELSE IF @EndpointName LIKE ‘%DMA_VMR%’
    BEGIN
    SET @Participant=’UnKnown’
    END
    ELSE IF ISNUMERIC(REPLACE(@EndpointName,’.’,”)) = 1
    BEGIN
    SET @Participant=’UnKnown’
    END
    ELSE
    BEGIN
    –If there is no space in the string then consider full string as first part
    IF CHARINDEX(‘ ‘,@EndpointName) = 0
    SET @FirstPart=@EndpointName
    ELSE
    –Extract data before first space as First part
    SET @FirstPart=LEFT(@EndpointName,CHARINDEX(‘ ‘,@EndpointName))

    IF @FirstPart LIKE ‘%@%’
    –Extract data before @ in first part as participant
    SET @Participant = LEFT(@FirstPart,CHARINDEX(‘@’,@FirstPart)-1)

    ELSE
    –If there is no @ in first part consider first part as participant
    SET @Participant = @FirstPart

    END
    set @Participant= case when isnumeric(@participant) = 1 then ‘UnKnown’ else @participant end
    RETURN @Participant
    END

    2) After query run

    select distinct dbo.fn_XYZ(EndpointName) Participant,EndpointName from dbo.vw_ABCD
    order by Participant desc

    Results are as below:

    Participant EndpointName
    UnKnown 123 — As per the above condition –
    User1 User1 user5724 — As per the above condition –
    Krupa€™s Krupa€™s BlackBerry — As per the above conditions —
    Varun Varun Malhothra Singh — Require full name —

    Required:
    Varun Malhothra Singh as it is (with spaces) as ‘Participant’ with out making any changes to my above given conditions.

  29. Pingback: SQL SERVER – Index Fragmentation Next Steps – Notes from the Field #020 | Journey to SQL Authority with Pinal Dave

  30. Pingback: SQL SERVER – SSIS Execution Control Using Precedence Constraints – Notes from the Field #021 | Journey to SQL Authority with Pinal Dave

  31. Dear PinalSir,
    I am working on more than 150gb database. I have created clustered index and non-clustered indexes on tables of those fields which are using on order by, group by, where condition and joins. but still my database performance is very poor. please do the needful.

  32. Pingback: SQL SERVER – Say No to DB Data Roles – SQL Security – Notes from the Field #022 | Journey to SQL Authority with Pinal Dave

  33. Pingback: SQL SERVER – Finding Jobs Shrinking Database Files – Notes from the Field #023 | Journey to SQL Authority with Pinal Dave

  34. how to fix microsoft sql server error 233:A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)—> in sql server 2012

  35. Pingback: SQL SERVER – Tools for Proactive DBAs – Central Management Server – Notes from the Field #024 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s