Tag Archives: sql

Two SQL Server Tools I Always Use for Troubleshooting and Performance Analysis

When troubleshooting Microsoft SQL Server environments—especially under pressure—having the right tools can make all the difference. Over time, I’ve found myself consistently relying on two lightweight but incredibly powerful tools that provide insight into what’s happening right now and help build historical context for analysis.

In this post, I’ll walk through the two tools I use the most:

  • sp_WhoIsActive (by Adam Machanic)
  • Extended logging setup using sp_WhoIsActive (inspired by Thomas S. Williams)

sp_WhoIsActive – Real-time insight into SQL Server

👉 http://whoisactive.com/

If you’re working with SQL Server and not using sp_WhoIsActive, you’re making life harder than it needs to be.

This stored procedure gives you a real-time snapshot of what’s happening inside your SQL Server instance:

  • Currently running queries
  • Wait types
  • Blocking chains
  • CPU and memory usage
  • Query execution plans

Example usage:

EXEC sp_WhoIsActive;

But where it really shines is when you start adding parameters:

EXEC sp_WhoIsActive
@get_plans = 1,
@get_blocking_info = 1,
@get_outer_command = 1;

Building History with sp_WhoIsActive Logging

This gives you a much deeper understanding of what’s going on—especially useful when dealing with blocking or long-running queries.

👉 https://thomasswilliams.github.io/sqlserver/2020/12/02/spwhoisactive.html

Real-time data is great—but when troubleshooting intermittent issues, you need history.

That’s where logging sp_WhoIsActive output becomes extremely valuable.

Using the approach described by Thomas S. Williams, you can:

  • Store snapshots of activity in a table
  • Schedule it via SQL Server Agent
  • Build a historical dataset for analysis

This allows you to answer questions like:

  • What happened last night at 02:00?
  • Which queries were causing blocking?
  • Was there unusual CPU or IO activity?

Example setup:

EXEC sp_WhoIsActive
@get_plans = 1,
@destination_table = 'dbo.WhoIsActiveLog';

Once you log data regularly (e.g. every minute), you essentially create your own lightweight monitoring system.


🧠 Why I use these tools

These two tools together give me:

  • 🔎 Real-time visibility when something is wrong
  • 📊 Historical insight when investigating past incidents
  • Fast troubleshooting without needing heavy monitoring tools

They’re simple, flexible, and incredibly effective.

💡 Final thoughts

You don’t always need expensive monitoring solutions to troubleshoot SQL Server effectively.

With sp_WhoIsActive and a simple logging setup, you can get:

  • Deep insight into query behavior
  • Visibility into blocking and waits
  • A historical baseline for performance

If you’re not already using these tools—I highly recommend adding them to your toolbox.


👉 I regularly use setups like this when troubleshooting large queries and performance issues across environments.

Veeam backup and Replication – Failed to call RPC function ‘Vss.TruncateSqlLogs’

Today i have this warning in my Veeam Backup and Replication backup job log, for an SQL server:
Failed to finalize guest processing. Details: Failed to call RPC function ‘Vss.TruncateSqlLogs’: Error code: 0x80004005. Failed to invoke func [TruncateSqlLogs]: Unspecified error. Failed to process ‘TruncateSQLLog’ command. Failed to truncate SQL server transaction logs for instances: . See guest helper log.

I found this in the event viewer on the SQL server:
BACKUP failed to complete the command BACKUP LOG [databasename]. Check the backup application log for detailed messages.

So i check the veeam backup log file on the SQL server:
C:\ProgramData\Veeam\Backup
File VeeamGuestHelper_Lastdatefile.log

Where i see this:

19-12-2022 02:08:56 5080 Database found: [databasename]. Recovery model: 1. Is readonly: false. State: 0.
19-12-2022 02:13:56 5080 WARN Cannot truncate SQL logs for database: [databasename]. Code = 0x80040e31
19-12-2022 02:13:56 5080 WARN Code meaning = IDispatch error #3121
19-12-2022 02:13:56 5080 WARN Source = Microsoft OLE DB Driver for SQL Server
19-12-2022 02:13:56 5080 WARN Description = Query timeout expired
19-12-2022 02:13:56 5080 WARN No OLE DB Error Information found: hr = 0x80004005

What i did to fix this error is that i give the user: NT AUTHORITY\SYSTEM the rights db_backupoperator rights to the specific database, that fault.

And also change this timeout value in regedit:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Veeam\Veeam Backup and Replication]
"SqlExecTimeout"=dword:00000600
"SqlLogBackupTimeout"=dword:00003600
"SqlConnectionTimeout"=dword:00000300

And also under WOW6432Node

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\VeeaM\Veeam Backup and Replication]
"SqlLogBackupTimeout"=dword:00003600
"SqlExecTimeout"=dword:00000600
"SqlConnectionTimeout"=dword:00000300

