Category: TSQL

By Posted on Posted in SQL 2017, TSQL No Comments Tagged with , , , , , , ,

SQL Server 2017 introduces a new column in the sys.dm_db_file_space_usage system table. The column is modified_extent_page_count, which tells how many pages have been modified in your data file since the last full backup. What is really interesting about this is that with a little math you can calculate the percentage of your data file that has been modified.  This would be useful …

Determining how much of your data file has been modified Read more »

By Posted on Posted in TSQL 1 Comment Tagged with , , , , ,

One of the common problems with when diagnosing what appears to be a slow scenario where there may be blocking involved is determining what is blocking on SQL Server. Here is a query that I like to use. If there is no blocking occurring then this query will return nothing.   This script works on SQL Server 2008, 2008R2, 2012, …

Finding Blocking Queries Read more »

By Posted on Posted in TSQL No Comments Tagged with , ,

With SQL Server 2016, there was a new compatibility level introduced, level 130 the new SQL Server 2016 Compatibility Level. SQL Server can run in a number of different compatibility levels, but how do you change it and how do you set it. These compatibility levels reflect the version of SQL server. 60 = SQL Server 6.0 65 = SQL Server …

SQL Server 2016 Compatibility Level Read more »

By Posted on Posted in TSQL No Comments Tagged with , , ,

  The question of how to delete a trigger after a specific date came up.  Here is what could be done. If you want to trigger to disappear after a specific date or time, you can just drop the trigger from inside the trigger itself. Something like this. If instead of dropping the trigger you just want to disable it, …

Drop a trigger after a specific date Read more »

By Posted on Posted in DBCC Commands No Comments Tagged with , , , , , , ,

TL;DR summary: Don’t do it. Stop reading here if you want, but just don’t do it. This post refers to shrinking your database files (mdf, or ndf files), not shrinking the log file. The log file is a completely different conversation, however shrink database does shrink the log file. Not shrinking your database is one of the more counter intuitive …

DBCC ShrinkDatabase – I want to shrink my database. Read more »

By Posted on Posted in SQL Server, TSQL 3 Comments Tagged with , , , ,

Its not always quick and easy in SQL Server to get a full list of the jobs that have been run, when they were run and how long they took. I created the following script to quickly check on the status and run time of SQL Server maintenance plans and jobs. Display Job History Just run the script in SQL …

SQL Server Script to Display Job History Read more »

By Posted on Posted in DBCC Commands No Comments Tagged with , , ,

If you use the SQL Server maintenance plans to run DBCC Check DB, you are not allowed to include TempDB in your DBCC CheckDB sequence. For a while I was using maintenance plans, and ending up adding a separate job step to run TSQL to just check TempDB. Rather than using two different steps, the regular maintenance plan, and a …

DBCC CheckDB All Databases Read more »

By Posted on Posted in Corruption, TSQL No Comments Tagged with , ,

Is a clustered table in SQL Server a BTree or Linked List? When you first learn about the structure behind clustered indexes in SQL Server, you find out that the clustered index is structured as a type of B+Tree where queries that make use of the tree structure to find the rows that you are looking for. However it is not the common …

SQL Server Tables – BTree or Linked List? Read more »