Unable to Login “Microsoft SQL Server, Error: 18456” error in SQL SERVER 2008 R2

Dear Friends,
Sometimes it happens when you did everything correct like Installation with mix mode, created new user assign credentials etc but still not able to login with the username and password credential.
You get “Unable to Login – Microsoft SQL Server Error: 18456”.
To resolve this error you need to do following steps
1) Login with Windows Authentication
2) Open server properties using right click
3) Go on security TAB
4) Check SQL Server And Windows Authentication mode Checkbox

Now you can login with user credentials.

Thanks & Best Regards,
Rajat Jaiswal

Database Space issue in SQL server 2005 (New feature VARDECIMAL)

Hello friends,

Many times we face space issues with our database. To resolve this problem SQL Server 2005 provided one more solution which is VARDECIMAL feature.

** VARDECIMAL feature exist in SQL SERVER’s Developer & Enterprise edition.

** This feature is not available in SQL Server 2005’s Standard edition.

I hope by the name it is clear that what VARDECIMAL functionality is.

If not then please read below line VAR + Decimal = VARDECIMAL

It is just like VARCHAR features means it will consume space which is actual require to store the decimal value. It ignores null and zero value to save space

. To understand it more lets implement it.

I am taking an example here

First we will check the database size without applying the VARDECIMAL Feature .

To check the current space use below command

Use Your Database name;

GO

Sp_spaceused;

The above command provides you current space used by the database. Now we will check whether the VARDECIMAL feature is enabled or not to check this we can use two way go to database property and check it as shown in below fig

Or

use below command

exec sp_db_vardecimal_storage_format

Now to enable the property on particular database either make the property true in above fig

or use following command

exec sp_db_vardecimal_storage_format ‘Your DatabaseName’, ‘ON’

Once the database has VARDECIMAL storage format.

We need to apply VARDECIMAL storage property on actual table. Before applying the feature first check the space of existing table which can be determine by following command.

Sp_spaceused table Name;

Now check whether the table has VARDECIMAL feature enable or not. To check this feature just right click on table and check table property.

Now to enable this property use following command

sp_tableoption ‘YourTableName’, ‘vardecimal storage format’, 1

Once you run this option on database you will find the property on the table is now true.

Now to check how much space gain you got use again

Sp_spaceUsed table name ;

command.

Now compare the space with existing result and check how much you gain in terms of space.

I hope this feature will help you in some where if you are using SQL Server 2005 Enterprise edition.

In the next session I will explain what new feature available in SQL Server 2008 R2 for same space problem.

Till than

Thanks & Best Regards,

Rajat Jaiswal

How to set sql server database mode to single user from multi user and vice versa

Dear Friends,

Sometimes you want to restrict your database. You want that your database is access by only by you (Single handed) no person can access it.

To achieve this task SQL server provide a simple command with the help of this command your database will have only single connection. Below is the command which allow only single connection

 ALTER DATABASE MYADDRESS  SET SINGLE_USER WITH NO_WAIT

Now after your maintenance you want to revert back to Multiuser mode then below is the  command which help you in that.

ALTER DATABASE YourDatabaseName  SET MULTI_USER WITH NO_WAIT

So enjoy.

Thanks & best regards, Rajat Jaiswal

Change your database mode from Read only to Read write and vice versa

Dear Friends,

Some time you want to change your database mode like make your database to read only and then again read write mode. So the simple scripts which help you to make your database read only

 ALTER DATABASE YourDatabaseName  SET READ_ONLY WITH NO_WAIT

With the above command your database comes in Read only mode

Now how can you change the mode back to read write mode So below is the again simplest script

ALTER DATABASE YourDatabaseName SET READ_WRITE WITH NO_WAIT

With  the above command your database comes in read write mode

Thanks & best regards,

Rajat Jaiswal