Category Archives: Views

Prevent TRUNCATE TABLE

The ability to truncate a table is a powerful tool. Truncation removes all data from a table, and, because the operation is minimally logged, it happens almost instantly. However, being minimally logged means that individual record deletions are not recorded in the transaction log. There are other potential pitfalls from truncation as well. Can someone suggest a few?

To avoid some of these pitfalls, we can implement a safeguard to prevent table truncation. As BOL states, truncating is not allowed on tables that:

  • Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
  • Participate in an indexed view.
  • Are published by using transactional replication or merge replication.

In addition to these three conditions, it is also true that when a table is enabled for Change Data Capture (CDC) it cannot be truncated. Why? Because CDC reads the transaction log to monitor data changes, and, as mentioned above, truncation does not write record deletes to the log. An attempt to truncate a table enabled for CDC will result in message 4711:

Msg 4711, Level 16, State 1, Line 1
Cannot truncate table 'tbl' because it is published for replication or enabled for Change Data Capture.

So, which of these four options should we use? Enabling CDC seems like a heavy solution simply to prevent truncation, plus it is only available at Enterprise Edition. Enabling replication also feels like an out-sized answer; plus, tables can be truncated within replication operations, so that does not provide reliable protection for the data.

That leaves two options: create a foreign key constraint, or include the table in an indexed view. If the table has a logical relationship with another table, then enabling a foreign key between them is probably the best answer as there is virtually no cost involved. However, if no logical relationship exists, we could create an empty table simply for the purpose of creating a foreign key.

Let’s demonstrate that approach first.

-- Set the database context
USE [db];

-- If the table already exists, drop it
IF OBJECT_ID(N'dbo.tbl', N'U') IS NOT NULL DROP TABLE dbo.tbl;

-- Create a table
CREATE TABLE dbo.tbl (
     ID INT IDENTITY(1,1) NOT NULL
     , Value VARCHAR(20) NOT NULL
     , CONSTRAINT PK_tbl PRIMARY KEY CLUSTERED (ID)
     )
;
GO

-- If the dummy foreign key table already exists, drop it
IF OBJECT_ID(N'dbo.tblFK', N'U') IS NOT NULL DROP TABLE dbo.tblFK;

-- Create the dummy foreign key table
CREATE TABLE dbo.tblFK (
     ID INT IDENTITY(1,1) NOT NULL
     , IDFK INT NOT NULL
     , CONSTRAINT FK_tbl_tblFK FOREIGN KEY (IDFK) REFERENCES dbo.tbl(ID)
     )
;
GO

-- Attempt truncation
TRUNCATE TABLE tbl;

Truncation will fail with error 4712:

Msg 4712, Level 16, State 1, Line 2
Cannot truncate table 'tbl' because it is being referenced by a FOREIGN KEY constraint.

We could also create an indexed view of the table. This approach is as simple as the previous, and it has the same drawback of creating a “dummy” object in the database. However, it also has the disadvantage of consuming more disk space. Why? Because the index has to be stored! But that may not be a bad thing… if you have a secondary use for the view.

Let’s demonstrate this approach as well, re-using the sample table we created previously.

-- If the dummy indexed view already exists, drop it
IF OBJECT_ID(N'dbo.vwTbl', N'V') IS NOT NULL DROP VIEW dbo.vwTbl;
GO

-- Create a view with schemabinding (necessary for indexing)
CREATE VIEW dbo.vwTbl
WITH SCHEMABINDING
AS
     SELECT ID, Value
     FROM dbo.tbl;
GO

-- Index the view
CREATE UNIQUE CLUSTERED INDEX UIX_vwTbl
ON dbo.vwTbl(ID);
GO

-- Drop the dummy foreign key table
IF OBJECT_ID(N'dbo.tblFK', N'U') IS NOT NULL DROP TABLE dbo.tblFK;

-- Truncation will fail because the table is referenced by an indexed view
TRUNCATE TABLE tbl;

Truncation will fail with error 3729:

Msg 3729, Level 16, State 2, Line 2
Cannot TRUNCATE TABLE 'tbl' because it is being referenced by object 'vwTbl'.

So, we just walked through a few options to prevent table truncation. What about using a DDL trigger on the database? Well, for whatever reason, there is no TRUNCATE TABLE event defined in SQL Server. And, while a truncation is effectively a drop and recreate of the table, it does not fire the DROP TABLE event. Thus, a DDL trigger will not work.

Does anyone have any other ideas or real-world experience with preventing TRUNCATE TABLE operations? Please send them in via a comment.

As ever, don’t forget to clean-up after yourself!

-- Drop the demonstration objects 
IF OBJECT_ID(N'dbo.vwTbl', N'V') IS NOT NULL DROP VIEW dbo.vwTbl;
IF OBJECT_ID(N'dbo.tbl', N'U') IS NOT NULL DROP TABLE dbo.tbl;

Advertisements

Find Databases w/o Backup

Imagine you have multiple databases on a server (maybe even hundreds or thousands?), each in full recovery mode, and a scheduled job that performs a log backup every fifteen minutes. All of a sudden the job starts failing. What could be the cause?

