Category Archives: LearnSQL

How to repair corrupted WMI Repository?

Windows Management Instrumentation (WMI) is the infrastructure for management data and operations on Windows-based operating systems. WMI is the Microsoft implementation of Web-based Enterprise Management (WBEM), which is an industry initiative to develop a standard technology for accessing management information in an enterprise environment.

The ability to obtain management data from remote computers is what makes WMI useful. This feature makes it indispensable for many software applications including SQL Server. SQL Server Configuration manager and SSRS Configuration manager are few examples which are dependent on WMI. Corruption of any type to WMI could be critical to all these applications.

In this post I’ll document a workaround to recreate WMI Repository.

The commands mentioned below are DOS commands to repair & recreate a corrupted WMI Repository. You can execute these steps manually or via Batch file whatever you wish:

net stop winmgmt

CD C:\windows\system32\wbem

rename repository repository.old

net start winmgmt

In the commands above we are:

  1. Stopping WMI Service
  2. Renaming Repository Folder
  3. Restarting WMI Service

 

To avoid such corruptions Microsoft released one Hot Fix, apply the appropriate Hot fix available for your version of Windows.

 

Hopefully this will help you.

Regards

Sarabpreet Anand

How to find the Isolation level being used by a Database?

Hi Friends,

Recently i was preparing a Dashboard sheet for one of our critical Box. The Client was having a long list of  Requirements and one odd requirement was to embbed the Isolation level being used for every Database. This was a unique requirement for me and i never knew how to get this, but within 5 minutes of searching i came to know about an interesting Command which returns this info along with other useful stuff.

The command is
DBCC UserOptions
This command also tells you about certain DateFormats and ANSI Settings.

The below pic shows an output of one of my Database where the isolation level is set to Snapshot

1_SQL_Server_How_to_find_the_Isolation_level_being_used_by_a_Database

 

Hope this will help you in some way.

 

Regards

Sarabpreet Anand

Compare Data in Two SQLServer Tables without Any 3rd-Party Tool

For comparing data in tables you don’t need any 3rd party tool, SQL Server ships with the tablediff utility which can be used to compare the data in two tables. You can use it for troubleshooting replication. This utility can be used from the command prompt or in a batch file to perform the following tasks:

  • A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
  • Perform a fast comparison by only comparing row counts and schema.
  • Perform column-level comparisons.
  • Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
  • Log results to an output file or into a table in the destination database.

Lets see how to use it…

For testing i’ve created two tables Table1 and Table2 in DatabaseA and DatabaseB respectively on my local Instance, Then inserted few records in each table.

–On ServerA

create database DatabaseA

go

use DatabaseA

go

create table Table1(

tid int primary key,

tname varchar(20))

go

insert into Table1 values(1,’Sarab’)

insert into Table1 values(13,’Amit B’)

insert into Table1 values(18,’Amit K’)

insert into Table1 values(21,’Sachin’)

–ServerB(I’ve created this on my local instance however, You can create this on another server\Instance)

create database DatabaseB

go

use DatabaseB

go

create table Table2(

tid int primary key,

tname varchar(20))

go

insert into Table2 values(1,’Sarab’)

insert into Table2 values(13,’Amit B’)

insert into Table2 values(12,’Rishu’)

insert into Table2 values(18,’MV Priyank’)

The source table in the comparison must contain at least one primary key, identity, or ROWGUID column.

Now if you query both the tables you can see that I’ve purposely inserted 2 same records in both the tables (tid 1 & tid 13 marked Blue in the screenshot) and inserted same primary key with different values for tname (tid 18 marked Red in the screenshot) to show you how TableDiff Utility consider these scenarios.

1_SQL_Server_Compare_the_Data_in_Two_Tables_without_Any_3rd_Party_Tool

 

Location of the Utility File:
The TableDiff.exe Utility can be found at this location: C:\Program Files\Microsoft SQL Server\100\COM

Note – In case you are using SQL Server 2005 replace 100 with 90.

To start the action launch command prompt and locate the directory having TableDiff.exe utility, then execute the command below:

TableDiff.exe -SourceServer . -SourceDatabase DatabaseA -SourceTable Table1 -DestinationServer . -DestinationDatabase DatabaseB -DestinationTable Table2 -et difft1 -f d:\my_Diff_File.sql

2_SQL_Server_Compare_the_Data_in_Two_Tables_without_Any_3rd_Party_Tool

 

Parameters used:

Most of the parameters are self explanatory, the odd ones I am explaining below:

-et table_name
Specifies the name of the result table to create. If this table already exists, -DT must be used or the operation will fail.

