Microsoft SQL Server

Welcome to SansSQL...

segu.sandesh@gmail.com (Sandesh Segu) — Sun, 27 Sep 2009 08:25:28 PDT

SansSQL is an SQL server community which is dedicated for discussions on SQL Server related topics, tips and tricks. In this Blog we will also be discussing about Business Intelligence and Data Warehousing too. For further questions, suggestions, feedback or topics that you would like to post on this blog, feel free to contact me @

Prevent Saving Changes in SQL 2008

segu.sandesh@gmail.com (Sandesh Segu) — Sat, 07 Nov 2009 07:15:05 PST

Prevent Saving Changes is a new option that is introduced in SQL Server 2008. This option helps in preventing the unexpected changes that can happen on a table which includes dropping and re-creating the table.

By default, Prevent Saving Changes Option will set to ON in SQL Server 2008.

When you change a table so that you alter the metadata structure of the table, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table. If you enable the Prevent saving changes that require the table re-creation option in the Designer section of the SQL Server Management Studio (SSMS) Options window, you receive the below error message

“Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.”

Demo:
Create a table named Tbl_PreventSavingChanges
1. Expand the Database AdventureWorks.
2. Right-Click on the Tables and select New Table

3. Save the Table

Now change the data type of the column ChangingColumn from nchar(10) to nvarchar(10)

And click on save button and notice an error popping out

Now to overcome this error, first click on cancel button and come out of the error message.

1. Go to "Options" under the "Tools" menu

2. In the options, expand “Designers” and select “Table and Database Designers”

3. Now, Uncheck the option “Prevent saving Changes that require table re-creation”

4. Click OK

5. Save the table. Now you will be able to save the table.

This article is also available in pdf format for downloading.

Please Click here to get your copy.

Blocked Process Report in SQL 2005 and SQL 2008

segu.sandesh@gmail.com (Sandesh Segu) — Sat, 07 Nov 2009 07:17:16 PST

Blocked Process Report is a new concept that was introduced in SQL server 2005 onwards. It is an enhancement added to the SQL server profiler.

The Blocked Process Report event class in SQL Server profiler indicates that a task has been blocked for more than a specified amount of time. This event class does not include system tasks or tasks that are waiting on non deadlock-detectable resources.

How to Use:-
To configure the threshold value for Blocked Process Report, use sp_configure.
At first we have to enable the advanced configuration options.
This can be done using the below query

EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE
GO

Then configure the Blocked Process Threshold value. This value will be in seconds. The below query sets the “Blocked Process Threshold” value to 5 seconds.
By default the Threshold value will be 0.

EXEC sp_configure 'blocked process threshold',5
GO
RECONFIGURE
GO

To generate the report, use profiler.

1. Start the Profiler
2. Connect to the server
3. In the Trace Properties, go to Event Selection tab and check the option “Show all events“

4. In the events column, expand the “Errors and Warnings” group and select the event “Blocked Process Report”

5. Click on run.

6. Now to test the setup, go to SQL Query Editor and run the below query.

USE AdventureWorks
GO
BEGIN TRAN
UPDATE HumanResources.Shift SET Name='NewShift' WHERE Name='Night'

7. Now go to a new session or open a new SQL Query Editor and run the below Query.

SELECT * FROM HumanResources.Shift

Since the Begin Tran from the step 6 is still not got the COMMIT or ROLLBACK, this command will be blocked until the COMMIT or ROLLBACK is executed

8. Now go to the profiler and notice that you should have got an event by name “Blocked Process Report” in RED Color.

9. Now click on the Blocked Process Report event to get the detailed Message / Report.

10. In the report it shows 2 sections, one is for the Blocked Process and the other one is for the Blocking Process.
11. Now Execute the below command to release the locks.
ROLLBACK

This article is also available in pdf format for downloading.
Please Click here to get your copy.

Central Management Server

segu.sandesh@gmail.com (Sandesh Segu) — Sat, 07 Nov 2009 07:24:00 PST

Introduction:
A SQL Server CMS is just a central repository that holds a list of managed servers. SQL Server 2008 SSMS introduces a new feature, Multiple Server Query Execution, in Query Editor. Centralize the management and administration of a number of SQL Server instances from a single source can allow the DBA to save significant time and effort. This feature intends to increase the productivity of running same query against multiple servers at once. Some of usage includes:
• Configure group of servers
• Generate report or document from multiple servers
• Analyze result from multiple servers
• Run any SQL query against multiple servers