Then try to running the job again.

MSSQL Backup History Query

To see MSSQL history Query, run this SQL query. It will show all backups between the 1 january 2021 and 1 february 2021, order by Database and then Last backup time.

It will also show if the backup is a CopyOnly job.

;WITH CTE_Backup AS
(
SELECT  database_name,is_copy_only,backup_start_date,type,physical_device_name
       ,Row_Number() OVER(PARTITION BY database_name,BS.type
        ORDER BY backup_start_date DESC) AS RowNum
FROM    msdb..backupset BS
JOIN    msdb.dbo.backupmediafamily BMF
ON      BS.media_set_id=BMF.media_set_id
AND		BS.backup_start_date >= '01/01/2021'
AND		BS.backup_start_date <= '02/02/2021'
)
SELECT      D.name
           ,ISNULL(CONVERT(VARCHAR,backup_start_date),'No backups') AS last_backup_time
           ,D.recovery_model_desc
           ,state_desc,
            CASE WHEN type ='D' THEN 'Full database'
            WHEN type ='I' THEN 'Differential database'
            WHEN type ='L' THEN 'Log'
            WHEN type ='F' THEN 'File or filegroup'
            WHEN type ='G' THEN 'Differential file'
            WHEN type ='P' THEN 'Partial'
            WHEN type ='Q' THEN 'Differential partial'
            ELSE 'Unknown' END AS backup_type
           ,physical_device_name,CTE.is_copy_only
FROM        sys.databases D
LEFT JOIN   CTE_Backup CTE
ON          D.name = CTE.database_name

ORDER BY    D.name,last_backup_time

SQL Agent Job history – T SQL

Here are a Script, that you can use to extract the SQL Agent Job History

select 
 j.name as 'JobName',
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
         as 'RunDurationMinutes'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
 ON j.job_id = h.job_id 
where j.enabled = 1   --Only Enabled Jobs
--and j.name = 'TestJob' --Uncomment to search for a single job
/*
and msdb.dbo.agent_datetime(run_date, run_time) 
BETWEEN '12/08/2012' and '12/10/2012'  --Uncomment for date range queries
*/
order by JobName, RunDateTime desc

This will also tell you the job steps

select 
 j.name as 'JobName',
 s.step_id as 'Step',
 s.step_name as 'StepName',
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
         as 'RunDurationMinutes'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobsteps s 
 ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h 
 ON s.job_id = h.job_id 
 AND s.step_id = h.step_id 
 AND h.step_id <> 0
where j.enabled = 1   --Only Enabled Jobs
--and j.name = 'TestJob' --Uncomment to search for a single job
/*
and msdb.dbo.agent_datetime(run_date, run_time) 
BETWEEN '12/08/2012' and '12/10/2012'  --Uncomment for date range queries
*/
order by JobName, RunDateTime desc

Thanks to Chad Churchwell  – https://www.mssqltips.com/sqlservertip/2850/querying-sql-server-agent-job-history-data/

Implement SQL Server Agent Jobs with AlwaysOn Availability Groups

The function I use fn_hadr_group_is_primary from this post is a UDF (User Defined Function) that we create in the master database. The function gets an Availability Group name as an input parameter and returns a Boolean value indicating weather this instance is a primary replica.

-- fn_hadr_group_is_primary
USE master;
GO
IF OBJECT_ID('dbo.fn_hadr_group_is_primary', 'FN') IS NOT NULL
  DROP FUNCTION dbo.fn_hadr_group_is_primary;
GO
CREATE FUNCTION dbo.fn_hadr_group_is_primary (@AGName sysname)
RETURNS bit
AS
BEGIN;
  DECLARE @PrimaryReplica sysname; 

  SELECT
    @PrimaryReplica = hags.primary_replica
  FROM sys.dm_hadr_availability_group_states hags
  INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
  WHERE ag.name = @AGName;

  IF UPPER(@PrimaryReplica) =  UPPER(@@SERVERNAME)
    RETURN 1; -- primary

    RETURN 0; -- not primary
END; 

Use the fn_hadr_group_is_primary function within a new job step to find if this sql instance is a Primary replica. If this is not a primary replica we issue a stop job request while identifying the current job name using SQL Server Agent Tokens

-- Detect if this instance's role is a Primary Replica.
-- If this instance's role is NOT a Primary Replica stop the job so that it does not go on to the next job step
DECLARE @rc int; 
EXEC @rc = master.dbo.fn_hadr_group_is_primary N'my-ag';

IF @rc = 0
BEGIN;
    DECLARE @name sysname;
    SELECT  @name = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = CONVERT(uniqueidentifier, '$(ESCAPE_NONE(JOBID))'));
    
    EXEC msdb.dbo.sp_stop_job @job_name = @name;
    PRINT 'Stopped the job since this is not a Primary Replica';