-f [ file_name ]
Generates a Transact-SQL script to bring the table at the destination server into convergence with the table at the source server. You can optionally specify a name and path for the generated Transact-SQL script file. If file_name is not specified, the Transact-SQL script file is generated in the directory where the utility runs.
To get the complete list of all supported parameters visit: http://msdn.microsoft.com/en-us/library/ms162843.aspx

When -et parameter is used, the result table will be created in Destination Database. This table returns all rows where the data got mismatched based on primary (or Unique) Key including the script to resolve the conflict. It also returns all those rows which are only present at source or destination.
Here’s a screenshot of the output:

3_SQL_Server_Compare_the_Data_in_Two_Tables_without_Any_3rd_Party_Tool

 

And -f parameter will generate a file with T-SQL Script named my_Diff_File.sql which will have all the commands to resolve all the conflicts. The Output looks like this:

— Host: .

— Database: [databaseB]

— Table: [dbo].[table2]

DELETE FROM [dbo].[table2] WHERE [tid] = 12

UPDATE [dbo].[table2] SET [tname]=N’Amit K’ WHERE [tid] = 18

INSERT INTO [dbo].[table2] ([tid],[tname]) VALUES (21,N’Sachin’)

Points to consider:

  • The tablediff utility cannot be used with non-SQL Server servers.
  • Tables with sql_variant data type columns are not supported.

Permissions Required:

  • To compare tables, you need SELECT ALL permissions on the table objects being compared.
  • To use the -et option, you must be a member of the db_owner fixed database role, or at least have CREATE TABLE permission in the subscription database and ALTER permission on the destination owner schema at the destination server.
  • To use the -dt option, you must be a member of the db_owner fixed database role, or at least have ALTER permission on the destination owner schema at the destination server.
  • To use the -o or -f options, you must have write permissions to the specified file directory location.

Security Note: When possible, supply security credentials at runtime. If you must store credentials in a script file, you should secure the file to prevent unauthorized access.

 

Have Fun!

Regards

Sarabpreet Anand

What all changes require a restart of SQL Service?

Question of the day: What all changes require a restart of SQL Service?

Let’s say you get a request to change a SQL Server setting and you don’t know whether it requires a restart of SQL Service to take into affect or not, now what will you do?

You’ve left with following options:

  1. Ask someone you know (You think this person must know the answer but what if he is also guessing?)
  2. Search for it, now a days you find everything on internet (but you must hit the right keywords and someone should’ve written that for you)
  3. Ask SQL Server ( Now that’s interesting)

 

Q. How to ask SQL Server if it requires a restart of Service to take a change into affect?

SQL Server 2005 and later versions are equipped with a catalog view named sys.configurations, you can query this view to get the answer. This view gives a detailed info on all server level configuration changes.

The View provides very detailed information on server level changes like:

configuration_id
Unique ID for the configuration value.

name
Name of the configuration option.

value
Configured value for this option.

minimum
Minimum value for the configuration option.

maximum
Maximum value for the configuration option.

value_in_use
Running value currently in effect for this option.

description
Description of the configuration option.

is_dynamic
1 = The variable that takes effect when the RECONFIGURE statement is executed.

is_advanced
1 = The variable is displayed only when the show advanced option is set.

If the value in is_dynamic column for any configuration is 1, that means the change is dynamic in nature and it doesn’t require SQL Server Service restart to take that change into effect, otherwise if the value is 0 that means restart is required.

You can query this view just like any other normal table\view.

Select * from sys.configurations

1_SQL_Server_What_all_changes_require_a_restart_of_SQL_Service

Hope you learned something new, do leave us a comment.

 

Regards

Sarabpreet Anand

Auto-Truncate Log in Full Recovery Model

FULL Recovery model: This means that all database changes are fully logged and ideally the log records should stay in the log file until the log records are safely stored away\backed up in a Transaction Log backup. As per MSDN:  If a DB is in Full Recovery Model, then No work is lost due to a lost or damaged data file & we Can recover to an arbitrary point in time (for example, prior to application or user error) provided we have all backups in place.

But unfortunately this is not completely correct. Your database can be in Auto-Truncate Mode while the recovery model is set to FULL.

Now what is Auto-Truncate Mode: This means your DB is still working as if it is in SIMPLE Recovery Model. or in other words, the log file will be truncated every time a CHECKPOINT is run against the database. CHECKPOINT happens at regular intervals. Log Truncation means that inactive parts of the log file will be overwritten if the log space is needed.  Log Truncation does not physically shrink the log file, but it can keep it from physically growing.

In this article i will explain you the different conditions which changes this behaviour & let you know how to avoid such pitfalls and make your database fully recoverable. This all is internally related to LSN.

 

Now What is LSN?

Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Once the LSN Chain (Log Chain) is broken, you can never do point in time recovery. (there are few exceptions to this, i’ll explain this later)

