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.
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.
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.
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.
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!
