ISSUE 3: SHRINKDATABASE (DIFFERENT PROCESS)

If you ever want to transfer a large DB to a new one with more than one file, here is the way I am going to use (tested and approved)

1. Create a file which is as large as the data in your primary file (call it "buffer")

2. Empty the primary file (DBCC SHRINKFILE (<FILENAME>, EMPTYFILE))

3. Restart SQL Server Engine

4. Shrink the primary file to the Data size divided by the number of files you're gonna create (DBCC SHRINKFILE (<FILENAME>, NEWSIZE))

5. Create all the new files with the size of data divided by the number of files

6. Restrict their growth in order to fill the primary file in the next operation

7. Empty the buffer file (DBCC SHRINKFILE (BUFFER, EMPTYFILE))

8. Delete the buffer file (ALTER DATABASE REMOVE FILE (NAME=BUFFER))

9. Set final size of data files and unrestrict their growth according to the final configuration needed

ISSUE 2: MOVING MASTER DATABASE

 

1. Create two folders and grant read write permissions to service account

            d:\master_data

            e:\master_log

2. Find the current path

            sp_helpdb   master

3. Stop SQL Server

4. Move the files (master.mdf, mastlog.ldf) into new folders

5.  Go to SSCM --> R.C on respective instance SQL Server Service -- properties -->   Advanced --> Startup Parameters--> Change the    path of data and Log file

            -dd:\master_data\master.mdf;-e....

            -le:\master_log\mastlog.ldf

6. Apply --> OK

7. Start the service. Go to SSMS --> check the new path

            sp_helpdb master

 

Recovering a SQL Server Database Marked as Suspect

When a SQL Server database enters suspect mode, it becomes inaccessible, indicating potential data corruption or resource issues. This guide explains why databases enter suspect mode and provides actionable recovery steps for common scenarios.

Why Does a Database Enter Suspect Mode?

A database may be marked as suspect due to:

Missing or unavailable database files (e.g., accidental deletion).

Full transaction log or data file (no free space for operations).

Corrupted database files (hardware failure, unexpected shutdown).

Resource locks (OS or another process holding files hostage).

How to Recover a Suspect Database

Below are recovery methods for three common scenarios:

Reason for suspect mode:

If one or more database files are not available.

2.   If the entire database is not available.

3.   If one or more database files are corrupted.

4.   If a database resource is being held by the operating system.

How to recover?

Ø  Scenario 1: If the file is full

Execute sp_resetstatus.

Syntax: sp_resetstatus database_name

Use ALTER DATABASE to add a data file or log file to the database.

Stop and restart SQL Server.

With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database

Ø  Scenario2: If the data file was damaged.

            * Take T.Log backup

            * Restore last Full backup

            * Restore T.Log backup

            * Database comes online

Ø  Scenario3: If the T.Log file was damaged

            * Take any user defined db for example: MyDB

            * Check the current location of files

                        sp_helpdb MyDB

            * Stop server

            * Move the T.Log file into different folder

            * Start server --> DB goes into suspect mode

               Select databasepropertyex ('mydb','status')

 Steps to Recover:

Step1: Make the db into single user

1) Alter database mydb set Single_User

Step2: Set the db into emergency mode

2) Alter database mydb set Emergency

Step3: Run checkdb with required repair level

3) DBCC CheckDB ('mydb', REPAIR_ALLOW_DATA_LOSS)

Step4: Set the db into multi user mode

4) Alter database mydb set Multi_User