Stop SSRS Rendering on SQl

Sometimes occur that a specific SSRS subcription is in pending or blocked or using too time to create report. if you want to force the stop of subcrption you can do it using this two string of code directly on sql database of report server:

truncate table  [ReportServer].[dbo].[ActiveSubscriptions]
truncate table  [ReportServer].[dbo].[Notifications]
Done that your Subscription is stopped but his status is not refreshed and remain on pending. If you want to change Status you have to execute this script:
update ReportServer.dbo.Subscriptions
set LastStatus = ‘Done: 0 processed of 0 total; 0 errors (Stopped).’
where Description = ltrim(rtrim(SubcriptionName))

Power BI desktop January News

Happy new year to everyone!

Here’s the complete list of January updates:


  • Show and hide pages: you can now hide any number of pages you want through right clicking the page name and selecting hide.
  • Control data label background color for Cartesian and maps visuals:  You’ll find the option under the Data labels card in the formatting pane.
  • Increase area used for axis labels in charts:  This new slider can be used to increase the percentage of the chart used by the axis labels.
  • Bar/column padding control: added the ability to control the inner padding between bars. You can make the inner padding up to 50% of the bar’s width.
  • Show dates as a hierarchy (preview): created it automatically for you when you drag your data column into a visual.  You’ll need to turn on the preview feature switch under File > Options and Settings > Options > Preview features.
  • Add an anchor date for relative date slicer: Once you have an anchor date set, the slicer will be evaluated relative to that specific date permitting to start further back in time than today. . You can set it in the date range options in the formatting pane.
  • Top N selection in Q&A


  • Correlation coefficient quick measure: calculates the Pearson correlation coefficient between two measures within a certain category

Custom Visuals

  • PowerApps: With the new PowerApps custom visual, you aren’t only able to quickly find insights in your data, you can take action on it as well.
  • TreeViz:you display your hierarchical data in a tree like structure. You can expand and collapse each level and decide how many children data points to show before grouping the smaller ones into a single child group.clip_image023
  • Funnel with Source by MAQ Software: The Funnel with Source custom visual is perfect to track any metric of interest over various stages along with the source of entry of the data point to the funnel.
  • clip_image025
  • Box and Whisker chart by MAQ Software:clip_image027
  • Agility Planning Matrix Light: Some benefits of this matrix visual is the ability to selectively drilldown on specific rows and to display currently applied filters on the visual itself.clip_image029
  • Image Grid: similar to a word cloud but with image:clip_image031
  • Gantt Chart by MAQ Software: This visual shows additional details of the task by providing a grid where one can view more data related to the task and can display the hierarchy of data category. Also, it can be sorted based on any data point of the task.

Data connectivity

  • Support for Azure Active Directory authentication for Azure SQL Database & Data Warehouse connectors: With this month’s update for Power BI Desktop, they are introducing a new “Microsoft Account” option within the Credentials dialog for the Azure SQL Database and Data Warehouse connectors, allowing users to authenticate using their AAD accounts.


  • Advanced language settings for the Windows store app: You’ll see these new options under the Regional Settings tab in the Options dialog. Changing the application language will ignore the Windows default display language and switch to the chosen language. Changing the model language will decouple the model language from the application language for any newly created reports. (Note that this won’t affect the model language for existing reports.) Changing either of these settings requires a restart.

Determine free space for SQL Server

One of the functions of a DBA is to keep an eye on free space within the database and database files. The auto grow feature is great as a last resort, but proactively managing the database files is a better approach. Also, this information can be used to shrink data files as needed if there is a lot of free space in the files.

One option is to use sp_spaceused. If we run the following command we can see how much free space there is in the database, but this shows us total free space including the transaction log free space which may be totally misleading.

USE Test5 



sp_spaceused output

Another option is to use the SpaceUsed property of the FILEPROPERTY function which will tell us how much space is used and then we can calculate free space based on the current size and what is being used.

USE Test5 

name AS FileName, 
size/128.0 AS CurrentSizeMB, 
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 

Here we can see the output from the above for the Test5 database.

sql server fileproperty output