Easy Solution On How to Stop Growing Log File Too Big

In various organizations, huge SQL databases are equipped, which perform more than millions of transactions per hour. A SQL server database has data files and transaction log files. Data files store the user data and transaction log files store all the changes user made in the database and all the details of the transactions performed while making changes.

Now, the issue is that this feature of logging the details, every time changes are made in SQL server can not be controlled or stopped. This causes a grave issue when the size of SQL server overgrows. However, the way in which these log files grow and configure can be controlled. So, to avoid SQL server log file growing unexpectedly, consider any of the following methods given below. Also, to manage the large area size, it is good to shrink log file size, we will discuss the ways to resolve the same issue in this content.

SQL Server- Solutions to Stop Growing Log File Too Big

There are numerous ways for truncating sql ldf too big file. Some of the chief solutions have been provided in the following segment of this content.

  • Monitor default Size Limit: In case SQL ldf file is growing too big then, put a large default size limit on the SQL server, so that it does not expands automatically and overloads the SQL server database.
  • Using the Memory Units: Remember to configure the expansion of log files by making use of the memory units instead of percentage if the SQL transaction log file grows quickly.
  • Changing the recovery model: Simple Recovery model definitely helps in controlling and shrinking the log file size. Based on how crucial the data is, user can choose any of the following recovery models namely,
    1. Simple Recovery Model
    2. Bulk-logged Recovery Model
    3. Full Recovery Model

In the simple model, the most recent backup of the database of SQL server is recovered while in the bulk-logged or full recovery model, database can be recovered up to the point of failure. This recovery is done by restoring transaction log file.

By default, Full recovery model is set. Then, user has to regularly back up the transaction log files to prevent them from becoming too large and to remove inactive transactions from transaction log. Also, consider this when taking back up of .ldf files.

NOTE: If user is defragmenting indexes, make use of DBCC INDEXDEFRAG and not DBCC DBREINDEX. If DBCC DBREINDEX is used then, transaction log file might expand drastically.

Using manual solution:

If maintenance is not carried on regularly, log file size grows too big. Therefore, it is recommended to take these manual steps before it takes up all available disk space. Now, let us look at the method to shrink a SQL Database’s transaction log file:

  1. Firstly, Open the SQL Server Management Studio and then log in to the proper SQL instance.
  2. In Object Explorer tree, expand the Database folder >> select database with large .ldf file.
  3. After this, Create a full backup of database by right-clicking on it >> Select Tasks >> Back Up.
    • User should make sure that Backup type is set to Full then, Delete any existing destinations >> add a new Disk destination.
    • Browse a location with a lot of free disk space >> rename the backup file with the .BAK extension.
    • Choose the bullet option for Overwrite all existing backup sets on the ‘Options’ page.
    • Finally, user can click on the ‘OK’ button to start the process of taking backup of log files.
  4. Similarly, create a transaction log backup of the database in the same manner as done above.
    • Right-click on database >> Select Tasks >> Backup and assure that backup type is set to Transaction log.
    • Choose the bullet option for Overwrite all existing backup sets on the ‘Options’ page.
    • Finally, user can click on the ‘OK’ button to start the process of taking backup of log files.
  5. The closing step is the shrinking of transaction log files by right-clicking database >> Tasks >> Shrink >> Files

NOTE: User may repeat steps 3,4 and 5 until the .ldf file size becomes physically smaller.

Conclusion

In this content, we have discussed various solutions on how to truncate .ldf files if transaction file size becomes too large. This is necessary in order to manage data with ease. Manual method and some general solutions have been written in this content to enlighten users when they come across such issues while dealing with SQL Server.

Learn How and When to Shrink Transaction Log File

Transaction logs consist of the records regarding activities taking place on the server database to which they are associated. At one point of time, these files run out of memory to record any more logs. Therefore, it is necessary to backup logs so that they can be cleared off whenever required. Shrinking is of the many procedures used for managing the transaction log files. The following segment acts as a guide on when to shrink transaction log files and what are the best practices to adopt when you do so.

Whys and Wherefores of Shrinking Transaction Log

When transactions are concerned, there are going to be instances where there is lack of storage space. If a transaction file consists of unused space it can be regained for future use. Doing this reduces the unnecessarily growing size of a transaction log file. What is being done here is a procedure known as shrinking of the log file.

