Category: SQL

SQL, Temp Tables & Variables

Temporary objects in TEMPDB database

With this blog post, a continuation of earlier post on “TEMPDB – the most important system database in SQL Server“, we are going to see more about the temporary objects created in TEMPDB database. There are basically 3 different types of temporary objects that can create manually as below.

1. Local Temporary Tables

Local Temporary tables are created for operations that may require to access a specific set of data instead of querying every time the actual table. This will reduce the number of reads on the actual table and reduce the blocks/locks on the table.Everytime, you create a temp table (irrespective of the databases), the temp table is created in TEMPDB database.

Scope of the Local Temporary tables

The scope of the temp table is session/connection based, hence if you create two connections (two different management windows) and create the temp table with the same name, it will create two different objects in TEMPDB. 


open two Windows and execute the below
create Table #T1(Col1 int)
--Get the temp table created using the below
SELECT
    *
FROM
    sys.tables AS T
WHERE
    T.name LIKE '#t%' ;

However, as local temporary tables scope is session/connection based, these objects can still be used at nested levels.

But, wait, Will you be able to use temporary table in a function? – BIG NO. That is one of the difference between procedures and functions.

It is important to understand about temporary table caching in sql server

2. Global Temporary Tables

Global Temporary tables are again another temporary tables in SQL Server created in TEMPDB. Those are created by prefixing “##” the tablenames.

Scope of Global Temporary tables

Global Temporary tables are visible to all sessions once its created. On the flip side, there will be more contention if global temporary tables are heavily used. In addition, the scope of the global temporary table ends immediately when the session that created is terminated (or) any other session that uses the object is dropped manually. So, if you are planning to use global temporary tables, you should be very careful on its usage.

3. Temporary procedures

I believe, most of us are familiar with Temporary tables. But what about temporary stored procedures? Yes, just like temporary table, you can create temporary procedures as well in two ways – Local and Global. As we discussed earlier, the scope of the Local and Global procedures exactly same as temporary tables. These temporary tables are created in TEMPDB.

Let us look at the usages of temporary procedures. I could not find any good reason for using temporary procedures, may be, we have not used with this. But, it may be helpful while testing any procedure where we do not want to pollute the database with unnecessary objects. While deploying database, we may need to create temporary procedures for any purpose which we may not be using later etc.

If you have any use case, I would request to share your ideas.

SQL, Temp Tables & Variables

TEMPDB – the most important system database in SQL Server

Introduction

If you had anytime worked in SQL Server, you would be knowing about a system database – TEMPDB. TEMPDB is one of the most important system databases in SQL Server. As the name denotes TEMPDB database is a temporary database to SQL Server to do certain operations. As the topic is vast and its impossible to cover all aspects of TEMPDB in a single blog, planning to cover in multiple blogs to explain about TEMPDB.

Characteristics of TEMPDB

    1. TEMPDB is automatically created/recreated every time you start/restart your SQL Service That means, data anything reside in the tempdb is not persistent forever. If the service has been restarted, all the data will be lost.

    2. Without TEMPDB, you will not be able to start your SQL Server

    3. TEMPDB cannot be dropped or created manually

    4. TEMPDB cannot be backup up

    5. TEMPDB can have ONLY on SIMPLE recovery model

    6. TEMPDB can have only one filegroup(PRIMARY Filegroup)

    7. TEMPDB is a shared database

    8. TEMPDB never stored redo information, That means, you can ONLY rollback with help of undo information

    9. TEMPDB databse cannot be take to OFFLINE

    10. DBCC CHECKALLOC and CHECKCATALOG cannot be issued

Usages of TEMPDB

TEMPDB has been used in SQL Server for various purposes.

    1. Global/Temporary tables/Temporary procedures are created in TEMPDB (more details)

    2. Table Variable (more details)

    3. Cursors and internal objects created by DB engine to store intermediate results

    SQL Server database engine will create internal work tables as required as per the instruction from the relational engine. This is to improve the performance of operations like sorting, spooling, hashing etc.(more details)

    4. Online index operations needed TEMPDB

    5. Snapshot and Read Committed Snapshot Isolation requires TEMPDB

    6. Triggers

    7. Version store

I am sure this not an exhaustive list, If I miss anything, please feel free to add as comments.

How to understand the usages of TEMPDB 

select
reserved_MB=(unallocated_extent_page_count+
			version_store_reserved_page_count+
			user_object_reserved_page_count+
			internal_object_reserved_page_count+
			mixed_extent_page_count)*8/1024. ,
unallocated_extent_MB =unallocated_extent_page_count*8/1024., 
internal_object_reserved_page_count,
internal_object_reserved_MB =internal_object_reserved_page_count*8/1024.,
user_object_reserved_page_count,
user_object_reserved_MB =user_object_reserved_page_count*8/1024.
from sys.dm_db_file_space_usage

See Also

Whats new with TempDB in SQL Server 2016

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

DBA Utilities, SQL

A quick look at SQL Server Logs

Logs are always important for anyone who works with any application. SQL Server has its own Logs that can be accessed to get the required information. Often, people call it as SQL Server Error logs. However, I personally would like to refer as SQL Server logs instead of Error Logs. The reason is that it does not only report the errors or exceptions, but also other important information.

What are the different types of logs in SQL Server?