END;

See if a database is using Enterprise features of an Microsoft SQL server.

Run this command to see if any database on the SQL Server is using enterprise features

IF OBJECT_ID('tempdb.dbo.##enterprise_features') IS NOT NULL
  DROP TABLE ##enterprise_features

CREATE TABLE ##enterprise_features
  (
     dbname       SYSNAME,
     feature_name VARCHAR(100),
     feature_id   INT
  )

EXEC sp_msforeachdb
N' USE [?] 
IF (SELECT COUNT(*) FROM sys.dm_db_persisted_sku_features) >0 
BEGIN 
   INSERT INTO ##enterprise_features 
    SELECT dbname=DB_NAME(),feature_name,feature_id 
    FROM sys.dm_db_persisted_sku_features 
END '
SELECT *
FROM   ##enterprise_features

 

Downgrade an SQL Server from Enterprise to Standard – Disable compression

If you got an error about compression, when trying to attach the database from an enterprise version to a standard version. You will have to attach the database to an enterprise database, and remove the compression first.

The error:

sg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Msg 909, Level 21, State 1, Line 2
Database 'abc' cannot be started in this edition of SQL Server because part or all of object 'def' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Msg 933, Level 21, State 1, Line 2
Database 'abc' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

The SQL Command to run to disabled compression. It should be run on a query on the database with compression.
(Baware of the database will then fill something extra on the storage, so keep in mind that you need to have space available for that.)

SELECT DISTINCT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);'
FROM sys.partitions p
join sys.objects o
on p.object_id = o.object_id
WHERE o.TYPE = 'u'
and data_compression_desc != 'NONE'
UNION
SELECT 'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);'
FROM sys.partitions p
join sys.objects o
on p.object_id = o.object_id
WHERE o.TYPE = 'u'
and data_compression_desc != 'NONE'

If the database have compression it will output with multiple SQL commands. Run these command against you database aswell

Example: (You should run what you rnu of the script output)

ALTER INDEX ALL ON [dbo].[MSSBatchHistory] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSChangeLogCookies] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlDeletedURL] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlDeletedURLLog] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlHistoryLocal] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlReportCrawlErrors] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlURL] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlURLLog] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSCrawlURLReport] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSDocIDsAvailable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSDocIDsDeleted] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER INDEX ALL ON [dbo].[MSSNextDocID] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSBatchHistory] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSChangeLogCookies] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlDeletedURL] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlDeletedURLLog] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlHistoryLocal] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlReportCrawlErrors] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlURL] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlURLLog] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSCrawlURLReport] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSDocIDsAvailable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSDocIDsDeleted] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [dbo].[MSSNextDocID] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);

 

Shring SQL log file

USE [TestDb]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([TestDb_Log], 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO

 

Where TestDB, is the database name, and TestDB_Log is the SQL Log file name.

Citrix XenApp failed to connect to Data Store

Today i have this problems on one of the Citrix XenApp servers, that the service IMA won’t connect to the Data store.

It failed with this events:

1. Citrix XenApp failed to connect to the Data Store. ODBC error while connecting to the database: S1000 -> [Microsoft][ODBC Microsoft Access Driver] Cannot open database ‘(unknown)’.  It may not be a database that your application recognizes, or the file may be corrupt.

2. Failed to load plugin C:\Program Files (x86)\Citrix\System32\Citrix\IMA\SubSystems\ImaPsSs.dll with error IMA_RESULT_FAILURE

3. Failed to load plugin C:\Program Files (x86)\Citrix\System32\Citrix\IMA\SubSystems\ImaRuntimeSS.dll with error IMA_RESULT_FAILURE

4. Failed to load initial plugins with error IMA_RESULT_FAILURE

5. The Citrix Independent Management Architecture service terminated with service-specific error 2147483649 (0×80000001).

 

But all my others servers in the farm was working just fine, so it must be a locally problems.
The Local Host Cache database, is used to allow a Citrix server in the Citrix Farm to pursue working if the server loses acces to date store temporay. The Local Host Cache database, is an Microsoft Access database, named lmalhc.mdb, and it store ind the Independent Mangement Architecture folder, located: <ProgramFiles>\Citrix\Independent Management Architecture\

 

So the solution to this problems must be to recreate the LHC database, this can be done with this command

dsmaint recreatelhc

But first check that

The datastore is available and function okay, and that the Citrix IMA Service is down :).

The command performs these actions:

Rename the existing Local Host Cache database, create a new one, and the setting PSRequired in registry to 1. Under HKEY_LOCAL_MACHINE\SOFTWARE\Citrix\IMA\Runtime\

Setting thise key to, force the server to connect to the data store, so that the Local Host Cache database, can be recreated.