Tuesday, 17 October 2017

Fixing CTRL+R Show/Hide Control Pane in SQL Server

To be straight forward, I experienced the issue of CTRL+R not working all of a sudden. As per my personal experience, this happens when there are multiple instances opened with different servers connected in respective instances. Additionally, the same issue of results not getting vanished using CTRL+R could be due to computer formatting. Once the computer is formatted or if there is a windows patching update, the settings may get changed. Not to panic about it. Here is a simple fix for the show/hide control pane result tab using the CTRL+R option in SQL Server.

Steps for Fixing Show/Hide pane in SSMS

1. Go to option “Tools”..
2. Click on “Options” to get a new window popped with more options.
3. On the left side tab, click on the option “Keyboard” to populate more options.
4. Set the mapping scheme keyboard to default (Default)
5. Tap the “Reset” button for the fix.

Take a look at the screenshot:

Saturday, 14 October 2017

SQL Server - How to Shrink Big Log File Size of a Database

I had previously written an article on how to shrink the logfile size of a database. But, while working intensely on databases that are pretty huge with respect to transactions, I felt it really challenging to shrink the log file. As I had previously mentioned, the log file size increases with increase in transactions such as bulk data insertions, Index ReOrg, Update Statistics, or Full backups and Log backups running for quite a long time. There are two ways through which a big log file size can be shrinked. 

Method 1:

Say for example, if the log file size is utilized to 90 GB of 100 GB. Then, for sure there will be high difficulty in shrinking the log file. Here we can shrink the log file by giving the user databases to 10GB and shrink the log file.

Before to shrink the log file, check the log file size and log percentage with the below DBCC command: 

DBCC SQLPERF(logspace)



Now, let’s take a database for example – SQLArena_TEST
First take the log backup of respective database

BACKUP LOG <Database_Name> TO  DISK = N'<Drive_Path>.trn'
WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP

Example:
BACKUP LOG SQLArena_TEST TO  DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST.trn'
WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP

Shrink the file by giving the user database to 10 GB, as said the example here was taken to be as 90 GB utilized log. The user database needs to be reduced in 10 GB recursively to completely shink the log file. 

USE SQLArena_TEST
GO
DBCC SHRINKFILE ('SQLArena_TEST_Log', 80328) 




Note: Take the log backup twice or thrice with a different filename for the path consecutively to shink the database to 10 GB reduction successfully.

Method 2:

As per method 1, where the shrinking happens at 10 GB reduction for the user database, here we will be shrinking the log file size at a single shot. This method as far I have seen seems to be something pretty faster than any other shrinking methods used in SQL Server. 

When the log file sizes are too high, we tend to make a mistake of giving just one path while taking the log backup and shrinking. Instead, let’s try to increase the drive path in the log backup script to finish the log backup and shrinking the data file of the user by giving zero and truncate. Take a look at the command mentioned:

– Given just one path for log backup and this fails in shrinking
BACKUP LOG SQLArena_TEST TO  DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST.trn'
WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP 

– Give 7-8 drive paths to shrink at a single shot, Take this log backup twice
BACKUP LOG SQLArena_TEST TO  DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path1.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path2.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path3.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path4.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path5.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path6.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path7.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path8.trn'
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10

Once the log backup with multiple path files are created, shrink the user data by zero with TRUNCATEONLY command to completely shrink the big log file size. 

USE SQLArena_TEST
GO
DBCC SHRINKFILE (N'SQLArena_TEST_Log' , 0, TRUNCATEONLY)
GO

Thursday, 12 October 2017

Advantages of Using UNION ALL Over UNION in SQL Server

Most of them working in development or SQL environment would have come across UNION and UNION ALL. But, when we look into the cause of using UNION and UNION ALL, it’s pretty interesting not just in terms of results, but also in terms of performance.

Basic difference between UNION and UNION ALL is that, UNION ALL results in almost all the duplicates between the tables, whereas UNION omits from being resulting the duplicate values.

Secondary difference and advantage would be getting the results at faster rate. The results will be generated at a pretty faster rate for UNION ALL when compared to UNION. However, the UNION ALL generating the duplicate results can eventually lead to long time querying.

For example, let’s take count of a table name CUS from two databases, one without duplicates and the other with duplicate counts. Take a look at the script below:

FOR UNION:

SELECT '-----CUSName',0
UNION
      SELECT 'Customer1',COUNT(*) FROM CUS
UNION  
      SELECT 'Customer1',COUNT(*) FROM CUS



Another scenario is to use UNION ALL instead of UNION in the statements to see a different result in the output set. 

UNION ALL:

SELECT '-----CUSName',0
UNION ALL
      SELECT 'Customer1',COUNT(*) FROM CUS
UNION ALL
      SELECT 'Customer1',COUNT(*) FROM CUS
From the above outputs, it is clear that UNION ALL functions in selecting even the duplicate counts. Seeing the example, Customer2 consists of 10 duplicate records, which was put forward along as a redundancy.

You may also need: "10 New Features SQL Server 2016 Does Better than SQL Server 2014"

Tuesday, 3 October 2017

How to resolve error “The local device name is already in use” in SQL server?

This happened very recently to me when I set up a job to fetch resource from a local machine, which caused the error as “The local device name is already in use”. Now, looking at the error we may find it very straight-forward to know that that there is a resource which is using the local device path. Further, if we are setting up two jobs or running a command, to fetch the same resource at a time, then there are high chances to get the error message.

 A simple way to fix the error could be either to change the schedule in the job or run the session/process in odd timings. Here are the steps to schedule the job timings.
  1. Right click on the job and select “Properties”.
  2. Under properties select “Schedules” option.
  3. Double click the step name under“Name” option to open the schedules.
  4. Now setup the schedules accordingly.
Now, let’s see a sample schedule setup for a job in the screenshot below.