Enable xp_cmdshell in SQLServer

“xp_cmdshell” is a stored procedure and this will allow to run operating system commands from SQLServer.

You can run below command to get a listing of the backup files under “d:\backup\*.bak”

xp_cmdshell 'dir d:\backup\*.bak '
or
execute xp_cmdshell 'dir d:\backup\*.bak '

You will get below error if xp_cmdshell is not enabled, by default xp_cmdshell is turned off.  

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

The xp_cmdshell can be enabled and disabled by executing “sp_configure” or using the Policy-Based Management.

-- Run below command to allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO

-- Run below command to update the currently configured value for advanced options.
RECONFIGURE
GO

-- Run below command to enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO

-- Run below command to update the currently configured value for this feature.
RECONFIGURE
GO

You should see below output results when you execute the commands

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

Users may receive below backup failure errors when xp_cmshell is disabled.

2012-12-07 06:00:17.03 spid50      Error: 18204, Severity: 16, State: 1.
2012-12-05 06:00:17.03 spid60      BackupDiskFile::CreateMedia: Backup device '\\SQL backup\MSSQL01\Full_Backup\AppCenter\AppCenter_backup_2012_12_07_060001_8233143.trn' failed to create. Operating system error 1330(Logon failure: the specified account password has expired.).
2012-12-07 06:00:17.03 Backup      Error: 3041, Severity: 16, State: 1.
2012-12-07 06:00:17.03 Backup      BACKUP failed to complete the command BACKUP LOG AppCenter. Check the backup application log for detailed messages.

If users are taking database backup over network drive, they may still encounter backup failures after enabling xp_cmshell .

When SQLServer is unable to connect or open connection with shared resource, you still see the backup failures. To resolve the issue create a job using below syntax in SQLServer and schedule the job, it will help to resolve connection with shared resource.

Syntax
 xp_cmdshell 'net use  \\SQLbackup\MSSQL01\ password /User:domain\userid'

Net use command - Connects / disconnects the computer from a shared resource like network drive, mapped drivers.

Refer below link for more information on xp_cmdshell
http://msdn.microsoft.com/en-us/library/aa260689(v=sql.80).aspx
http://msdn.microsoft.com/en-us/library/ms175046.aspx

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Configure SQLServer to listen on a Static port or Multiple Static Ports

Dynamic port is used by named instance only. By default the named instance listens on Dynamic port.  When you start the SQL server for the first time, it requests for port from O/S and it is allocated to SQL Server. This port information is written into the windows registry.  

Every time you start the named SQLServer, it uses the port that is allocated. In case if the port is used by another program then SQL Server chooses another port at the time of restart.

You will receive “Error ID 10048” If the port is used by another program.

Static Port, If you configure an SQLServer instance to use a static port then you should restart the instance to take effect of changes and all clients must send their request to static port

By default, the default instance listens on fixed TCP Port 1433. The default instance of SQLServer does not support dynamic port allocation, but can also make the default instance to listen on multiple static ports.  The named instances of SQL Server support allocation of both static and dynamic ports

Follow these steps to configure Static TCP Port

1.      Click Start à All Programsà Microsoft SQLServer à Configuration Tools à Launch  “SQLServer Configuration Manager”

2.      Under “SQLServer Network Configuration” à Select “Protocols for <Instance Name” à Got to TCP/IP Properties

3.      Select “IP Addresses Tab” à Go to “IPAll” section

4.      Remove TCP Dynamic ports and update TCP Port: <Static port>

For ex: - 5000

Restart the SQLServer to take effect of new Static port.

You can configure the SQLServer to listen on multiple Static TCP Ports. Adding Additional ports to SQL Server will increase the performance of the SQL Server.

Follow these steps to configure additional TCP ports:

1.      Click Start à All Programsà Microsoft SQLServer à Configuration Tools à Launch  “SQLServer Configuration Manager”

2.      Under “SQLServer Network Configuration” à Select “Protocols for <Instance Name” à Got to TCP/IP Properties

3.      Select “IP Addresses Tab” à Go to “IPAll” section

4.      Enter the additional port separated by a comm for TCP ports

For ex:- TCP Port: 1234,5000

Verify the port configuration

1.      You should be able to see the new port information in error.log file

Server is listening on [ <IP Address> <Port Number>].

For ex: - Server is listening on [ <10.10.10.10> <ipv4>  <5000>].

