Exists Function - MDX Query

In MDX query is we want to find that particular set or tuples exists in the given second set ,we can use this function
If we provide optional measure group then it will also check the tuples has non NULL data in that measure group

How to delete job history - sp_purge_jobhistory

sp_purge_jobhistory
We can use this procedure to delete old history for jobs.

Troubleshooting Database Mail

Here I am giving some step to troubleshoot Database Mail related problems

Cant find sp_send_dbmail procedure
This procedure is in MSDB database.

sp_addlinkedsrvlogin - To modify or add new login on linked server

sp_addlinkedsrvlogin

To modify or add new login for linked server

Trigger for new Database

I got a mail asking for trigger on new Database

Hi.
I need to implement a trigger to email me when someone creates a database on a server without letting me know.I dont know how to write the trigger.Can you help please?
Thanks
Rezwan

How to find Index information - sp_msindexspace,sp_helpindex,sp_mshelpindex

Index are very important part for any database.
We need to regularly monitor index and maintain index for better performance of the system.
I have already written an article on how to find highly used index

sp_changesubscription for changing subscriber password

For DBA its a routine activity to change server passwords.
If we are using replication between servers and if we are changing password of subscribers
we have to change it on publisher also.

we can use sp_changesubscription here to change password for the subscriber.

We can use it for changes the properties of a snapshot or transactional push subscription or a pull subscription involved in queued updating transactional replication

set context_info -- Option to get parameter value during session across multiple batches

/*some time we need to use parameter values to be available for multiple bathces in same session
We can use context_info this case. Its value is available during current session and we can get its info from
sys.dm_exec_requests
sys.dm_exec_sessions
sys.sysprocesses

*/

Maintenance :- Job History (sp_delete_backuphistory)

/*
Recently for one of my client I found that his backuphistory table has become very large. Since SQL Server do not clear backupjob history we need to clear it regular interval
*/

MDX:-Total of last N Days from Today.

Since I dont have forums on this blog.I would like to post the problems by mail using blogs.
Recently I got a problem to get sum of last N days from current Date.

Table Partitions

Table Partition is a new feature in SQL Server 2005.
Here I am posting a sample code how to create a Partitioned table and view its meta data in system tables.
/* We need to create a partition scheme and function on which we will partition the data */

SQL 2008: Trace process in activity monitor

In SQL Server 2008 when we open activity monitor we can see SPID and information related for that SPID . But what if we want to get detail information for that SPID.

Alter table rebuild option for data_compression

For table which one is stored as a heap we can now reclaim free space from this tables using alter table rebuild option.
lets see

Trigger on system tables

Lets say for example
If someone disable a job and forgot to inform other team members

Whats new SQL Server Express 2008

We all know SQL Server Express 2008 is coming with many new features.
we can get some cool information for this from here

http://blogs.msdn.com/sqlexpress/archive/2008/06/10/sql-server-2008-express-rc0.aspx

Cheers

script for insert data sql statement

Some times we need insert into select statement for tables from one database to other database. Here is the script which will create the script for this process.

How to get size info for database file using dbcc showfilestats

CREATE TABLE freefilespace

    (

         fileid       INT,

         filegroup    INT,

         totalextents INT,

         usedextents  INT,

         dbname       VARCHAR(256),

         filename     VARCHAR(2000)

    )

Undocumented Commands

Note:-All the samples here are run against AdventureWorks sample DB

DBCC proccache

Provides procuedure cache information

lets play with XML

Here is Nested XML which contains data from 2 tables ,Customer and Order
I want to get data for this 2 tables from XML

How to restore MSDB Database

How to restore MSDB database

New features in Named Sets in Analysis Service 2008

One of the important feature in SQL Server 2008 is Dynamic Named Sets. Till SQL Server 2005 named sets were static. In SQL Server 2005 once named sets are executed they were remain static during whole session or query or cube. This was giving wrong results during Topcount or Bottomcount like functions.

/* How to get linked values in one group using CTE*/ create table table_a (col1 varchar(100), col2 varchar(100)) go

Session propery SQL Server 2005

In sql server we can get session property many ways
1. dbcc useroptionsdbcc useroptions
This will give us information about all the enabled setting for that session.
In SQL Server 2005 two new options are available
2. sessionproperty function