Activity Monitor Auto Refresh

View refresh settings on the left side pane of the Activity Monitor dialog , will give u a window to setup a auto refresh period for the Activity Monitor. Be aware that it is not recommended on a production server.

Reuse of Schedule in SQL 2005

In previous versions SQL Server, the job schedule was tightly coupled to a specific job and could never be reused. In SQL Server 2005, a user can create a schedule and then link multiple jobs to it. However, other users cannot link to a shared job schedule for security reasons.

Remove Secondary Transaction Log File from Database

At time your database may have multiple Transaction log physical files and you may want to remove one of the Transaction log file from the database. Then run the following script

DBCC SHRINKFILE (,EMPTYFILE)
alter database test remove file

Eg.
DBCC SHRINKFILE ('test1_log',EMPTYFILE)
alter database test remove file Test1_log

Note : The primary data file and log file cannot be removed.

Dedicated Administrator Connection (DAC)

SQL Server 2005 provides a special diagnostic connection for administrators when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.
Only members of the SQL Server sysadmin role can connect using the DAC.
The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A). You can also connect prefixing admin: to the instance name in the format sqlcmd -Sadmin:. You can also initiate a DAC from a SQL Server Management Studio Query Editor by connecting to admin:.

SQL 2005 Online Index (Only Available in Enterprise Edition)

Index operations can now be performed online; users can still access the table data and use other indexes on the table while one index is being created, altered, or dropped. In SQL Server 2005 EE , you can create, rebuild, or drop indexes online. The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations.
Eg.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD WITH (ONLINE = ON);

Online Restore in SQL 2005

Restoring data while the database is online is called an online restore. Online restore is supported only on SQL Server 2005 Enterprise Edition. In this edition, a file, page, or piecemeal restore is online by default. Online Restore applies to only those database which have multiple files or file groups. The database can be online when the primary filegroup is online and all other secondary filegroups are offline. By default the restore command in SQL 2005 EE is Online only.

SQL Server 2005- Piecemeal Restores

In SQL Server 2005, databases that contain multiple filegroups can be restored and recovered in stages through a process known as piecemeal restore. Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one of more secondary filegroups. After the restore sequence is completed, recovered files, if they are valid and consistent with the database, can be brought online directly Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

All editions of SQL Server 2005 support offline piecemeal restores. In SQL Server 2005 Enterprise Edition, a piecemeal restore can be either online or offline.

FAQ : Table row count from all the tables in the database

1. Simple Method using undocumented stored procedure sp_msforeachtable

CREATE TABLE #TableCount (tablename sysname, ROWCNT int)

INSERT INTO #TableCount

EXEC sp_msforeachtable 'select ''?'', count(*) from ?'

SELECT * FROM #TableCount where rowcnt>0

DROP TABLE #TableCount

Method 2 : Using SysIndexes and Sysobjects system tables

SELECT O.[name]
,I.rowcnt
FROM sysobjects O
JOIN sysindexes I
ON O.[id] = I.[id]
WHERE O.xtype = 'U'
AND O.status > 0
AND I.indid <2


Method 3. Using Cursors


Create proc spCountNoofRecords
as
Begin
SET NOCOUNT ON
create table #RecCount(TableName varchar(300),NoofRecords int)
Declare @RecCount1 int,@TableName varchar(300),
@SqlStatement nchar(2000),@ParmDefinition NVARCHAR(500)
DECLARE cRecCount CURSOR FOR
select Name from Sysobjects where xtype='u' order by name
OPEN cRecCount
FETCH NEXT FROM cRecCount INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
set @SqlStatement=N'Select @RecCount=count(*) from ['+@TableName +']'
SET @ParmDefinition = '@RecCount int OutPut'
EXECUTE sp_executesql @SqlStatement, @ParmDefinition,
@RecCount = @RecCount1 OUTPUT;
insert into #RecCount select @TableName,@RecCount1
FETCH NEXT FROM cRecCount INTO @TableName
END
CLOSE cRecCount
DEALLOCATE cRecCount
Select *From #RecCount
End

Multiple Activity Monitor dialogs

You can have multiple activity monitor in SQL Server 2005 and you can also filter on Database/Process/User/BlockType etc. It really a wonderful enhanced feature from SQL 2000. Activity Monitor provides monitoring of blocking and lock information in the same interface, with options such as filtering and auto-refresh.

RESTORE VERIFYONLY Different behavior in SQL 2000 and SQL 2005

You have a RESTORE VERIFYONLY option in both SQL 2000 and SQL 2005. But it has different behavior in SQL 2000 and SQL 2005. In 2000 what VERIFYONLY do is, it will check the media header and then returned a success or an error. The entire backup set could be invalid, and every other sector on the media could be bad. But aslong as the media header was intact, it would return a success. So this behavior effectively rendered this command worthless in SQL 2000.I would tell in sql 2000 you should not use this command. However, SQL Server2005 now performs the necessary checks, so you should execute this command every time you perform a backup.

Grant SQL Profiler Run Privilege to Login

In SQL 2000 , If you want to grant permission to run profiler to user /login , then the only way was to add that login to SysAdmin Serverrole. And no DBA would like to do that and ultimately DBA has to run profiler for this user. This was always a administrative hindrance. In SQL 2005, this long standing demand has been granted by Microsoft. Now what you can do is u can Grant Alter Trace permission to any login who want to run profiler and no need to add this login to SysAdmin Server role.

Grant Alter Trace LoginName

Significance of Tempdb in SQL Server

Tempdb is instance wide Temporary workspace that SQL Server uses for various operations. Tempdb is created from Model Database whenever the SQLSERVER service is started/restarted. Tempdb has simple recover model. It is a rule of thumb that you should never keep tempdb on the same location where your user database is stored. It is always recommended that tempdb should locate in a high performing disk subsystem (preferably RAID 10 or RAID 0). It is also to be configured with enough size for better performance, so that system no need to do the space allocation dynamically. You can also create multiple tempdb datafiles to minimize on IAM and SGAM and there by improve the performance.

Very common operation which consume tempdb are appended below :-

(a) Store explicitly created Temp tables , Table variables, Stored procedure, cursors etc.
(b) Stores Private and Global variable used throughout the instance
(c) Worktables associated with ORDER BY, GROUP BY, CURSOR
(d) Many System administration activities like DBCC commands uses TempDB
(e) If you have choose Snapshot Isolation Level then it uses Tempdb
(f) If your Report Server Uses Caching, then tempdb is used extensively
(g) If your have used SORT_IN_TEMPDB option while rebuilding indexes.

To relocate Tempdb Follow these steps

(a) Alter the file path by following command

Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
GO

(b) Restart the service and run sp_helpfile tempdb to confirm the above action

Refer :

Tempdb in Sql Server 2005 -http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Shrinking -- http://support.microsoft.com/kb/307487

Concurrency enhancements for the tempdb database --

http://support.microsoft.com/kb/328551