There are four conditions in which Auto-Truncate Mode gets enabled:

  1. By switching the recovery model of your database to simple.
  2. Using BACKUP LOG command with NO_LOG | TRUNCATE_ONLY option. (Note:  The BACKUP LOG … WITH NO_LOG | TRUNCATE_ONLY option is no longer available in SQL Server 2008)
  3. If you have never taken a FULL backup of your database, log backup of that database will also be not available. Your database is in   Auto-Truncate Mode until the first FULL backup of the database is taken.
  4. If you have not taken a FULL\Differential backup after the last time you switched from SIMPLE to either FULL or BULK_LOGGED recovery model.

You should also be aware that taking a log backup will truncate the log, but taking a FULL database backup will not truncate the log. (I received many queries from DBA’s asking this specific question)

 

How to check whether your database is in Auto-Truncate Mode or not?

You can query a system view called sys.database_recovery_status. If the value of last_log_backup_lsn column of this view is NULL it means the database is not maintaining a sequence of log backups and it is in Auto-Truncate Mode:

Note: To see the row for a database other than master or tempdb, you should have one of the following permission:

  • You should be owner of the database.
  • You should have ALTER ANY DATABASE or VIEW ANY DATABASE server-level permissions.
  • You should have CREATE DATABASE permission in the master database.

SELECT @@servername,db_name(database_id) as ‘database’, last_log_backup_lsn FROM sys.database_recovery_status(nolock)

 

How to resolve this?

To resolve this you need to either take a FUll or Differential backups to bridge a gap & recreate a new LSN Chain.

 

To replicate the same and test following is the step by step script.

CREATE DATABASE sarab_12

GO

— Check the status of auto-truncate option after first creating Database

SELECT db_name(database_id) as ‘database’, last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = db_id(‘sarab_12’)

GO

–Now take a full backup of sarab_12 Database

BACKUP DATABASE sarab_12 TO disk = ‘C:\sarab_12.bak’

GO

— Check status of auto-truncate option after taking full backing up of sarab_12 Database

SELECT db_name(database_id) AS ‘database’, last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = db_id(‘sarab_12’)

GO

–Try to truncate the inactive portion of sarab_12 database without taking backup of the same

BACKUP LOG sarab_12 WITH TRUNCATE_ONLY

GO

— Check status of auto-truncate option after truncating log

SELECT db_name(database_id) AS ‘database’, last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = db_id(‘sarab_12’)

GO

–Clean your instance

DROP DATABASE sarab_12

GO

 

APPLIES TO
MS SQL Server 2000 till 2014

 

Regards

Sarabpreet Anand

Built in Functions in SQL Server 2012

Microsoft SQL Server 2012 introduced 14 new built in functions. These new functions are:

Conversion functions

  • PARSE
  • TRY_CONVERT
  • TRY_PARSE

Date and time functions

  • DATEFROMPARTS
  • DATETIME2FROMPARTS
  • DATETIMEFROMPARTS
  • DATETIMEOFFSETFROMPARTS
  • EOMONTH
  • SMALLDATETIMEFROMPARTS
  • TIMEFROMPARTS

Logical functions

  • CHOOSE
  • IIF

String functions

  • CONCAT
  • FORMAT

In addition to the 14 new functions, one existing function has been changed. The existing LOG function now has an optional second base parameter.

Obviously all of these functions are useful in one way or the other, but I’ll be explaining five very useful functions which I feel are common\useful to all the environments.

 

1. EOMONTH

This function takes two parameters first being start_date which is mandatory and the second one is Month_to_add which is optional. This function will return the last day of the month (also termed as EOM or end of the month) for the given date, By passing the second argument also it will add the months to the start_date and then returns the last day of the month as per the final date (start_date + Month_to_add)

This can be easily understood with the help of an example:

DECLARE @date DATETIME;

SET @date = ’12/06/2010′;

SELECT EOMONTH (@date) AS Result;

–or

DECLARE @date VARCHAR(255);

SET @date = ’12/06/2010′;

SELECT EOMONTH (@date) AS Result;

Both of these queries will return the same output i.e.,

–>   2010-12-31 00:00:00.000

 

In order to find the last day of the month for any future\previous month you must use the second parameter. You can provide a positive or negative value to the second argument based on the requirements. The example below explains it in a better way.

DECLARE @date DATETIME;

SET @date = GETDATE();

SELECT EOMONTH ( @date ) as ThisMonth;

SELECT EOMONTH ( @date, 1 ) as NextMonth;

SELECT EOMONTH ( @date, -1 ) as LastMonth;

 

2. CHOOSE

This function can be used to return the value out of a list based on its index number (Note: Index no. here starts from 1) This function takes at-least 2 arguments, where the first must be an INT and the second onwards can be varchar.

The following example returns the Second item from the list of values that are provided.

Select Choose (2, ‘January’, ‘February’, ‘March’);