If you have configured multiple static TCP ports then you should see below information in error.log file

  Server is listening on [ <IP Address> <Port Number>].

  Server is listening on [ <IP Address> <Port Number>].

  For ex: - Server is listening on [ 10.10.10.10 <ipv4> 1234].

   Server is listening on [ 10.10.10.10 <ipv4> 5000].

2.      You can also check the updated port information in “regedit”

Start Registery (regedt32.exe) and locate the following registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer\SuperSocketNetLib\Tcp

In case of 2005,

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL.x>\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

If the allocated port is Static then you should see below values

TCPDynamicPorts = Blank

TCPPort = New Static port

If the allocated port is Dynamic then you should see below values

TCPDynamicPorts = Current used port

TCPPort = Current used port

To configure a static port for the specialized Dedicated Administrator Connection (DAC), you must update the registry key that corresponds to your instance.  For ex:-  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\ MSSQL.X\MSSQL Server\SuperSocketNetLib \AdminConnection\Tcp

Where X indicates the number

Please note that it is not recommended that named instance listen on port 1433

Regards,

Satishbabu Gunukula

http://sqlserver-expert.com

Create New Error Log or Recycle Error log in SQLServer

SQLServer - Every time you restart the SQLServer a new error log will be created. The old error will be renamed as ERRORLOG.1. In most of the production environment the SQLServer will be restarted rarely and you will notice a large ERRORLOG.

In order to recycle or create a new error log you no need to restart the SQLServer.
You can recycle the error log using below command.

EXEC sp_cycle_errorlog
GO

You can limit the number of error log files before they are recycled.
1. In Object Explorer, Click the plus sign to expand Managment.
2. Right-click on the SQLServer Logs folder and select Configure.
3. In the Configure SQL Server Error Logs dialog box, you can enter maximum number of Error logs.

SQLServer Agent also has an error and it will be recreated every time you restart SQLServer Agent. The new error log will be generated as SQLAGENT.OUT and old error log will be renamed as SQLAGENT.1...etc. The SQLServer Agent can maintain up to 9 SQLServer Agent Error logs.

You can recycle the SQLServer Agent Error log using below
EXEC sp_cycle_agent_errorlog
GO

It is always good practice to setup a job to recycle both SQLServer and SQLServer Agent error logs.

Following below steps you can reanme SQLServer Agent error log
1. In Object Explorer, Click the plus sign to expand SQL Server Agent.
2. Right-click on the Error Logs folder and select Configure.
3. In the Configure SQL Server Agent Error Logs dialog box, enter the new file path and file name and click OK

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com/

How to generate Cluster.log for Windows or SQLServer Cluster troubleshooting

If you are doing troubleshooting with your Windows/ SQLServer Cluster you need to generated Cluster.log

Use below command to generate the cluster.log
C :\> cluster /cluster:clustername log /gen

The logs will be generated on each cluster node under %WinDir%\Cluster\Reports folder.You need to login into each node in order to collect the stats.

You can use below command to generate the cluster.log for all nodes on the node where you are running the command.

C:\> cluster /cluster:clustername log /gen /copy:.

Using Powershell - PowerShell is a cluster scripting language.

For the Windows Server 2008 R2 you may access PowerShell management
Start à Administrator Tools à Windows PowerShell Modules.

You can load PowerShell with Failover Clustering in two ways:
1. Open “Failover Cluster PowerShell Management” from the shortcut in Administrative Tools
2. Run powershell.exe as administrator

You will find the powershell.exe under C:\Windows\System32\WindowsPowerShell\v1.0

You can also generate the cluster log using PowerShell.
Syntax:- Get-ClusterLog [-InputObject ] [[-Node] ] [-Cluster ] [-Destination ] [-TimeSpan ] []

Use below command to generate a log file for each node of the local cluster
Get-ClusterLog

Use below command to generate a log file for each node of the local cluster, and copies all logs to the local TEMP folder
Get-ClusterLog – denstination c:\Temp

Use below command to generate a log file for the local cluster in the cluster reports folder on each node of the cluster. The log covers the last 5 minutes
Get-ClusterLog -TimeSpan 5

You can refer below link for Failover Cluster Cmdlets in Windows PowerShell
http://technet.microsoft.com/en-us/library/ee461009.aspx

Windows PowerShell user guide
http://technet.microsoft.com/en-us/library/bb978526.aspx

Regards
Satishbabu Gunukula
http://sqlserver-expert.com/

Index was outside the bounds of the array

I was trying to connect to SQLserver 2012 using SQLserver 2008 Management studio and received below error. I was able to connect and able to query.

I tried the same with SQL2005 Management studio and same error. I didn’t see useful information under Technical details.

