Slicer Pane in Power BI
When watching the February 2019 update video and learning about the new Filter Pane experience (in preview). I learned about another feature, which had gone again unnoticed by me.
I am trying to keep on top of new features, but it is a daunting challenge. Anyhow when I first heard about the “Slicer Pane”, I went hunting under visualizations, options, properties for a slicer on how to make a pane out of this. With no luck, I was getting frustrated. Where was this functionality, because it was annoying so much of my page space was stolen by slicers. To get around this, I created a page for slicers, and relied on slice syncing. Which was good, expect when slicers didn’t update as expected under sync scenario. Then like any good IT geek, I took to Bing! (Yaah! I use Bing!) and found video Guy in Cube).
So learning about slicer pane was cool. What made it more cool was it was not a new feature, rather it was creative way to use bookmarks.
High-level approach:
- Build slicers you need for the report page.
- Build a button to show slicers.
- Build a button to hide slicers.
- Build a bookmark where slicers + hide slices button is visible but show slicers button is hidden.
- Build another bookmark where show slicer is visible but slicers + hide slicer button is hidden.
You can make this a bit more fancy by booking box around it, but it is just steps above. Let’s walk through an example.
I have a simple report with graph showing sales by product. It has two slicers by product category and region name. I want to change the slicers into a slicer pane to allow for bigger graph view.
So first, I’ll create a box around my slicers and grow my graph. So slicers and box over lap with graph. Then I’ll make sure the view order is correct (under Format for visual, or by using the selection pane). So when slicer/box is visible they on top of the graph.
Next I add couple of buttons (I chose right/left arrows). One button will be used to hide the slicers and another to show them.
Next we are going to create couple of bookmarks, name them “Show slicers” and “Hide slicers”. Right now they are just place holders, we will update them later.
Now we’ll open the Selection Pane and hide the left arrow button and update the Show Bookmarks.
Next we’ll hide the right arrow button, slicers, and the box and update the Hide Bookmarks. We are have the main work done, now we just need to program the action for each button and we have a slicer pane that can show up on demand.
While having the right arrow selected, update the action property. To bookmark, and set the bookmark to “Hide bookmark”.
Next select having the left arrow selected, update the action property again. This time set it to “Show Bookmarks”. Now you can test the behavior out in Power BI Desktop by CTRL+Left Clicking arrow to making sure it works as intended.
So this was cool learning for me :). Here’s to more Power BI learning!
PS With this post I am caught up for my promise of one post/month :). Now the next challenge not to fall behind again.
PowerBI Syntax Highlight in Advance Editor
This is a continuation of the post from last week. Where I discovered by accident smart guides. Next thing I discovered was Syntax Highlight. Again another update — rather preview feature — I missed.
I tell customers to update their PowerBI Desktop frequently as Microsoft updates it monthly. However, I get caught in daily grind and forget about my own updates. So I miss important functionality that will make my life easier.
I was working on a function in Power Query Language (M Language) in advance editor. However, I was running older version, so editing code was in single color (black).
Now most of the code for Power Query is written by Power BI (thank-god). But if you had to do some custom coding, then you are on your own. No syntax high-light, now brackets intelligent. It got very frustrating when working on a more complex code.
So after turning on preview feature “M Intellisense”, now …
Again, I was jumping up and down. Because I hated writing all black code. I am a developer at heart. And all Microsoft IDE have syntax high-light. It was a big disappointment PowerBI did not have this. Next thing that was disappointment in PowerQuery no intellisense. Whoever heard of Microsoft products without, I was in hell. Dam it! Looking up references for each function was nightmare. This update made that pain go away! Yeeeh for preview features *bows*.
See, my PowerBI life is complete! Yes! Well until the next thing that gets me all happy :).
Power BI Smart Guides for Aligning Objects
It seems all I am teaching for last year is PowerBI. In my on journey to learn PowerBI development, one of the biggest things I hated was lack of alignment options in visualizations.
We have the align objects, when we select more the one visualization:
This is handy but it was still annoying because coming from other Microsoft products that have guides right on-screen for alignment, this was nice but was a big miss for me :(.
Next my pain was, if I want all the visuals to be the same size, now what? There is no option for same size under format.
This was again handy but still annoying. Because doing this for each visual is painful. To come over this I can use snap objects to grid, which helped a bit was not enough. So when I discovered the guidelines I was in cloud 9 for a while (I am amused very easily these days :P). I am very lazy in updating in my PowerBI Desktop, and I am further busy with reviewing all the updates released each month. So I had completely missed this update from January 2019, Smart Guides!~ Discovered this while helping client build reports.
If you don’t have the newest version, I recommend updating it. If you are running older than January look at preview options. Anyhow, now when we move objects around we start seeing red-line to let me see the smart guidelines. For bottom of object alignment, top of object alignment, center of an object, plus to help with sizing to make sure they are same size.
Center Align Smart Guide
Top Align Smart Guide
Bottom Align Smart Guide
PS. This is post for Jan 2019. I am a bit behind, goal was to get one post/month. So I hope to catch up for Feb post and Mar post before end of March :).
Get to Know How to Shrink Transaction Log in SQL Server
Many times the administrators ask about the transaction log size limit and how to shrink it. Even the log records are not managed properly and at last fill up the disk causing no longer changes to the database. The size of transaction log file will increase due to various reasons like long running transactions, lack of log backups and incorrect recovery model configuration. Thus, it is necessary to reduce the size of transaction log in SQL server. Now, in this blog, we are going to discuss a step-by-step procedure to shrink transaction log in SQL Server in an absolute way.
Shrink Transaction Log in SQL Server – Manual Tricks
There are following methods by which you can easily reduce the size of transaction log file. Please have a look:
- Using SQL Management Studio
a)In the Object Explorer, connect to instance of Microsoft SQL Server Database Engine and expand that instance
b) Expand all Databases and right-click on the database that you need to shrink
c) Choose Tasks, Shrink, and then Files. Then, choose the file type and file name.
d) Alternatively, check the Release unused space check box. If you select this option that causes an unused space in file to be released to operating system and shrink files to the last allocated extent. It will reduces the size of a file without moving any important data.
e) As an alternative, choose this check box Reorganize files before releasing the unused space. If this option is selected, reduce file to value must be identified. Now, the default option is cleared. Select this option causes the unused space in file to be released to operating system and attempt to relocate rows to some unallocated pages
f) Optionally, you need to enter the free space percentage in database file after shrinking the database. A permissible value must be between 0 and 99. This feature is only present when you enabled Reorganize files before releasing unused space
g) Alternatively, choose the Empty file by moving data to other files in similar filegroup check box. Select this option that converts all data from specified file to other files within the filegroup. The empty files can be deleted. This option is same as executing DBCC SHRINKFILE command using EMPTYFILE option.
h) Press OK button
- Using Transaction SQL (T-SQL)
If the database is available in SIMPLE recovery model, thus you can run the following statement to reduce the size of log files:
You must have to replace TestDB_log with logical name of a transaction log file you want shrunk and change 1 to number of MB you wish the transaction log file shrunk to.
If the SQL database is available in FULL recovery model you can set it to SIMPLE, perform DBCC SHRINKFILE cmdlet, and go back to FULL if you do not care about losing data in the transaction log.
You can search the logical name of transaction log file by using a given query:
Another option to reduce the log file size with FULL recovery model is to backup the transaction log for database using BACKUP LOG statement and then the main issue SHRINKFILE to shrink the transaction log in SQL Server:
Time to Conclude
Nowadays, most of the people are aware about how to shrink transaction log in SQL server e., Office 365 Backup. Sometimes, there is an unexpected delay, the log truncation will occur after the checkpoint or after a log backup automatically. Therefore, in this blog, we have explained the manual solution that will help to reduce the size of transaction logs. Make sure you follow each step very carefully.
Ways of Managing SQL Server Database Fragmentation
If you are an SQL admin or user, you must be familiar with the term” fragmentation.” In every SQL Server, indexes get fragmented as the database usage is increased. Fragmentation within a limit is harmless, but as the fragmentation percentage increases, it affects the database efficiency. Keeping this situation in mind, this post will discuss methods of managing SQL Server database fragmentation. We will also talk about other matters related to SQL Server database fragmentation.
What is Fragmentation and Its Categories
SQL database indexes help your database to perform properly. Whenever an SQL Server is used for a query, view, or data retrieval, indexes are being used one way or another. Especially in case of lengthy database operations, support from properly managed indexes is mandatory for correct and fast result. Along with the database usage growth, page split keeps occurring and at the same time those page indexes keep fragmenting. That is how fragmentation occurs in SQL Server. There are two types of index fragmentation: internal and external. External fragmentation means the logical page order is not similar to physical page order. Internal fragmentation happens when there is too much white space on the pages.
How Fragmentation Affects SQL Server Performance
Fragmentation forces database to go through multiple index entries to process a query. As a result, it takes more time to use SQL Server to execute any query. The situation gets worse when you need to fetch data from a read-only table quickly, but in case of dynamic table without any page split it does not matter much.
What is the Desired Level of SQL Server Database Fragmentation
So, we can see that the effect of index fragmentation depends on index usage and category of table that is being processed. For this reason, there is no fixed SQL Server database index fragmentation value that can be considered universally standard. Users need to consider their specific requirement and manage database fragmentation.
Learn to Manage SQL Server Database Fragmentation
This process of fragmentation is impossible to avoid, so the only option left is to manage index fragmentation properly. Considering the importance of this process, most of the SQL experts consider it as a regular task. In this segment, we will learn how to manage the fragmentation in SQL Database.
Detecting Fragmentation
For adopting any measure for fragmentation management, the level of fragmentation has to be determined. In order to analyze fragmentation, sys.dm_db_index_physical_stats” DMS is commonly used. This function will get you a result set containing the fragmentation level. Some important columns of the result set are:
- avg_page_space_used_in_percent: This column shows how much database space is used by the pages. The value is shown in percentage and indicates internal index fragmentation.
- avg_fragmentation_in_percent: In this column, external index fragmentation percentage can be viewed. For Heap Tables and Tables with Clustered Indexes, this column shows different values.
- fragment_count: This column displays continuous data fragments in the index.
Managing SQL Server Index Fragmentation
If you want to keep the index fragmentation rate in control, make sure to apply these measures for your SQL Server database.
- Apply index fill factors settings
- Avoid adding records using random key values
- Select cluster key according to the Insert pattern of the table
- Stop making records longer by updating them
- Avoid updating key columns of the index
How to Apply Fill Factor Settings
The most useful technique to manage the Index fragmentation is to set Fill Factor settings. In this parameter, users can set the data page fulfillment percentage reaching which only, an index will be created. The value has to be entered between 1 to 100 where 100 means an index will be made when a page is 100% full. Remember that Fill Factor is only used during the creation, rebuilding, or reorganization of an index. If you want to set instance Fill Factor you can use sp_configure, but it is not recommended.
What If My Index Fragment Rate is High
As mentioned earlier, there is no fixed standard for index fragment rate. But there are some common practices among the SQL users if their index fragmentation percentage reaches a certain level. If the SQL database fragmentation rate is 0 to 5-10% you do not have to do anything. If it is between 5-10% to 30%, you have to REORGANIZE your indexes. If the rate crosses 30%, i.e., it is within 30% to 100%, people usually REBUILD the indexes.
How to Reorganize Indexes
Using ALTER INDEX REORGANIZE command, users can reorganize their indexes and keep the index fragmentation in control. All indexes can be reorganized at a go by mentioning All during the command execution. When this command is running, the Tables can be used simultaneously. Irrespective of the Recovery Model, this operation gets completely logged in Transaction Log. Depending on the fragmentation rate, it can take more space in the Log.
How to Rebuild Indexes
When the database is highly fragmented, ALTER INDEX REBUILD command is used. It can be used to rebuild all the database indexes. During its execution, users cannot use the database. Based on the database recovery model, this gets included in the Log. In full recovery model, the operation takes a lot of space to get completed.
Conclusion
The article thoroughly discussed various approaches of managing SQL Server Database fragmentation. By reading the entire write-up, users will get a clear idea about the concept of SQL Server index fragmentation and they can secure their database from its adverse effect.
Quick Methods to Restore Database from MDF File Only
“Recently, my system has encountered a severe breakdown. I lost most of my files and applications attached to it. Luckily, the MDF file of the attached SQL Server could have been recovered. Now is the time for a fresh start and I am in need to restore database from MDF file only. I heard there are different methods to attach MDF file to the database. Can anyone tell me which one is the easiest method to restore from MDF file to the SQL database?”
Like this user, many people put forth their query regarding MDF file restoration to the SQL Server Database. MDF files are the primary data file that stores all the database items of a SQL Server. Due to some technical issues of SQL database, users often need to restore database from MDF file only. To help users in such situations, we will share some approaches of MDF file attachment to the database. If you have the similar query about MDF file restoration, read the full blog.
What is MDF File: The full form of MDF is Master Database File. Of all the files created in SQL Server database, it is the most significant one. It contains all the items of the database. It plays a vital role in the smooth functioning of the database and also used for database restoration in case of any SQL Server issue. If the MDF files get corrupt, it will cause great inconvenience to the users. That is why, SQL users are always advised to have a regular backup of MDF file. Besides, they should always perform best practices to protect MDF files from corruption.
How to Restore Database from MDF File Only?
If you are facing the need of database restoration from MDF file, you can choose any of the methods from this segment.
- Using SQL Server Management Studio
- Go to the list of all applications in your system and click on Microsoft SQL Server.
- Choose SQL Server Management Studio (SSMS) and a dialog box will appear.
- Select the correct Server. Also, choose Windows Authentication as the mode of authentication and click on Connect button.
- SSMS will open and you have to right-click on Databases node. Then click on Attach option from context menu.
- Attach Database window will open and there, you need to click on Add button.
- Navigate to the MDF file location, select the MDF file and click on OK.
- MDF file will get added in the main windows and you need to click on OK button on this page too.
- The file will get restored in your database. You can check the database to find out.
Note: After MDF file attachment is done, SQL Server will create an LDF file.
- Using TSQL
- Use credentials to access SQL Server database engine.
- Go to New Query Window and run the below script.
Remember: Before performing this method, make sure to store the MDF file in the above specified location: “C:\Program Files\Microsoft SQL Server\…”
Looking For A Simpler Solution? Try SQL Recovery
In case you are not willing to restore database from MDF file only using the manual techniques, you can do that with MDF File Repair Tool. This application will let the users restore MDF file to same database or a different database. The biggest benefit of using this software is its ability to fix corruption issues in case the MDF file is damaged in some way. The application is suitable for SQL Server 2000 and all the above versions including SQL Server 2017.
Conclusion
In most cases of corruption and technical issues, users often do not have any other option than to delete the database. After you delete your troubled database, you can also restore database from MDF file only to get back the database as earlier. By following the manual methods mentioned here, users can easily attach their MDF file to the SQL database. For faster and smoother MDF file restoration, users can choose SQL Recovery Tool. The application can perform the database restoration without any data loss and fix MDF file corruption, if any.
Learn How to Find Who Deleted Records in SQL Server
Query 1: “Hello friends, I am writing for the first time hoping that some of the learned friends can help me sort out the problem. Some of the tables of my SQL database are accessible to other users too. Yesterday, I noticed that certain rows got missing from the database table. How to find who deleted those rows from that table? Please help me with some genuine method.”
Query 2: “I received a query from a database user who wants to know “how to find who deleted records in SQL Server”. Here, I am talking about the Express edition of database. As per my assumption, this data can be found in the LDF file (if it has not been truncated). If I am correct, tell me how to find out this particular information from LDF file.”
If you want to know how to find out who deleted records from SQL Server, you may find these queries familiar. Hundreds of users like these are looking for ways to find out the culprit who deleted SQL data that caused them serious problem. If you are one of them, hold on, as we will tell you the most impressive method to locate who deleted SQL Server records.
How to Find Who Deleted Records in SQL Server
The record of the user who deleted some data from SQL Server can be found in the Transaction Log file. If you want to know, you need to retrieve that information from LDF file of your SQL database. For this, you will require SQL Transaction Log Viewer that can open and read LDF file data easily. Here is how you can read Log file data using this software.
a. Install and start SQL Log Analyzer Tool of your Windows system. Click on Open to add your log file.
b. A dialog box will appear that will showcase two different tabs for Online Database and Offline Database. Here we will show you the procedure of reading Log file form Online database. Select Online DB Options tab and then choose the Server name from the list. Also, select the Authentication mode and Select Database from the list. Click OK when done.
c. The software will scan the LDF file and prompt when it is finished. Click OK.
d. It will display the summary of LDF file including counts of records, inserts, updates, and deletes. Again, click OK.
e. Now, you will see the complete LDF file data on the software screen. Any record can be previewed.
f. Users can sort the LDF file entries based on Transaction, Login Name, Time, table Name, and Transaction Name.
g. Just click on any table entry to select and preview the corresponding log entry of that operation. If you want to know who deleted a record, click on Delete transaction of that record. You will be able to see the Login Name of the person who deleted that data.
h. If you wish to export these data, check the boxes beside the necessary tables and click on Export.
i. Another dialog box will appear where you can apply record type filter, date filter, Export To/As, Database Credentials for Source database, and Destination Database.
j. Click on Export button when all the fields are filled properly.
k. The tool will export the selected data and upon finishing, prompt the users. Click OK.
l. The software will also display a report which you can save as CSV file for reference.
Note: Besides analyzing Log file data, this software can also retrieve deleted data if the database is in simple recovery mode.
Conclusion
SQL database often contains essential information and deletion of such data causes great inconvenience. Hence, we answered the question how to find who deleted records in SQL Server in this post. Using SQL Log Analyzer, users can open LDF file and find out who performed any particular transaction like Insert, Update, and Delete.
Where have I been?
Lost I guess. I had to put my blog on the back burner while I focus on life and work. I hope to come back to it this year. Hopefully with more topics than just SQL Server. Lately, I find I been only teaching PowerBI.
So as time permits, I’ll get posts rolling her this year :).
New year resolution; 1 post-month :).
Quick Things to Do When DBCC CHECKDB Display Corruption Message
Have you ever wondered that you are working on Microsoft SQL Server and database shows some abnormal file crash or error issue? And, when you execute the DBCC CHECKDB command, it reports corruption in your SQL Server database.
Definitely, this scenario is a nightmare and the saddest part, it is frequently faced by many SQL database administrators.
Now, the biggest question is ‘What to do when DBCC CHECKDB reports corruption in database?’
So, The long wait is finally over!
Here, we are going to tell you what step should be taken when the SQL database gets corrupted. Thus, one can secure its confidential and important database in any data disaster situation. But, before understanding the method, a user must know about DBCC CHECKDB and which type of corruption it is reported?
Without wasting any minute, Let’s begin!
Take a Quick Look on SQL Server DBCC CHECKDB
DBCC CHECKDB is a type of T-SQL command whose prime function is to monitor the logical as well as physical integrity of all object in a selected database. DBCC CHECKDB command is supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, Azure SQL Database, and SQL Server 2019. Furthermore, it comprises three main commands in it for different purposes.
DBCC CHECKALLOC – Its function is to verify the organization of disk space allocation or page usage for a selected SQL database. Besides this, it also checks various internal structures to maintain the record of allocation of all pages in the database as well as relationship between them too.
DBCC CHECKTABLE – This Transact-SQL command is used to check the logical and physical consistency of the pages and structure that create table or system table indexes.
DBCC CHECKCATALOG – This command is used to perform various catalog consistency between system metadata tables. For the execution of this T-SQL command, the database must be in online mode.
The above-described command runs as a part of DBCC CHECKDB. If the user executes the DBCC CHECKDB command, then there is no requirement to use all these commands separately. In case, when the database DBCC check reports any error, just take the help of following solutions in order to save the important database table.
Methods Use to Repair Corrupted Database in SQL Server
In the following section, three different workarounds will be described to restore data objects from a damaged or corrupted SQL database. A user can go through all these methods and select that is appropriate.
Approach #1: Use REPAIR_ALLOW_DATA_LOSS Command
When the DBCC CHECKDB reports corruption error, then you can use Repair command on the specified database. It is devised to fix all the reported error. However, the biggest disadvantage of this method is it leads to data loss. Even, when the user uses this command to extract the database from last clean backup, it results in more data loss. In fact, Microsoft does not recommend this method suitable to recover data from the good backup. It is marked as an emergency option that can be used when the data cannot be recovered from the last backup file.
Approach #2: Restore Database from Backup
If the last good backup of database is available, then it is better to recover the database from it with the help of RESTORE DATABASE command. With the help of Restore statement, one can perform multiple operations.
- Retrieve only a part of SQL database.
- Recover specific pages of a database.
- Extract complete database from full backup.
- Capable to get back a transaction log onto a database.
- Provide an option to restore only desired file or filegroups.
In case, when the user does not have a last clean backup of the database, then the Restore command does not work.
Approach #3: Use Expert Solution SQL Database Recovery
The above two solutions have some consequences, so it is better to show your trust in a reliable solution because data loss cannot be ignored. That is why many SQL administrators uses MDF Repair Tool when DBCC CHECKDB shows any data corruption error. This utility has an advanced scanning technique that scans the highly corrupted primary (MDF) or secondary (NDF) file. And, recover the data object such as triggers, tables from it.
The great part of this software is user can export the recovered file in SQL Server Database or SQL Server Compatible SQL Scripts. Moreover, SQL Database Recovery Tool supports to repair corrupted Database of version 2017, 2016, 2014, 2012, 2008, 2008 R2, 2005, 2000.
Final Note
It is a head-scratching situation when the SQL database got corrupted and no relevant solution is not in the hand. So, after considering this painful situation, in this article, we have discussed about DBCC CHECKDB that reports error in the database. Along with this, three different solutions have illustrated to recover data from corrupted SQL database.
How to Fix Microsoft SQL Restore Database Error 3154
“Yesterday I made backup of my SQL database and tried to restore my database. But, unfortunately my database has been deleted, then, I tried to re-create a new database with common names. I thought this will work, but this message “The backup set holds a backup of a database other than the existing database error 3154” encountered. Now, I am stuck in a very difficult situation and unable to figure out what to do next. I do not even know how to fix SQL database error 3154. Kindly suggest me a feasible approach to resolve it.”
Are you getting SQL Restore database error 3154? Needs an appropriate solution to fix it? Do not worry, you are landed at the right page. In this blog, we are going to explain different methods that will help you to resolve SQL error 3154. Before moving towards the solution, let us understand all possible reasons that are responsible for SQL database error 3154.
SQL Restore Database Error 3154 – Reason
When we analyze the root cause of an error, it becomes easy that the name of database they want to restore the backup set and database of backup is same. Although the difference is CREATE DATABSE statement between two databases. This simply means that the similarity is of name, but the database is completely different from each other. Follow the possible reasons given below:
- For log shipping, more than one database was configured.
- Transaction logs for databases were backed up to similar folder.
- The only difference between both database names is “_tlog”. For example, xyz_test and xyz_test_tlog.
Fix Restore Database is Terminating Abnormally. (Microsoft SQL Server, Error: 3154)
Go through the following methods that will help to resolve SQL Database error 3154:
Approach 1: Using T-SQL command
You can resolve this SQL error with the help of T-SQL command:
a) Use WITH REPLACE while using RESTORE command.
b) Delete old database which is conflicting and again restore with the help of RESTORE command.
c) You just take an Example :
Approach 2: Using SQL Server Management Studio (SSMS)
If you want to do same with SSMS, follow the steps given below:
- Create the database with your preferred name
- Then, right click on database and select Tasks option
- In the next step, navigate to Restore and choose Database option
- Under the restore screen, select “Overwrite the existing database (WITH REPLACE)” checkbox
- Click OK button. It should restore the database successfully.
Note: When you restore the database WITH REPLACE it will overwrite an existing database.
Fix SQL Database Error 3154 – Feasible Solution
Most people face challenges while implementing the manual method. Thus, to overcome all the problems faced by non-technical users while executing the manual steps, it is recommended to opt for an instant solution. One such application is SQL Backup Recovery Tool. With this utility, users can repair corrupt SQL Server bak file without any risk or data loss. The tool is capable enough to repair both primary (.mdf) and secondary (.ndf) SQL database. It is having user-friendly interface, with some useful features.
Time to Conclude
SQL Server is used by several users across the globe to manage and store their crucial data. While restoring the SQL database, the user may encounter an error message “The backup set holds a backup of a database other than the existing database Error 3154”. Therefore, in this blog, we have discussed the step-by-step process to fix SQL Restore Database Error 3154. But, at times, it becomes very difficult for a user to implement the manual steps. Thus, users can make use of a commercial software that is SQL backup Recovery to repair a corrupt bak file.
