Recover suspect database

Today I have working on recover suspect database. Sometimes you cannot recover database to normal(no data loss with read/write) mode, sometimes it’s recover with read-only mode depend on your database suspect ratio. Most of the time disk space problem or hard disks (located on database data file) issues can be cause of database suspect issue.

Note : most of the time (in your Development databases) you will noticed database shows as “recovery pending” mode. In this case you need to check SQL server error log if it’s has some error reported or not, if it is errors then you have to work with that error else if it’s only “information message no user action required” these situations you have to let them handle to SQL Server Engine.

if user action required then most of the time you can set database offline and then move back to online,

ALTER DATABASE <DB_NAME> SET OFFLINE WITH ROLLBACK IMMEDIATE;
Go
ALTER DATABASE <DB_NAME> SET ONLINE WITH ROLLBACK IMMEDIATE;
Go

if database is suspect then you can try following commands with depend on situations,

ALTER DATABASE <DB_NAME> SET EMERGENCY;

DBCC CHECKDB('<DB_NAME>');

ALTER DATABASE <DB_NAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DBCC CHECKDB ('<DB_NAME>', REPAIR_ALLOW_DATA_LOSS);

ALTER DATABASE <DB_NAME> SET MULTI_USER;

DBCC CHECKDB ('<DB_NAME>');

SQL server table space used

SQL Server table space used

                                            Recently I have plan to publish some of T-SQL scripts to find table allocation space or space used. Initially i want to show how to find space in single table if you want.   

sp_spaceused '<Your Table Name>';

All list of tables,

--Partition Tables
SELECT
      s.name                        [Schema_Name],
      t.name                        Table_Name,
      i.name                        Index_Name,
      ds.name                        Partition_Scheme,  
      p.partition_number            Partition_Number,
      fg.name                        File_Group_Name,
      p.[rows]                        Row_Count,
      (au.total_pages * 8)            Total_Space_KB,
      (au.used_pages * 8)            Used_Space_KB
FROM    sys.partitions    p INNER JOIN
        sys.indexes    i ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id INNER JOIN
        sys.tables t ON    t.[object_id] = i.[object_id] INNER JOIN
        sys.data_spaces    ds ON ds.data_space_id = i.data_space_id INNER JOIN
        sys.partition_schemes    ps ON ps.data_space_id = ds.data_space_id INNER JOIN
        sys.partition_functions pf ON pf.function_id = ps.function_id INNER JOIN
        sys.destination_data_spaces  dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number INNER JOIN
        sys.filegroups fg ON fg.data_space_id = dds.data_space_id INNER JOIN
        sys.allocation_units au ON p.partition_id = au.container_id LEFT OUTER JOIN
        sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE OBJECTPROPERTY(p.[object_id], 'ISMSShipped') = 0
ORDER BY s.name,t.name; 


--Non Partition Tables

SELECT
    s.name                                            [Schema_Name],
    t.name                                            Table_Name,
    fg.name                                            File_Group_Name,
    p.partition_number                                Partition_ID,
    p.[rows]                                        Row_Counts,
    SUM(au.total_pages) * 8                            Total_Space_KB,
    SUM(au.used_pages) * 8                            Used_Space_KB,
    (SUM(au.total_pages) - SUM(au.used_pages)) * 8    Unused_Space_KB
FROM
    sys.tables t INNER JOIN     
    sys.indexes i ON t.[object_id] = i.[object_id] INNER JOIN
    sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id INNER JOIN
    sys.allocation_units au ON p.partition_id = au.container_id INNER JOIN
    sys.data_spaces ds ON ds.data_space_id = i.data_space_id INNER JOIN
    sys.filegroups fg ON fg.data_space_id = i.data_space_id LEFT OUTER JOIN
    sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE OBJECTPROPERTY(p.[object_id], 'ISMSShipped') = 0
GROUP BY s.name,t.name,fg.name,p.partition_number,p.[rows]
ORDER BY s.name,t.name;  

--file space info

SELECT
b.groupname [File Group],
name [LogicalFilename],
[Filename],
CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2))
[Currently Allocated Space (MB)],
CONVERT (Decimal(15,2),
ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2))
[Space Used (MB)],
CONVERT (Decimal(15,2),
ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))
[Available Space (MB)]
FROM    sysfiles a (NOLOCK)INNER JOIN
        sysfilegroups b (NOLOCK) ON a.groupid = b.groupid
ORDER BY b.groupname

    

 Above script get some help to file table wise space and file group space allocation.