Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

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
  • Posted in SQL Server Agent, Tips and Tricks | Tagged: , , , , | 2 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 »

    Solution : Cannot add new node – Rule "SQL Server Database Services feature state" failed.

    Posted by blakhani on September 11, 2014


    While deploying SQL Server 2014 cluster in my lab I ran into this problem when I was trying to add second node (Node 2) to SQL cluster.

    —————————
    Rule Check Result
    —————————
    Rule "SQL Server Database Services feature state" failed. The SQL Server Database Services feature failed when it was initially installed. The feature must be removed before the current scenario can proceed.
    —————————
    OK
    —————————

    Above error means that when I installed SQL Server on first cluster node, it didn’t install properly. There were some errors while creating cluster and due to that AddNode is blocked. In my case also this is true because I had an issue with SQL Server Network Name Resource and it didn’t come online earlier. Below was the original error on Node 1

    Cluster network name resource ‘SQL Network Name (Balmukund)’ cannot be brought online. The computer object associated with the resource could not be updated in domain ‘MyDomain.com’ for the following reason:
    Unable to update password for computer account.

    The text for the associated error code is: Access is denied.

    The cluster identity ‘WinCluster$’ may lack permissions required to update the object. Please work with your domain administrator to ensure that the cluster identity can update computer objects in the domain.

    I fixed above error by giving proper permission to cluster computer object on Balmukund (which was the network name of SQL) After fixing that I created SQL Server and SQL Agent manually. Sometime it may also happen that SQL Agent didn’t come online on Node 1 due to some failure and it was fixed after setup was completed.

    So, if we see above rule failure then we should go to the first node and check setup log files to see if the installation succeeded or failed. If the first node installation had some failures which were fixed later then we should do a repair of the installation. Repair option can be found under the "Maintenance" page in SQL Server Installation Center (setup).  This action will clear the failure state and allow the node addition action.

    If you are lazy like me to do repair then you can also check registry key
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\ConfigurationState

    If you see value as 2 for any component then there were some failures of those components. In most of the cases since issue would be with SQL Server or SQL Agent, we might see 2 for MPT_AGENT_CORE_CNI, SQL_Engine_Core_Inst, SQL_FullText_Adv, SQL_Replication_Core_Inst

    Note: that we MUST fix the error on first node before taking shortcut of registry value. Once error is fixed, we can make the value of those component to 1 (means success)

    The highlighted value MSSQL12.SQL2014 might vary based on SQL version and Instance name. The first piece for SQL 2008 it would be MSSQL10, for SQL 2008 R2 it would be MSSQL10_50, for SQL 2012 it would be MSSQL11 and for SQL 2014 it would be MSSQL12. Second piece is instance name (for me instance name is SQL2014)

    Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Installation, Troubleshooting | Tagged: , , , | 2 Comments »

     
    Follow

    Get every new post delivered to your Inbox.

    Join 818 other followers