Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,552,617 hits
  • Select GETDATE()

    January 2019
    M T W T F S S
    « Apr    
     123456
    78910111213
    14151617181920
    21222324252627
    28293031  
  • Advertisements

Archive for the ‘Tips and Tricks’ Category

Tips and Tricks : SQL Agent Job history data missing?

Posted by blakhani on September 18, 2014


I have many good friend who call me only when they have SQL Server related problem to solve. I really love you guys (in a manly kind of way!) for remembering me, at least during problem time.

One fine day my DBA friend called me and said that they have a data missing problem. There is a job which is suppose to move the data from one table to another. Application team reported that job didn’t run on one specific time as per their data calculation. As per their logic they pull hourly data from Table1 to Table2. They have observed that 1 hour data is missing. When they looked into job history they don’t see any evidence of job history. Oldest record for this job is 2 hours back.

My immediate question was – do you have many jobs running on the server? As expected, answer was Yes! They had may such data movement jobs and few of them run every 5 minutes. I asked him to run below command

select count(*) from msdb.dbo.sysjobhistory

and answer was 1000. And I said – that is THE problem! The SQL Agent Job system limits the total number of job history on per job basis as well as overall basis. This information is stored in the MSDB database. I asked him to open SQL Server Agent Properties and go to History page. Over there Modify the ‘Maximum job history log size (rows)’ and ‘Maximum job history rows per job’ to suit, or change how historical job data is deleted based on its age.

I also told that this won’t give you back the history, but it’ll help with your future queries! He was happy with the answer.

Later I did more research and found that this can also be done using below T-SQL command

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=1000, 
        @jobhistory_max_rows_per_job=100
GO 

If there is a need to purge the data manually using UI then we can use “Remove Agent History” checkbox and define the duration. The equivalent of that below

EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date='2014-09-14T09:02:37'
GO

We need to provide the oldest date in T-SQL

Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in SQL Server Agent, Tips and Tricks | Tagged: , , , , | 6 Comments »

    Tips and Tricks : Why TempDB files lesser than Configured?

    Posted by blakhani on September 16, 2014


    TempDB is one of the special system database which has special characteristics. Recently I was trying to simulate a problem and demonstrate the solution to tempdb PFS page contention. While doing something I realized that number of tempDB files were only 2 as opposed to 4 which I configured.

    Here is the query which I used to find configured files vs actual files.

    use master
    select    name, physical_name 
    from    sys.master_files
    where    database_id = 2
    go
    use tempdb
    go
    Select    name,physical_name 
    from    sys.database_files
    

     

    Then I looked into the SQL Server ERRORLOG and found below messages during startup.

    2014-09-16 17:45:56.330 spid11s      Starting up database ‘tempdb’.

    2014-09-16 17:45:57.380 spid11s      Error: 5123, Severity: 16, State: 1.

    2014-09-16 17:45:57.380 spid11s      CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

    2014-09-16 17:45:57.380 spid11s      Error: 5123, Severity: 16, State: 1.

    2014-09-16 17:45:57.380 spid11s      CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

    Why this happened? Well. I had done some goof-up and pointed two instances to use same file. Which means that it is expected behavior that if tempdb database is able to initialize the primary files, it would come up and use only two files. This would also happen if there is a problem with the location of newly added files.

    2014-09-16 15:31:14.290 spid11s      Starting up database ‘tempdb’.

    2014-09-16 15:31:14.890 spid11s      Error: 5123, Severity: 16, State: 1.

    2014-09-16 15:31:14.890 spid11s      CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

    2014-09-16 15:31:14.890 spid11s      Error: 5123, Severity: 16, State: 1.

    2014-09-16 15:31:14.890 spid11s      CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

    To repro above, I have renamed the folder TempDB to something else. Note that same thing might happen if we have space issues to create new files.

     

    Hope this helps.

     

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Tips and Tricks | Tagged: , , | 3 Comments »

    Tips and Tricks: One Query – Multiple Servers – One Result Set

    Posted by blakhani on September 9, 2014


    How many times you have been into a situation where you need to run one query across many servers? I have asked his question from one of the DBA and his answer was I will create a linked server and then modify the query to refer linked server. Well this needs modification to server configuration and most of the production server don’t allow changed without change request.

    The better approach would be to use an under-estimated feature of SQL Server Management Studio called as Multiserver query. This can be done via by creating a group and registering one or more registered servers within the groups. Once group is created then we can query the complete group and the result format can be customized. Pinal (b|t) has written a blog here. This is a extension of that blog in little more details. 

    To get to below option we need to open “Registered Servers” Window. If you don’t see it hit Ctrl+Alt+G or go to View > Registered Servers. I have registered two server under a group called “Fake Production”. We can created your own hierarchy to logically group the servers. Display Name can also be changed in this windows. I have given a name as “HiddenName” to one of my instance for display purpose. Effectively I have 4 Servers under “Local Server Groups” and 2 Servers under “Fake Production Group”

    We can right click on any of the node and choose “New Query” as shown below.

    First thing you would notice is that the color of status bar is changed. Also notice that we can see how many servers are connected out of total servers. For demo purpose I have used different account to connect each server that’s why we are seeing “<various logins>”

    Color Customization can be done by going to Tools > Option > Text Editor > Editor Tab and Status Bar as shown below.

     

    From above, we can also change other setting for status bar but they are not specified to multi-server.

    If we ran a query in this query window, it would run on all the servers and give results.

    We are seeing same number of databases on 1st and 4th row in my lab because they are same servers. dot (.) and (local) both means local server.

    This result can be customized using “Tools > Option > Query Results > SQL Server > Multiserver Results” as below

    Note: We can’t join two tables across the servers using multiserver query feature.

    Hope this would help in getting some quick reports from various servers.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Tips and Tricks | Tagged: , , | Leave a Comment »