SQL Database Corruption If You Shrink The Database When It Is Use

In MS SQL Server, you can shrink tempdb database to a size smaller than last configuration of the database. There are various methods that you can use to remove the unused pages of the database and reduce its size. You can also shrink the individual files in SQL Server database. In some methods, you must not do any operation on the database while shrinking. You should not shrink the database when it is in use. It may damage the database and cause serious data loss situations. If you mistakenly do it, SQL Database Recovery becomes essential.

In Microsoft SQL Server, you should use the SQL Server Management Studio in place of Enterprise Manager and Query Analyzer to carry out the tempdb shrink operations. SQL Server Management Studio doesn't show proper size of the tempdb files after shrink operation. The value of “Currently allocated space” is taken from the sys.master_files DMV and it isn't updated after you shrink the database. You can get the exact size of tempdb database using the below mentioned statement after shrinking the database:

“use tempdb

select (size*8) as FileSizeKB from sys.database_files”

What happens if you shrink the tempdb database using DBCC SHRINKFILE or DBCC SHRINKDATABASE commands while the database is in use:

If you try to shrink the database through DBCC SHRINKFILE or DBCC SHRINKDATABASE that is in use by another process, you may come across various database consistency errors, similar to the below errors:

“Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'. Check sysobjects.”

Or

“Server: Msg 8909, Level 16, State 1, Line 0 Table Corrupt: Object ID 1, index ID 0, page ID %S_PGID. The PageId in the page header = %S_PGID.”

After the above error message, shrink operation fails.

Error 8909 indicates corruption in tempdb database. You may also encounter this problem if the SQL Server database is damaged. In order to sort out this problem and fix consistency errors, use Eseutil utility.

If the above method does not work, Repair SQL database using commercial applications. MS SQL Recovery software methodically scan the damaged database and restore all of its data.

The SQL Repair applications do not demand sound technical skills to restore the database due to simple and rich graphical user interface. They have read-only conduct and thus preserve integrity of your valuable data.

SQL Recovery software works in all cases of MS SQL Server database corruption scenarios. It is designed for Microsoft SQL Server 2008, 2005, and 2000. The software restores all MDF items such as tables, reports, forms, queries, triggers, constraints, and stored procedures.