NOTE: It is only possible to shrink a transaction log file in a scenario where the database is in an online state and a virtual log is free. However, in certain cases until the next truncation a log cannot be shrunk.

Generally, the truncation process occurs on its own as part of the simple recovery model when a database it is associated to is backed up. In addition, it also occurs as part of the full recovery model where the transaction is backed up. Nevertheless, a number of factors can affect the truncation procedure and delay it.

How Does Shrinking Take Place?

The procedure of shrinking a transaction log file is nothing but the removal of one or more than one virtual logs that exist. The unit of the reduction in size is equal to a virtual log file.

This can better be understood with an example:

There is a log file of 600 MB that is divided into multiple portions, i.e. 100 MB of virtual log file each, which makes 6 of them. This is done because the size of a log can only be decreased with an increment of up to 100 MB each. The variation in sizes can be 500 or 600 MB and not 533 or 625 MB.

Any virtual log that consists of active log records, i.e. active virtual log – is a part of the logical log file. Therefore, such virtual logs cannot be removed.

Things to Remember

In order to performing the shrinking of log files, it is necessary to first monitor the log space then a sequence of two stages follow up, i.e. shrinking of log file and then monitoring the shrinking events that have taken place.

To monitor available log space:

  1. DBCC SQLPERF (Transact-SQL)
  2. Sys.database files (Transact-SQL) – Lets you see the – size, growth, and max_size columns in association to the log

To shrink a transaction log:

  1. DBCC SHRINKFILE (Transact-SQL)
  2. Shrink transaction log file via SSMS

To monitor shrinking events:

  • Event Class – Log File Auto Shrink must be used

When a transaction log is shrunk removal of virtual logs (inactive) takes place. Therefore, in order to remain on the safer side and ensure database integrity during the procedure, it is highly recommended that backups be maintained.

Tips for SQL Server Transaction Log Backup

  1. In order to truncate SQL Server transaction log files, you must have them backed up. Not having regular backups of the transaction log may result in filling up the disk space.
  2. A full backup is not recommended, as it does not include the transaction logs.
  3. Use of ‘BACKUP LOG’ must be made in order to backup transaction logs.
  4. If transaction logs are not to be backed up according to the Database Administrator, Simple Recovery is suggested.

Necessity Of Backing Up Tail Log When Restoring The Database

One of the features that have been incorporated with MS SQL Server 2005 and its newer versions is Tail-Log Backups, which is mainly associated with backup and restore of SQL Server databases using full or bulk-logged recovery models. In the article, we will be learning about the necessity of backing up Tail log when restoring the database of SQL Server.

What is Tail-Log Backup?

The Tail-Log Backups can be defined as the process to capture all the log records that have not been backed up (as the name suggests, the tail of the log). It is a feature that was introduced in SQL Server 2005 and its later versions. This form of log backup is mainly done to avoid loss of work and to make sure that the log chain is kept intact. It is necessary to have a backup of transaction log tail in order to start restore database operation to the point of failure. The tail-log backup will be the last backup of which one can use for the recovery purpose of database.

Though Tail-Log Backup proves to be quite beneficial in the cases stated above, there are situations that does not require Tail-log backup. They are:

  • There is no need of Tail-log backup if recovery point is already present in an earlier log backup
  • When the user needs to replace the database, that may include operations such as overwriting or moving database to different location
  • When User does not need to restore the database to a point of time after the latest backup of the database

Scenarios that Requires Tail-Log Backup During Restore

Here are some of the common scenarios where a tail-log backup is needed:

  1. When the database is Online
    • If the user wishes to perform restore operation of the online database, the first step is to back up the tail of the log. It is recommended to use ‘WITH RECOVERY’ option of the BACKUP T-SQL statement in order to avoid risk of having errors for the online database.
    • NORECOVERY can be used to back up the tail of the log and to keep the database in RESTORING state. It is useful when saving the tail log of the log before RESTORE operation or failing over to a secondary database.
    • User can use both NO_TRUNCATE and NORECOVERY options together to create a log backup, which will skip the log truncation and keep the database into RESTORING state in an atomic state
  2. When the database is Offline
  3. If the database is in offline state and fails to start, user needs to restore the database by creating backup of the tail of log. It is optional whether to use WITH NORECOVERY or not, as no transactions can occur during this time.

  4. When the database is damaged
  5. In case of database that is damaged, user can take tail-log backup with the help of WITHCONTINUE_AFTER_ERROR option of the BACKUP statement. Another option is to use CHECKSUM