Pre-requisite:
1. SQL Server 2008 for Registering Central Management Server
2. SQL Server 2008 or SQL Server 2005 or SQL Server 2000 servers other than the registered Central Management Server.

To deploy or test Multiple Server Query Execution, you need to setup central management server in SSMS in SQL Server 2008.

How to:
To register a central management server and run the query against all the registered servers follow the below steps.

1. Open the “Registered Servers” from the “View” Menu in the management studio of SQL server 2008.

2. Right click on the Central Management Servers and select “Register Central Management Server”

3. Then register the SQL 2008 server.

4. Create the sub folders and register the required servers.

5. To run a query against all the servers, right click on the central management server and select “New Query”

6. In the Query editor, type your query and execute it.

Select SERVERPROPERTY('ProductVersion') AS 'Version',

SERVERPROPERTY('ProductLevel') AS 'Level',

SERVERPROPERTY('Edition') AS 'Edition'

7. In the results, you can notice that for each record the respective server name will be displayed.

This article is also available in pdf format for downloading.
Please Click here to get your copy now.

DBCC DBREINDEX in next versions

segu.sandesh@gmail.com (Sandesh Segu) — Sun, 11 Oct 2009 09:32:02 PDT

DBCC DBREINDEX rebuilds one or more indexes for a table in the specified database.
This was the command which we used to rebuild the indexes.
This feature will be removed in the next version (after 10.0 or SQL Server 2008) of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Alter INDEX command to reorganize or rebuild indexes instead.
Below query would reorganize the table if table fragementation is between 10.0 to 30.0 and Rebuild the index if table fragementation is above 30.0

use DB_name
go
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
SELECT
   object_id AS objectid,
   index_id AS indexid,
   partition_number AS partitionnum,
   avg_fragmentation_in_percent AS frag
INTO #Temp
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
DECLARE partitions CURSOR FOR SELECT * FROM #Temp

OPEN partitions

WHILE (1=1)

BEGIN

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0 BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname =QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

IF @frag < 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' +@schemaname + N'.' + @objectname + N' REORGANIZE';

IF @frag >= 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' +@schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1

SET @command = @command + N' PARTITION=' +CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

PRINT N'Executed: ' + @command;

END;

CLOSE partitions;

DEALLOCATE partitions;

DROP TABLE #Temp

Note: Rebuilding a clustered index does not rebuild associated non-clustered indexes unless the keyword ALL is specified

When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.

USE DB_name;

ALTER INDEX ALL ON schema.table_name

REBUILD WITH (SORT_IN_TEMPDB = ON,

STATISTICS_NORECOMPUTE = ON);

This piece of information was shared to me by Kumaravyas.

Thanks to Kumaravyas for sharing this useful info.

Search for a Column in all databases

segu.sandesh@gmail.com (Sandesh Segu) — Wed, 30 Sep 2009 07:32:24 PDT

Here is a Stored Procedure which scans all your databases for the Column which you are searching for.
It might be easy to find a Column in a server which has less databases, but it is a bit difficult when there are more number of databases and it is also time consuming to do it manually. So to avoid the manual job, use the below stored procedure FindColumn. This will search for the given Column in all databases and provides the result with the schema name, Table Name and the database name in which it is present.
This Code is tested for SQL Server 2005 and SQL server 2008.

Code:
Create Proc FindColumn
@ColumnName nVarchar(50)
As
/*
Purpose : Search a Column in all databases
Author : Sandesh Segu
Date : 17th July 2009
Version : 1.0
More Scripts : http://sanssql.blogspot.com
*/

Create Table #temp (DatabaseName varchar(50),SchemaName varchar(50),TableName varchar(50),ColumnName varchar(50))

Declare @SQL Varchar(500)
Set @SQL='Use [?] ;
insert into #temp
Select ''?'' AS DatabaseName ,SS.Name as SchemaName ,ST.Name AS TableName ,SC.Name AS ColumnName from sys.tables ST ,sys.columns SC ,sys.schemas SS where SC.object_id=ST.object_id and ST.schema_id=SS.schema_id and SC.name like '''+@ColumnName+''''

EXEC sp_msforeachdb @SQL

Select * from #temp

Drop table #temp
GO

