Archive for the ‘SQL Server’ Category

SQL Server 2008 Backup

Backup Types

SQL Server 2008 allows you to create four different types of backups:

  • Full Backups
  • Differential Backups
  • Transaction log Backups
  • Filegroup Backups

Full Backups

A full backup captures all pages within a database that contain data. Pages that do not

contain data are not included in the backup.

Because it is more common to back up a database than to restore one, the backup engine

is optimized for the backup process. When a backup is initiated, the backup engine grabs

pages from the data files as quickly as possible, without regard to the order of pages. Because

the backup process is not concerned with the ordering of pages, multiple threads can be used

to write pages to your backup device.

Because changes canbe made to the database while a backup is running, SQL Server needs to be able to accommodate the changes while also ensuring that backups are consistent for restore purposes. To ensure both concurrent access and backup consistency, SQL Server performs the steps of the backup procedure as follows:

1. Locks the database, blocking all transactions

2. Places a mark in the transaction log

3. Releases the database lock

4. Extracts all pages in the data fi les and writes them to the backup device

5. Locks the database, blocking all transactions

6. Places a mark in the transaction log

7. Releases the database lock

8. Extracts the portion of the log between the marks and appends it to the backup

The only operations that are not allowed during a full backup are

  • Adding or removing a database file
  • Shrinking a database

The only two parameters required for a backup are the name of the database and the

backup device. When you specify a disk backup device, a directory and a fi le name can

be specifi ed. If a directory is not specifi ed, SQL Server performs a backup to disk and writes

the file to the default backup directory configured for the instance. Although most backups

are written to a single disk file or a single tape device, you can specify up to 64 backup

devices. When you specify more than one backup device, SQL Server stripes the backup

across each of the devices specified.

Enhanced by Zemanta
Advertisements

SQL Server Data Warehousing concepts Video

An introduction to SQL Server data warehousing concepts

Data warehousing Concepts
Relational warehouse Concepts
Multi-Dimensional Concepts

Data Warehouse Components

Data Sources

ETL

Relational Data warehouse (SQL Server)

Dimensional Data warehouse (Analysis Services)

Graphical User Interface Tools

  • Use SQL to query relational warehouse
  • Use MDX (Multi dimensional expressions¬† to query cubes)
Reblog this post [with Zemanta]

Examining data files when SQL Server tempdb is full

Source Link

What does it mean when the tempdb is full?

When SQL Server’s tempdb is full, upper management will often demand a fix, a group of developers will frantically deflect blame and junior DBAs will become afraid to touch anything at all.

As I tell administrators, the first rule of thumb is simple: Keep calm. Never let the scenario unfolding cause extra stress, as that can lead to critical mistakes.

Now that we have set up the scenario, let’s address the question. The tempdb database consists of two parts: the data file(s) in the primary file group, and the tempdb log file(s). Either area could be the culprit, but the error message will tell you which part is full. Let’s concentrate on the data file portion; we will address the log file in a later article.

How can I narrow down the source?

First, let’s take a look at how to determine what takes up the most space, whichserver process ID number (SPID) we are dealing with and which host the request comes from. The query below will return the top 1000 SPIDs that are taking up space in the database. Keep in mind that the values returned are page counts. To make it easier, I included the calculations to come up with the memory values (in megabytes). Also, note that these counters are cumulative over the life of the SPID:

SELECT top 1000
s.host_name, su.[session_id], d.name [DBName], su.[database_id],
su.[user_objects_alloc_page_count] [Usr_Pg_Alloc], su.[user_objects_dealloc_page_count] [Usr_Pg_DeAlloc],
su.[internal_objects_alloc_page_count] [Int_Pg_Alloc], su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc],
(su.[user_objects_alloc_page_count]*1.0/128) [Usr_Alloc_MB], (su.[user_objects_dealloc_page_count]*1.0/128)
[Usr_DeAlloc_MB],
(su.[internal_objects_alloc_page_count]*1.0/128) [Int_Alloc_MB], (su.[internal_objects_dealloc_page_count]*1.0/128)
[Int_DeAlloc_MB]
FROM [sys].[dm_db_session_space_usage] su
inner join sys.databases d on su.database_id = d.database_id
inner join sys.dm_exec_sessions s on su.session_id = s.session_id
where (su.user_objects_alloc_page_count > 0 or
su.internal_objects_alloc_page_count > 0)
order by case when su.user_objects_alloc_page_count > su.internal_objects_alloc_page_count then
su.user_objects_alloc_page_count elsesu.internal_objects_alloc_page_count end desc

The next query is similar; it returns the top 1000 items in which SPIDs have de-allocated the most space. This query can be used to trace a process that loops, creates objects as it goes, or creates and deletes many temporary objects as it runs:

SELECT top 1000 s.host_name, su.[session_id], d.name [DBName], su.[database_id],
su.[user_objects_alloc_page_count] [Usr_Pg_Alloc], su.[user_objects_dealloc_page_count] [Usr_Pg_DeAlloc],
su.[internal_objects_alloc_page_count] [Int_Pg_Alloc], su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc],
(su.[user_objects_alloc_page_count]*1.0/128) [Usr_Alloc_MB], (su.[user_objects_dealloc_page_count]*1.0/128)
[Usr_DeAlloc_MB],
(su.[internal_objects_alloc_page_count]*1.0/128) [Int_Alloc_MB], (su.[internal_objects_dealloc_page_count]*1.0/128)
[Int_DeAlloc_MB]
FROM [sys].[dm_db_session_space_usage] su
inner join sys.databases d on su.database_id = d.database_id
inner join sys.dm_exec_sessions s on su.session_id = s.session_id
where (su.user_objects_dealloc_page_count > 0 or
su.internal_objects_dealloc_page_count > 0)
order by case whensu.user_objects_dealloc_page_count > su.internal_objects_dealloc_page_count then
su.user_objects_dealloc_page_count elsesu.internal_objects_dealloc_page_count end desc

 

Since the tempdb does not report its size properly after it has shrunk, the following query will get you the available space in tempdb:

SELECT sum(unallocated_extent_page_count) [Free_Pages],
(sum(unallocated_extent_page_count)*1.0/128) [Free_Space_MB]
FROM sys.dm_db_file_space_usage

 

Once you have determined the SPID, you can determine what T-SQL is running with dbcc inputbuffer(SPID).Let’s presume you know the T-SQL code that ran, but you also need to know the temporary table(s) involved. You could run the following:

select * from tempdb.sys.objects where type = 'u'

 

Temporary tables originating from users in T-SQL should have the format of#YourDefinedTblName____UniqueID. This will help you identify the code involved. You could also join the SPID involved from the sys.dm_exec_requestscommand and use sys.dm_exec_sql_text(SQL_Handle) to get the line running at the time, but this requires a "polling loop" to monitor when the script is actually running.