SQL Server – Check SQL Server Error Logs via T-SQL

Below is a script​​ to check SQL Server Error​​ logs.

 

 

declare​​ @Time_Start​​ datetime;

declare​​ @Time_End​​ datetime;

set​​ @Time_Start=getdate()-2;

set​​ @Time_End=getdate();

-- Create the temporary table

CREATE​​ TABLE​​ #ErrorLog​​ (logdate​​ datetime

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ,​​ processinfo​​ varchar(255)

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ,​​ Message​​ varchar(MAX))

-- Populate the temporary table

INSERT​​ #ErrorLog​​ (logdate,​​ processinfo,​​ Message)

 ​​ ​​​​ EXEC​​ master.dbo.xp_readerrorlog​​ 0,​​ 1,​​ null,​​ null​​ ,​​ @Time_Start,​​ @Time_End,​​ N'desc';

-- Filter the temporary table

SELECT​​ LogDate,​​ Message​​ FROM​​ #ErrorLog

WHERE​​ (Message​​ LIKE​​ '%error%'​​ OR​​ Message​​ LIKE​​ '%failed%')​​ AND​​ processinfo​​ NOT​​ LIKE​​ 'logon'

ORDER​​ BY​​ logdate​​ DESC

-- Drop the temporary table​​ 

DROP​​ TABLE​​ #ErrorLog

 

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL Server – TSQL Script to Check Job Run Status

Below is a T-SQL script to check SQL job​​ run​​ status.

 

SET​​ NOCOUNT​​ ON

--Checking for SQL Server verion

IF​​ CONVERT(tinyint,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1)))​​ <>​​ 8

BEGIN

---This is for SQL 2k5 and SQL2k8 servers

SET​​ NOCOUNT​​ ON

SELECT​​ Convert(varchar(20),SERVERPROPERTY('ServerName'))​​ AS​​ ServerName,

j.name​​ AS​​ job_name,

CASE​​ j.enabled​​ WHEN​​ 1​​ THEN​​ 'Enabled'​​ Else​​ 'Disabled'​​ END​​ AS​​ job_status,

CASE​​ jh.run_status​​ WHEN​​ 0​​ THEN​​ 'Error Failed'

WHEN​​ 1​​ THEN​​ 'Succeeded'

WHEN​​ 2​​ THEN​​ 'Retry'

WHEN​​ 3​​ THEN​​ 'Cancelled'

WHEN​​ 4​​ THEN​​ 'In Progress'​​ ELSE

'Status Unknown'​​ END​​ AS​​ 'last_run_status',

ja.run_requested_date​​ as​​ last_run_date,

CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration​​ *​​ 9​​ +​​ jh.run_duration​​ %​​ 10000​​ *​​ 6​​ +​​ jh.run_duration​​ %​​ 100​​ *​​ 10)​​ /​​ 216e4,108)​​ AS​​ run_duration,

ja.next_scheduled_run_date,

CONVERT(VARCHAR(500),jh.message)​​ AS​​ step_description

FROM

(msdb.dbo.sysjobactivity ja​​ LEFT​​ JOIN​​ msdb.dbo.sysjobhistory jh​​ ON​​ ja.job_history_id​​ =​​ jh.instance_id)

join​​ msdb.dbo.sysjobs_view​​ j​​ on​​ ja.job_id​​ =​​ j.job_id

WHERE​​ ja.session_id=(SELECT​​ MAX(session_id) ​​​​ from​​ msdb.dbo.sysjobactivity)​​ ORDER​​ BY​​ job_name,job_status

END

ELSE

BEGIN

--This is for​​ SQL2k servers

SET​​ NOCOUNT​​ ON

DECLARE​​ @SQL​​ VARCHAR(5000)

--Getting information from sp_help_job to a temp table

SET​​ @SQL='SELECT job_id,name AS job_name,CASE enabled WHEN 1 THEN ''Enabled'' ELSE ''Disabled'' END AS job_status,

CASE last_run_outcome WHEN 0 THEN ''Error Failed''

 WHEN 1 THEN ''Succeeded''

 WHEN 2 THEN ''Retry''

 WHEN 3 THEN ''Cancelled''

 WHEN 4 THEN ''In Progress'' ELSE

 ''Status Unknown'' END AS ​​ last_run_status,

CASE RTRIM(last_run_date) WHEN 0 THEN 19000101 ELSE​​ last_run_date END last_run_date,

CASE RTRIM(last_run_time) WHEN 0 THEN 235959 ELSE last_run_time END last_run_time,​​ 

CASE RTRIM(next_run_date) WHEN 0 THEN 19000101 ELSE next_run_date END next_run_date,​​ 

CASE RTRIM(next_run_time) WHEN 0 THEN 235959 ELSE next_run_time END next_run_time,

last_run_date AS lrd, last_run_time AS lrt