/*
Usage: If the exact table name is known then specify the table name else include the wild cards
*/
EXEC FindColumn 'EmployeeID'
EXEC FindTable '%Employee%'

Search for a Table in all databases

segu.sandesh@gmail.com (Sandesh Segu) — Wed, 30 Sep 2009 07:30:46 PDT

Here is a Stored Procedure which scans all your databases for the table which you are searching for.
It might be easy to find a table in a server which has less databases, but it is a bit difficult when there are more number of databases and it is also time consuming to do it manually. So to avoid the manual job, use the below stored procedure FindTable. This will search for the given table in all databases and provides the result with the schema name and the database name in which it is present.
This Code is tested for SQL Server 2005 and SQL server 2008.

Code:
Create Proc FindTable
@TableName nVarchar(50)
As
/*
Purpose : Search for a Table in all databases
Author : Sandesh Segu
Date : 17th July 2009
Version : 1.0
More Scripts : http://sanssql.blogspot.com
*/
Create Table #temp (DatabaseName varchar(50),SchemaName varchar(50),TableName varchar(50))

Declare @SQL Varchar(500)
Set @SQL='Use [?] ;
if exists(Select name from sys.tables where name like '''+@TableName+''')
insert into #temp
Select ''?'' AS DatabaseName ,SS.Name AS SchemaName ,ST.Name AS TableName from sys.tables as ST , sys.schemas SS
where ST.Schema_ID=SS.Schema_ID and ST.name like '''+@TableName+''''

EXEC sp_msforeachdb @SQL

Select * from #temp

Drop table #temp
GO

/*
Usage: If the exact table name is known then specify the table name else include the wild cards
*/
EXEC FindTable 'Employee'
EXEC FindTable '%Employee%'

Record Count Utility

segu.sandesh@gmail.com (Sandesh Segu) — Fri, 25 Sep 2009 22:47:13 PDT

Hello Guys,

I have been working from past three days on developing a tool to get the record count of all the tabes in a particular database to an excel sheet directly.
Here is the tool that i have developed. It has got options to select the version of SQL server and get the count.

To download this utility click on the below links.

This tool is available in Excel 2003 format and Excel 2007 format also.

To Download this utility in Excel 2003 format, Click on the below link / Image.

File Name: Get Record Count_V2.0_2003.xls

To Download this utility in Excel 2007 format, Click on the below link / Image.

File Name: Get Record Count_V2.0_2003.xlsm

Test this utility and provide your feedback and suggestions for improvement.

Please send your feedbacks and suggestions to segu.sandesh@gmail.com

What is Statistics in SQL Server

segu.sandesh@gmail.com (Sandesh Segu) — Thu, 17 Sep 2009 07:20:22 PDT

Statistics in SQL Server refers specifically to information that the server collects about the distribution of data in columns and indexes. These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data. Good statistics allow the optimizer to accurately assess the cost of different query plans, and choose a high-quality plan.
This feature is Set to ON by default in SQL Server 2000 onwards i.e. in SQL Server 2000, SQL Server 2005 and SQL Server 2008.

The automatic statistics creation function can be disabled at the database level by executing
ALTER DATABASE DBName SET AUTO_CREATE_STATISTICS OFF

Create Statistics:
Syntax:
CREATE STATISTICS statistics_name
ON { table | view } ( column [ ,...n ] )
         [ WITH
                 [ [ FULLSCAN
                       | SAMPLE number { PERCENT | ROWS } ] [ , ] ]
                 [ NORECOMPUTE ]
         ]

Example:
This example creates the City statistics group (collection), which calculates random sampling statistics on
five percent of the AddressLine1 and City columns of the Address table.
Use AdventureWorks
Go
CREATE STATISTICS City
ON Person.Address (AddressLine1, City)
WITH SAMPLE 5 PERCENT
Go

Drop Statistics:
Syntax:
DROP STATISTICS table.statistics_name | view.statistics_name
Example:
Use AdventureWorks
Go
DROP STATISTICS Person.Address.City

Update Statistics:
Syntax:
UPDATE STATISTICS table | view
           [
                   index
                   | ( statistics_name [ ,...n ] )
           ]
           [       WITH
                          [
                               [ FULLSCAN ]
                               | SAMPLE number { PERCENT | ROWS } ]
                               |RESAMPLE
                          ]
                          [ [ , ] [ ALL | COLUMNS | INDEX ]
                          [ [ , ] NORECOMPUTE ]
           ]