Note: Backing up the tail-log on a damaged database will only succeed if the log files are in healthy state, the database is in the state that supports tail-log backups and no bulk-logged changes is present in the database. If the tail-log backup cannot be created, any transactions committed after the recent log backup will be lost. In such scenario you can take the help of third party SQL database repair to recover lost SQL database.

Tail-Log Backups with missing Backup Metadata

As discussed in the above section, the tail-log backups have the capability to capture the tail of the transaction log in case of the offline database, missing data or damaged files. It might lead to incomplete metadata from the restore information commands and msdb. Even though the metadata may be incomplete, the log that has been captured is complete and usable.

  • If the tail-log backup has incomplete metadata, the values of the columns of has_incomplete_metadata and HasIncompleteMetadata are set to 1.
  • If metadata in tail-log backup is incomplete, the backupfilegroup table will be missing most of the information related to filegroups during the tail-log backup. The table backupfilegroup will contain several columns that are having null values.

Conclusion

It can be concluded that there is a high necessity of backup up tail log when restoring the database in SQL Server. If the user does not backup the tail of the log, any transactions may be lost that has occurred since the last backup of the database. The article has discussed about tail log backup and the scenarios where backup of the tail log is needed for restoring the database.

Steps to Know How to Rebuild Index in SQL Server

What is Index in SQL Server?

In RDBMS, there is a data structure that is used to improve the performance of SQL server at time of retrieving any data and this data structure is known as database index in SQL server. Instead of searching each row in table, one can use indexes that will provide a way of locating data and hence, accessing the database table.

What is Index Rebuilding?

The process of recreating a new structure of index in SQL Server for maintaining health of database is known as Index Rebuilding. There is a Rebuild option in SQL Server through which one can recreate the database index. If index is disabled from database, then one can use Rebuild operation for returning back to its working state.

NOTE: If user interrupts/cancel the working of rebuilding operation, then all the modification made will be rolled back to its previous state.

Need to Rebuild Index in SQL Server

Generally, while performing changes or modification index fragmentation takes place in form of wasted memory (termed as Gaps in data pages) and logical fragmentation. Gaps in data pages result in reduction of rows, which are archived in cache of the server. On the other hand, logical fragmentation occupies the disk storage space with useless data. Therefore, the only way to prevent rows reduction and save disk storage space is to rebuild indexes in SQL server. This will highly boost up the speed and performance of the database.

When to Rebuild Indexes?

If one constantly rebuild index in SQL server, then it will effect the physical storage of machine or will result in any other hazardous situation. Therefore, it is important to learn that when to rebuild indexes.

  • If there is rapid index fragmentation, and user eagerly wants to execute Rebuild Index tasks with all other tasks related to database maintenance, then they can do so for boosting server performance.
  • For weekly maintaining database and boosting server performance, user can perform rebuilding operation once in a week. However, it would be risky if user do not rebuild index since a week.
  • Another condition is when user has not performed rebuilding from long time. In this condition, users will have to take following two alternatives into consideration:
    • Use Reorganize Index tasks with Update Statistics tasks
    • Use online version of Rebuild Index tasks

Measures to Rebuild Indexes

A very common question among SQL server users that How to rebuild index in SQL server? Well, by making use of Maintenance Plan Wizard of SQL server in following way, user can perform rebuild operation:

  • Launch the Maintenance Plan Wizard on your machine
    Maintenance Plan Wizard
  • Click on the scroll down button of Databases field & then select databases on which you want to perform rebuilding operation. You will have to select any of the displayed databases and then click on OK button to continue.
    select displayed databases
  • Now you will return back to previous wizard. In this wizard you will come across two options:
    two options
  • Object: This option is only enabled with some selective database. In other words, it will not be enabled with all the databases of the server. However, there are three options provided to you in this field i.e. Tables, Views, and Tables and Views. If you select Tables and Views option, then Rebuild operation will be applied to indexes related to tables and indexed views of the selected database. Well as per your need, you can also choose Tables or Views option.
    options
  • Selection: By clicking on selection drop-down button, you can choose that whether you want to perform rebuilding operation on all objects or selected objects. If you want to perform selective operation, then enable These objects option and select the objects that you want to take in operation; else activate All objects option. After finishing with all your selection procedure click on OK button.
    all objects or selected objects
  • Next come Free space options field in which you are provided with two options and by default ‘Reorganize pages with default amount of free space’ option is enabled. You can make use of any one of the two options to clean the Gaps in data pages.
    Free space options
  • Now comes an advance options field. With help these two option your will be able to determine that how much benefit will you have during rebuilding procedure.
    advance options field
  • The last step of rebuilding indexes in SQL server is to define a valid schedule that can run your Rebuild Index job.
    Rebuild Index job
  • Fill all the mandatory fields of this window with correct details and click on OK button.
    Job Schedule

