ReportServerService logs not deleted

No Gravatar

I was performing some initial discovery on a SCCM primary site server and noticed a lack of disk space. Using WinDirStat.exe I started digging deeper and discovered almost 100gb of ReportServerService_<timestamp>.log files. These are associated with the SQL Server Reporting Service and should be cleaned up after 14 days by default as configured in the ReportingServicesService.exe.config file via the parameter

However that was not happening on this server and it was soon to die under the weight of a year worth of log files.  It turns out this is a known bug as described in Microsoft KB2706518.  The solution is to upgrade SQL 2008 to Service Pack 3 and Cumulative Update 5 or higher.  Until that upgrade can happen the logging level can be changed to a value less than 3 in the config file.

You could enable NTFS compression, manually delete the files older than about 30 days, or write a script to automate that.  But why bother… just upgrade SQL! 🙂

June 11, 2014

Posted In: ConfigMgr 2007, ConfigMgr 2012, SQL Reporting

SCCM backup failing with VSS error

No Gravatar

On a SCCM 2012 SP1 CU0 primary server I started getting messages that the Site Backup was failing.

Status messages were showing these errors:

  • Site Backup could not find the SMS Writer in the list of writers provided by the VSS. Aborting the backup operation. Please verify that the SMS Writer service is up and running.
  • Site Backup failed while reading the VSS writers meta data. Aborting the backup operation.
  • Site Backup failed. The error reported by the service is Error: GatherWriterMetadata failed.. Backup operation is not completed. Please see smsbkup.log for more information.

SMSBKUP.log was showing these errors and significant entries:

[cc lang=’text’ line_numbers=’false’]Registered connection to SQL server SERVER1.contoso.com and database CM_PRI.
Registered connection to master database on SQL server SERVER1.contoso.com
Starting backup…
Verified that backup folder E:\Backup\SCCM\Last exists.
Verified backup service has permission to access the backup folder E:\Backup\SCCM\Last
Sql Writer service is running.
Starting VSS initialization…
Starting Asynchronous GatherWriterMetadata.
Number of writers that responded: 13.
Error: SMS Writer service either does not exist or is not running .
Error: GatherWriterMetadata failed.
SMS_SITE_BACKUP failed. Please see previous errors.
Raised backup task failure alert.[/cc]

After some digging I found some forum entries that were relevant.  The VSSAdmin command was particularly interesting, but in the end it didn’t resolve the issue.

Then I noticed that the E:\Backup\SCCM\Last folder had the dreaded lock icon on the folder.  The NTFS security showed that SYSTEM and the local Administrators group had Full Control; there were no other entries.  As there was nothing of value in the folder anymore I just deleted and recreated it.  No more lock icon!

I kicked off the SCCM backup and surprise, surprise it worked!  The folder lock icon returned, but a subsequent backup worked.

 

May 29, 2013

Posted In: ConfigMgr 2007, ConfigMgr 2012

Microsoft Windows Custom Support Agreement Patches

No Gravatar

One of my customers has now had two occasions where they deemed it necessary to purchase a Custom Support Agreement (CSA) from Microsoft to keep a specific version of Windows patched for a short time.  As I’ve been able to find NO information about the process of deploying these special patches via SCCM or WSUS I decided to document the experience here.

Note: I legally cannot and thus will not share any of the bits/files.

Step 1:  Acquire the files

The first trick is to actually acquire the custom catalog/metadata file and the actual patches.  In my case these are delivered by the Microsoft Technical Account Manager (TAM) associated with the legal entity that actually has the agreement.  My customer is global and another region owns the CSA, thus I get the patches after they’ve gone through several hands inside the company.  This is what the files look like.

CSA-metadata-CAB CSA-payload-CABs

Nothing really special on the surface, just a bunch of CAB files.

 Step 2: Import the Metadata

If this is the first time dealing with CSA patches, you’ll need to get the TAM to send you the import/insert tool (it goes by a few different names in the documentation including WSUS CSA Insert Tool, WSUS 3.0 SP2 CSA Insert Tool, and WSUS Import Tool).  This is what reads the metadata CAB and injects it into the WSUS database.

Once you have the utility, run it with proper command line parameters as shown in the screen shot.

CSA-WSUSImport

Here is the command line [cc lang=’dos’ line_numbers=’false’]WSUSImportTool.exe “x:\FolderWithMetadata\metadata.cab” “x:\FolderWithPayloadFiles”[/cc]

Notice that I didn’t have the payload files (patch CABs) in the directory I specified on the command line.  That’s because the TAM sent me MSU files instead of CABs the first time around.  WSUSImportTool.exe can’t handle MSU files, only CABs.