Very likely, a database was added to the server and has not yet had a full database backup performed. A log backup can only be performed after a full backup has been taken, since log backups are by definition incremental from a full backup.

How are we going to figure out which of our thousands of databases is missing a backup? Since it must have been recently created, we could simply sort sys.databases by created_date in descending order. However, there is no guarantee that the most recently created database (or any of the top X, for that matter) is, in fact, the one missing the full backup.

The following view will help us find the databases missing full backups. They will be identified by having a NULL value for backup_finish_date. This view extends that logic further to identify any database with an “old” backup as well. In this case, old is defined as over one day ago. Obviously that threshold should be adjusted to suit your environment.

-- Select databases without a current database backup
SELECT
    'DatabaseName' = db.name
    , 'LastBackup' = MAX(backup_finish_date)
FROM
    sys.databases AS db
    LEFT OUTER JOIN msdb.dbo.backupset AS bak
        ON db.name = bak.database_name
        AND bak.[type] = 'D' -- D = Database
WHERE db.recovery_model_desc <> N'SIMPLE'
GROUP BY db.name
HAVING
    ISNULL(MAX(backup_finish_date), '1900-01-01')
      < DATEADD(DAY, -1, GETDATE());

Also of note, in this use-case we are excluding databases with a “simple” recovery model since they would not be eligible for a log backup.

UPDATE 08 May 2015: There is a flaw in the query originally presented above. This flaw is exposed through the following use case. If a database is backed up and then deleted, without removing the backup history, the record of it’s back remains. If a new database is created with the same name as the deleted database, the query above will indicate that it has a backup when, in fact, there is none. This is due to the join between databases and backupsets having been solely on the database name field.

To correct this flaw, we can add a second join criterion for the database creation date, thereby ensuring we only consider backupsets that were created after the database itself was created. See updated query below.

-- Select databases without a current database backup
SELECT
    'DatabaseName' = db.name
    , 'LastBackup' = MAX(backup_finish_date)
FROM
    sys.databases AS db
    LEFT OUTER JOIN msdb.dbo.backupset AS bak
        ON db.name = bak.database_name
        AND DATEADD(SECOND
            , DATEDIFF(SECOND, CONVERT(DATE, db.create_date), db.create_date)
            , CONVERT(DATETIME2(0), CONVERT(DATE, db.create_date))
            ) <= bak.database_creation_date
        AND bak.[type] = 'D' -- D = Database
WHERE db.recovery_model_desc <> N'SIMPLE'
GROUP BY db.name
HAVING
    ISNULL(MAX(backup_finish_date), '1900-01-01')
      < DATEADD(DAY, -1, GETDATE());

vwTraceInfo

Here is a simple view that can help decipher the traces running on a SQL Server. It is especially handy if you inherit an existing server, but can also be useful to review your own traces as needed. When used in combination with SELECT * FROM sys.traces; it provides a rather robust overview of server trace activity.

-- Return column-level information on non-default traces
CREATE VIEW vwTraceInfo
AS
SELECT
    N'TraceID' = t.id
    , N'CategoryID' = tCat.category_id
    , N'Category' = tCat.name
    , N'EventID' = tInfo.eventid
    , N'Event' = tEvent.name
    , N'ColumnID' = tInfo.columnid
    , N'Column' = tCol.name
FROM
    sys.traces AS t
    CROSS APPLY fn_trace_geteventinfo(t.id) AS tInfo
    INNER JOIN sys.trace_events AS tEvent
        ON tInfo.eventid = tEvent.trace_event_id
    INNER JOIN sys.trace_categories AS tCat
        ON tEvent.category_id = tCat.category_id
    INNER JOIN sys.trace_columns AS tCol
        ON tInfo.columnid = tCol.trace_column_id
WHERE
    t.id <> 1 -- exclude the default trace
;

vwDates

A record set of calendar dates with dimensional attributes can often come in handy. Leveraging the utfNumbers function, such a record set is easily constructed. The view below will return every calendar date between 1 Jan 1900 and 31 Dec 2100 — a full two-hundred years. Of course, it is easily modified to return more (or fewer) records.

If your application is such that join performance is an issue, you can simply materialize the view as a table or index the view (which results in transparent materialization).

-- Create a record set of dates with dimensional attributes
CREATE VIEW dbo.vwDates
AS
SELECT
    Dates.CalendarDate
    , CalendarDay = CONVERT(TINYINT, DATEPART(DAY, Dates.CalendarDate))
    , WeekDayInt = CONVERT(TINYINT, DATEPART(WEEKDAY, Dates.CalendarDate))
    , NameDay = DATENAME(WEEKDAY, Dates.CalendarDate)
    , CalendarMonth = CONVERT(TINYINT, DATEPART(MONTH, Dates.CalendarDate))
    , NameMonth = DATENAME(MONTH, Dates.CalendarDate)
    , CalendarYear = CONVERT(SMALLINT, DATEPART(YEAR, Dates.CalendarDate))
FROM
    DB.dbo.utfNumbers(DATEDIFF(DAY, '1/1/1900', '1/1/2100')) AS Nums
    CROSS APPLY (
        SELECT CONVERT(DATE, DATEADD(DAY, Nums.n, '12/31/1899')) AS CalendarDate
    ) AS Dates
;