You can further enhance this functionality and make it dynamic, just declare a variable and depending upon the logic set the value and pass the variable as the first parameter.

Here’s the example for this:

declare @a int

set @a =2

SELECT CHOOSE ( @a, ‘January’, ‘February’, ‘March’);

 

3. IIF

If you’ve ever done some programming in Java or VB you must’ve realized what is this? Yes this is the same Conditional function which will return the value based on the condition you specified as the first argument. It takes three parameters where the first declares the condition and rest two are the results you want it to return in case the condition comes out to be true or false respectively.

A. Simple IIF example

DECLARE @a int = 45;

DECLARE @b int = 40;

SELECT IIF (@a > @b, ‘TRUE’, ‘FALSE’) AS Result;

You can also put multiple conditions using ‘and’, ‘or’ keywords which will help you to evaluate the condition based on multiple things.

 

B. Complex IIF example

DECLARE @a int = 45;

DECLARE @b int = 40;

SELECT IIF (@a>@b and @b>30, ‘TRUE’, ‘FALSE’) AS Result;

‘And’ keyword specifies that both the conditions @a>@b and @b>30 should be satisfied in order to make it true, whereas if you replace ‘and’ with ‘or’ keyword then the condition will be accepted as true even if one of them fails.

 

4. CONCAT

It’s the same concatenate function that we use in excel, it will concatenate two or more strings to make it single string.  It implicitly converts all arguments to string types. It accepts a minimum of 2 (at-least) Arguments and maximum of 254 Arguments.

The return type depends on the type of the arguments. The following table illustrates the mapping.

 

Input type Output type  and length
If any argument is a SQL-CLR system type, a SQL-CLR UDT, or nvarchar(max) nvarchar(max)
Otherwise, if any argument isvarbinary(max) orvarchar(max) varchar(max) unless one of the parameters is an nvarchar of any length. If so, then the result is nvarchar(max).
Otherwise, if any argument isnvarchar(<= 4000) nvarchar(<= 4000)
Otherwise, in all other cases varchar(<= 8000)unless one of the parameters is an nvarchar of any length. If so, then the result isnvarchar(max).

 

A. Using CONCAT

SELECT CONCAT (‘Today ‘, ‘is ‘, 6th, ‘-‘, ‘December’) AS Result;

 

5. TRY_CONVERT

Seems this Function is carrying try and catch block with itself. In earlier versions of SQL Server whenever the cast was not successful we were getting error messages but now no need to worry using this function will try to convert the data-type and return the value if it succeeds otherwise it will return null rather than that ugly error, but wait a minute there is a catch to it, if you are requesting this function to convert something which is explicitly not allowed this will definitely end-up giving an error.

 

A. TRY_CONVERT returns null

SELECT TRY_CONVERT(float,’test’)

You can decorate this with a custom message to make it more clear like:

SELECT

CASE WHEN TRY_CONVERT(float,’test’) IS NULL

THEN ‘Cast failed’

ELSE ‘Cast succeeded’

END AS Result;

GO

B. TRY_CONVERT fails with an error

SELECT TRY_CONVERT(xml, 4);

The result of this statement is an error, because an integer cannot be cast into an xml data type.

 

Hope this post helped you, do leave comments.

Regards

Sarabpreet Anand

Why the size of the backup file changes?

Hi Friends,

Have you ever noticed, while taking a compressed backup the file which gets created initially has a different size than the final file size when the backup completes. To be more precise, the file which gets created initially allocates more space than the final backup file size.

 

But the question is why?

Actually the size of the backup file depends upon the compression settings applied while taking the backup. Now since SQL Server is programmed to get maximum performance, SQL Server calculates the estimated size of the backup and initially allocates the space depending upon an algorithm to avoid frequent growth which results in size changes.

 

Now the question arises does this happens every time?

The answer is No; SQL Server can estimate accurate size of the backup if we are not using any compression settings, based on the size of the Database.

 

Then on what algorithm the SQL Engine decides the backup size?

The size is estimated from the number of allocated extends in the data files, this should be close to the reserved column in the output of sp_spaceused.  In the case of compressed backups, the size depends upon the compression setting which is again dependent upon the data as to how much it can be compressed. In this case SQL server creates the initial file equal to one third of the reserved size of the database. If at any point during backup more space is required the file is extended and if in case the final size is smaller than the initial size (which will be the case most of the time), SQL Server will trim the backup file to the actual used size.

For Example: The DB Size is 50 GB, the target backup size decided by SQL Algorithm comes out to be 17 GB, means the initial size of the backup file would be somewhere around 17GBs but the final size comes to 10GB, because of the compression. The file gets trimmed before releasing the lock since the actual used size was just 10 GBs.

 

Regards

Sarabpreet Anand