Cause: Please note that old versions of SSMS (SQLServer Management Studio) are not forward compatible. Using 2012 SSMS you can connect to SQLServer 2005/2008 databases as it is backward compatible.

It looks like Microsoft will not fix the forward compatiable.

Solution: Upgrade all your client tools to highest version, and then you won't be missing out on any functionality or getting error messages.

Regards,

Satishbabu Gunukula

http://www.sqlserver-expert.com

Manage Database files and file groups in SQLServer

SQLServer has three types of files

Primary – Primary data file contains the startup information of the database and also has information about other files in the database. The file extension is .mdf

Secondary – Secondary data file is used to store user data and these files are optional. The secondary files are used to spread data across multiple disks. The file extension is .ndf

Transaction log- Transaction log file holds the log information that is needed to recover the database. The file extension is .ldf

Every database has one primary file group and it is the best practice to create a user-defined file group for user data.

Create a user-defined file group:  Use below syntax to create user defined file group “USER_DATA”

USE master
GO
ALTER DATABASE TestDB
ADD FILEGROUP USER_DATA;
GO

ALTER DATABASE TestDB
ADD FILE
(
    NAME = USER_DATA1,
    FILENAME = ‘D: \DATA\user_data.ndf',
    SIZE = 10MB, MAXSIZE = 1000MB, FILEGROWTH = 5MB
),
TO FILEGROUP USER_DATA;
GO

You can also create a user-defined file group using Microsoft SQLServer Management Studio

1       . Right click on the database and choose Properties

2       . Go the FileGroups page, check on add and provide the file group name.

3       . Select the Default checkbox to make it as default file group

Modify User-defined/seconday file group as default:

USE master
GO
ALTER DATABASE [TestDB] MODIFY FILEGROUP [USER_DATA] DEFAULT

Please note that all the user objects will create in default file group.

Resize the file or Modifying a file:  Use below syntax to increase the max size of the file

USE master;
GO
ALTER DATABASE TestDB
MODIFY FILE
    (NAME = USER_DATA1, MAXSIZE = 2000MB);
GO

Add a data file to existing file group:  Many of the users will come across this If you primary data file drive is out of space. Create a new drive and add a new ndf file to the file group.

USE [master]
GO
ALTER DATABASE [TestDB] ADD FILE ( NAME = N'USER_DATA2', FILENAME = N'E:\SQLData\user_data2.ndf' , SIZE = 10MB, MAXSIZE = 1000MB, FILEGROWTH = 5MB) TO FILEGROUP [USER_DATA]
GO

Moving a file to a new location: You must physically move the file to the new disk/direcotry before running this syntax.

USE master;
GO
ALTER DATABASE TestDB
MODIFY FILE
( NAME = USERd_ATA2,
    FILENAME = N’E:\SQLData\user_data2.ndf'
); GO

Make sure to stop and start the SQL Server or take the database OFFLINE and then ONLINE To implement the change.

Removing a file from database: Use following syntax to remove a file from database

USE master;
GO
ALTER DATABASE TestDB
REMOVE FILE USER_DATA2;
GO

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com/

Msg 306, Level 16, State 2, Line 23

You will receive below error when you try to GROUP BY or ORDER BY a column with data type TEXT,NTEXT or IMAGE.

Msg 306, Level 16, State 2, Line 23
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

The text, ntext, and image data type columns cannot be compared or sorted, except when using IS NULL or LIKE operator.

NTEXT – Data type is used for variable-length of Unicode data
TEXT – Data types is used for variable-length non-Unicode data
IMAGE – Data type is used for variable-length binary data.

The work around for this error is to convert TEXT or NTEXT columns to VARCHAR or NVARCHAR when you use these columns in ORDER BY or GROUP BY clause. You can convert IMAGE columns to VARBINARY.

Example for ORDER BY clause-
Select * from dbo.employee order by comments;

Modify the query as
Select * from dbo.employee order by CAST (comments as nvarchar)

Example for GROUP BY clause-
Select * from dbo.employee group by dept;

Modify the query as
Select * from dbo.employee group by CAST (dept as nvarchar)

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

How to move TempDB in SQLServer?

If the TempDB grows fast and does not have have enough space to grow then the best option is move the TempDB to another physical drive, which will help to improve database performance.

You will receive below error messages, if tempdb is full

Source: MSSQLSERVER
Event ID: 17052
Description: The log file for database 'tempdb' is full.
Back up the transaction log for the database to free up some log space

Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.

Follow the below steps to move the tempdb to new location.

Step 1: Open a “New Query” Window and run the below command to get the path and names of the TempDB.

USE TempDB
GO
EXEC sp_helpfile
GO

Or

SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID('TempDB');

Step 2: In the following query Replace “NewPath” with new drive path and run the command to change the LDF and MDF file path.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '}\templog.ldf');
GO

The tempDB definition has been changed, but no changes are made to until you restart SQLServer. Please stop and restart SQL Server and

Step 3: Stop and Start the SQLServer

Control Panel -- > Administrative Tools --> Services --> Right click on SQLServer (MSSQLSERVER) --> Restart

or

SQL Server Configuration Manager --> SQLServer 2008 Services --> Right Click on SQLServer (MSSQLSERVER) --> Restart

Now TempDB files created in new drive.

Step 4: Check the TempDB new file location patch

SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID('TempDB');

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Authentication Modes in Microsoft SQLServer

Microsoft SQLServer has two authentication modes
1. SQLServer authentication – The logins are created in SQLServer which are not available at Windows level.
2. Windows authentication – The windows user accounts validated by SQLServer at the time of log using Windows Active Directory.

The sa account connects by using SQL Server Authentication. For SQLServer accounts, three option password policies available.
• User must change password at next login
• Enforce password expiration
• Enforce password policy

Users will receive below errors, if only Windows authentication mode enabled.

2012-03-09 15:41:06.44 Logon Login failed for user 'App_user'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 172.20.251.53]

To change the authentication mode follow the below steps.
1. Launch SQLServer Management Studio, right click on Server and select properties
2. Go to Security page, Under “Server Authentication” select the authentication mode
3. Restart the SQLServer

To enable the SA Login execute the following command.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'Passowrd';
GO

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Whats new in SQLServer 2012?

There are many new features introduced in SQLServer 2012, but 5 of them or most important.

1.Great Availability
2.Rapid Data Exploration
3.Blazing-Fast Performance
4.Optimized Productivity
5.Credible, consistent Data

SQLServer 2012 Licensing
From SQL Server 2012, a new licensing model introduced i.e. core-based licensing. Only enterprise and Standard will be available under this licensing. To license a physical server, you must license all the cores in the server, with a minimum of 4 core for each processor in the server.

Please refer below link for SQLServer 2012 Licensing Overview
http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-licensing.aspx

SQLServer 2012 Editions
SQLServer introduced new edition called “Business Intelligence”. Please see the below link for Editions and SQLServer 2012 capabilities.

http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx

SQLServer 2012 enable mission-critical environment with maximum uptime and data availability. You can achieve greater availability using Synchronous or Asynchronous data movement. Please check below link for detailed info.

http://www.microsoft.com/sqlserver/en/us/future-editions/SQL-Server-2012-mission-critical-confidence.aspx

SQLServer 2012 Overview
SQLServer 2012 is a cloud ready platform and you can quickly build solution based up on your organization needs. Please see the blow link for overview.
http://download.microsoft.com/download/3/6/C/36C733CA-394B-4C44-A83C-945600644B0A/SQL_Server_2012_Overview_Feb2012.pdf

Download SQLServer 2012
SQLServer 2012 RC0 is here and you can download using below link.
https://profile.microsoft.com/RegSysProfileCenter/wizard.aspx?wizid=29ad67d7-2f89-4fbc-9f95-8069f2d34723&lcid=1033

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

How to Shrink Tempdb in SQLServer

Once in a while DBA’s will come across the situation that tempdb is full and no longer able to expand. You can avoid this situation by proactively monitoring the system with notification system setup for particular threshold.

To avoid this situation proactive monitoring is the best solution and it is always recommend to setup alerts for particular threshold.

It's highly recommended to disable "Autogrow" option for better performance. By default this option is enabled and it grows by 10% of its initial size. While performing auto growth, it will lock all applications and internal operations. This will also lead to fragmentation in the tempdb database.

Each time the SQL Server service starts, the tempdb is newly created by copying from the model database and inheriting some database configurations from it. The default size of tempdb is 8MB

You can try below solutions to shrink the databases.

Method 1: Shrink database using DBCC SHRINKDATABASE.

DBCC SHRINKDATABASE (tempdb,’’)
For ex:-
DBCC SHRINKDATABASE(tempdb,40)

This command shrinks the tempdb database as a whole with specified percentage.

Method 2: Shrink the tempdb data file or log file

Use tempdb
GO
--shrink the data file
DBCC shrinkfile (tempdb_data,’< target_size_in_MB’>)
GO
--shrink the log file
DBCC shrinkfile (tempdb_log, ’)

Ex:-
DBCC shrinkfile (tempdb_data, 5000);

Method 3: shrink the tempdb data file or log file using T-SQL Command

-- Shrink the data file
ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp_data', SIZE = target_size_in_MB)

-- Shrink the log file
ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp_log', SIZE = target_size_in_MB)

Where target_size_in_MB= Desired target size for the data/log file

Errors while shrinking tempdb
If you have any active transactions in tempdb then you may receive below consistency errors.

1. Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'. Check sysobjects.

This error may not be an indicative of any corruption, but it causes the shrink operation to fail.

2. Server: Msg 8909, Level 16, State 1, Line 0 Table Corrupt: Object ID 1, index ID 0, page ID %S_PGID. The PageId in the page header = %S_PGID.

This 8909 error indicates tempdb corruption. You can clean up the consistency errors by restarting the SQL Server. If you still see any errors then restore the database from valid backup.

3. DBCC SHRINKFILE: Page 1:456120 could not be moved because it is a work file page.

This error indicates that Page could not be moved because it is a work file page. You can release the cached objects by running “DBCC FREEPROCCACHE”. Now try shrink tempdb once again to release the free space.

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Shrink Database fails with Error “Backup, file manipulation operations”

In some cases users may get below error while trying to shrink their databases manually or using SQLServer management studio.

Executing the query "DBCC SHRINKDATABASE(N'OperationsManagerDW', 10, TR..." failed with the following error: "Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

Possible reason:
The backup is running when user are trying to perform the database shrink. Please note that below operations may not be performed at the same time as a backup operation

• File management operations such as the ALTER DATABASE statement with either ADD FILE or REMOVE FILE clauses.
• The file truncation phase of shrink database or shrink file.

Please note that if backup is running and one of the above operations is attempted, then operation fails immediately. When user is trying to add or remove a file and if backups is started then backup will wait for a timeout period, then fail.

If user is trying to shrink database or file while a backup is running, the shrink stops without truncating the file. If backup started after shrink database operation then backup waits for shrink operation to complete.

Action:
Reissue the statement after the current backup or file manipulation operation is completed.

Regards,
Satishbabu Gunukula
http://sqlserver-expert.com

What is TEMPDB in SQLServer?

What is TempDB?
The tempdb is a system database, which is global resource available to all users.

The tempdb is a temporary workspace for storing temporary tables, worktables that hold intermediate results during the sorting or query processing and materialized static cursors, which intern increase the performance for SQLServer.

The SQLServer records the information in tempdb which is required to roll back a transaction in case required. It will not record any information which is required for database recovery. The tempdb is re-created each time the SQL Server service starts with clean copy of the database. The tempdb is created from model database and reset to its last configured size.

Create or move your temp db in a driver where you have adequate storage. It is advised to place tempdb on RAID 1+0 (dependent on TEMPDB usage) or RAID 10 for performance benefits.

Who can access tempdb?
Any user can create the objects in tempdb, but they can only access their own objects unless until they have additional permissions.

Can I Backup tempdb Backups?
The tempdb can only be configured in the simple recovery model. Note that no backup and restore operations are not allowed on tempdb.

How to estimate Tempdb size?
It is very difficult to estimate the tempdb space requirement for an application. You can only estimate the size with experience and experiment to gain a satisfactory result. It is always recommend keeping 20% more space for tempdb.

The following features use tempdb:
• Temporary tables, table variables, and table-valued functions
• Query, Cursors, Triggers
• Snapshot isolation and read committed snapshot
• MARS
• Index creation, Online index creation
• DBCC CHECK
• LOB parameters , XML and LOB variable
• Service Broker and event notification
• Query notifications, Database mail
• User-defined functions

If your application using tempdb database more and causing it to grow larger than its default size then it is advised to increase the default size of the tempdb to a size closer to what is typically used by your application.

Can I shrink the tempdb?
Yes, you can shrink tempdb. But shrinking files regularly is not a recommended practice, because these files may probably grow again. Also shrink operations causes’ data fragmentation.

We have some limitations on shrinking tempdb:
• Shrink operations do not shrink the version store or internal objects.
• In some cases the DBA might need to restart the server in a single user mode to allow shrinking of the tempdb.

You can query sys.dm_db_file_space_usage table to see the space allocation. Use below query to see space allocation information for the files associated with tempdb

SELECT
SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage

Major Problems with TEMPDB:-
1. tempdb has run out of space.
2. tempdb is experiencing I/O bottleneck
3. tempdb is experiencing contention

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com