Thursday, April 12, 2012

Free SQL Server command line tool "SQLS*Plus"

There is a free SQL Server command line tool "SQLS*Plus" (on ) - exactly like SQL*Plus for SQL Server.
It is very flexible with data formatting (set lines size, pagesize, etc), variables (&, &&), spool, parameters, batch processing, etc

Moving the system database "tempdb"

If the system database "tempdb" is defined on several data files as recommended in general, each data file is moved by a ALTER DATABASE statement.

USE master;
MODIFY FILE (NAME = tempdb_data, FILENAME = 'H:\MSSQL\tempdb.mdf');
MODIFY FILE (NAME = tempdb_01, FILENAME = 'I:\MSSQL\tempdb_01.ndf');
MODIFY FILE (NAME = tempdb_02, FILENAME = 'J:\MSSQL\tempdb_02.ndf');
MODIFY FILE (NAME = tempdb_03, FILENAME = 'K:\MSSQL\tempdb_03.ndf');
MODIFY FILE (NAME = tempdb_log, FILENAME = 'L:\MSSQL\templog.ldf');

The output of each statement is a message that the system catalog is modified and that the new path will be used after a restart of the database instance.

The file "tempdbXXXX" has been modified in the system catalog. The new path will be used the next time the database is started.

Please notice that you have to remove the old tempdb files manually after the restart.

If the size of the files is configured before the move, a side benefit is de-fragmented files.

Modify SQL Agent Errorlog path and Default Database Path

If you are moving databases to a new drive/volume or another server (different drive letter), you will find it useful to also change the SQL Server Agent log path as well as the Database Default Locations path. If you do not change the SQL Agent log path, SQL Agent will fail to start. If you do not modify the Database Default Locations path, creating a database may fail, if the original drive letter\path does not exist any more.

To change the SQL Agent log path:

1. In SQL Server Management Studio expand SQL Server Agent

2. Right-click on Errorlogs
3. Click on Configure
4. Change the Error Log File path to the new desired location

To change the Database Default Locations path

1. In SQL Server Management Studio right-click on the SQL Server name
2. Choose Prooperties
3. Choose Database Settings
4. Under "Database Default Locations" modify the Data and Log paths to the new, desired location

TSQL Script to return files and Free Space

Here is the script