Example:
Use AdventureWorks
Go
UPDATE STATISTICS Person.Address(City)
       WITH FULLSCAN, NORECOMPUTE
Go

sp_updatestats:
Runs UPDATE STATISTICS against all user-defined tables in the current database.

Example:
Use AdventureWorks
Go
EXEC sp_updatestats

sp_autostats:

Displays or changes the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.
Syntax:

Exec sp_autostats [ @tblname = ] 'table_name'
                [ , [ @flagc = ] 'stats_flag' ]
                [ , [ @indname = ] 'index_name' ]
Example:
This example displays the current statistics status of all indexes on the authors table.
USE AdventureWorks
Go
EXEC sp_autostats Person.Address
This example enables the automatic statistics setting for all indexes of the authors table.
USE AdventureWorks
Go
EXEC sp_autostats Person.Address, 'ON'

DBCC SHOW_Statistics:
Displays the current distribution statistics for the specified target on the specified table.

Syntax:
DBCC SHOW_STATISTICS ( table , target )

Example:
USE AdventureWorks
Go
DBCC SHOW_STATISTICS (Person.Address, City)
Go

Refrences: Books Online

Find Statistics Info for all tables

segu.sandesh@gmail.com (Sandesh Segu) — Sun, 06 Sep 2009 06:31:07 PDT

Here is the code to find Statistics info (if exists) of all the tables in a database.
This code is tested for SQL 2005 and SQL 2008.

If exists (Select * from sys.tables where name='StatsInfo')
Drop Table StatsInfo

Create Table StatsInfo
(TableName varchar(500),
ColumnName varchar(500),
StatsName varchar(500))

Exec sp_msforeachtable 'insert into StatsInfo (StatsName,ColumnName)
Exec sp_helpstats ''?'';
Update StatsInfo set TableName=''?'' where TableName is NULL'

Select * from StatsInfo

Find row count of all the tables in a Database

segu.sandesh@gmail.com (Sandesh Segu) — Wed, 16 Sep 2009 02:47:08 PDT

Here is a quick and easy way to find the row count of all the tables in a database.
Before getting the row count, you have to update the usage of your database.

/*This Query works for SQL 2000, SQL 2005 and SQL 2008*/

DBCC UPDATEUSAGE('Your DB Name') WITH COUNT_ROWS
GO
Select object_name(id) as [Table Name],rowcnt as [RowCount] from sysindexes
where indid < 2 AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
Order by object_name(id)

/*This Query works for SQL 2005 and SQL 2008 as it uses DMV*/

DBCC UPDATEUSAGE('Your DB Name') WITH COUNT_ROWS
GO
Select Distinct Object_Name(object_id) as [Table Name], row_count as [RowCount] from sys.dm_db_partition_stats where OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
Order by Object_Name(object_id)

Finding Identity Key Columns in SQL Server 2005

segu.sandesh@gmail.com (Sandesh Segu) — Tue, 26 May 2009 10:41:14 PDT

To find the Identity Key Cloumns in a particular database

SELECT Object_Name(Object_ID) AS TableName,
Name AS ColumnName,
Seed_Value AS SeedValue,
Increment_Value AS IncrementValue,
ident_current(Object_Name(Object_ID)) AS CurrentValue,
Last_Value AS LastValue
FROM sys.identity_columns
Order by TableName

To find the Identity Key Cloumns in all databases

EXEC sp_msforeachdb 'Use ?

SELECT ''?'' AS DatabaseName, Object_Name(Object_ID) AS TableName,
name AS ColumnName,
Seed_Value AS SeedValue,
Increment_Value AS IncrementValue,
ident_current(Object_Name(Object_ID)) AS CurrentValue,
Last_Value AS LastValue
FROM sys.identity_columns
Order by TableName'

Find out who has changed what

segu.sandesh@gmail.com (Sandesh Segu) — Sun, 24 May 2009 07:04:25 PDT

There are many cases where the objects in a database gets changed without any information to the admins. This may be accidenatal or ----- :) .
So if your server instance has the default trance enabled, then you can find out who has changed what in you databases.

1. This Query gives the trace flie path.
    SELECT * FROM ::fn_trace_getinfo(0)
2. Execute the Below Query to get the data from trace file. The filters can also be applied to the below query to get the exact data
    SELECT * FROM ::fn_trace_gettable (':\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_59.trc', default)
