Available Methods for Sending a Mail from SQL Server

-->

Are you looking for a method that enables you to send a mail from your SQL server? For you Microsoft SQL server offers a special feature that will enable you to send a mail from it. Earlier to SQL server 2000, it is known as SQL mail but with release of SQL Server 2005 it has been changed & the new name is database mail. Database mail is very useful for sending a mail from SQL server to end users as well as to database administrator. It can contain data from files, queries, or many other resources available on the network.

What is SQL Mail? It is available in earlier version of SQL server 2005 & uses Message application programming interface (MAPI) to send mail. You can't use it without installation of outlook.

What is Database Mail? It is an enhanced version of SQL mail that is available in SQL server 2005 & upper versions. The best thing is that there is no any requirement to install outlook first to use it. It uses simple mail transfer protocol (SMTP) for sending a mail. In this article I would discuss about how to configure database mail in SQL server 2005 because SQL mail is a very old fashion.

How to Configure Database mail? You can configure database mail with the help of SQL server management studio. For exercise, we will learn how to setup a database mail using SQL Server Management Studio.

• To setup database mail in SQL server 2005, connect to the instances of SQL server database.
• Browse the management.
• Now right click from your mouse on database mail.
• Select configure database mail section.
• Choose one option from list for a particular task.

How to Check Database is enabled or not? If you want to check database mail is enabled or not on your database then connect to an instance of SQL server database by a query editor and execute bellow: 

 

sp_configure 'show advanced', 1; GO RECONFIGURE; GO sp_configure; GO

Result: Go to the result pane and check “run_value” of the database. If run_value is 1 then database mail is enabled on your database otherwise not.

Read More

Best solution for you when your database is suspect mode due to insufficient space in filegroup?

SQL server uses filegroup for grouping all the database objects & files, there are two types of SQL server filegroup, one is primary & another is user-defined. Primary filegroup contains the primary data file and all the pages for system table while any other filegroups that are specified with Create or Alter statements are known as user-defined filegroups. No any file can be member of more than one filegroup and log file is not a part of any filegroup. It is managed from data space.

Note: One filegroup in each database is specified as a default filegroup. When you create a table or indexes in your database without specifying with any filegroup then it is assumed that all the pages are allocated from the default filegroup.

Consider a scenario wherein you are working on your SQL server 2000 database and suddenly unable to perform any operation on the database due to your database goes in suspect mode and got below error message:

Could not allocate space for object '%.*ls' in database '%.*ls' because the '%.*ls' filegroup is full

Cause: As specified by the above error message, your specified filegroup has insufficient space.

Possible Solution: There are two possible solutions for the above error message and the solutions are:

1. Free Disk Space
2. Add a Data File

1. Free Disk Space: You can gain free disk space by increasing the size of any disk drive that contains files for filegroup and allow filegriup to grow or can gain space by adding a data file with the specified database. If you cannot increase the size of disk drive that contains filegroup then move the filegroup to another disk drive. Follow the steps to free disk space on another drive:

• Move the data file in filegroup with insufficient free disk space to another drive.
• Now detach the database by executing stored procedure sp_detach_db.

Systax for detach stored procedure

sp_detach_db [ @dbname= ] 'database_name'

[ , [ @skipchecks= ] 'skipchecks' ]

[ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ]

• And finally attach the database by stored procedure sp_attach_db

Systax for attach stored procedure

sp_attach_db [ @dbname= ] 'dbname'

, [ @filename1= ] 'filename_n' [ ,...16 ]

2. Add a Data File: You can add a file to specified database by using ADD file clause of ALTER database statement or enlarge the size of data file by using Modify clause of Alter database statement. 

Read More

Unraveled: Possible Resolution Methods to Work Around Database Corruption in SQL Server 2008

SQL Server 2008 has been specifically designed to work with large data sets and to handle multiple users. It facilitates ease of use and maintenance through its feature-laden application interface. It can easily interpret the bandwidth required by the users and considerably help to improve performance. However, it has a few downsides. Any unexpected server crash may leave your SQL database in a suspect state. This database corruption would thrive if no timely action is taken to counter the problem. In these circumstances, all your valuable database components become inaccessible. To overcome this problem, you should go for SQL server recovery.

Let us consider a real-world scenario. Suppose you use MS SQL Server 2008 on your Windows XP based computer. You may occasionally notice the below error message in your application log:

'Table error: Object ID O_ID, index ID I_ID, partition ID PN_ID, alloc unit ID A_ID (type TYPE). Duplicate keys on page P_ID1 slot SLOT1 and page P_ID2 slot SLOT2.'

The above error is generated by 'SQLengine' because there are two slots having the same or identical keys. In addition, the error may pop-up as a result of corruption in your SQL database.

To work around this problem, you need to isolate the cause of the error and accordingly, work out a solution. You can try the given steps in order to resolve the issue:

• Hardware failures and defects cause corruption in SQL databases more frequently than other sources. First off, you should view the SQL Server error log and the Windows system and application logs to check for hardware failures. If you encounter any hardware problem, run hardware diagnostics.
• If you continue to face problems, you should try swapping different hardware parts to examine the root cause. Additionally, ensure that the disk controller does not perform write caching.
• If the issue is not hardware-specific, you can try restoring the database from a recent clean backup.
• If you do not have a backup, run DBCC CHECKDB with an appropriate repair clause to fix database corruption.

If you receive the same error again, then take help of advanced third-party SQL database recovery software. These competent tools incorporate advanced, proprietary mechanisms to repair your damaged SQL database easily and recover all lost or inaccessible objects, such as tables, views, keys, indexes, constraints, and stored procedures. Furthermore, they are compatible with Windows 7, Server 2008, Vista, Server 2003 and XP.

Read More

A safe approach to database recovery in case of IAM chain corruption in SQL Server 2008

SQL Server 2008 is the best tool for database developers and administrators as it incorporates some of the major improvements over the earlier versions. It has support for large data sets and the ability to handle multiple users. The new 'Performance Studio' provides you with a collection of tools for easy monitoring and troubleshooting the system. It allows you to build up an efficient policy management system and help database administrators in carrying out various administrative tasks. Regardless of all these benefits, there are various contingencies that lead to corruption in the SQL databases. The potential reasons behind database corruption include virus infection, hardware failures, operating system bugs, etc. As a consequence, all the valuable objects in the SQL database become inaccessible. In order to recover your lost invaluable data, you should perform SQL recovery through proficient third-party software.


IAM chain corruption is a common occurrence on SQL Server 2008. This is usually accompanied by the below given error by DBCC CHECKDB:


"Msg 2576, Level 16, State 1, Line 1 
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:153) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594042384384 (type Unknown), but it was not detected in the scan."


The system table 'sysallocunits' usually has a reference to the first IAM page, the root page, and the first page. The above error says that metadata does not include a reference to the first IAM page in the IAM chain (page 1:153). This indicates corruption in the 'sysallocunits' system table.


If you try to use the 'repair_fast' and 'repair_rebuild' options in this case, you may get no significant results. In order to resolve the issue, you may try the following series of steps:

•     Start a transaction
•     Write the rowcounts for all system objects and tables in your database.
•     Now use CHECKDB for repairing the database.
•     Make sure that the problem is resolved and the error is not seen again
•     Rerun DBCC CHECKDB to verify the database.
•     Examine the rowcounts once again.
•     Commit the transaction

The 'repair_allow_data_loss' should be the minimum repair level for this case. If the problem persists, you may try professional SQL 2008 database recovery software. These third-party tools extensively scan SQL databases to recover damaged or inaccessible objects, including tables, views, indexes, constraints, triggers, etc. Moreover, they are compatible with Windows 7, Server 2008, Vista, Server 2003 and XP.

Read More

Repair Corrupt SQL Server Database

-->

In this blog post, I will try to explain all possible steps that you will have required to repair your corrupt SQL server database. Let's consider a case where you are working on SQL server database, trying to access some records from a table. During table access process you have got an error message that specifies, you can't access desired table due to database corruption. Corruption in SQL server tables can happen due to several reasons; some most prominent reasons are virus attack, power outage, improper system shutdown, hardware failure etc.

What to Do Now? It is the first question that will come in your mind. I will suggest some tips at this point.

• Don't Panic
• Keep Patience
• Take Backup of your database
• Check hardware configuration

How to Check Error Message?

Run DBCC CHECKDB command on the database, it will write a reported error message in SQL server error log. The reported error message will show you how many consistency errors are found.

For example: You may get below error message in your error log

2010-03-31 22:07:06.34 spid53 DBCC CHECKDB (mydb) executed by MYDOMAIN\theuser found 15 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000026:0000089d:0001 and first LSN = 00000026:0000089c:0001. This is an informational message only. No user action is required.

Note: You can run DBCC CHECKDB command on the database any time no matter your database is online or offline but you will get information message only when DBCC CHECKDB will run on online database.

How to Fix Corruption: There are three methods that you can try to repair corrupt table. The methods are....

• Check Hardware Configuration
• Restore from Backup
• Run DBCC CHECK with repair_allow_data_loss

Check Hardware Configuration: First of all check your hardware configuration. If you are not a hardware geek then connect with your system support team and fix the issue.

Restore from Backup: If backup of corrupt table is available then restore from updated backup.

Run DBCC CHECKDB with repair_allow_data_loss: DBCC CHECKDB is a free utility offered by Microsoft to repair & recover SQL server database with minimum data loss.

ALTER Database Employee_Details Set Single_User

GO

DBCC CHECKDB (Employee_Details, REPAIR_ALLOW_DATA_LOSS) With ALL_ERRORMSGS;

GO

ALTER Database Employee_Details set multi_user

GO

Read More

Causes & Resolutions for MS SQL Server Error 5120

-->

Are you getting SQL server error 5120, when you try to attach some database files to Microsoft SQL server? This may happen because you have selected a file that is in use by other SQL server instance or selected a wrong file for the attachment. To fix this, firstly you have to check the location and status of SQL server files that you want to attach.

The default location of data & log files are:

C:Program-files\MSSQL\Data\Database-Name_Data1.mdf

C:Program-files\MSSQL\Data\Database-Name_Log1.ldf

Note: Your data & log files may have different location. You can change it according to your need.

The error message that you may got is look like:

Unable to open the physical file "C:Program-files\MSSQL\Data\Database-Name_Data1.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

Possible Cause: As I have mentioned, this error message occurs due to many reasons but it may also occurs when you are not allowed to access the file directories. For the security purpose, database administrator does not allowed you to access the file directories. To access the file, you have to login as administrator or get permission to access the database files.

Resolution: As we know that solution is not rigid for sql database recovery. It may change according to the scenarios or causes. Here, we will discuss two scenarios and its possible solutions.

Solution 1: If you have found access permission problem with the database files then login as a administrator and fix the problem.

Solution 2: If you have found data file is present in C drive & log file is present in E drive then change the location of data or log and placed data and log files in a same drive. Now attach the database and fix it.

Point to Ponder: Generally, solution 1 is enough to solve Microsoft SQL error message 5129 because most of the database administrator (owner) does not allow every administrator to access the database files.

Read More

Discussing ways to easily recover SQL Server 2000 suspect database

-->

MS SQL Server 2000 is widely used all over the world as the data storage component of several large websites. It is a cross-platform application that supports various advanced and user-friendly features, such as Microsoft search service, federated servers, indexed views, etc. It can help you achieve the desired performance levels required for running large web applications. However, an SQL database may occasionally become corrupt. The potential reasons responsible for this corruption are virus infection, hardware errors, abnormal system shutdown, etc. As a consequence, all valuable objects in the database become inaccessible. To overcome this problem, you need to go for SQL recovery through a reliable third-party utility.

Suppose, you use MS SQL Server 2000. While trying to access one of your databases, you may receive the following error message on your screen:

“96/11/18 10:48:32.60 kernel udopen: Operating System Error 32 (The process cannot access the file because it is being used by another process.) during the creation/opening of physical device,
C:\DATA\SQL\MSDB.DAT (or C:\DATA\SQL\MSDB.MDF)”

Cause: The above error occurs because you are trying to access a database that is marked as 'suspect'. There could be a number of reasons responsible for this, such as database corruption, missing device driver, database being moved to another location, etc.

This would render your SQL database inaccessible and lead to severe data loss. At this stage, you may use an updated backup to restore the database. However, if the backup is unavailable, you should follow the below mentioned resolution steps.  

Resolution: You can try to resolve the issue by following any of the given methods:

• Make sure that your system has all the necessary device driver files.
• If your SQL database is corrupt, you need to follow the given procedure.

• First, drop and recreate all indexes in your database.
• Create a new database table and copy all records from the original table to this newly created table.
• Now, update the statistics.
• Run the following command:

'DBCC UPDATEUSAGE'.

• Finally, run the 'sp_recompile' procedure

If the problem still persists, you should take help from a competent SQL server recovery tool. These third-party software are proficient in repairing corrupt SQL databases to recover valuable objects, including tables, views, indexes, constraints, triggers, stored procedures, etc. They allow you to take a sneak peek at the database objects prior to recovery.

Read More