Ricky Valencia | SQL Conjuror

No comments

SQL Server – Check Latest Transaction Log Backup

By Enrique 13/03/2017 30/04/2020 SQL SERVER, Database Administration

To know what the last transaction log backup of your database was, execute the T-SQL script below.

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS ServerName,

msdb.dbo.backupset.database_name AS DatabaseName,

MAX(msdb.dbo.backupset.backup_finish_date) AS Last_TLog_Backup_Date

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'L'

GROUP BY

msdb.dbo.backupset.database_name

ORDER BY

msdb.dbo.backupset.database_name

Facebook

Google

Twitter

No comments

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

By Enrique 13/03/2017 30/04/2020 SQL SERVER, Database Administration, T-SQL, Uncategorized

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

SQL Server – TSQL Script to Check Job Run Status

By Enrique 11/03/2017 30/04/2020 SQL SERVER, Database Administration

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

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

By Enrique 09/03/2017 30/04/2020 SQL SERVER, Performance Optimization

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

SQL Server – Get CPU Usage History for last 256 minutes

By Enrique 15/11/2016 30/04/2020 SQL SERVER, Troubleshooting

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.

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

By Enrique 29/10/2016 30/04/2020 SQL SERVER, Troubleshooting

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.

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

By Enrique 06/09/2016 30/04/2020 SQL SERVER, Troubleshooting

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.

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

By Enrique 05/09/2016 30/04/2020 SQL SERVER, Database Administration

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

SQL Server – View Recovery Model using T-SQL

By Enrique 30/08/2016 30/04/2020 SQL SERVER, Database Administration

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

The following table summarizes the three recovery models.

SQL SERVER – LCK_M_XXX – Wait Type

By Enrique 28/08/2016 30/04/2020 SQL SERVER, Performance Optimization

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.

Facebook

Google

Twitter

Tag: Ricky Valencia

SQL Server – Check Latest Transaction Log Backup

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

SQL Server – TSQL Script to Check Job Run Status

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

SQL Server – Get CPU Usage History for last 256 minutes

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

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

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

SQL Server – View Recovery Model using T-SQL

SQL SERVER – LCK_M_XXX – Wait Type

Recent Posts

Archives

Categories

Recent Posts

Archives

Categories

Tag Cloud