3. Map the trace table to find what type of event has happened.
    SELECT TE.name, T.*

    FROM dbo.temp T -- table that contains the trace results
    JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
4. To get the list of possible events
    SELECT * FROM sys.trace_events where name like '%alter%' ORDER BY trace_event_id

Find the size of all databases at once

segu.sandesh@gmail.com (Sandesh Segu) — Sun, 24 May 2009 07:05:37 PDT

You may run into cases where you have to find the size of all the databases in a server in less time...
This will be easy and quick when you have less databases on the box.
What happens if the box has more number of databases??? Here is a quick solution for it...

Run the below Query and get your results in less time and in one shot.

EXEC sp_msforeachdb 'Use ?

Declare @dbsize float
Declare @logsize float
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles
select ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2) + '' MB'') AS [Size of ?]'

Finding Cluster Nodes or Cluster Name

segu.sandesh@gmail.com (Sandesh Segu) — Sun, 24 May 2009 07:06:22 PDT

Here is a Query to find the Cluster Nodes or Cluster Name using SQL server 2005.

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

This helps in finding which node the instance is currently running.

Juat for Laughs :) :) :) Marriage invitation in SQL Server Stored Procedure Style

segu.sandesh@gmail.com (Sandesh Segu) — Mon, 26 Jan 2009 04:52:35 PST

CREATE PROCEDURE MyMarriage
@BrideGroom Char(NotBad),
@Bride Char(Good)
AS

SELECT Bride FROM
BridesList
WHERE
FatherInLaw = 'Millionaire'
AND CarCount > 2
AND HouseStatus ='TwoStoreyed'
AND BrideEduStatus='PG_or_Above'
AND HavingBrothers='NO'
AND HavingSisters ='No'
AND AllowRelocate ='YES'

SELECT Gold ,Cash,Car,BankBalance FROM FatherInLaw

UPDATE MyBankAccout SET MyBal = MyBal + FatherinLawBal
UPDATE MyLocker SET MyLockerContents = MyLockerContents + FatherinLawGold

INSERT INTO MyCarShed VALUES ('Ford')

@@CONNECTIONS and @@MAX_CONNECTIONS

segu.sandesh@gmail.com (Sandesh Segu) — Mon, 29 Dec 2008 05:10:06 PST

@@CONNECTIONS returns the number of attempted connections, either successful or unsuccessful since SQL Server was last started.Connections are different from users. For example, Applications can open multiple connections to SQL Server without the user observing the connections.

Syntax: - Select @@CONNECTIONS

@@MAX_CONNECTIONS is the maximum number of connections allowed simultaneously to the SQL server. The number returned is not necessarily the number currently configured. The actual number of user connections allowed also depends on the version of SQL Server installed and the limitations of your applications and hardware.

To Change the max connections, Use sp_configure

Syntax: - Select @@MAX_CONNECTIONS

@@CONNECTIONS is incremented with each login attempt, therefore @@CONNECTIONS can be greater than @@MAX_CONNECTIONS.

Example: -

SELECT GETDATE() AS 'Current Date and Time',

@@CONNECTIONS AS 'Login Attempts',

@@MAX_CONNECTIONS AS 'Max Connections Allowed'

Code Names of SQL server releases

segu.sandesh@gmail.com (Sandesh Segu) — Sun, 28 Dec 2008 04:34:51 PST

SQL Server Release Project Code Name

SQL Server 2008 Katmai
SQL Server 2005 Yukon
SQL Server 2000 64 bit Liberty
SQL Server 2000 32 bit Shiloh
SQL Server 7.0 Sphinx
SQL Server 6.5 Hydra
SQL Server 6.0 SQL95

Add a logo to the Report Manager

segu.sandesh@gmail.com (Sandesh Segu) — Wed, 10 Dec 2008 05:07:48 PST

Everyone wants to put their custom logo to the Report Manager. Here is how it can be achieved.

You just need to change the below code for .msrs-uppertitle in the ReportingServices.css file which is located at
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles

Note : Before doing this please backup the ReportingServices.css file for safety.

Put the below code under .msrs-uppertitle and you will be able to see your custom logo on the report Manager.

.msrs-uppertitle
{
BACKGROUND: url(Image Location) no-repeat;
HEIGHT: 35px;
WIDTH: 120px;
TEXT-INDENT: -5000px;
}