Conclusion

How to rebuild index in SQL server is query that arises when user has to deal with index fragmentation problem. After going through whole blog, one can say that rebuilding index is an effective resource intensive tasks. In case, if the above workaround failed to rebuild indexes in SQL Server then you can also go with a SQL Recovery tool to fix the index fragmentation issues.

Usage of DBCC SQLPERF for Transaction Log Management

Problem

Transaction log is one of the main aspect of all databases. In some circumstances, the transaction logs get large and not know how much space is utilized by it, become a major issue. It effects the performance of SQL Server because of which user faces problem while working on it. So, how to resolve this issue to improve the SQL Server performance?

How to Use DBCC SQLPERF to Check Transaction Log Performance ?

User can overcome from the problem by checking the transaction log performance. They can use DBCC SQLPERF (logspace) syntax as it helps to give the information of size of transaction logs. It is used to manage various things such as it shows a list of all transaction logs as well the space that is used in transaction log. It clears the wait and latch stats. It also lists all the wait stats on SQL Server.

NOTE: Before using DBCC SQLPERF syntax, some permissions are required as mentioned:

  • To run this syntax on SQL Server, View Server State permission is required.
  • Alter Server State approval is needed to reset wait and latch statistics on SQL Server.
  • View Database State permission is required on SQL Database premium tiers.
  • SQL Database admin account is needed on Basic Tiers and SQL Database Standard.

There are some options which user can use to run DBCC SQLPERF syntax according to their requirement. Each option has its own importance as discussed below.

WAITSTATS

When there are some troubleshooting performance issue in SQL Server and it is difficult to understand from where the check. Then, it is recommended to look at high level on your SQL Server and after that find out the root cause. User can use the DBCC SQLPERF WAITSTATS; it gives the high-level information that is required to succeed. Each time a process, which is inside the SQL Server that has to wait for anything, a time that is spend on it is tracked. The wait could be tracked because of the delays in reading data, writing data or some external way. The waiting procedure is allocated a wait type. The wait types are not offensively descriptive. It requires a translation into something that is more meaningful. If the waits are reduced in SQL Server then, user can attain a better overall performance.

LOGSPACE

User can use DBCC SQLPERF LOGSPACE, as it helps to give the details of the Log Space that is used to increase or decrease on database. It gives the information of the used recovery models and transaction log backups that user is using. Even it also gives the details of the transaction logs for auto grow and auto shrink. Users can also check the size of file that has been increased or decreased. It helps to resolve the problem that is faced by them.

‘sys.dm_os_latch_stats’,CLEAR

DBCC SQLPERF (‘sys.dm_os_latch_stats’ clear) is lightweight synchronization mechanism, which defend the access to read as well as change in memory structure. It is held only for the duration of operation. It acts as the synchronization mechanism that helps to avoid the updation of two threads at the same time. However, if the thread requires a latch it will be moved from the running to suspended mode. Later on, it is placed on the wait list to await for the notification, which the latch has acquired in the mode of request.

‘sys.dm_os_wait_stats’,CLEAR

DBCC SQLPERF (sys.dm_os_wait_stats’ clear) offers a necessary metrics for analyzing SQL Server performance issues. Users can gathering all the information about the completed waits that are encountered at time of executing threads. It is a useful way to identify the issues such as waits on locks or IO latency issues. All the counters are reset when the SQL Server is restarted or when the commands are run.

Conclusion

In the above blog, a way to improve the SQL Server performance by using DBCC SQLPERF is discussed. There are various options being discussed that helps user to overcome from the problem. User can utilize any of the mentioned option to increase performance of SQL Server.

