Home » Temporary Database

Temporary Database

sql server DBA 999

SQL Server TSQL Temporary Table

Temp tables in SQL Server: they are just like any normal tables except that they are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that it calls. Just like normal tables and have primary keys, constraints, indexes and column statistics which are kept for the table which makes query execution really fast in most cases. Temp tables can be created in all versions of SQL Server including SQL Server Express Edition.

Temp tables have space assigned to them in the TempDB database and are generally accessed only from memory, unless the server is under … Read the rest

sql server DBA 999

CTE vs Temp Table vs Temp variables

Temp tables: they are just like any normal tables except that they are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that it calls. Just like normal tables and have primary keys, constraints, indexes and column statistics which are kept for the table which makes query execution really fast in most cases.

Temp tables have space assigned to them in the TempDB database and are generally accessed only from memory, unless the server is under memory pressure or the amount of data in the table is large.

Table Variables: These tables behave very much … Read the rest

sql server DBA 999

Temporary Variables vs Temporary Tables SQL Server 2008/2005

Myth: A table variable is a memory-only structure.
This is not True. A Temp table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables.

Note: If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

Normally whether to go with Temp Variables or Temp Tables depends on:
•The number of rows that are inserted to the table.
•The number of recompilations the query is saved from.
•The type of queries and their dependency on indexes and statistics for performance.
Some other important advantages of temporary tables:
•Non-clustered … Read the rest

sql server DBA 999

Moving system databases in SQL Server 2008/2005

Moving system databases in SQL Server 2008, 2005

To move all the system and user databases from “C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA” folder to “C:System databases” location.

We can use below query to check current location of system databases:

Select name, physical_name from sys.master_files


1.Moving Tempdb database:

We need to use the Alter database command to move tempdb database files from one location to another as shown in the below screenshot.
How to move system databases sql server 3

Use master
go
Alter database tempdb modify file (name=tempdev,filename='C:System databasestempdb.mdf')
go
Alter database tempdb modify file (name=templog,filename='C:System databasestempdb.ldf')
go

Once we execute the above command the o/p will be something like,

The file “tempdev” has been modified in the system catalog. The new path will be used the next time … Read the rest

sql server DBA 999

Unable to Shrink Temp DB Size in SQL Server 2008

It occurred in production environment when I was Unable to Shrink Temp DB Size SQL Server 2008 eventhough nothing was using it.

To check the free space you can use the query below:

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

and to get idea on files

sp_helpdb 'Tempdb'

When trying to shrink Tempdb it does not let me shrink with command below to 1000MB.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 1000)
GO

I have tried setting intial sizes for tempdb which did not work either,But when we run below query to set the tempdb appropriately with fixed filegrowth then it works

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', … Read the rest

sql server DBA 999

SQL Server Raid Configurations and Recommendations

A SQL Server DBA given choice to select his Disk configuration should know the different Raid configurations and should select them appropriately.

Common RAID Levels used are:
RAID 0
RAID 0 which is also called a strip set. It is Very fast but have no redundancy.
A RAID 0 array must have 2 or more disks. A RAID 0 array has no redundancy. As data is written to the array it is spread across the disks for maximum speed. This RAID level should never be used for a SQL Server. In the event of a failure of a single disk in the array all data is lost. To calculate the space created from a RAID 0 array multiple the number … Read the rest

sql server DBA 999

SQL Server Tempdb Management

Some Facts about TempDB
The tempdb is a temporary workspace which is normally used for:
• Storage of explicitly created temporary tables.
• Worktables that hold intermediate results created during query processing and sorting.
• Materialized static cursors.
SQL Server records only enough information in the tempdb transaction log to roll back a transaction but not to redo transactions during database recovery. This feature increases the performance of INSERT statements in the tempdb. In addition, there is no need to log information to redo any transactions because the tempdb is re-created every time you restart SQL Server.
Therefore, it does not have any transactions to roll forward or roll back. When SQL Server starts, the tempdb is re-created by using … Read the rest

sql server DBA 999

SQL Server Optimistic Concurrency

SQL Server Concurrency Terminology
Optimistic concurrency control inside the SQL Server 2005/2008 database engine technically means that row versioning is used instead of a pessimistic locking approach. I believe this is a source of much confusion to both application developers and DBAs because row versioning isn’t required for applications to use optimistic concurrency control by using hints like (nolock).
Applications have used optimistic concurrency long before row versioning was introduced in SQL Server 2005/2008. In fact, the rowversion data type (a.k.a. timestamp) exists specifically to facilitate optimistic concurrency control.

Choosing the SQL Server concurrency control features (transaction isolation levels, transactions, locking hints) that are most appropriate for the task at hand without getting tripped up on terminology. For example, SQL … Read the rest