Finding Restore and Backup dates

segu.sandesh@gmail.com (Sandesh Segu) — Wed, 10 Dec 2008 04:47:32 PST

/*Works for both SQL 2000 and SQL 2005 */

USE msdb

GO

SELECT
destination_database_name AS DBRestored ,
restore_date AS RestoreDate ,
RH.USER_NAME AS RestoredBY,
BS.name AS BackupName,
BS.USER_NAME AS DBBackedUpBY,
BS.server_name SourceServerName,
BackupType= CASE
                       when BS.type='D' Then 'Database '
                       when BS.type='I'   Then 'Differential database'
                       when BS.type='L' Then 'Log'
                       when BS.type='F'  Then 'File or filegroup'
                       when BS.type='G' Then 'Differential file'
                       when BS.type='P' Then 'Partial '
                       when BS.type='Q' Then 'Differential partial '
END,
BS.database_name AS SourceDB,
physical_name AS SourceFile,
backup_start_date AS BackupDate
FROM RestoreHistory RH
INNER JOIN BackupSet BS
ON RH.backup_set_id = BS.backup_set_id
INNER JOIN BackupFile BKF
ON BKF.backup_set_id = BS.backup_set_id
WHERE destination_database_name='pubs'
ORDER BY RestoreDate

Query to Find time remaining to complete database backup or restore in SQL 2005

segu.sandesh@gmail.com (Sandesh Segu) — Tue, 18 Nov 2008 01:30:13 PST

USE master
GO

SELECT
          Percent_Complete,
          Start_Time ,
          Command,
          b.Name AS DatabaseName, --Sometimes this will be "Main" as the database will not be accesiable.
          DATEADD(ms,estimated_completion_time,GETDATE()) AS RemainTime,
          (estimated_completion_time/1000/60) AS MinutesToFinish
FROM sys.dm_exec_requests a
          INNER JOIN sys.databases b
          ON a.database_id = b.database_id
WHERE
          Command like '%Restore%'
          OR Command like '%Backup%'
          AND Estimated_Completion_Time > 0

When was my table last scaned or updated?

segu.sandesh@gmail.com (Sandesh Segu) — Tue, 18 Nov 2008 01:30:56 PST

USE DatabaseName
GO

SELECT
         t.name,
         i.last_user_lookup,
         i.last_user_scan,
         i.last_user_seek,
         i.last_user_update
FROM sys.dm_db_index_usage_stats i
         INNER JOIN sys.tables t
         ON i.object_id = t.object_id
WHERE
         database_id = db_id( 'DatabaseName' )

Find Domain Name Using T-SQL

segu.sandesh@gmail.com (Sandesh Segu) — Sun, 16 Nov 2008 21:24:58 PST

DECLARE @Domain varchar(100), @key varchar(100)

SET @key = 'SYSTEM\ControlSet001\Services\Tcpip\Parameters\'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@key,@value_name='Domain',@value=@Domain OUTPUT
SELECT 'Server Name: '+@@servername + ' Domain Name:'+convert(varchar(100),@Domain)

Which SQL Statements are Currently Executing on my Database?

segu.sandesh@gmail.com (Sandesh Segu) — Wed, 12 Nov 2008 23:12:51 PST

SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text, er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
WHERE session_Id > 50 -- This is to ignore system SPID's.
AND session_Id NOT IN (@@SPID) -- This is to ignore this current Statement/Session.

Issue with renaming the objects using Management Studio 2005

segu.sandesh@gmail.com (Sandesh Segu) — Wed, 15 Oct 2008 06:14:02 PDT

Whenever we rename an object like stored procedure, function, view or trigger using the management studio in SQL server 2005, the syscomments table will not be updated. Hence when we try to get the text/code of the renamed object using the sp_helptext or OBJECT_DEFINITION () we will get the text/code of the object with the old name itself.

Try it yourself:

Use AdventureWorks
Go
CREATE PROC TestRename AS
Print ‘XYZ’
GO

Now, rename this SP using the management studio object list to “RenameTest”

Now Execute the below Query on AdventureWorks Database.

sp_helptext RenameTest

Now if you observer the result pane, the result will be the same statement with the procedure name still not changed even after renaming the SP using the management studio. The result will be as shown below.

CREATE PROC TestRename AS
Print 'XYZ'