Monitor tempdb In Sql Server 2005

Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prevent applications that are running from completing operations. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used by these features in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. There are also several counters that can be used to monitor the free space that is available in tempdb and also the resources that are using tempdb. For more information, see Troubleshooting Insufficient Disk Space in tempdb.

Shrink Log file

use test
GO
DBCC sqlperf(logspace)


Use Test
GO
--'Nul' it is an old DOS trick that behaves as if you are writing a file,
-- but really just dumps the information off, It's not use diskspace
BACKUP LOG test TO DISK = 'Nul'
go
dbcc shrinkfile(2,256)--Shrink file upto 256
GO

--TO know what are the files created for 'Test' database
Use Test
GO
SP_Helpdb 'test'

Create Folder in SQL Server 2005

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @DBName sysname
DECLARE @DataPath nvarchar(500)
DECLARE @LogPath nvarchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)

-- 2 - Initialize variables
SET @DBName = 'Foo'
SET @DataPath = 'C:\zTest1\' + @DBName
SET @LogPath = 'C:\zTest2\' + @DBName

-- 3 - @DataPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DataPath

-- 4 - Create the @DataPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @DataPath

-- 5 - Remove all records from @DirTree
DELETE FROM @DirTree

-- 6 - @LogPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @LogPath

-- 7 - Create the @LogPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @LogPath

SET NOCOUNT OFF

GO

How do we know which files and file groups are ONLINE

select fg.name as FilegroupName, fg.type_desc as FileGroupType, df.name as FileName, df.physical_name, df.state_desc from sys.database_files df left outer join sys.filegroups fg on df.data_space_id = fg.data_space_id

Common Table Expression

Common Table Expression

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

·         Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

·         Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

·         Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Structure of a CTE:

A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT <column_list>

FROM expression_name;

Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression).

Here, the result of CTE is repeatedly used to get the final resultset. The following example will explain in detail where I am using AdventureWorks database and try to find hierarchy of Managers and Employees.

Introduction:
The common table expression is one of the new features in sql server 2005. It can be used instead of temp table or table variables in the stored procedures in the circumstances. Let's see CTE with some example queries.

Background:
Most of the developers while writing the stored procedures they create the temp tables or table variables. They need some table to store the temporary results in order to manipulate the data in the other tables based on this temp result.

The temp variables will be stored on the tempdb and it needs to be deleted in the tempdb database.

The table variable is best when compare with the temp tables. Because the table variable initially will be there in the memory for the certain limit of size and if the size increase then it will be moved to the temp database. However the scope of the table variable is only up to that program. When compare with table variable the CTE is best. It just store the result set like normal view.

CTE (Common Table Expression):
The CTE is one of the essential features in the sql server 2005.It just store the result as temp result set. It can be access like normal table or view. This is only up to that scope.

The syntax of the CTE is the following.

WITH name (Alias name of the retrieve result set fields)
AS
(
//Write the sql query here
)
SELECT * FROM name

Here the select statement must be very next to the CTE. The name is mandatory and the argument is an optional. This can be used to give the alias to the retrieve field of the CTE.

CTE 1: Simple CTE
WITH ProductCTE
AS(  SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
  FROM Products
)SELECT * FROM ProductCTE

Here all the product details like ID, name, category ID and Unit Price will be retrieved and stored as temporary result set in the ProductCTE.

This result set can be retrieved like table or view.

CTE2:Simple CTE with alias
WITH ProductCTE(ID,Name,Category,Price)AS(  SELECT ProductID,ProductName,CategoryID,UnitPrice
  FROM Products
)SELECT * FROM ProductCTE

Here there are four fieds retrieves from the Products and the alias name have given in the arqument to the CTE result set name.

It also accepts like the following as it is in the normal select query.

WITH ProductCTE
AS(  SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
  FROM Products
)SELECT * FROM ProductCTE

CTE 3: CTE joins with normal table
The result set of the CTE can be joined with any table and also can enforce the relationship with the CTE and other tables.

WITH OrderCustomer
AS(  SELECT DISTINCT CustomerID FROM Orders
)SELECT C.CustomerID,C.CompanyName,C.ContactName,C.Address+', '+C.City AS [Address] FROM Customers C INNER JOIN OrderCustomer OC ON OC.CustomerID = C.CustomerID

Here the Ordered Customers will be placed in the CTE result set and it will be joined with the Customers details.

CTE 4: Multiple resultsets in the CTE
WITH MyCTE1
AS(  SELECT ProductID,SupplierID,CategoryID,UnitPrice,ProductName FROM Products
), 

• MyCTE2AS(  SELECT DISTINCT ProductID FROM "Order Details")SELECT C1.ProductID,C1.ProductName,C1.SupplierID,C1.CategoryID FROM MyCTE1 C1 INNER JOIN MyCTE2 C2 ON C1.ProductID = C2.ProductID
  
  Here, there are two result sets that will be filtered based on the join condition.
  
  CTE 5: Union statements in the CTE
  WITH PartProdCateSaleAS(SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName='Condiments')UNION ALL
  SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName='Seafood')
  )SELECT OD.ProductID,SUM(OD.UnitPrice*OD.Quantity) AS [Total Sale] FROM "Order Details" OD INNER JOIN PartProdCateSale PPCS ON PPCS.ProductID = OD.ProductIDGROUP BY OD.ProductID
  
  Normally when we combine the many result sets we create table and then insert into that table. But see here, we have combined with the union all and instead of table, here CTE has used.
  
  CTE 6: CTE with identity column
  WITH MyCustomCTE
     AS   (      SELECT CustomerID,row_number() OVER (ORDER BY CustomerID) AS iNo FROM
           Customers
     )SELECT * FROM MyCustomCTE

AdvantagesCan be referenced multiple times in a query.

• Can be used to create recursive queries.
• Can be used in place of a view in scenarios when the metadata definition need not be stored.
• Improves readability.
• CTEs help improve readability of the code without compromising performance.
• They help improve maintainability of the code without compromising performance.
• They make writing recursive code in T-SQL significantly easier than the previous SQL Server versions.

Killing all Sql Server Data Bases

Declare @DBName varchar(50), @withmsg bit
Set @withmsg=1
Set @DBName='yourdatabasenmame' ---Change your databasename here
SET NOCOUNT ON
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END
IF @withmsg =1
PRINT  CONVERT(VARCHAR(10), @ConnKilled) + ' Connection(s) killed for DB '  + @DBName
GO

Note: Change the databasename for @DBName variable

IBM Interview Questions

1.     How many IP’s are required for 2 node cluster?

Ans: 6

2.     Brief about IP’s usage?

Ans: 6 IP (  2 external IP (public), 4 Private IP)
 
3.     Scenario: My SQL server instances are working fine on Node A but instances are not failover from Node B, what could be the reason?

Ans: Network down

4.     What are differences between SQL 2005 and 2008 w.r.t Cluster?

Ans:

5.     What are the differences between SQL 2005 and SQL 2008 installation wise?

Ans:

6.     What are the agents created when we configure Transactional replication?

Ans: snapshot agent, distribution agent

7.     How to trouble shoot if Primary key violation is happened in Transactional replication?

Ans: sp_browsereplcommands

8.     How transactions are move between Publisher to Distributor and Distributor to Subscriber?

Ans:

9.     What is difference between Transactional replication and Merge replication?

Ans:

10.  What is diff between Log shipping and Mirroring?

Ans:

               With Log Shipping:

Data Transfer:    T-Logs are backed up and transferred to secondary server
Transactional Consistency:  All committed and un-committed are transferred
Server Limitation:   Can be applied to multiple stand-by servers
Failover:   Manual
Failover Duration:  Can take more than 30 mins
Role Change:   Role change is manual
Client Re-direction:  Manual changes required

With Database Mirroring:

Data Transfer:    Individual T-Log records are transferred using TCP endpoints
Transactional Consistency:  Only committed transactions are transferred
Server Limitation:   Can be applied to only one mirror server
Failover:   Automatic
Failover Duration:  Failover is fast, sometimes < 3 seconds but not more than 10 seconds
Role Change:   Role change is fully automatic
Client Re-direction:  Fully automatic as it uses .NET 2.0


11.  I want to give permissions to only one job for creation/deletion of that job only, out of 100’s of jobs, how?

Ans:

12.  Where we find Log files for clustering?

Ans: c:/program files/ windows/cluster.log


13.  How to move Tempdb, Is it require to re-start sqlserver?

Ans: Yes