How to add new instance in SQL Server

  1. All program -> SQL Server Installation center
    In windows 8 -> Search -> SQL Server Installation Cepter
  2. From left menu select “Installation->New Sql server standalone installation or add features”
  3. Browse CD or Physical directory of SQL Server
  4. Auto run “Setup support rules” click ok
  5. Product update -> click next
  6. Install setup files -> click next
  7. Setup support rules and click next
  8. Select Installation type -> select “Perform new installation of SQL Server 2012” and click next1
  9. Enter product key and click next
  10. I accept the licence key
  11. Setup rules -> select “All features with default” -> Click next
  12. Feature selection -> Click Next
  13. Instance Configuration -> Named instance -> Type SQLExpress
    And InstanceId -> Type SQLEXPRESS and click next2
  14. Server configuration -> click next
  15. Database engine configuration -> Select windows authentication mode -> Click add current user and click next3
  16. Click next …. Next … next like as usual sql server installation.
  17. After complete installation. Try to enter using new created instance

4

SQL Join with Microsoft SQL Server

What is SQL Joins?

A SQL join clause is used to combine records from two or more tables in a database based on common field between them. It creates a set of rows in a temporary table.

There are different types of joins available in SQL:
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• SELF JOIN
• CARTESIAN JOIN/CROSS JOIN

To understand JOINs, let’s consider the following two tables Customers and Orders is as follows

Customers

01-Customer02

Orders

02-Orders02

INNER JOIN
INNER JOIN returns match data between tables. It returns rows when there is at least one match in both the tables.

03-InnerJoin

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
INNER JOIN Orders ord
ON Cust.Id = ord.CustomerId;

Output:

Id          Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

2           Hamidur Rahman                          4000.00                                 2014-01-10

2           Hamidur Rahman                          5000.00                                 2014-01-10

3           Jamilur Rahman                            3000.00                                 2014-02-13

4           Hasan Sarwar                                  600.00                                  2014-03-22

LEFT JOIN/LEFT OUTER JOIN
Left Outer Join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

04-Left join

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
LEFT JOIN Orders ord
ON Cust.Id = ord.CustomerId;

Output:

Id          Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

1           Mahedee Hasan                               NULL                                    NULL

2           Hamidur Rahman                           4000.00                                 2014-01-10

2           Hamidur Rahman                           5000.00                                 2014-01-10

3           Jamilur Rahman                              3000.00                                 2014-02-13

4           Hasan Sarwar                                  600.00                                  2014-03-22

5           Kamal Hossain                                NULL                                    NULL

6           Imran Khan                                     NULL                                    NULL

7           Abu Sayem                                       NULL                                    NULL

RIGHT JOIN/RIGHT OUTER JOIN
Right outer join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

05-Right join

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
RIGHT JOIN Orders ord
ON Cust.Id = ord.CustomerId;

Output:

Id               Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

2                 Hamidur Rahman                        4000.00                                 2014-01-10

2                 Hamidur Rahman                        5000.00                                 2014-01-10

3                 Jamilur Rahman                           3000.00                                 2014-02-13

4                 Hasan Sarwar                                600.00                                  2014-03-22

NULL        NULL                                               800.00                                  2014-03-22

FULL JOIN /FULL OUTER JOIN
Full outer join is combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match

06-Full Join

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
FULL JOIN Orders ord
ON Cust.Id = ord.CustomerId;

Output:

Id                    Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

1                      Mahedee Hasan                             NULL                                    NULL

2                     Hamidur Rahman                          4000.00                                 2014-01-10

2                    Hamidur Rahman                           5000.00                                 2014-01-10

3                    Jamilur Rahman                             3000.00                                 2014-02-13

4                   Hasan Sarwar                                   600.00                                  2014-03-22

5                   Kamal Hossain                                 NULL                                    NULL

6                   Imran Khan                                      NULL                                    NULL

7                    Abu Sayem                                       NULL                                    NULL

NULL          NULL                                                800.00                                  2014-03-22

SELF JOIN
The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Syntax:

SELECT A.Id, A.Name, B.Name AS Introducer
FROM Customers A, Customers B
WHERE A.IntroducerId = B.Id

Output:

Id          Name                                               Introducer

———– ————————————————– ————————————————–

1           Mahedee Hasan                             Hamidur Rahman