INTO ##jobdetails

FROM OPENROWSET(''sqloledb'', ''server=(local);trusted_connection=yes'', ''set fmtonly off exec msdb.dbo.sp_help_job'')'

exec​​ (@SQL)

--Merging run date & time format, adding run duration and adding step description

select​​ Convert(varchar(20),SERVERPROPERTY('ServerName'))​​ AS​​ ServerName,jd.job_name,jd.job_status,jd.last_run_status,

CONVERT(DATETIME,RTRIM(jd.last_run_date))​​ +(jd.last_run_time​​ *​​ 9​​ +​​ jd.last_run_time​​ %​​ 10000​​ *​​ 6​​ +​​ jd.last_run_time​​ %​​ 100​​ *​​ 10)​​ /​​ 216e4​​ AS​​ last_run_date,

CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration​​ *​​ 9​​ +​​ jh.run_duration​​ %​​ 10000​​ *​​ 6​​ +​​ jh.run_duration​​ %​​ 100​​ *​​ 10)​​ /​​ 216e4,108)​​ AS​​ run_duration,

CONVERT(DATETIME,RTRIM(jd.next_run_date))​​ +(jd.next_run_time​​ *​​ 9​​ +​​ jd.next_run_time​​ %​​ 10000​​ *​​ 6​​ +​​ jd.next_run_time​​ %​​ 100​​ *​​ 10)​​ /​​ 216e4​​ AS​​ next_scheduled_run_date,

CONVERT(VARCHAR(500),jh.message)​​ AS​​ step_description

from​​ (##jobdetails jd ​​ LEFT​​ JOIN ​​​​ msdb.dbo.sysjobhistory jh​​ ON​​ jd.job_id=jh.job_id​​ AND​​ jd.lrd=jh.run_date​​ AND​​ jd.lrt=jh.run_time)​​ where​​ step_id=0​​ or​​ step_id​​ is​​ null

order​​ by​​ jd.job_name,jd.job_status

--dropping the temp table

drop​​ table​​ ###jobdetails

END

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL Server – Setup SQL Profiler to Capture Costly Queries / Stored Procedures

  • Click Tools >​​ SQL Server Profiler

 

 

 

  • Connect to the instance

 

 

 

  • On this page, perform the following:

    • Indicate​​ Trace name

    • Choose ​​ “Tuning” template

    • Save to file

    • Set​​ 1000​​ maximum file size

    • Set trace stop time (optional. You may stop the trace anytime)

 

 

 

  • Go to​​ Events​​ Selection​​ tab

C:\Users\Ricky\AppData\Local\Temp\SNAGHTML4fc4102.PNG

 

 

  • Tick​​ Show all columns​​ and choose the following columns

 

 

 

  • Click​​ Column Filters

 

 

 

 

 

  • Filter​​ DatabaseName​​ and​​ Duration​​ (>= 500ms)

 

 

 

  • Click​​ Run

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL Server – Get CPU Usage History for last 256 minutes

The other day, I was asked by our Application Team Lead to check why the CPU usage is very high in the Production DB server. It remained consistently high throughout the day. He asked whether it is coming from SQL Server or other processes.

 

 

Below is a useful script to review the CPU usage history for last 256 minutes (This will work on SQL Server 2008 and up)​​ 

 

 

DECLARE​​ @ts_now​​ bigint​​ =​​ (SELECT​​ cpu_ticks/(cpu_ticks/ms_ticks)​​ FROM​​ sys.dm_os_sys_info​​ WITH​​ (NOLOCK));​​ 

 

SELECT​​ TOP(256)​​ SQLProcessUtilization​​ AS​​ [SQL Server Process CPU Utilization],​​ 

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ SystemIdle​​ AS​​ [System Idle Process],​​ 

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 100​​ -​​ SystemIdle​​ -​​ SQLProcessUtilization​​ AS​​ [Other Process CPU Utilization],​​ 

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ DATEADD(ms,​​ -1​​ *​​ (@ts_now​​ -​​ [timestamp]),​​ GETDATE())​​ AS​​ [Event Time]​​ 

FROM​​ (​​ 

  ​​​​ SELECT​​ record.value('(./Record/@id)[1]',​​ 'int')​​ AS​​ record_id,​​ 

 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]',​​ 'int')​​ 

AS​​ [SystemIdle],​​ 

 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',​​ 

'int')​​ 

AS​​ [SQLProcessUtilization],​​ [timestamp]​​ 

  ​​​​ FROM​​ (​​ 

SELECT​​ [timestamp],​​ CONVERT(xml,​​ record)​​ AS​​ [record]​​ 

FROM​​ sys.dm_os_ring_buffers​​ WITH​​ (NOLOCK)

WHERE​​ ring_buffer_type​​ =​​ N'RING_BUFFER_SCHEDULER_MONITOR'​​ 

AND​​ record​​ LIKE​​ N'%<SystemHealth>%')​​ AS​​ x​​ 

  ​​​​ )​​ AS​​ y​​ 

