How to resolve Space issue in SQL server 2005 TIP #93

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.
Enjoy!!
RJ

Data Compression–A unique feature PART–II TIP #92

In last post TIP #91  we talked about  What is Data compression ? What are the  features ?  Now in this tip we will take implement the data compression with basic steps.
So lets follow the steps
1) Right click on the table on which you need to implement compression. You will get following menu just select manage compression as shown in below figure

2) Once you click on the above option you will get following screen.

3) Now you can choose compression type from dropdown of the row either Page or Row . You can calculate estimated space saving by selecting the compression type. as shown in below figure
a) Page compression (In the last column you will find requested compressed space)

b) Row Compression (in the last column you will find request compression space)

Now once you selected appropriate compression type you can click on Next button of the wizard you will get below screen

Now you can generate the script for this compression type. We can directly run the script on database itself if we are sure which with compression type.


Once we run the above command on database we are good to go and our table is compressed.
I hope this steps will help you to compress our database tables.
Enjoy compression !!
Rj !!

A Comprehensive Manual to Manage SQL Server Database

SQL Server is a uniquely designed Relational Database Management System developed by Microsoft. Its basic functionalities include storing, manipulating and retrieving data requested by multiple software applications running on a single computer or on any other machine across a network.

Details of SQL Server Database:

For storing SQL Server data individual files are created for storing data and log information.

·         Primary data file:

This file contains the information required for startup of the database and points to the other files in the database. Each database contains a single primary file that stores tables, indices and procedures. The default file extension for the primary file is .mdf.

·         Secondary data files:

These files are user defined and store the complete user data. Once the database exceeds the maximum size limit for a single Windows file, the secondary files are employed to accommodate the increasing database. The SQL server can have more than one secondary file, each with .ndf default file extension.

·         Transaction Log Files:

These files contain the log information that includes the complete modification history of the database. The Log files effectively analyze the damage causing actions and further recover the database. Each database compulsorily contains at least one log file. The default file extension for the transaction log files .ldf. Read more

Backup SQL Server Database:

Providing a backup for the complete SQL Server database to an off - site location is mandatory to protect the server data from potential catastrophe. This will help you to recover data from the following issues:

ü  Media failures.

ü  User errors, such as dropping a table.

ü  Hardware failures, such as hard disk damage or disconnection from server etc.

Let us discuss the various backup types:

·         Copy only backup:

This is a special purpose backup which is independent of regular sequence of SQL Server backups.

·         Full data backup:

This type includes backing up the entire data of a specific database, the file groups or files and the logs.

·         Differential data backup:

This backup is based on the latest full backup of a complete or partial database, files or file groups and contains the data extents changed since the previous partial backup, known as differential base.

·         Log backup:

Using this type you can create backup of the transaction logs that includes the log records.

·         File backup:

A single database file or file groups can be backed up using this type. 

·         Partial backup:

This backup contains data from a limited file groups in the database, including data in primary file group.

The backed up data can be saved on to backup devices, such as, tapes or disk files. For more details visit here

SQL Server Database Encryption:

The database is encrypted using an encryption key so as to convert the data into a non comprehensive format, referred to as cipher text. This is practiced in order to prevent unintended users from accessing the data. However, the data can be read or manipulated only by the one possessing the decryption key to convert the cipher text back into its original understandable form. SQL Server uses encryption key to protect the database, credentials, and information stored in the server. There are two types of keys- symmetric and asymmetric.

The symmetric key is generated during the initialization of SQL Server. These are the encryption keys using the same password to encrypt and decrypt data. Whereas, the asymmetric keys use different passwords for both encryption and decryption. The public key is used to encrypt data while the private key is used for decrypting. The public and private keys are created by the operating system. For more details about encryption visit here

Password Policy for SQL Server Database:

SQL Server makes use of the Windows password policy mechanisms. This policy applies to a login that is using SQL Server authentication. It also applies to a contained database user with the password. SQL Server can apply the complexity and expiration policies of Windows to its passwords. However, there are a certain guidelines to be followed while setting the password.

·         The password can be 128 characters long.

·         It should not contain the user’s account name.

·         The password must be at least eight characters long.

·         It must contain characters from three of the four categories mentioned as follows:

- Latin upper case letters (A to Z)

- Latin Lower case letters (a to z)