Each one represents the logs for different purposes.
1. SQL Server – Logs SQL Server specific information
2. SQL Server Agent – Logs SQL Server Agent information
3. Database Mail – Logs Database Mail related information
4. Windows NT – Logs System related information

How do we see these log information?

There are multiple ways to get the log information as below.

using TSQL command using XP_READERRORLOG

XP_ReadErrorlog is an extended procedure available in SQL server. Please note that this is an undocumented procedure.

Parameter details:

There is also another method using sp_readerrorlog. However, if you take the definition of the procedure, it uses XP_readerrorlog internally.

Using Object Explorer

Management provides two options for us to view the logs as below.
1. Management -> SQL Server Logs

2. SQL Server Agent -> Error Logs

How to find the location of SQL Server log file?

The logs are not storing in SQL Server anywhere, but in the disk. When you access the logs in any of the above methods, it reads from a file from the predefined location. We can identify the location of the log files by any of the below methods.

using XP_Readerrorlog

using SQL Server configuration Manager

– SQL Server Configuration Manager -> Right click on SQL Service, properties -> Select “Startup Parameters”
– Parameter that starts with “-e” represent the SQL Server log path

using SERVERPROPERTY


How do we clear the Logs from SQL Server?

To clear the Logs, there are two options as below. To be clear, there is no concept of clearing the log *immediately*, but closing the most recent one and clearing the oldest one as per the configuration.

sp_cycle_errorlog
– Closes the current log for SQL Server logs and flush the oldest one.
– Customize the number of maximum archives that you want to keep. The range is between 6(Min) to 99(Max).
– You can configure the number of error logs by Right click on “SQL Server Logs” under Management -> Configure -> specify the number of files. By default, the value is 6.
sp_cycle_agent_errorlog
– Closes the current log for SQL Server logs and flush the oldest one.
– Make sure the current database is msdb.
– Make sure the SQL Server Agent is up and running.
– Can have maximum of 9 archives for agent logs.

Hope this post helps you to understand the Logs in SQL Server, Looking for your feedback if I missed anything.

Always Encrypted, SQL

Task failed due to following error: Cannot save package to file. Error SQL71561: Error validating element in Always Encrypted Implementation

Problem Summary
While I am trying to configure Always Encrypted for one of my databases, I received an error as below.

Sep 19 2018 02:31:58 [Informational] TaskUpdates: Message:Task: ‘Performing encryption operations’ — Status: ‘Failed’ — Details: ‘Task failed due to following error: Cannot save package to file. The model has build blocking errors:
Error SQL71561: Error validating element [dbo].[ViewTableName]: View: [dbo].[ViewTableName] has an unresolved reference to object [DBName].[dbo].[TableName].

Environment Details:
Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 6.3 (Build 14393: ) (Hypervisor)

Observations
In fact, looking at the wizard error log report, I have seen hundreds of error messages similar to above for different elements.

While  investigating, it is found that the error has raised due to the usage of three part reference in one of views. And it is confirmed by Microsoft that its a bug in the Always Encrypted tool that it performs a strict integrity verification for the entire database schema instead of the targeted verification, that means, only for the columns to be encrypted.

Solution/Work around

To solve the problem, you need to make sure the referenced objects are present in your environment. In my case, I copied all dependent databases in testing environment and followed the below steps.

  1. Use Generate Scripts(right click on the database, select Generate Scripts in the Tasks, in the wizard, we can select View) to back up the view.
  2. Drop all the selected views which use the three-part naming conventions.
  3.  Apply always encrypt feature to columns.
  4.  Execute the generated script to re-create the view  if needed

Additional Information

When I tried to replicate the same issue in another environment with the below configuration, the above issue could not be replicated.

Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 14393: ) (Hypervisor)

Test Scripts


--Create two databases
create Database Test1
create Database Test2

use test1
--Create the below table in Test1
create Table testtable (Col1 varchar(5000))
Insert into dbo.testtable Select ('Test data')

use test2
--Create the below table in Test1
create Table testtable (Col1 varchar(5000))
Insert into dbo.testtable Select ('Test data')

--Create the below view in database Test2
create View vw_test1 as Select Col1, Cast(GetDate() as datetime) as [DATECREATED],   
  Cast(GetDate() as datetime) as [DATELASTMODIFIED],   
  Cast(newid() as uniqueidentifier) as [_IMPORTED]   From Test1.dbo.Testtable

--Apply "Always Encrypted", you will see the mentioned behavior.

Try yourself with the above code and share your comments…

Date & Time, SQL

CURRENT_TIMESTAMP in SQL Server

CURRENT_TIMESTAMP is a system function to retrieve the current system date and time. CURRENT_TIMESTAMP is an ANSI SQL function whereas GETDATE is the T-SQL version of that same function. That says it is a non-deterministic function. If you are very specific to ANSI comaptibility or thinking of heterogenous system support, I would suggest to use CURRRENT_TIMESTAMP instead of GETDATE(). As an envision, I prefer to use CURRENT_TIMESTAMP over GETDATE() personally wherever possible.

Interesting facts:

1. When we create a default constaint with CURRENT_TIMESTAMP, by default, SSMS properties are showing GETDATE().


My thought: This could be because SSMS properties have nothing to do with ANSI compatibility nor hetrogeneous support.

2. Tool tip text of the function adds parenthesis – ()

My thought: I have no clue why Microsoft has made this so.Its more concerned to me the function with “()” gives an error as below.