Step 3: Update SCCM SUP Products

If you are running SCCM 2012, you must update the Products in your Software Update Point.  For whatever reason, this is not necessary in SCCM 2007 even though the Product is listed.  I’m hoping Microsoft will give an explanation for this seeming impossibility.

CSA-new-product

Step 4: Synchronize SCCM with WSUS

After the metadata is added to the WSUS database, SCCM needs to synchronize just like it does every Patch Tuesday or whatever your schedule is.  Here is the before and after.

CSA-synchronize-DB-1   CSA-sync-log CSA-synchronize-DB-2

The WSYNCMGR.log looks pretty normal.

Notice that CSA patches have a title that clearly differentiates them from normal patches.

Step 5: Download the Patches

Now that SCCM knows about the patches it is time to add the binaries to a Software Update Package (SCCM 2007 in this case).  There isn’t anything special here except that you can’t download them from the Internet; you’ll have to point them to a local/network path.

CSA-download-path CSA-download-success CSA-download-status

All done!

Now the CSA patches can be deployed just like all the others and you can feel safe and secure that the truckload of cash you paid to avoid Service Pack 1 for Windows 7 is worth it! 😉

 

May 29, 2013

Posted In: ConfigMgr 2007, ConfigMgr 2012

SQL Reports Subscription Ownership

No Gravatar

We have a number SCCM Reporting Services subscriptions for our automated reporting.  Yesterday I had a former team member’s ID removed from SCCM access and today most of the automated reports did not run.

When a report subscription is created the owner is the logged on user.  If that user ever loses access to SCCM/SSRS then the subscription will fail to run.  I decided the best course of action was to reassign ALL subscriptions to the SCCM service account.  That won’t prevent problems with new subscriptions, but it will prevent the problem with existing ones.  I could run the t-SQL on a schedule in SQL itself to handle that.  The only downside is that there will be no results when clicking the “My Subscriptions” link.

Thanks to Jeremiah Clark for the explanation and some t-SQL: http://blogs.msdn.com/b/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx

[cc lang=’sql’ line_numbers=’false’]
USE ReportServer
Declare @NewUserName [nvarchar](260); Set @NewUserName = ‘NewDomain\NewUser’
Declare @NewUserID [uniqueidentifier]

— Show Subscriptions for a User
select U.UserName [OwnerUserName], S.*
From Subscriptions AS S Inner Join Users AS U on S.OwnerID = U.UserID
where U.UserName = @NewUserName

— Show UserID for a User
select @NewUserID=U.UserID From Users AS U where U.UserName = @NewUserName
Select @NewUserID [NewUserID], @NewUserName [NewUserName]

— Show records for Reassigning the User (Owner)
Select @NewUserID [NewUserID], U.UserID, U.UserName, S.*
From Subscriptions AS S Inner Join Users AS U on S.OwnerID = U.UserID
where U.UserName = ‘OldDomain\OldUser’ —<> @NewUserName

— Reassign the User (Owner)
Update S Set S.OwnerID = @NewUserID From Subscriptions AS S Inner Join Users AS U on S.OwnerID = U.UserID
where U.UserName = ‘OldDomain\OldUser’ —<> @NewUserName
[/cc]
Enjoy

May 1, 2013

Posted In: ConfigMgr 2007, ConfigMgr 2012, SQL Reporting, T-SQL

SQL 2012, BIDS, and SSDT

No Gravatar

With SQL Server 2012 Business Intelligence Development Studio is replaced with SQL Server Data Tools.

BIDS = Business Intelligence Development Studio

SSDT = SQL Server Data Tools (http://msdn.com/data/tools.aspx)

If you customize SQL Reporting Services reports for SCCM 2007 or ConfigMgr 2012 in you’ll probably want some GUI tool to help you edit or design the report.  Microsft ReportBuilder 2.0/3.0 is quasi built-in to Reporting Services and allows creating and editing of RDL files in a limited manner.  If you need a powerful tool to create a complex report you’ll likely turn to BIDS.  However, BIDS is now depricated and beginning with SQL 2012 SSDT is the tool going forward.

SSDT utilizs the Microsoft Visual Studio 2010 SP1 or 2012 Shell which is very nice.

A word of caution: It appears that SSDT comes in several flavors and not all support Reporting Services.  Be sure to install SQL Server Data Tools from the SQL Server 2012 SP1 install soruce and NOT the stand-alone / downloadable installer.

Some useful links:

March 1, 2013

Posted In: ConfigMgr 2007, ConfigMgr 2012, SQL Reporting