Category: DBA Utilities

DBA Utilities, SQL

How to find Table Name from Page ID in SQL Server
Recently, we had a performance issue in one of our testing environment and while we are troubleshooting the issue with Live Monitoring Query, we identified a locking scenario which is the root cause for the performance problem.

Now, we know there is a locking and the wait resource is showing as PAGE: 9:1:401776. What does it mean? How do we know which object is being locked? Let us try to answer these questions.

We know there is a LCK_M_IX which means an intent exclusive modification lock happened on the object “PAGE: 9:1:401776”. If we have a page number like this, then there is an easy way to get the object information with the help of a DBCC command – DBCC PAGE. 

DBCC TRACEON (3604);
DBCC PAGE (9, 1, 401776, 0);
DBCC TRACEOFF (3604);

The result of DBCC PAGE provides lots of information, however, we should specifically look at PAGE HEADER section for “Metadata: ObjectId “. It would provide us the ObjectId. Once we have the object_id, it is an easy to get the object name as below. Please note, we should select the database name while executing the command otherwise, we might end up no result or wrong information. 

Select db_name(9)
GO
use <dbname_from_previous_query>
GO
Select object_name(946818435)

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

DBA Utilities, SQL

How to recover restoring database in SQL Server
At times, we end up with situations like databases suddenly goes into restoring state and becomes not accessible. Few cases those are valid like mirror database in a database mirroring or removed database from AlwaysOn etc. Today, we are not discussing about those valid cases, but as outcome of an incident or unfortunate db state change.

If you happened to see a database in restoring and you want to get it online, you can simply restore with recovery as below. 

use master
restore database << dbname >> with recovery

In most cases, the db would get online immediately. Sometimes, it might give you an error message as below:

Msg 4333, Level 16, State 1, Line 3 The database cannot be recovered because the log was not restored. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally.

If you result with the above error message, then we should understand that the particular database is not restored in a proper way. May be. the restore process would have terminated due to network issues or cancelled the restore part way through etc. Such database cannot be brought online. The best way would be to drop the database and restore from the backup.

restore database << dbname >> from disk ='<filepath>\<backup filename>'

It is important to have a valid database available for us to quickly get this db online. So ensuring the backup is important as always, hope we all will have a good backup plan in place by default, if not, that should be the first priority item.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

DBA Utilities, SQL

How to Repair Database in Suspect Mode in SQL Server
Today, one of my colleague reached out to me that one of her database has gone into SUSPECT mode. She explained that while she was deploying the db changes, there was an unexpected shutdown happened on the server. While rebooting the server, it is observed the database has gone into SUSPECT mode.

Primary reasons when an SQL Server Database is marked in Suspect Mode

There may be many reasons for a db is getting into a SUSPECT mode. This is the worst case for any database can get as this would lead the database unusable.Here are few reasons I could think immediately, but there could be many other reason, feel free to add in the comment section if I miss anything.

    SQL Server Crash

    Database Files are inaccessible

    Improper shut down of SQL Server System

    Sudden Power Outage

    Hardware Failure etc.

Steps to Fix the SQL Server Database Suspect Mode Error

There are many articles on how to repair a SUSPECT db out there on a google search.

    Bring Database in EMERGENCY MODE

    Perform Consistency Check – DBCC CHECKDB

    Bring the Database in SINGLE_USER Mode with rollback immediate

    Take a Full Backup of the User Database

    Execute DBCC CHECKDB WITH REPAIR ALLOW DATA LOSS (Note: It might result in Data Loss)

    Bring the Database in MULTI_USER Mode for normal operations

Now, Let us look at our specific issue. Firstly, we tried to get this database in EMERGENCY mode with the below T-SQL.Unfortunately, we did end up with an exception given below which is clearly saying that this db has got a severe consistency issue which cannot be used further. 
ALTER DATABASE dbname SET EMERGENCY
User does not have permission to alter database ‘dbname’, the database does not exist, or the database is not in a state that allows access checks.
How to drop SUSPECT database?

As we identified that this database has severely corrupted and it cannot be repair nor recovered, there is no way other than dropping the database. If you have backup of this database, you can always do restore from the latest valid backup (even up to the point in time). But unfortunately, in our case, it will not even allow you to drop and it would end up with the above same error message for DROP as well.

So, how do we drop this db? Its bit tricky and needs some outage of SQL Server (since one of the step is stopping the service).
    Stop the SQL Server service.

    Rename the file (MDF or LDF or both) of this database.

    Start the SQL Server service.

    Drop the database.

Caveat: The above method is recommended on non- production environments generally.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

DBA Utilities, SQL

How to read and write backup directory in SQL Server
There is a requirement for me to change the backup directory of a SQL Server instance. Here are few tips related to the sobject and hope it will be useful as a future reference for all of us.

How to read the value of BackupDirectory? 

DECLARE @path NVARCHAR(4000)

EXEC master.dbo.xp_instance_regread
            N'HKEY_LOCAL_MACHINE',
            N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
            @path OUTPUT, 
            'no_output'

How to write/set a new value of BackupDirectory? 

EXEC master.dbo.xp_instance_regwrite
            N'HKEY_LOCAL_MACHINE',
            N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
            REG_SZ,
            N'\\xx.xxx.xx.xxx\C$\data\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup';

Few other important information: 

DECLARE @SQLDataRoot nvarchar(512)
DECLARE @DefaultData nvarchar(512)
DECLARE @DefaultLog nvarchar(512)

--Installation Root Info
EXEC master.dbo.xp_instance_regread 
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\Setup',
	N'SQLDataRoot',
@SQLDataRoot OUTPUT
Select @SQLDataRoot

-- SQL Data file Info
EXEC master.dbo.xp_instance_regread
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'DefaultData',
@DefaultData OUTPUT
Select @DefaultData

-- SQL Default Default Log file info
EXEC master.dbo.xp_instance_regread
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'DefaultLog',
@DefaultLog OUTPUT
Select @DefaultLog
xp_instance_regread & xp_instance_regwrite are undocumented features, so it may be deprecated any time, however, can be used for non-production or DBA specific tasks at own discretion.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

DBA Utilities, SCRIPT GALLERY, SQL

When was the database taken offline/online in SQL Server
Here is a script to identify when was the database taken offline/online and few other information like login name, host name and application name.

Script

DECLARE  @DBNAME nvarchar(100)
  ,@FileName nvarchar(max)
  ,@Status nvarchar(10)
  
SET @DBNAME = NULL -- Provide [DBNAME]
SET @Status = 'OFFLINE' --Provide [OFFLINE / ONLINE]
SELECT @FileName=[path] FROM sys.traces WHERE is_default=1

DECLARE @ErrorLogTable table (Logdate datetime, ProcessInfo nvarchar(10), [Text] nvarchar(max))

INSERT INTO @ErrorLogTable
EXEC xp_readerrorlog 0,1, @Status, @DBNAME, NULL, NULL, 'desc'

SELECT Distinct DatabaseID, DatabaseName, HostName, ApplicationName, LoginName, StartTime, B.ProcessInfo, B.Text
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) A
Inner join @ErrorLogTable B on A.SPID = cast(SUBSTRING(B.ProcessInfo,5,5) AS int)
and CAST(StartTime AS nvarchar)=cast(B.Logdate AS nvarchar)

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!