Category Archives: Connect Items

The Activity Monitor is unable to execute queries against the server !

Posted on by

I’m generally not a great fan of Activity Monitor which is built in with SSMS,however today I wanted to check some processes details and opened Activity Monitor after a long long time.

Straight away I was struck with this error.

Activity Monitor1

By using the ‘Show Technical Details’ button I tried to see if there is something which I can fix by myself,however there were no much details other than the program details for which I don’t have any visibility.

I was connected to a SQL2005 (9.0.5000) Instance using SSMS 2012 (11.0.3339.0) and was trying to launch Activity Monitor. Ideally this should have worked well without issues as higher version of SSMS should not have any issues connecting working with lower versions of Database Engine.

The error message also says that Activity Monitor is in paused state and use the context menu in the overview pane to resume it.

Activity Monitor2

This resume did not worked and apparently I received the same error message which I received in the beginning.

I tried multiple options, however there is nothing which I could find to get this fixed and start Activity Monitor properly.

Note – Rebooting SSMS unfortunately wont fix any problem permanently here.

I finally decided to use SQL 2008 R2 SSMS 10.50.4000 ( Yes, I had one !) and connected to the instance.

Viola, that worked ! Activity Monitor was just fine this time.

Activity Monitor3

This clearly means that the inability of SSMS 2012 to pull Activity Monitor for a SQL2005 Instance is because of a BUG.

A quick search on connect revealed some items related to this issue. Even though this connect item is dealing with a different version of SSMS and a SQL Engine, the issue seems to be the same.

I also tried connecting SQL2012 SSMS with SQL2012 and SQL2008 R2 instances and launched Activity Monitor successfully. Apparently the issue seems to be floating around with higher version of SSMS connecting to SQL2005 and trying to launch Activity Monitor.

Workaround –  I generally maintain major versions of SSMS on my work machine and this  comes handy during some instances.

Conclusion

Again this is not a mission critical bug which will cause work stoppage, however this should be fixed eventually and I hope SQLServer Product Team will fix this during upcoming SPs.

Thanks for reading and keep watching this space for more.

Plan cache bloat by SQLAgent (Prior to SQL2008) !

Posted on by

Recently I was checking the plan cache utilization with the help of the below query which I normally use for this purpose. This is written by Glenn Berry(B/T) and is available along with his SQL Server 2005 Diagnostic Information Queries.All credit goes to him.

SELECT TOP(50) [text] AS [QueryText], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE cp.cacheobjtype = N'Compiled Plan' 
AND cp.objtype = N'Adhoc' 
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);

This query will pull top 50 single use ad-hoc queries which are bloating the plan cache. Glenn has mentioned that SQLServer Agent will create lot of ad-hoc single use queries in SQL 2005,however I had never noticed that before.

That day when I ran the above query I received multiple statements like

BEGIN TRAN UpdateMediaTables
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @BackupSetId int, @MediaSetId int, @LogDevName varchar(512)
SELECT @MediaSetId = media_set_id
FROM msdb..backupmediafamily AS bmf WITH (NOLOCK)
WHERE substring(bmf.physical_device_name,5,36) = '98E410FA-78ED-43BE-864F-C4F014EBD055'

This confirmed the fact that SQLAgent indeed fires lot of ad-hoc queries and that was not a good sign.

I was curious to check more and see if this issue was reported anytime to Microsoft and wanted to know if SQLServer product team did fix this issue for releases after SQL 2005.

I was able to find this connect item which was dealing this issue, and on this item Microsoft confirmed that they have fixed this in SQL2008.

I’m not sure for which SQLServer 2008 SP or CU did Microsoft released this fix. Dave Ballantyne who opened the above connect item have also mentioned that this fix is in place for SQL2008 R2 too.

Yes,we have yet another reason for moving forward with upgrading your SQLServer environment to run the latest builds.

Thanks for reading.

Backing up the “log” of a database which is in Simple recovery model ! – Watch out for this!

Posted on by

In theory we cannot backup the log of a database which is in Simple recovery model, however there is a small bug in SQL 2012 which allows you to do this, and you need to be very careful about this.

Below is the detailed repro steps for this issue and the testing has been done using SQL 2012 RTM bits.

1. Change the recovery model of Model database to Simple.

2. Create a new database.

3. The newly created database will be having recovery model as simple as it inherits the properties of the model database.

4. Backup the newly created database.

BACKUP DATABASE Test_LogBackup
TO DISK='I:\Test_LogBackup_Full.bak'
WITH STATS=1

Processed 264 pages for database 'Test_LogBackup', file 'Test_LogBackup' on file 1.
100 percent processed.
Processed 2 pages for database 'Test_LogBackup', file 'Test_LogBackup_log' on file 1.
BACKUP DATABASE successfully processed 266 pages in 0.570 seconds (3.632 MB/sec).

5. Now try to backup the log of this database which is in simple recovery model

BACKUP LOG Test_LogBackup
TO DISK='I:\Test_LogBackup_Tran.trn'
WITH STATS=1

Processed 3 pages for database 'Test_LogBackup', file 'Test_LogBackup_log' on file 1.
BACKUP LOG successfully processed 3 pages in 0.003 seconds (5.371 MB/sec).

This behavior is not expected and this was reported by one of the users(@nayanraval) on twitter. After multiple successful repro’s the user was suggested to open a case with Microsoft PSS group for a quick resolution.  A connect item is also open for you to sign-in and vote.

https://connect.microsoft.com/SQLServer/feedback/details/765315/when-model-db-is-set-to-simple-recovery-newly-created-databases-are-not-truely-in-simple-recovery

I will keep watching this connect item and will update this post once it’s resolved or we get a response from Microsoft on this behavior.

Thanks for reading.

SQL Server 2008 Agent History retention does not “stick”

Posted on by

Have you ever observed this behavior

Under SSMS > right-click SQL Server Agent > Properties>  History. Enable checkbox “Remove agent History Older than 4 weeks.”. Press OK. Everything is fine (no error message or warning.)

Go back to SSMS, hit Refresh and check out History again. The “Remove agent history” checkbox (That we had selected) is now deselected. Try changing the retention period to different values, hit OK,  but setting are not present when we return to the History dialog.

This issue is as a result of a glitch in SSMS,and is already reported as a BUG.

https://connect.microsoft.com/SQLServer/feedback/details/485232/kjtap-sap-sql-server-agent-remove-agent-history-cannot-be-activated

As always there is a workaround for this issue –

You can use sp_purge_jobhistory command to accomplish what you need.

Example – EXEC msdb.dbo.sp_purge_jobhistory @oldest_date= @adjusteddate”