2           Hamidur Rahman                         Mahedee Hasan

3           Jamilur Rahman                           Hamidur Rahman

4           Hasan Sarwar                                Jamilur Rahman

5           Kamal Hossain                              Hasan Sarwar

6           Imran Khan                                   Kamal Hossain

7           Abu Sayem                                     Imran Khan

CARTESIAN JOIN/CROSS JOIN
The SQL cross join produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause uses in the second table

07-crossjoin

Syntax:

SELECT cust.Id, cust.Name, ord.Amount, ord.[Date]
FROM Customers cust, Orders ord

SELECT cust.Id, cust.Name, ord.Amount, ord.[Date]
FROM Customers cust CROSS JOIN Orders ord

Output:

Id          Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

1           Mahedee Hasan                            4000.00                                 2014-01-10

2           Hamidur Rahman                       4000.00                                 2014-01-10

3           Jamilur Rahman                          4000.00                                 2014-01-10

4           Hasan Sarwar                                4000.00                                 2014-01-10

5           Kamal Hossain                             4000.00                                 2014-01-10

6           Imran Khan                                  4000.00                                 2014-01-10

7           Abu Sayem                                     4000.00                                 2014-01-10

1           Mahedee Hasan                            5000.00                                 2014-01-10

2           Hamidur Rahman                       5000.00                                 2014-01-10

3           Jamilur Rahman                          5000.00                                 2014-01-10

4           Hasan Sarwar                               5000.00                                 2014-01-10

5           Kamal Hossain                             5000.00                                 2014-01-10

6           Imran Khan                                  5000.00                                 2014-01-10

7           Abu Sayem                                    5000.00                                 2014-01-10

1           Mahedee Hasan                            3000.00                                 2014-02-13

2           Hamidur Rahman                       3000.00                                 2014-02-13

3           Jamilur Rahman                          3000.00                                 2014-02-13

4           Hasan Sarwar                               3000.00                                 2014-02-13

5           Kamal Hossain                             3000.00                                 2014-02-13

6           Imran Khan                                  3000.00                                 2014-02-13

7           Abu Sayem                                    3000.00                                 2014-02-13

1           Mahedee Hasan                            600.00                                  2014-03-22

2           Hamidur Rahman                       600.00                                  2014-03-22

3           Jamilur Rahman                          600.00                                  2014-03-22

4           Hasan Sarwar                               600.00                                  2014-03-22

5           Kamal Hossain                             600.00                                  2014-03-22

6           Imran Khan                                  600.00                                  2014-03-22

7           Abu Sayem                                    600.00                                  2014-03-22

1           Mahedee Hasan                            800.00                                  2014-03-22

2           Hamidur Rahman                        800.00                                  2014-03-22

3           Jamilur Rahman                          800.00                                  2014-03-22

4           Hasan Sarwar                               800.00                                  2014-03-22

5           Kamal Hossain                             800.00                                  2014-03-22

6           Imran Khan                                  800.00                                  2014-03-22

7           Abu Sayem                                    800.00                                  2014-03-22

Additional Information Related JOIN

Left Outer Join Where Null
Left outer join where null returns all the rows from the left table in conjunction without the matching rows from the right table.

08-left Join and Null

Syntax

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
LEFT JOIN Orders ord
ON Cust.Id = ord.CustomerId
where ord.CustomerId is null

Output

Id          Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

1           Mahedee Hasan                              NULL                                    NULL

5           Kamal Hossain                               NULL                                    NULL

6           Imran Khan                                    NULL                                    NULL

7           Abu Sayem                                      NULL                                    NULL

Right Outer Join Where Null
Right outer join where null returns all the rows from the right table in conjunction without the matching rows from the left table.

08-Right Join and Null

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
RIGHT JOIN Orders ord
ON Cust.Id = ord.CustomerId
where Cust.Id is null

Output

Id              Name          Amount        Date
———– ————————————————– ————————————— ———-
NULL       NULL          800.00        2014-03-22

Full Outer Join Where Null
Full outer join is combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match

10-full Join and Null

Syntax

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
FULL OUTER JOIN Orders ord
ON Cust.Id = ord.CustomerId
WHERE Cust.Id is null
OR ord.CustomerId is null

Output

Id          Name                                                  Amount                                  Date

———– ————————————————– ————————————— ———-