ORDER​​ BY​​ record_id​​ DESC​​ OPTION​​ (RECOMPILE);

 

 

Now, if the value in “Other Process CPU Utilization (%)” column is higher​​ than​​ “SQL Server Process CPU”, then you may​​ ask​​ your sys admins to investigate the cause. But in our case, it​​ was indicated​​ clearly that the cause of high CPU usage was coming from SQL Server Process.​​ Below is the output of the​​ script.

 

 

 

After seeing the results, I investigated from the SQL queries perspective and found out that there are few​​ expensive queries that are​​ causing high CPU usage. ​​ How did I find out? That would be the subject of another blog.​​ 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL Server – Error: 5042 – The file ‘MyFileName’ cannot be removed because it is not empty.

I was trying to delete a data file but I encountered the error below.

 

Error: 5042 - The file 'MyFileName' cannot be removed because it is not empty.

 

 

 

I executed the script below​​ to empty the file.

 

DBCC​​ SHRINKFILE(First_Data_File,EMPTYFILE​​ )

 

 

After the file has been emptied, I was able to remove the data file.

 

ALTER​​ DATABASE​​ DBNAME​​ REMOVE​​ FILE​​ First_File;

 

 

 

EMPTYFILE​​ Migrates all data from the specified file to other files in the same filegroup. In other words, EmptyFile will migrate the data from the specified file to other files in the same filegroup. Emptyfile assures you that no new data will be added to the file.The file can be removed by using the ALTER DATABASE statement.

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL Server – Agent Job Fails with Error: Unable to connect to SQL Server ‘(local)’. The step failed

I was​​ checking one of our client’s DB server for the first time.​​ I noticed a​​ couple of their SQL Agent Jobs have been failing due to the error “Unable to connect to SQL Server ‘(local)’”

 

 

 

The reason for this error is because the Database (drop down box) to run the step from is empty.

 

 

 

The solution is to choose the relevant database to run the job step against.

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL Server – Check Last Restart Date and Time of SQL Server

To check the date and time SQL Server last restarted, execute the T-SQL script below.

 

SELECT​​ sqlserver_start_time​​ FROM​​ sys.dm_os_sys_info

 

 

 

 

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL Server – View Recovery Model using T-SQL

Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

 

Use the script below to view the database recovery model.

 

SELECT​​ name​​ AS​​ [Database Name],

recovery_model_desc​​ AS​​ [Recovery Model]

FROM​​ sys.databases

GO

 

The following table summarizes the three recovery models.

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL SERVER – LCK_M_XXX – Wait Type

Locking is a mechanism used by the SQL Server Database Engine to synchronize access by multiple users to the same piece of data, at the same time. In simpler words, it maintains the​​ integrity of data by protecting (or preventing) access to the database object.

 

LCK_M_BU
Occurs when a task is waiting to acquire a Bulk Update (BU) lock.

 

LCK_M_IS
Occurs when a task is waiting to acquire an Intent Shared (IS) lock.

 

LCK_M_IU
Occurs when a​​ task is waiting to acquire an Intent Update (IU) lock.

 

LCK_M_IX
Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock.

 

LCK_M_S
Occurs when a task is waiting to acquire a Shared lock.

 

LCK_M_SCH_M
Occurs when a task is waiting to acquire a Schema Modify lock.

 

LCK_M_SCH_S
Occurs when a task is waiting to acquire a Schema Share lock.

 

LCK_M_SIU
Occurs when a task is waiting to acquire a Shared With Intent Update lock.

 

LCK_M_SIX
Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock.

 

LCK_M_U
Occurs when a task is waiting to acquire an Update lock.

 

LCK_M_UIX
Occurs when a task is waiting to acquire an Update With Intent Exclusive lock.

 

LCK_M_X
Occurs when a task is waiting to acquire an Exclusive lock.

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

SQL Server – Resolve Error 5030 to Rename Database

When you want to rename your database and you hit the error below, you need to set the database to​​ Single User Mode. After you rename your database, then you set the database back to Multi-User mode.​​

Msg 5030, Level 16, State 2, Line 1

The database could not be​​ exclusively locked to perform the operation

Follow the steps​​ below to rename your database.​

  1. Set the database to single-mode
ALTER DATABASE OLD_DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

2. Rename the Database

ALTER DATABASE OLD_DBName MODIFY NAME = NEW_DBNAME;

3. Set the database to Multi-user mode

ALTER DATABASE NEW_DBNAME SET MULTI_USER WITH ROLLBACK IMMEDIATE;

You may check out this blog post where I show a better way to rename a database.

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter