Practical Handbook for Configuration Manager 2012 R2 Reporting


This project has been in the works for a few months, very proud of this work. More details to follow.

Posted in SQL Server, SSRS, ConfigMgr, SQL Server 2012, SQL Server 2014 | Leave a comment

Change SQL Server default backup compression

The default backup mode for SQL Server databases is no compression.

When creating maintenance plans, you’d need to select enable database backup compression for each plan.

In order to change the server default setting, use the following:

— View current setting. If result is 0, then compression is not enabled.

SELECT value 
FROM sys.configurations 
WHERE name = 'backup compression default' ;

— To change SQL Server default setting, run the following:

EXEC sp_configure 'backup compression default', 1 ;

Posted in SQL, SQL Server, SQL Server 2012, SQL Server 2014 | Leave a comment

ConfigMgr – List all Servers with Maintenance Windows

Developed a report to show all servers that have collection maintenance windows. Very handy for cross checking servers in multiple maintenance windows, or supplying a list to support change control. Here is the query:

 -- List Computers with Maintenance Window
SELECT  sw.Name AS [MW Name], sw.Description, 
    sw.Duration AS 'Duration Minutes',
    sw.IsEnabled AS 'MW Enabled', 
    sys.Name0 AS ServerName, 
    sys.Operating_System_Name_and0 AS OperatingSystem
FROM  dbo.v_ServiceWindow AS sw INNER JOIN 
      dbo.v_FullCollectionMembership AS fcm ON sw.CollectionID = fcm.CollectionID 
INNER JOIN  dbo.v_R_System sys ON fcm.ResourceID = sys.ResourceID 
WHERE  (sys.Operating_System_Name_and0 LIKE '%server%') 
ORDER BY [MW Name], sys.Name0

Posted in Uncategorized | Leave a comment

SQL Server – List SQL Agent Jobs

Recently, I had a need to list the SQL agent jobs, owner names and whether each job was enabled or not. Here is the result:

SELECT AS AgentTaskName,
    SUSER_SNAME(j.owner_sid) AS JobOwner,
    WHEN (j.enabled = 1) THEN 'True' ELSE 'False' END AS Enabled
FROM msdb.dbo.sysjobs j
LEFT OUTER JOIN MASTER.DBO.syslogins l ON j.owner_sid = l.sid
ORDER BY j.enabled DESC,

And, the results


Posted in SQL, SQL Server | Leave a comment

T-SQL Tuesday #66: Monitoring

T-SQL Tuesday

Topic today is monitoring. Catherine Wilhelmsen (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.

I’ve been looking a lot of SQL Server installations over the past year. If there are reports of performance issues, the challenge is finding them.

One way is instrumenting the Server and SQL Server to help identify potential bottlenecks. I really like a new utility called Performance Monitor. OK, I jest. Its been around since NT 4. The nice thing, it is readily available on every server.

Here my approach, capture the key performance counters to a log file, every 5 minutes for a 24 hour period. Once the sampling period is complete, review.

To start, download the following XML file locally

Save it with the following name: SQLPerformanceV3.xml

Start perfmon.exe

From Data Collector Sets > User Defined, Right click Create a new data collector set, and give it a name”


Click Next, then Browse (for a custom template), select the SQLPerformanceV3.xml file saved in the previous step. Click OK


Then, click Next

Change the root directory to a drive with available free space


Click Next, then Finish

Back to Performance Monitor, there should be a User defined data collector set named SQLPerformance. Right click that set, and select properties. Select the Stop Condition tab


Change the overall duration to 1 day. Click OK.

Now, starting it (Right Click > Start ) will create a folder for the performance log file with the Computer name, and a date time entry.

From Performance Monitor, once you are done collecting data and have stopped the trace. Import the log file:


Click Performance Monitor, then click second icon from the left


Choose, log file, then Add… select the log file that was captured. Then click OK.

Now you can analyze your trace, adding and removing counters as needed. The full extent would be enough for another blog post! I’ve found this to be a very effective method to help identify performance issues.

Lastly, as you make changes to help improve performance, run another 24 hour perfmon capture and compare the results.

Posted in SQL, SQL Server, SQL Server 2012, SQL Server 2014, SQLTuesday | Leave a comment

Microsoft Ignite 2015 – Download Videos and Slides

Posted in Microsoft | Leave a comment

ConfigMgr 2012 R2 CU5 released


Description of Cumulative Update 5 for System Center 2012 R2 Configuration Manager

Posted in ConfigMgr | Leave a comment