;WITH c AS (
SELECT AS [FileGroupName]
       ,s.physical_name AS [FileName]
       ,s.size * CONVERT(FLOAT, 8) AS [Size]
       ,CAST(CASE s.type
               WHEN 2 THEN 0
               ELSE CAST(FILEPROPERTY(, 'SpaceUsed') AS FLOAT) * CONVERT(FLOAT, 8)
             END AS FLOAT) AS [UsedSpace]
FROM    sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ( ( s.type = 2
                                        OR s.type = 0
                                      AND s.database_id = DB_ID()
                                      AND ( s.drop_lsn IS NULL )
                                    AND ( s.data_space_id = g.data_space_id )
SELECT *, [size] - [UsedSpace] RemainingSpace

Tuesday, March 20, 2012

How to disable constraints on a table

Sometimes it's useful to disable one or more constraints on a table, do something significant like a BULK INSERT / DELETE, and then re-enable the constraint(s) once you're done.

You can disable / enable the constraint using the ALTER TABLE … NOCHECK/CHECK CONSTRAINT  …

Here is an example that disables all the constraints in a table


--disable all the constraints for Customer table 
--do something 
--disable all the constraints for Customer table 

Here is the one which disable /enable  a particular constraint

--disable the foreign key constraint FK_customer_countryId constraint 
--do something
--enable the FK_customer_countryId constraint 
ALTER TABLE Customer CHECK CONSTRAINT FK_customer_countryId


Once you disable/enable a constraint, make sure to enable it after you are done with your BULK operation

Wednesday, March 14, 2012

Script to find the size of an Index

Simple query to find the size of  the indexes


	OBJECT_NAME(i.OBJECT_ID) AS TableName, AS IndexName,
	i.index_id AS IndexID,
	8 * SUM(a.used_pages)/1024 AS 'IndexSize in KB'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE i.object_id = object_id ('yourTableName')--- put the table name here
GROUP BY i.OBJECT_ID,i.index_id,

Tuesday, March 6, 2012

SQL Server 2012 Virtual launch event

Finally.. the most anticipated version of sql server is ready for launch. Just few more hours..want to register for the virtual launch, click the below link

Experience the excitement of the SQL Server 2012 Virtual Launch Event

Tuesday, February 21, 2012

ACID properties of transactions

In the context of transaction processing, the acronym ACID refers to the four key properties of a transaction: atomicity, consistency, isolation, and durability.

All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are.
For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.
Data is in a consistent state when a transaction starts and when it ends.
For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.
The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized.
For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.
After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure.
For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed.

Saturday, February 11, 2012

How to get a history of SQL scripts that have been run on a DB

If you don’t have any sort of auditing mechanism enabled, its really hard to find get these information. But you may get that info from the cached plan. p

first step is to make sure that “optimize for ad hoc workloads” option is disabled on the sql server.

EXEC sp_CONFIGURE 'show advanced options',1
EXEC sp_CONFIGURE 'optimize for ad hoc workloads'

if the configured value is 1, then you may find those result using the following query which uses one of my favorite dmvs

SELECT  dest.text,deqs.last_execution_time
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

remember it is not guaranteed to return a results; the dmvs stores the data from the last sql server service start.

Thursday, February 2, 2012

Find all SQL Objects with QUOTED_IDENTIFIER set to OFF

Whenever you create or recreate an SP, make sure QUOTED_IDENTIFIER is set to ON. The default value of OFF is there for backward compatibility only. Many modern features of SQL Server (such as filtered indexes, computed persisted columns, xml columns ) require Quoted Identifiers enabled.

Here’s is the script to list all objects with QUOTED_IDENTIFIER set to OFF

  1: SELECT  SCHEMA_NAME(s.[schema_id]) + '.' + AS name
  2:        ,s.create_date
  3:        ,s.modify_date
  4:        ,OBJECTPROPERTY(s.[object_id], 'ExecIsQuotedIdentOn') AS IsQuotedIdentOn
  5:        ,s.type_desc AS object_type
  6: FROM    sys.objects s
  7: WHERE   s.type IN ( 'P', 'TR', 'V', 'IF', 'FN', 'TF' )
  8:         AND OBJECTPROPERTY(s.[object_id], 'ExecIsQuotedIdentOn') = 0
  9: ORDER BY SCHEMA_NAME(s.[schema_id]) + '.' + DESC 

Jobs failing due to "incorrect settings: QUOTED_IDENTIFIER"

Did you ever noticed after adding a filtered index or a Computed column, jobs/stored procedures that were using these tables suddenly started failing with the following error

Executed as user: XXXXX\XXXXXX. DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed.

The work around of this is to add SET QUOTED_IDENTIFIER ON to each job step.

Thursday, January 19, 2012

Indexes and Constraints on a Partitioned Table

In a partitioned table, the partition column must be a part of:

  •     The clustered index key.
  •     The primary key.
  •     Unique index and uniqueness constraint keys.

The partition column must be part of the clustered index key. This makes sense because one of the purposes of a clustered index is to physically organize the pages of a table, and partitioning affects the physical structure of a table as well. SQL Server will internally enforce that the partition column is part of the clustered key, when the table is placed on the partition scheme and the clustered index is created.

The partition column must also be part of the primary key of the partitioned table, if one is declared, whether the primary key is clustered or no clustered. A primary key has an underlying index to enforce uniqueness. You can place the partitioned column after the original primary key columns.

Any unique index must also have the partition column as part of its key, so that SQL Server can enforce uniqueness across the entire set of partitions. Therefore any uniqueness constraint must also have the partition column as part of its key. If your unique index or constraint cannot contain the partitioned column, you can enforce the uniqueness using a DML trigger.

For secondary indexes that are not unique or clustered, the requirements are relaxed somewhat. Still, the benefits of including the partition column in a secondary index can be significant. When secondary indexes have the partition column as a part of their key, and use the same or equivalent partition scheme, the indexes are partitioned and are said to be aligned with the underlying object (heap or clustered index). SQL Server automatically adds the partition column to a secondary nonunique index as an included column if the CREATE INDEX statement does not already contain the partition column.

A secondary index does not have to use the same partition function as the underlying partitioned table to achieve index alignment, as long as the partition function used by each has the same characteristics (equivalent data types of the partition column, number and values of the boundary values, and range direction.) However, it is much more convenient to use the same partition function and partition scheme for the indexes and the underlying partitioned table.

Index alignment helps in achieving partition elimination, where the query processor can eliminate inapplicable partitions from a query plan to access just the partitions required by the query. Index alignment is also required for using the SWITCH statement, which we’ll cover in the next section. If you have a nonaligned secondary index on a table and need to use the SWITCH option, you can always disable the index during the switch process and re-enable it when done.

More details :

What’s new on SQL SERVER 2008 SP3

Microsoft SQL Server Sustained Engineering group is pleased to announce the release of SQL Server 2008 Service Pack 3. Both the Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. As part of our continued commitment to software excellence for our customers, this upgrade is free and doesn’t require an additional service contract.

SQL Server 2008 SP3 contains fixes to issues that have been reported through our customer feedback platforms and Hotfix solution provided in SQL Server 2008 SP2 Cumulative Update 1 thru to Cumulative Update 4.  Service Pack 3 also includes supportability enhancements and issues that have been reported through Windows Error Reporting system.

A few customers requested updates in Microsoft SQL Server 2008 SP3 are:

  • Enhanced upgrade experience from previous versions of SQL Server to SQL Server 2008 SP3. In addition, we have increased the performance & reliability of the setup experience.
  • In SQL Server Integration Services logs will now show the total number of rows sent in Data Flows.
  • Enhanced warning messages when creating the maintenance plan if the Shrink Database option is enabled.
  • Resolving database issue with transparent data encryption enabled and making it available even if certificate is dropped.
  • Optimized query outcomes when indexed Spatial Data Type column is referenced by DTA (Database Tuning Advisor).
  • Superior user experience with Sequence Functions (e.g Row_Numbers()) in a Parallel execution plan.

To obtain SQL Server 2008 SP3 with its improved security and supportability please visit the links below: