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