Tip: Rename a live production database.

I had a requirement from application team that I should rename a database when the application is running and the transactions were happening. The reason was that they didn’t want any outages registered for the entire application. If they stop the services, it would impact the the whole application.

Steps

  1. Set the target database into Single user mode.
  2. Rename database.
  3. Bring the database back into Multi user mode.

Use below query to perform above steps.

Here you go !. You are done.

Workaround: Control SQL Agent Job Steps based on query outcome.

Below workaround will help you incase if you wanna control SQL agent job steps based on a SQL query output. For eg: Suppose you have to check if any backup jobs are running before executing a resource intensive job step. Here is how we can work around and control job steps. You can modify below steps anyway based on your needs as I have just used below strategy as an example.

Methord Summary

  1. Run the query to check if the backup is running. Query to check backup status is
    select count(*) from sys.dm_exec_requests where command like '%BACKUP%'
    The result will be > zero if the backup is running. and zero if no backups are running.
  2. Execute the resource intensive task if the above query returned ZERO else exit the job reporting success.

Steps

1. Create a job with first step to check the backup status.
2. Use below query to check backup status and thereby navigate the job steps by making it raise an error causing the job step to fail if the backup is running.

3. Go to advanced option for this job step and set as “quit the job reporting success” if the step fails, and to proceed to next step if the step succeeds which will trigger the resource intensive query. Instead of setting “Quite the job reporting success”, You can also make it move to a step that could send some email alert saying the task was aborted since a backup was currently running.
4. Create the next step that would be triggering the resource intensive query.

Read Disk Read/ Write for all databases in an instance and Sort them

Below Query will help you read live file level Disk read and write information. WIll behandy for performance tuning capacity planning.

Error: Transaction log file got damaged or corrupted

Once we had an issue in our environment. We had a maintenance activity on the Database server during a weekend and the server was shutdown for a Storage level snapshot backup. After the backup the server was brought back online. But unfortunately the user database was in suspect mode and could not be accessed. SQL Server Error Log says

“An error occurred while processing the log for database ‘<databasename>’.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.”

Solution

Below is what I did to rescue my beloved database.

1. Enabled emergency mode for that database.

ALTER DATABASE set EMERGENCY

2. Then brought the database into single user mode.

ALTER DATABASE set SINGLE_USER

3. Executed DBCC CHECKDB with repair_allow_data_loss.

DBCC CHECKDB (<databasename>,repair_allow_data_loss) with no_infomsgs

I got below warning in Error Log right after I triggered the CHECKDB command.

Warning: The log for database ‘<databasename>’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

And once the CHECKDB completed, The errorlog log reported below message which made me very happy.

EMERGENCY MODE DBCC CHECKDB (<databasename>, repair_allow_data_loss) WITH no_infomsgs executed by SPANSION\admin_sponnamb found 0 errors and repaired 0 errors. Elapsed time: 0 hours 52 minutes 54 seconds.

After the CHECKDB completed, I reverted back the database into multiuser mode with below command.

ALTER DATABASE <databasename> SET MULTI_USER

Now the application was successfully able to access the database and everything was function very well as expected. A full backup has to be triggered to reset LSN numbers and to take transaction log backups.