I encountered the error below during Transaction Replication setup between SQL Server 2008 R2 (Publication) and SQL Server 2012 (Subscriber). When you hit this error, what you need to do is to set up the Subscriber from SQL Server 2012 Management studio in the ‘Subscriber’ database server.
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
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
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
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
Tick Show all columns and choose the following columns
Click Column Filters
Filter DatabaseName and Duration (>= 500ms)
Click Run
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.
We have a 3-Node AlwaysOn Availability Group setup. I wanted to copy all SQL Agent jobs from the primary replica to the secondary replica. The easiest way is to script out all the jobs at the same time. The following are the steps to do that.
Go to View > Object Explorer Details.
On the Object Explorer Details window, click SQL Server Agent.
Click Jobs.
Highlight all the jobs to be copied. Right-click > Script Job as > Create To >
s
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.
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.
