Month: December 2020

How to Move TempDB to Another Drive

I figured i will store this on my blog since each time i scramble to google for the script to move the files. So here we are:

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
	+ ' FILENAME = ''T:\TempDB\' + f.name
	+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
	+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');

Copy the output of the above script and paste it in SSMS. I kind of like using the script to dynamically generate the script I need to run. Sometimes, depending of available space, I may have to move log file to another drive.

Sample Output (run this on a new query window)

Please be sure to verify the file path used above. Otherwise, you would fail to start up the SQL server instance after you bounce the services.

If you need up in that situation. Here is what you do:

Re-start the instance in safe mode (-f startup parameter) and move tempdb. Then re-start without the parameter.

Here is an example of what that would look like:

Get Backup history

The following code will get you backup history across all databases in a SQL instance:

SELECT 
 bs.database_name,
(bs.backup_start_date),
 bs.backup_finish_date,
 CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
 CAST(DATEDIFF(second, bs.backup_start_date,
 bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
 CASE bs.[type]
 WHEN 'D' THEN 'Full Backup'
 WHEN 'I' THEN 'Differential Backup'
 WHEN 'L' THEN 'TLog Backup'
 WHEN 'F' THEN 'File or filegroup'
 WHEN 'G' THEN 'Differential file'
 WHEN 'P' THEN 'Partial'
 WHEN 'Q' THEN 'Differential Partial'
 END AS BackupType,
 bs.server_name,
 bs.recovery_model
 From msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 group by bs.backup_start_date,bs.database_name,bs.backup_finish_date,bs.backup_size,bs.[type], bs.server_name, bs.recovery_model
 ORDER BY bs.database_name asc,bs.backup_start_date desc;
 GO

If you need to look at the backup history at a specific database, use the following:

SELECT 
 bs.database_name,
(bs.backup_start_date),
 bs.backup_finish_date,
 CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
 CAST(DATEDIFF(second, bs.backup_start_date,
 bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
 CASE bs.[type]
 WHEN 'D' THEN 'Full Backup'
 WHEN 'I' THEN 'Differential Backup'
 WHEN 'L' THEN 'TLog Backup'
 WHEN 'F' THEN 'File or filegroup'
 WHEN 'G' THEN 'Differential file'
 WHEN 'P' THEN 'Partial'
 WHEN 'Q' THEN 'Differential Partial'
 END AS BackupType,
 bs.server_name,
 bs.recovery_model
 From msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 group by bs.backup_start_date,bs.database_name,bs.backup_finish_date,bs.backup_size,bs.[type], bs.server_name, bs.recovery_model
 HAVING bs.database_name = 'SQLDB'
 ORDER BY bs.database_name asc,bs.backup_start_date desc;
 GO

Why you cannot sometimes kill a DBCC CHECKDB

what does it do in the background?

Lets say you run DBCC CHECKDB regularly at night during off-peak load times and has no problems with it but last week you ran it during the day because of a corruption indication, then decided to stop it, but couldn’t. You killed the SPID and nothing happened. Except now the SPID is showing as KILLED/ROLLBACK state.

What happens under the hood?

It needs to see a transactionally-consistent, unchanging view of the database. Prior to SQL Server 2005 it did this by essentially running its own version of crash recovery inside itself, by analyzing the database’s transaction log. The newer logic was to instead use a private database snapshot, because a database snapshot gives a transactionally-consistent, unchanging view of a database.

So, the first thing DBCC CHECKDB does in SQL server 2005 and later versions is to create a database snapshot.

A database snapshot runs crash recovery of the target database into the database snapshot, and there in lies the problem: crash recovery cannot be interrupted, and there’s no check in the crash recovery code to tell whether it’s real crash recovery, or crash recovery into a database snapshot or into the DBCC CHECKDB private snapshot.

If there’s a lot of transaction log to be recovered as part of the initial crash recovery into the database snapshot, that could take a long time. And if someone tries to kill the DBCC CHECKDB while the database snapshot is still running crash recovery, nothing will happen until the crash recovery finishes. It’s not a DBCC shortcoming, it’s a shortcoming of a database snapshot, and you have need to let it finish.

How do you find which page an index row is on – Corruption scenario

There’s an undocumented column that you can select %%PHYSLOC%% that gives the page:record:slot of the selected record. It gives the information back in hex, so there’s a companion function named sys.fn_PhysLocCracker that formats the output nicely.

For example, lets use Stackoverflow database.

Let’s say there’s a corruption issue where it says there’s a missing row for DisplayName = ‘anonymous’ and Reputation = 7777 in the DisplayName_Reputation nonclustered index (which is index ID 4) of the dbo.Users table. If you want to prove that the row is not missing, here is what you can do:

SELECT DisplayName,
       Reputation,
       physloc.*
FROM dbo.Users
WITH (INDEX = 4)
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS [physloc]
WHERE DisplayName = 'anonymous'
AND Reputation = 7777;

You are selecting the index keys for the nonclustered index and using an index hint to force that index to be used.

And if you can get any output, the row exists. In this case, you get:

DisplayName	Reputation	file_id	page_id	slot_id
Anonymous	7777	3	11284812	133

Pretty awesome.

You can also use this to find rows that need to use DBCC WRITEPAGE to around corruptions to allow data recovery from trashed databases – exhaustive, but possible.

Revive a database from SUSPECT mode

In scenarios where you have no backups and the data or log files are damaged, the only way to access the database is with EMERGENCY mode.

The sequence of events was:

  1. Set the database to EMERGENCY mode.
  2. Use the undocumented and unsupported DBCC REBUILD_LOG command to build a new transaction log.
  3. RUn DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option to fix up corruptions in the data files – both those may have caused the issue, and those caused by rebuilding the transaction log
  4. Figure out what data was lost or is inconsistent as far as your business logic is concerned
  5. Take the database out of EMERGENCY mode
  6. Perform root cause analysis

EMERGENCY mode example

EXEC sp_resetstatus 'SuspectDemoDB';
ALTER DATABASE SuspectDemoDB SET EMERGENCY;
GO
DBCC CHECKDB ('SuspectDemoDB',REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
ALTER DATABASE [SuspectDemoDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [SuspectDemoDB] SET MULTI_USER
ALTER DATABASE SuspectDemoDB SET ONLINE

EMERGENCY mode is not SINGLE_USER mode. A database needs to be in SINGLE_USER mode for repair to run and EMERGENCY mode allows multiple connections from members of the sysadmin role. You can set SINGLE_USER mode as well as EMERGENCY mode – however, the sys.databases field state_desc will still just say EMERGENCY.

Btw, if the ALTER DATABASE statement to set the database into EMERGENCY mode fails, try setting the database OFFLINE and then ONLINE again first. Then set EMERGENCY mode again and retry the above script

How the state of the database looks like when in suspect mode:

SELECT [state_desc]
FROM sys.databases
WHERE [name] = N'SuspectDemoDB';
GO
state_desc
-----------------
RECOVERY_PENDING

How the state of the database looks like when it is back Online :

SELECT [state_desc]
FROM sys.databases
WHERE [name] = N'EmergencyDemo';
GO
state_desc
-----------
ONLINE

Please note that, you should only use this as a last resort, but if you do get yourself into trouble, you know there’s a command that should be able to help you.