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 
GO 

sp_spaceused 

 

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 
GO 

SELECT DB_NAME() AS DbName, 
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
Annunci

Check progress of shrink database

Its very simple. Open management studio create a new query on the instance where is the database that you are shrinking and paste this query:

Select percent_complete, status, command , estimated_completion_time from sys.dm_exec_requests

And find dbccfilescompact

Power BI desktop march Resume

Hi everybody, today we speak about some interesting new features on power bi desktop. This upgrade is very interesting. IN particular this month:

Report view

  • Report theming preview: When you import a theme, all your charts will automatically update to use the theme colors, and you’ll have access to the theme colors from the color palette.You can upload a theme file using the Import Theme option under the Switch Theme button.clip_image004A theme file is a JSON file that includes all the colors you want us to use in your report. Se my Next article where i explain how to use it.
  • New matrix visual preview: new version of the matrix as a separate visual Once you turn on the preview by selecting New matrix visual under File > Options and settings > Options > Preview features, you will see the new matrix in the visualization pane. Benefits of this new matrix include:
    • Performance improvements
    • Drilling into hierarches
    • Stepped layout
    • Cross highlighting and filtering from matrix headers and cell contents
  • Textbox font color: added the ability to change font color of your text in textboxes
  • Numeric range slicer preview: adding a preview of a new type of slicer, the numeric range slicer. You can use this slicer type with any numeric column in your data model. You can choose to filter your data between two values, less than or equal to a value, or greater than or equal to a value
  • Percent of total on pie and donut chart data labels: added percent of total as another data label option for pie and donut charts
  • Cross-highlight using multiple series: you can now multi-select multiple series in a chart when cross-highlighting.

Analytics

  • Clustering is now generally available

Data connectivity

  • New Connector: Azure Analysis Services : adding a new connector to allow you to import data or connect live to your Analysis Services instances running on Azure.
  • Azure Data Lake Store connector is now generally available
  • DB2 connector: option to specify package collection . The DB2 connector dialog now exposes a new option to specify the package collection to connect to, within a given DB2 server.
  • Combine binaries: ability to specify sample file to use. exposing a new control in the file preview dialog to select one of the files available in the selected folder. Default selection is the first file found, but this can now be easily customized by you.

Query editing

  • Split column by delimiter: automatic detection of delimiter character

Ataira Analytics BI – Collaboration for SharePoint Power BI Groups Support

During my browsing online about Power Bi i found an interesting solution to Sharepoint power bi. His Name is Ataira Analytics +BI  and with their solution can view your Power BI items within a convenient SharePoint web part. You can also send notifications to your corporate groups and add follow-up actions for your team to more tightly collaborate on your business intelligence efforts in one location. Continua a leggere

Power BI Mobile February 2017 Update

Here we are with the news about Power Bi Mobile:

  • Power BI app for Android tablets is now available globally
  • New dashboard tile actions (All): To access this menu, just tap and hold on a tile in a dashboard, or tap the ellipsis (…). The menu allows you to navigate directly to the underlying report, expand the tile, or even manage an alert for the tile if the type supports it.

TileActions

  • SSRS – multi server support (iOS): you can connect to up to five different SQL Server Reporting Services servers at the same time. All the servers you connect to will be available under the settings screen.
  • Scrolling Improvements: added the ability to scroll in any Cartesian chart (Bar, Colum etc.) by touching the chart itself, rather than touching the scroll bar. This new interaction is available in any visual in a report, report focus mode, phone report, or Q&A tile.