Skip to main content
NetApp Knowledge Base

Why doesn't SnapCenter create Snapshots on the SQL instance system tempdb volume?

  1. Last updated
  2. Save as PDF
Views:
46
Visibility:
Public
Votes:
0
Category:
snapcenter
Specialty:
SNAPX
Last Updated:

Applies to

SnapCenter Plug-in for SQL (SCSQL)

Answer

Note: The fact that SCSQL doesn't back up tempdb is documented (see below), but it doesn't explain why.
 
Two reasons:
  • none of the data in the SQL instance's system tempdbs is of any importance, since it's transitional data not required to restart the instance
  • the I/O on these databases is relatively high, especially during an ESEUtil run
Ergo: it makes no sense to back up the tempdb databases, and due to the high I/O, any snapshots would quickly grow and take unnecessary extra space in the volume/aggregate for the ONTAP LUN or SBM3 Share (supported from SC 6.0 onwards) containing the SQL instance's system tempdb databases.
 

Additional Information

Reference (1): the SnapCenter documentation with respect to the concept of SCSQL, specifically this phrase:

" You cannot perform backup,restore, clone, and clone lifecycle operations on tempdb system databases. "

Reference (2): the Best Practice Guide for SQL Server with SnapCenter Plug-in for SQL on Page 15, says:

"Do not include the tempdb database in a backup because the data it contains is temporary. Place tempdb on a LUN or an SMB share that is in a storage system volume in which Snapshot copies will not be created"

Note: since SQL Server 2016, the number of tempdb databases depends on the number of core of the system that SQL Server is running on.

NetApp provides no representations or warranties regarding the accuracy or reliability or serviceability of any information or recommendations provided in this publication or with respect to any results that may be obtained by the use of the information or observance of any recommendations provided herein. The information in this document is distributed AS IS and the use of this information or the implementation of any recommendations or techniques herein is a customer's responsibility and depends on the customer's ability to evaluate and integrate them into the customer's operational environment. This document and the information contained herein may be used solely in connection with the NetApp products discussed in this document.