April 2026
M	T	W	T	F	S	S
	1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

Archive for 2014

Help : Who dropped login from server?

Posted by blakhani on September 23, 2014

Long time ago I blogged about Who dropped objects from database and it was very useful for many readers. Few days back someone asked me via Facebook – “Is it possible to find who dropped login from server?” Initially I check the “Schema Change History” report but it was not showing expected output. I went ahead and looked into default trace and found that there are some entries when I delete a login. Since the data was available in default trace, I have written below script to read those files and show them in readable format.

DECLARE @enable INT

SELECT TOP 1 @enable = convert(INT, value_in_use)
FROM sys.configurations
WHERE NAME = 'default trace enabled'

IF @enable = 1 --default trace is enabled
BEGIN
    DECLARE @d1 DATETIME;
    DECLARE @diff INT;
    DECLARE @curr_tracefilename VARCHAR(500);
    DECLARE @base_tracefilename VARCHAR(500);
    DECLARE @indx INT;

    SELECT @curr_tracefilename = path
    FROM sys.traces
    WHERE is_default = 1;

    SET @curr_tracefilename = reverse(@curr_tracefilename)

    SELECT @indx = PATINDEX('%\%', @curr_tracefilename)

    SET @curr_tracefilename = reverse(@curr_tracefilename)
    SET @base_tracefilename = LEFT(@curr_tracefilename, len(@curr_tracefilename) - @indx) + '\log.trc';

    SELECT TargetLoginName 'Login which was dropped'
        ,StartTime 'When'
        ,LoginName 'who dropped'
        ,ApplicationName 'from where'
        ,HostName 'machine name'
    FROM::fn_trace_gettable(@base_tracefilename, DEFAULT)
    WHERE EventClass IN (
            104                    -- SQL Login would have "Audit Addlogin Event"
            ,105                -- Windows Login would have "Audit Login GDR Event"
            )
        AND EventSubclass = 2 --drop
END
ELSE
Select 'Default trace is not enabled'

Hope this would help some DBA to find culprits and punish them.

Cheers,
Balmukund Lakhani
Twitter @blakhani
Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in Script, Security | Tagged: audit addlogin, audit login GDR, default trace, who dropped login | 7 Comments »

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

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in SQL Server Agent, Tips and Tricks | Tagged: Delete SQL Job History, Job History Missing, Missing Job History, sql job history disappears, SQL Job History Query | 9 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 AlwaysOn – Paperback, Kindle

Posted in Tips and Tricks | Tagged: SQL, sql server, tempdb | 3 Comments »

« Previous Entries

Next Entries »

Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Blog Stats

Select GETDATE()

Subscribe

Follow Blog via Email

Date < getdate()

Select location from world

Meta

Archive for 2014

Tips and Tricks : SQL Agent Job history data missing?

Tips and Tricks : Why TempDB files lesser than Configured?