1           Mahedee Hasan                                  NULL                                    NULL

5           Kamal Hossain                                   NULL                                    NULL

6           Imran Khan                                        NULL                                    NULL

7           Abu Sayem                                          NULL                                    NULL

NULL        NULL                                            800.00                                  2014-03-22

Recovering SQL Server Database from Suspect Mode

Sometimes we have to face a critical situation when SQL Server database going to Suspect Mode. In that moment no work can be done on database. Database may go into suspect mode because the primary file group is damaged and the database cannot be recovered during the startup of the SQL Server

Reason for database to go into suspect mode:

  1.  Data files or log files are corrupt.
  2.  Database server was shut down improperly
  3.  Lack of Disk Space
  4.  SQL cannot complete a rollback or roll forward operation.

How to recover database from suspect mode:

1.    Change the status of your database. Suppose database name is “BluechipDB”

EXEC sp_resetstatus '';

Example:

EXEC sp_resetstatus 'BlueChipDB'

  1.  Set the database in “Emergency” mode
ALTER DATABASE  SET EMERGENCY;

Example:

ALTER DATABASE BlueChipDB SET EMERGENCY
  1.  Check the database for any inconsistency
DBCC CHECKDB('');

Example:

DBCC checkdb('BlueChipDB')

4. If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.

ALTER DATABASE  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Example:

 ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

5. For safety, take the backup of the database.

6. Run the following query as next step. Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);

Example:

DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)

7. Finally, bring the database in MULTI USER mode

ALTER DATABASE  SET MULTI_USER;
ALTER DATABASE [BlueChipDB]  SET MULTI_USER
  1.  Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.

Retrieve Store Procedure’s Output parameter by C# (ASP.net)

How to retrieve store procedure’s output parameter by C#? Here I have explained with a simple example. I created a simple store procedure with output parameter. Then I catch it from code behind page of asp.net and displayed it to a label. Here I used a class DBConnector to connect with database. Lets look on the demo.

Step 1: Set connection string in web.config.


     


Step 2: Create a store procedure with output parameter.

CREATE PROCEDURE [dbo].[sp_output_param]
(
     @input_param VARCHAR(200)
    , @Response VARCHAR(250) OUTPUT
)
AS
    --DECLARE @myName
BEGIN  
    SET @Response = 'Welcome ' + @input_param
END

Step 3: Create a DBConnector Class to retrieve data from database.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
 
public class DBConnector
{
    private string connectionString = null;
    private SqlConnection sqlConn = null;
    private SqlCommand cmd = null;
 
    public DBConnector()
    {
        connectionString = ConfigurationManager.ConnectionStrings["SQLServerConnectionString"].ToString();
    }
 
 
    public SqlCommand GetCommand()
    {
        cmd = new SqlCommand();
        cmd.Connection = sqlConn;
        return cmd;
    }
 
    public SqlConnection GetConn()
    {
        sqlConn = new SqlConnection(connectionString);
        return sqlConn;
    }
 
}

Step 4 : Create a label name lblMsg in asp page. Then in a button event, write the following code. You will see your desired output in the lebel which is actually the value of output parameter. Here btnGetOutputParam_Click is the click event of Button btnGetOutputParam.

protected void btnGetOutputParam_Click(object sender, EventArgs e)
{
        SqlConnection sqlConn;
        SqlCommand cmd;
 
        DBConnector objDBConnector = new DBConnector();
        sqlConn = objDBConnector.GetConn();
 
        cmd = objDBConnector.GetCommand();
 
        SqlDataReader rdr = null;
 
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_output_param";
        cmd.Parameters.AddWithValue("@input_param", "Mahedee");
        cmd.Parameters.Add("@Response", SqlDbType.VarChar, 250);
        cmd.Parameters["@Response"].Direction = ParameterDirection.Output;
 
        try
        {
            if (sqlConn.State == ConnectionState.Closed)
                sqlConn.Open();
 
            rdr = cmd.ExecuteReader();
 
            string outputValue = cmd.Parameters["@Response"].Value.ToString();
            this.lblMsg.Text = outputValue;
        }
        catch (Exception exp)
        {
            throw (exp);
        }
        finally
        {
            if (sqlConn.State == ConnectionState.Open)
                sqlConn.Close();
        }
 
    }

You will see in label – Welcome Mahedee which is provided by output parameter of store procedure.