- Base 10 digits (0 to 9)

- Non alphanumeric characters (such as !, $, %, #).

SQL Server Transaction Log File Analysis:

Every database contains at least one transaction log file with .ldf default file extension. These transaction log files are the store for the complete modification history of the server database. It contains the transaction details of all the actions including update, insert, delete, etc. In case of any damage caused to the SQL Server database due to any modification action, the SQL transaction log file proves useful in analyzing as to which action lead to the damage, further successfully brings back the database. For more details visit blog

SQL Server Database Recovery:

Though SQL server is known to be extremely reliable, it cannot be concluded that it is immune to corruption owing to the frequent retrieving, managing or storing of data.

SQL Server facilitates a utility to backup and restore operations, provided they occur within the domain of Recovery Model for database. The database Recovery Model is a property that is responsible for controlling the transaction log maintenance. There are three recovery models available, namely, simple, full and bulk – logged. The server usually employs the full or simple recovery model to recover the sql master database. However, at any point of time, the database can be switched to another recovery model.

Data Compression– a unique feature of SQL Server TIP #91

A part from performance many times we faced challenges related to space  of our database. Sometimes our database is actually taking huge space and sometimes it our mistake due to which it took space.

It is worst condition when you are on a dedicated hosting or cloud hosting with limited space. In such case “Compression” is one of the best option.

This feature introduce in SQL SERVER 2008 onwards with Enterprise & Developer Edition.

By the name it is clear that it compress the data and provide more space on disk.

There are 2 options available in Data compression

Row level compression:-

 Row level compression primarily turns fixed-length data-types into variable data-types, thereby saving space. It also ignores zero and null values saving additional space. Because of this, more number of rows can be accommodated in a single data page.

Page level compression:-

Page level compression initially performs Row Level compression and adds two additional compression features – Prefix and Dictionary Compression. Page level compression offers better space saving than row level compression. Although the page level compression produce more space but obvious the CPU utilization is higher in page level compression. Overall we can say the page level compression is highly CPU consuming operation means it involves lot of  CPU efforts for compress & de compression. So choosing this option requires a high CPU & RAM configuration machine. Due to this feature it mostly used in data warehouse database where repeated values occurs. using this option with a table where lots of write operation occurs in day to day activity will be a bad idea.

Now we aware of data compression and its two options. Now lets discussed some of the major points before using this valuable feature

1) Data compression is SQL SERVER feature so we don’t need to do any tweak or rewrite our application code which is a good thing.

2) Compression is real time so again you don’t need to re run a maintenance job each time to compress the data.

3) As mention earlier you have to pay bit CPU cost for compression.

4) with Compression data files MDF files, ldf files and buffer cache size can be improve.

5) Compression not compress FILESTREAM data object which is obvious.

6) Although maintenance of compressed index is higher.

In next post I will show simple steps for implementing this best feature.

Enjoy !!!

Thanks

RJ!!!

Kill–use this weapon carefully in SQL SERVER . TIP #90

When we heard “Kill” then first impression of this word is very bad. We always scare with this word.

In real world we never want this action  from anyone but in SQL Server case it is very helpful and help us many times.

This feature we can use but very carefully. It is something like we have to kill a cruel giant not the common innocent person.

Lets understand this by an example.

Suppose we wrote a stored procedure or function and by mistake we wrote a condition which is never ending. We didn’t realize this and when we run the stored procedure/function the SQL Server hangs , system is slow and many other issues occur and our stored procedure which we run still running and laughing on us.

In such situation we have to kill that particular stored procedure execution statement which is cruel giant and eating the resources.

See below statement

WHILE 1=1
BEGIN
  SELECT 'Infinite Loop'
END

It is never ending loop. and it is running on our SQL SERVER and will never stop.

Now first step is to Identify the process id for this statement for this we can use either sp_Who or sp_who2 which we discussed in TIP #82.

Now when we run it you will find on Database “IndiandotnetDB” we have a SPID 53 which is last statement on below image. This SPID 53 is reference of above while loop which we have to stop any ways.

Now we know SPID so we can stop or KILL  as shown below the syntax is very simple of KILL

KILL SPID

Now we are good to go.

Note: Please use this KILL statement very carefully.

I hope this tip will help you some where to KILL or stop SQL SERVER enemies.

Enjoy !!!

RJ!!!