Find the list of server on Network

Method 1 :

Set SQLCMD mode => Use below query

!!SQLCMD -L

Method 2 :

use below query:

EXEC master..XP_CMDShell 'OSQL -L'

Moving the system Database in SQL Server

Moving the system Database in SQL Server

Below are the simple tips for moving system databases to differnet location.


Master Database =>

right-click on SQL Server in Enterprise Manager (EM) and choose Properties.
click the Startup Parameters

Replace the path for -d and -e parameter with new one

-d New patha\msater.mdf.
-l New patha\msatlog.ldf

Stop SQL Server and move the master data and log files to its new location.
start SQL Server and you have successfully moved your Master



Tempdb Database =>
open query analyzer and run the following query:

use master go
Alter database tempdb modify file (name = tempdev, filename = ‘E:Sqldata empdb.mdf’)
go
Alter database tempdb modify file (name = templog, filename = ‘E:Sqldata emplog.ldf’)
Go

Stop sql server move tempdb files to new location and delete the older one.
Restart sql server


MSDB Database =>

Right-click the SQL-Server name and click properties.
choose your startup parameters.
enter the parameter -T3608.
Restart SQL Server.
detach the database and move them to their appropriate place.
and reattach it

Checkpoint Vs LazyWriter

Difference between Checkpoint and LazyWriter

C: Flush dirty pages to Disk
L: Flush dirty pages to disk.

C: Flush only Data pages to disk
L: Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)

C: Default, Occurs approximately every 1 minute
L: Occurs depending upon memory pressure and resource availability

C: Can be managed with sp_confige -recovery interval option
L:It is lazy, Sql server manages by its own.

C: Does not check the memory pressure
L:Monitor the memory pressure and try maintain the available free memory.

C: crash recovery process will be fast to read log as data file is updated.
L: No role in recovery

C: Occurs for any DDL statement
L: Occurs per requirement

C: Occurs before Backup/Detach command
L: Occurs per requirement

C: Depends upon the configuration setting, we can control.
L: Works on Least recent used pages and removed unused plans first, no user control.

C: for simple recovery it flush the tlog file after 70% full.
L: No effect on recovery model.

C: can manually /Forcefully run command “Checkpoint”
L: No command for Lazy Writer

C: Very Less performance impact
L: No performance impact

Checkpoint can be run manullay using command "Checkpoint":

Find log space and disk utilization

DECLARE @dbname VARCHAR(200),@sql VARCHAR(8000)
SET @sql = '' SET @dbname = ''

IF OBJECT_ID('tempdb..#T_Drive') IS NOT NULL DROP TABLE #T_Drive
IF OBJECT_ID('tempdb..#T_LogSpace') IS NOT NULL DROP TABLE #T_LogSpace
IF OBJECT_ID('tempdb..#T_DBInfo') IS NOT NULL DROP TABLE #T_DBInfo
IF OBJECT_ID('tempdb..#T_FileSpace') IS NOT NULL DROP TABLE #T_FileSpace
IF OBJECT_ID('tempdb..#T_Database') IS NOT NULL DROP TABLE #T_Database

CREATE TABLE #T_Drive([DriveName] VARCHAR(5) PRIMARY KEY,[FreeDriveSpace] BIGINT)

INSERT INTO #T_Drive EXEC master..xp_fixeddrives

CREATE TABLE #T_LogSpace ([DBName] VARCHAR(200) NOT NULL PRIMARY KEY,
[LogSize] MONEY NOT NULL,[LogPercentUsed] MONEY NOT NULL,[LogStatus] INT NOT NULL)

SELECT @sql = 'DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'

INSERT INTO #T_LogSpace EXEC(@sql)

CREATE TABLE #T_DBInfo (
[DBName] VARCHAR(200),[FileLogicalName] VARCHAR(200),[FileID] INT NOT NULL,[Filename] VARCHAR(250) NOT NULL,
[Filegroup] VARCHAR(100) NOT NULL,[FileCurrentSize] BIGINT NOT NULL,[FileMaxSize] VARCHAR(50) NOT NULL,
[FileGrowth] VARCHAR(50) NOT NULL,[FileUsage] VARCHAR(50) NOT NULL,[FileGrowthSize] BIGINT NOT NULL)

CREATE TABLE #T_Database ( [DBName] VARCHAR(200) PRIMARY KEY)

INSERT INTO #T_Database
SELECT DBName = LTRIM(RTRIM(name)) FROM master.dbo.sysdatabases
WHERE category IN ('0', '1','16')AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE' ORDER BY name

CREATE TABLE #T_FileSpace ( [DBName] VARCHAR(200) NULL,[Fileid] INT NOT NULL,
[FileGroup] INT NOT NULL,[TotalExtents] MONEY NOT NULL,[UsedExtents] MONEY NOT NULL,
[FileLogicalName] sysname NOT NULL,[Filename] VARCHAR(1000) NOT NULL)

SELECT @dbname = MIN(dbname) FROM #T_Database

WHILE @dbname IS NOT NULL
BEGIN
SET @sql = 'USE ' + @dbname + '
INSERT INTO #T_DBInfo (
[DBName],[FileLogicalName],[FileID],[Filename],[Filegroup],[FileCurrentSize],
[FileMaxSize],[FileGrowth],[FileUsage],[FileGrowthSize])
SELECT DBName = ''' + @dbname + ''',FileLogicalName = SF.name,
FileID = SF.fileid, Filename = SF.filename, Filegroup = ISNULL(filegroup_name(SF.groupid),''''),
FileCurrentSize = (SF.size * 8)/1024, FileMaxSize =CASE SF.maxsize WHEN -1 THEN N''Unlimited''
ELSE CONVERT(VARCHAR(15), (CAST(SF.maxsize AS BIGINT) * 8)/1024) + N'' MB'' END,
FileGrowth = (case SF.status & 0x100000 when 0x100000 then
convert(varchar(3), SF.growth) + N'' %''
else
convert(varchar(15), ((CAST(SF.growth AS BIGINT) * 8)/1024)) + N'' MB'' end),
FileUsage = (case WHEN SF.status & 0x40 = 0x40 then ''Log'' else ''Data'' end),
FileGrowthSize = CASE SF.status & 0x100000 WHEN 0x100000 THEN
((((CAST(SF.size AS BIGINT) * 8)/1024)* SF.growth)/100) + ((CAST(SF.size AS BIGINT) * 8)/1024)
ELSE
((CAST(SF.size AS BIGINT) * 8)/1024) + ((CAST(SF.growth AS BIGINT) * 8)/1024)
END
FROM sysfiles SF
WHERE filename like ''%.ldf''
ORDER BY SF.fileid'

EXEC(@sql)

SET @sql = 'USE ' + @dbname + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS'

INSERT INTO #T_FileSpace ([Fileid],[FileGroup],[TotalExtents],[UsedExtents],[FileLogicalName],[Filename])
EXEC (@sql)

UPDATE #T_FileSpace SET [DBName] = @dbname WHERE ISNULL([DBName],'') = ''

SELECT @dbname = MIN(dbname) FROM #T_Database WHERE dbname > @dbname
END

SELECT 'DBName' = DFI.DBName,
'FileLogicalName' = DFI.FileLogicalName,
'Filename' = DFI.[Filename],
'FileMBSize' = DFI.FileCurrentSize,
'FileGrowth' = DFI.FileGrowth,
'FileMBGrowth' = DFI.FileGrowthSize,
'DriveName' = TD.DriveName,
'DriveMBEmpty' = TD.FreeDriveSpace,
'FileMBUsed' = CAST(ISNULL(((FS.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),
'FileMBEmpty' = DFI.FileCurrentSize - CAST(ISNULL(((FS.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),
'LogSpaceUsed' = LSP.LogPercentUsed
FROM #T_DBInfo DFI
LEFT OUTER JOIN #T_Drive TD
ON LEFT(LTRIM(RTRIM(DFI.[FileName])),1) = LTRIM(RTRIM(TD.DriveName))
LEFT OUTER JOIN #T_FileSpace FS
ON LTRIM(RTRIM(FS.[Filename])) = LTRIM(RTRIM(DFI.[Filename]))
LEFT OUTER JOIN #T_LogSpace LSP
ON LtRIM(RTRIM(LSP.DBName)) = LTRIM(RTRIM(DFI.DBName))
ORDER BY 1

Rebuild MSDB Database

1.Put MSSQL into single user mode

1.Click -> START -> Microsoft SQL Server 2005 -> Configuration Tools – > SQL Server Configuration Manager
2.Right click on SQL Server and choose Properties
3.Click on the Advanced tab. Under Startup Parameters you will be adding the
following parameters to the beginning of the string: -m;-c;-T3608;



2.Restart SQL Server


3.Connect to SQL server through the Management Console or through command prompt using sqlcmd.
From this point on we will be using TSQL to issue the commands so click the New Query button on the top left. At this point you should be in the master database inside the query window.

Stop reporting or integration services it might stop you to connect in single user mode.


4.Detach the MSDB database using the following commands:

use master
go
sp_detach_db ‘msdb’
go


5.We need to move (or rename, I prefer moving them) the existing MDF and LDF files for the MSDB database so that we can recreate it.


1.Usually these files are located in the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Your’s might differ.

2.Move (or rename) the MSDBDATA.mdf and MSDBLOG.ldf files.



6.Back to the Management Studio. Open up the instmsdb.sql file in a new query window. This file is usually located in the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install


7.Execute the file. If you see any errors about xp_cmdshell, just ignore them. They are common and the script will take care of it.


8.At this point you should have your MSDB database restored. The only thing left is cleanup.



9.Execute the following command to make sure that xp_cmdshell is once again set to disable for security reasons:

EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure ‘xp_cmdshell’, 0
GO
RECONFIGURE WITH OVERRIDE
GO


10.Shutdown SQL Server


11.Go back into your Startup Paremeters for your server in the SQL Server Configuration Manager and removed the -c;-m;-T3608 parameters we added earlier.


12.Restart SQL Server

Everything should be cool at this point and you’ll be able to recreate any Maintenance Plans and Jobs.

Rebuilding or Restoring The SQL Server 2008 Master Database

Rebuild master database in 2008 is different from 2005 and 2000

We don't need externale binary files or setup cd, We can find setup.exe at below location:

"C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release"


Follow the steps below to rebuild the Master Database.


1. From a command prompt window change to the following directory:

"C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release"

2. Next paste the following into the command prompt window and press “Enter”:

Setup.Exe /Action=RebuildDatabase /InstanceName=MSSQLSERVER SAPWD=sa

If master.mdf file is missing manually copy the necessary file(s) from C:\Program Files\Microsoft SQL Server\MSSQL10.ONE2008\MSSQL\Binn\Templates

In case it doesn't work restore the master database on another server and replace the master data and log files on original server and then start repairing from setup file.


Restore latest backup of master and other databases for minimum data loss.

Tablediff utility using SQLCMD

The tablediff utility is used to compare the data in two tables and generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.


/*
Tablediff command from source to destination server with windows login, will give result to DiffsTable and will create query to fix the issue in MyTable1_MyTable2_diff.sql file
*/

EG : Run in SQLCMD mode:

!!"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver ADITYA-PC\ONE2005 -sourcedatabase payroll_AAA1 -sourcetable empmst -destinationserver ADITYA-PC\ONE2008 -destinationdatabase payroll_AAA -destinationtable empmst -et DiffsTable -f F:\MyTable1_MyTable2_diff.sql

Changing Sql Server name

To change the server name first drop the old one:

EXEC SP_DropServer 'OLDNAME'


And now add new server

EXEC SP_AddServer 'NEWNAME','local'


Restart Sql Service for the changes to take place.

Memeber of setupadmin and sysadmin fixed server roles can only execute sp_addserver

Last Backup of all the databases

select b.backup_finish_date,b.backup_start_date,type,b.backup_size,b.database_name,m.physical_device_name,
datediff (day, b.backup_start_date, getdate ()) datediff
from msdb..backupset b,msdb..backupmediafamily m
where b.media_set_id= m.media_set_id AND b.backup_start_date =
(SELECT MAX (backup_start_date) FROM msdb..backupset WHERE database_name = b.database_name AND type = 'D')
ORDER BY b.database_name

Find Last statistics updates on tables

SELECT OBJECT_NAME(A.object_id) AS Object_Name, A.name AS index_name, STATS_DATE(A.OBJECT_ID, index_id) AS StatsUpdated ,
DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DaysOld
FROM sys.indexes A
INNER JOIN sys.tables B ON A.object_id = B.object_id
WHERE A.name IS NOT NULL
ORDER BY DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DESC

USE AdventureWorksGO
SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('HumanResources.Department')

sp_change_users_login

sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @LoginName = ] 'login' ]
[ , [ @Password = ] 'password' ]

This example produces a report of the users in the current database and their security identifiers.
EXEC sp_change_users_login 'Report'


--Change the user account to link with the 'Login1' login.
USE DB1
go
EXEC sp_change_users_login 'Update_One', 'OrphanUser', 'Login1'

This example shows how to use the Auto_Fix option to map an existing user to a login with the same name, or create the SQL Server login 'Login1' with the password '@a122ld' if the login Mary does not exist.
USE pubs
go
EXEC sp_change_users_login 'Auto_Fix', 'Login1' , NULL,a122ld'
go

Defaut Trace

SELECT * FROM sys.configurations WHERE configuration_id = 1568 --(check if trace option is set)
SELECT * FROM ::fn_trace_getinfo(0)

SELECT loginname, loginsid, spid, hostname, applicationname, servername, databasename, objectName,
e.category_id, cat.name AS [CategoryName], textdata, starttime, eventclass, eventsubclass,
--0=begin,1=commit e.name AS EventName FROM
::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)
INNER JOIN sys.trace_events e ON eventclass = trace_event_id INNER JOIN
sys.trace_categories AS cat ON e.category_id = cat.category_id WHERE
databasename = 'db' AND objectname IS NULL AND --filter by objectname
e.category_id = 5 AND
--category 5 is objects
e.trace_event_id = 46 --trace_event_id: 46=Create Obj,47=Drop Obj,164=Alter Obj


SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)

Search Object, Strings in database

CREATE FUNCTION [dbo].[FindStringTableDependency](@StrText varchar(500),@tableStrInd varchar(10))
RETURNS @tmpDepend TABLE (id varchar(100),Type varchar(10))
BEGIN
IF @tableStrInd <> 'T'
INSERT INTO @tmpDepend
SELECT DISTINCT name,xtype FROM sysobjects s INNER JOIN syscomments s2
ON s.id = s2.id WHERE s2.text LIKE '%'+ @strText + '%'
ELSE
INSERT INTO @tmpDepend
SELECT DISTINCT name,'T' FROM sysobjects WHERE id IN (
SELECT DISTINCT s.id FROM sysdepends s ,sysobjects o WHERE
s.depid = o.id AND o.name = '' + @StrText + '')

RETURN
END

Table size and heaviest table

-- sp_spaceused pay_detl

DECLARE @id INT
DECLARE @type character(2)
DECLARE @pages INT
DECLARE @dbname SYSNAME
DECLARE @dbsize dec(15, 0)
DECLARE @bytesperpage dec(15, 0)
DECLARE @pagesperMB dec(15, 0)

CREATE TABLE #spt_space
(
objid INT NULL,ROWS INT NULL,reserved dec(15) NULL,
DATA dec(15) NULL,indexp dec(15) NULL,unused dec(15) NULL
)

SET NOCOUNT ON

-- Create a cursor to loop through the user tables
DECLARE c_tables CURSOR
FOR
SELECT id FROM sysobjects WHERE xtype = 'U'
OPEN c_tables

FETCH NEXT FROM c_tables
INTO @id

WHILE @@fetch_status = 0
BEGIN
/* Code from sp_spaceused */
INSERT INTO #spt_space
(
objid, reserved
)
SELECT objid = @id, SUM(reserved) FROM sysindexes
WHERE indid IN (0, 1, 255) AND id = @id

SELECT @pages = SUM(dpages) FROM sysindexes
WHERE indid < 2 AND id = @id

SELECT @pages = @pages + ISNULL(SUM(USED), 0)
FROM sysindexes WHERE indid = 255 AND id = @id

UPDATE #spt_space SET DATA = @pages WHERE objid = @id


/* index: sum(used) where indid in (0, 1, 255) - data */
UPDATE #spt_space
SET indexp = (
SELECT SUM(USED) FROM sysindexes WHERE indid IN (0, 1, 255) AND id = @id
) - DATA
WHERE objid = @id

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
UPDATE #spt_space
SET unused = reserved
-(
SELECT SUM(USED) FROM sysindexes WHERE indid IN (0, 1, 255)
AND id = @id
)
WHERE objid = @id

UPDATE #spt_space SET ROWS = i.rows
FROM sysindexes i WHERE i.indid < 2
AND i.id = @id AND objid = @id

FETCH NEXT FROM c_tables
INTO @id
END

SELECT TOP 25 Table_Name = (SELECT LEFT(NAME, 25) FROM sysobjects WHERE id = objid ),
ROWS = CONVERT(CHAR(11), ROWS),reserved_KB = LTRIM(STR(reserved * d.low / 1024., 15, 0) + ' ' + 'KB'),
data_KB = LTRIM(STR(DATA * d.low / 1024., 15, 0) + ' ' + 'KB'),
index_size_KB = LTRIM(STR(indexp * d.low / 1024., 15, 0) + ' ' + 'KB'),
unused_KB = LTRIM(STR(unused * d.low / 1024., 15, 0) + ' ' + 'KB')
FROM #spt_space, MASTER.dbo.spt_values d
WHERE d.number = 1 AND d.type = 'E'
ORDER BY reserved DESC

DROP TABLE #spt_space
CLOSE c_tables
DEALLOCATE c_tables

Wait Search Query

SELECT
ws.wait_type,
ws.waiting_tasks_count,
CASE WHEN ws.waiting_tasks_count = 0 THEN 0 ELSE ws.wait_time_ms / ws.waiting_tasks_count END as average_wait_time_ms,
ws.wait_time_ms as total_wait_time_ms,
CONVERT(DECIMAL(12,2), ws.wait_time_ms * 100.0 / SUM(ws.wait_time_ms) OVER()) as wait_time_proportion,
ws.wait_time_ms - signal_wait_time_ms as total_wait_ex_signal_time_ms,
ws.max_wait_time_ms,
ws.signal_wait_time_ms as total_signal_wait_time_ms
FROM
sys.dm_os_wait_stats ws
WHERE
-- Restrict results to requests that have actually occured.
ws.waiting_tasks_count > 0
ORDER BY
ws.wait_type

Backup file detail

RESTORE HEADERONLY FROM DISK = N'E:\SOFTWARES\GnB_EnS.bak'
RESTORE FILELISTONLY FROM DISK = N'E:\SOFTWARES\GnB_EnS.bak'
RESTORE LABELONLY FROM DISK = N'E:\SOFTWARES\GnB_EnS.bak'

Backup file contents

exec xp_objectrecovery_viewcontents
@FileName = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Payroll_201004111708.bak',
@FileNumber = 1,
@Type = 'All'

Server properties

SELECT SERVERPROPERTY('BuildClrVersion') AS BuildClrVersion,
SERVERPROPERTY('Collation') AS Collation,
SERVERPROPERTY('CollationID') AS CollationID,
SERVERPROPERTY('ComparisonStyle') AS ComparisonStyle,
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS
ComputerNamePhysicalNetBIOS,
SERVERPROPERTY('Edition') AS Edition,
CASE SERVERPROPERTY('EditionID')
WHEN -1253826760 THEN 'Desktop'
WHEN -1592396055 THEN 'Express'
WHEN -1534726760 THEN 'Standard'
WHEN 1333529388 THEN 'Workgroup'
WHEN 1804890536 THEN 'Enterprise'
WHEN -323382091 THEN 'Personal'
WHEN -2117995310 THEN 'Developer'
WHEN 610778273 THEN 'Enterprise Evaluation'
WHEN 1044790755 THEN 'Windows Embedded SQL'
WHEN 4161255391 THEN 'Express with Advanced Services'
ELSE '???'
END AS EditionID,
CASE SERVERPROPERTY('EngineEdition')
WHEN 1 THEN 'Personal-Desktop'
WHEN 2 THEN 'Standard'
WHEN 3 THEN 'Enterprise'
WHEN 4 THEN 'Express'
ELSE '???'
END AS EngineEdition,
SERVERPROPERTY('InstanceName') AS InstanceName,
SERVERPROPERTY('IsClustered') AS IsClustered,
SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled,
SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsIntegratedSecurityOnly,
SERVERPROPERTY('IsSingleUser') AS IsSingleUser,
SERVERPROPERTY('LCID') AS LCID,
SERVERPROPERTY('LicenseType') AS LicenseType,
SERVERPROPERTY('MachineName') AS MachineName,
SERVERPROPERTY('NumLicenses') AS NumLicenses,
SERVERPROPERTY('ProcessID') AS ProcessID,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS
ResourceLastUpdateDateTime,
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
SERVERPROPERTY('ServerName') AS ServerName,
SERVERPROPERTY('SqlCharSet') AS SqlCharSet,
SERVERPROPERTY('SqlCharSetName') AS SqlCharSetName,
SERVERPROPERTY('SqlSortOrder') AS SqlSortOrder,
SERVERPROPERTY('SqlSortOrderName') AS SqlSortOrderName,
SERVERPROPERTY('FilestreamShareName') AS FilestreamShareName,
SERVERPROPERTY('FilestreamConfiguredLevel') AS FilestreamConfiguredLevel,
SERVERPROPERTY('FilestreamEffectiveLevel') AS FilestreamEffectiveLevel

SP_WHO@_DB Procedure

sp_who2_db 'staf'
create PROCEDURE sp_who2_db(@dbname varchar(100))
AS
BEGIN
IF object_id('tab1') IS NOT NULL
DROP TABLE tab1
CREATE TABLE tab1( i1 varchar(1000),i2 varchar(1000),i3 varchar(1000),i4 varchar(1000),
i5 varchar(1000),i6 varchar(1000),i7 varchar(1000),i8 varchar(1000),
i9 varchar(1000),i10 varchar(1000),i11 varchar(1000),
i12 varchar(1000),i13 varchar(1000))

INSERT INTO tab1
EXEC ('sp_who2 ')
SET @dbname = '%' + @dbname +'%'
SELECT * FROM tab1 WHERE i6 LIKE @dbname
END

Longest running query

select top 10 db_name(dbid),
total_worker_time/execution_count as avg_cpu_cost, plan_handle,
execution_count,
(select substring(text, statement_start_offset/2 + 1,
(case when statement_end_offset = -1
then len(convert(varchar(max), text))* 2
else statement_end_offset end – statement_start_offset)/2)
from sys.dm_exec_sql_text(sys.dm_exec_query_stats.sql_handle)) as query_text
from sys.dm_exec_query_stats join sys.sysprocesses
on sys.dm_exec_query_stats.sql_handle = sys.sysprocesses.sql_handle
order by total_worker_time desc

Parameter to query

DECLARE @g int
DECLARE @g1 nvarchar(4000)
SET @g1 = 'SELECT @g=10'
EXEC SP_EXECUTESQL @g1 ,N'@g int out',@g out
PRINT @g

Password encrypt and decrypt of a string

SELECT
encryptbyPASSphrase('key','ABC')
SELECT
CONVERT(VARCHAR(100),DecryptbyPASSphrase('key',0x01000000D5635122845D73E9C224A4B4007A61A9673055D29C966C81))
select
EncryptByPassPhrase('key', 'abc' )
result encrypted
value: 0x01000000674FB952B3F9D575EB4B9E7A22BEC4800F6310C7 F96CA821
select
convert(varchar(100),DecryptByPassPhrase('key', 0x01000000011FFAE33EA305F8DE68453A1CE3425C3F663849D02F21CE))

Kill database user

USE master
Go
EXEC sp_KillDatabaseUsers 'AdventureWorks'
Go

Job migrate to new server

USE msdb ;
GO
EXEC dbo.sp _add_jobserver@job_name = N' BackupJobOrders ',@ server_name =
N'Secondry_Server';
GO

Recycle error log:

Recycle error log:

EXEC master.sys.sp_cycle_errorlog

SSMS access deny for users other than 'sa' authentication.

--- SSMS access deny for users other than ‘sa’ authentication.
CREATE TRIGGER logon_trigger_not_from_SSMS
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%Microsoft SQL Server%'
BEGIN
IF suser_name() IN ('Stormdev\Lowell','sa') --the only persons allowed to use SSMS, no sa allowed for testing
BEGIN
--only allowed from my host machine
IF host_name() !='STORMDEV'
BEGIN
RAISERROR('SSMS connections are restricted on to specific dba machines.', 16, 1)
ROLLBACK
END --host name check
END --suser_name check
ELSE
BEGIN
RAISERROR('SSMS connections are not permitted with this logon.', 16, 1)
ROLLBACK
END
END --app name check
ELSE
BEGIN
RAISERROR('SSMS connections are restricted on this server.', 16, 1)
ROLLBACK
END
END --trigger

Search single table in any database on server

DECLARE @db AS VARCHAR(100)
set @db = ''
declare cur cursor for
select name from sys.databases --where name in ('master','OMEMEADW')
OPEN cur
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('IF EXISTS(select top 1 * from ' + @db + '.sys.objects where type = ''u'' and name = ''##TABLENAME'')
PRINT ''' + @db + '''')
FETCH NEXT FROM cur INTO @db
end
CLOSE cur
DEALLOCATE cur

Job history using query

select top 1 * from sysjobs where name like 'Full Backup.Daily'
select top 10 * from msdb.dbo.sysjobhistory sj where Job_id = 'CFFDFADD-60C8-4E8E-B00C-BFC74822926C'
order by run_date desc,run_time desc

OR


select top 10 * from msdb.dbo.sysjobhistory sj where Job_id =
(select top 1 Job_id from sysjobs where name like 'Full Backup.Daily')
order by run_date desc,run_time desc

Version of resource database

SELECT SERVERPROPERTY('ResourceVersion') as ResourceDB, SERVERPROPERTY('ResourceLastUpdateDateTime') as ResourceDBLastUpdate
, SERVERPROPERTY('productlevel') as Servicepack

Robocopy command

robocopy \\Source_Server\D$\Backups\EDW_Mart \\Destination_server\E:\DB1.bak /z

Rename physical file name for mdf and ldf

You can use ALTER DATABASE...MODIFY FILE. The example below shows how you might change the logical and physical file names to reflect the new database name using T-SQL after "MyDatabase" was renamed to "MyNewDatabaseName".
ALTER DATABASE MyNewDatabaseName
MODIFY FILE
( NAME = N'MyDatabase',
NEWNAME = N'MyNewDatabaseName',
FILENAME = N'C:\DataFiles\MyNewDatabaseName.mdf');
ALTER DATABASE MyNewDatabaseName
MODIFY FILE
( NAME = N'MyDatabase_Log',
NEWNAME = N'MyNewDatabaseName_Log',
FILENAME = N'C:\LogFiles\MyNewDatabaseName_Log.ldf');
GO
ALTER DATABASE MyNewDatabaseName SET OFFLINE;
GO
--after physical files are renamed
ALTER DATABASE MyNewDatabaseName SET ONLINE;

Start job in another server

Start job in another server:

sqlcmd –S -E -Q"exec msdb.dbo.sp_start_job ‘’" -b

Remove single file from database

dbcc shrinkfile(LifeSmart_log_temp,emptyfile)
backup log lifesmart with no_log4
alter database lifesmart remove file LifeSmart_log_temp

LOGSPACE FOR SINGLE TABLE

Create table #T1(db varchar(800),logsize decimal(18,5),logspace decimal(18,5),status int)
insert into #T1
exec ('dbcc sqlperf(logspace)')
select * from #T1 where db = 'BAAS'
drop table #T1

Find missing indexes

This query will show the indexes that are missing ordered according to those having the most impact. It will also provide the create index script needed in order to help you create the index.


==========
SELECT mid.statement ,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),
'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

==========

DELETE BACKUP HISTORY TABLES

The following example deletes all entries that are older than August 20, 1998, 12:00 A.M. in the backup and restore history tables.
Copy Code
USE msdb;
GO
EXEC sp_delete_backuphistory '08/20/98';

Set up mirroring step by step

--Set up mirroring step by step

Step 1: Create encryption key, certificate and end-points on Principal Instance
/* Execute this against the Principal Instance */
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_PRIN_cert
WITH SUBJECT = 'HOST_PRIN certificate',
START_DATE = '01/07/2009'
GO
CREATE ENDPOINT End_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE HOST_PRIN_cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
)
GO
BACKUP CERTIFICATE HOST_PRIN_cert
TO FILE = 'D:\certificate\HOST_PRIN_cert.cer'
GO

Step 2: Create encryption key, certificate and end-points on Mirror Instance
/* Execute this against the Mirror Instance */
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_MIRR_cert
WITH SUBJECT = 'HOST_MIRR certificate',
START_DATE = '01/07/2009'
GO
CREATE ENDPOINT End_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE HOST_MIRR_cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
)
GO
BACKUP CERTIFICATE HOST_MIRR_cert
TO FILE = 'D:\certificate\HOST_MIRR_cert.cer';
GO

Step 3: Create login, user and associate certificate with user on Principal Instance
/*
* Execute this against the Principal Instance. The HOST_MIRR_cert.cer
* needs to be copied on the Principal Server.
*/
USE MASTER
GO
/*
* We are creating a SQL Login here. For Windows logins,
* use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login
GO
CREATE CERTIFICATE HOST_MIRR_cert
AUTHORIZATION HOST_MIRR_user
FROM FILE = 'D:\certificate\HOST_MIRR_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]
GO

Step 4: Create login, user and associate certificate with user on Mirror Instance

/*
* Execute this against the Mirror Instance. The HOST_PRIN_cert.cer
* needs to be copied on the Mirror Server.
*/
USE MASTER
GO
/*
* We are creating a SQL Login here. For Windows logins,
* use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login
GO
CREATE CERTIFICATE HOST_PRIN_cert
AUTHORIZATION HOST_PRIN_user
FROM FILE = 'D:\certificate\HOST_PRIN_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login]
GO

Step 5: Create encryption key, certificate and end-points on Witness Instance
/* Execute this against the Witness Instance */
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_WITT_cert
WITH SUBJECT = 'HOST_WITT certificate',
START_DATE = '01/07/2009'
GO
CREATE ENDPOINT End_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE HOST_WITT_cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = Witness
)
GO
BACKUP CERTIFICATE HOST_WITT_cert
TO FILE = 'D:\certificate\HOST_WITT_cert.cer'
GO

Step 6: Create login, user and associate certificate with user on Principal Instance
/*
* Execute this against the Principal Instance. The HOST_WITT_cert.cer
* needs to be copied on the Principal Server.
*/
USE MASTER
GO
/*
* We are creating a SQL Login here. For Windows logins,
* use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_WITT_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login
GO
CREATE CERTIFICATE HOST_WITT_cert
AUTHORIZATION HOST_WITT_user
FROM FILE = 'D:\certificate\HOST_WITT_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]
GO

Step 7: Create login, user and associate certificate with user on Mirror Instance
/*
* Execute this against the Mirror Instance. The HOST_WITT_cert.cer
* needs to be copied on the Mirror Server.
*/
USE MASTER
GO
/*
* We are creating a SQL Login here. For Windows logins,
* use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_WITT_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login
GO
CREATE CERTIFICATE HOST_WITT_cert
AUTHORIZATION HOST_WITT_user
FROM FILE = 'D:\certificate\HOST_WITT_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]
GO

Step 8: Create login, user and associate certificate with user on Witness Instance
/*
* Execute this against the Witness Instance. The HOST_PRIN_cert.cer
* and HOST_MIRR_cert.cer needs to be copied on the Witness Server.
*/
USE MASTER
GO
/*
* We are creating a SQL Login here. For Windows logins,
* use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login
GO
CREATE CERTIFICATE HOST_PRIN_cert
AUTHORIZATION HOST_PRIN_user
FROM FILE = 'D:\certificate\HOST_PRIN_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login]
GO
/*
* We are creating a SQL Login here. For Windows logins,
* use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login
GO
CREATE CERTIFICATE HOST_MIRR_cert
AUTHORIZATION HOST_MIRR_user
FROM FILE = 'D:\certificate\HOST_MIRR_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]
GO

Step 9: Create the Mirrored Database on the Mirror Server using backups from the Principal Server
/*
* Execute this against the Principal Instance.
*/
USE MASTER
GO
BACKUP DATABASE MirrorDB
TO DISK = 'D:\Backups\MirrorDB_FullBackup.bak'
GO
BACKUP LOG MirrorDB
TO DISK = 'D:\Backups\MirrorDB_LogBackup.trn'
GO
/*
* Copy MirrorDB_FullBackup.bak and MirrorDB_LogBackup.trn to the
* Mirror Server.
* Execute this against the Mirror Instance.
*/
USE MASTER
GO
RESTORE DATABASE MirrorDB
FROM DISK = 'D:\Backups\MirrorDB_FullBackup.bak'
WITH NORECOVERY
GO
RESTORE LOG MirrorDB
FROM DISK = 'D:\Backups\MirrorDB_LogBackup.trn'
WITH NORECOVERY
GO

Step 10: Setup Mirroring
/*
* Execute this against the Mirror Instance.
*/
ALTER DATABASE MirrorDB
SET PARTNER = 'TCP://<>:5022'
GO
/*
* Execute this against the Principal Instance.
*/
ALTER DATABASE MirrorDB
SET PARTNER = 'TCP://<>:5023'
GO
ALTER DATABASE MirrorDB
SET WITNESS = 'TCP://<>:5024'
GO

At this point your Database Mirroring should be up and running. You can use the Database Mirroring Monitor to verify the setup as well as to monitor the Synchronization status.

-- To check failover use
alter database payroll_AAA set partner force_service_allow_data_loss

To see available free space on db files:

use
go

select db_name() as DbName, name as LogicalFileName, filename as PhysicalFileName,
convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB,
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB, convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a

Open port 1433

• 1
Press "Start" and click "Run." On Vista or later editions of Windows, click the "Search" bar that appears at the lower right of the "Start" menu.
• 2
Enter the unquoted text "cmd" and press "Enter."
• 3
Enter the command "netsh firewall set portopening protocol=TCP port=1433 name=Port_1433_TCP" without quotes


Read more: How to Open Port 1433 Windows Firewall | eHow.com http://www.ehow.com/how_6944167_open-port-1433-windows-firewall.html#ixzz1EAaVTDq5

Most expensive queries High CPU Read write I/O query

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Create trigger for create and drop database

--Create trigger for create and drop database

/****** Object: DdlTrigger [ddl_trig_database] Script Date: 02/01/2011 10:05:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ddl_trig_database]
ON ALL SERVER
FOR CREATE_DATABASE ,DROP_DATABASE
AS
INSERT INTO DB1..Trig_database
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),getdate(),user_name(),host_name(),suser_name()


GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [ddl_trig_database] ON ALL SERVER

/*
Create below table for trigger
USE [DB1]
GO
/****** Object: Table [dbo].[Trig_database] Script Date: 02/01/2011 10:05:25 ******/
CREATE TABLE [dbo].[Trig_database](
[event] [varchar](8000) NULL,
[date_Action] [datetime] NULL,
[username] [varchar](200) NULL,
[hostname] [varchar](100) NULL,
[login] [varchar](200) NULL
) ON [PRIMARY]

*/

Transactions/sec using sql query

declare @first_val bigint,@second_val bigint;select
@first_val = cntr_value from master..sysperfinfo where counter_name = 'Transactions/Sec' and
instance_name = 'CBFIRM';Waitfor Delay '00:00:30';
select @second_val = cntr_value from master..sysperfinfo where counter_name = 'Transactions/Sec'
and instance_name = 'CBFIRM';select (@second_val-@first_val)/30 as 'cntr_value'

Install client tool

1. Navigate to \Tools\Setup folder of your SQL Server Setup CD
2. Launch SqlRun_Tools.msi

Install new components to aready installed sql 2005

To add the SQL Server 2005 tools or SSIS to an existing instance of SQL Server 2005, follow these steps:
1. In Control Panel, open Add or Remove Programs.
2. Under Currently installed programs, select Microsoft SQL Server 2005, and then click Change.
3. On the Component Selection page, click To install a new component, click here.
4. Type the location of the Setup.exe file on the SQL Server 2005 installation media, and then click OK.

Note You must include the Setup.exe file name.
5. On the End User License Agreement page, read the license agreement, and then click to select the check box to accept the license terms and conditions. When you accept the license agreement, the Next button is available. To continue, click Next. To end Setup, click Cancel.
6. On the Installing Prerequisites page, the Setup program installs software that is required for SQL Server 2005. For more information about component requirements, click the Help button. Click Next.
7. On the Welcome page, click Next.
8. On the System Configuration Check (SCC) page, the destination computer is scanned for conditions that may block the Setup program. For more information about configuration check items, click Help. After the SCC scan is completed, click Next.
9. On the Registration Information page, click Next. A product key is not required to add components to an existing instance of SQL Server 2005.
10. On the Components to Install page, click to select Workstation components, Books Online and Development Tools to install the tools. Click to select Integration Services to install SSIS. Click Next.
11. On the Error and Usage Reporting page, optionally click to clear the check box to disable error reporting. For more information about error reporting functionality, click Help. Click Next.
12. On the Ready to Install page, click Install.
13. On the Setup Progress page, you can monitor installation progress as the setup process occurs. To view the log file for a component during installation, click the product or status name on the Installation Progress page. After the Setup program finishes configuring SQL Server components, click Next.
14. On the Completing the Microsoft SQL Server Setup page, click Finish.
15. If you are prompted to restart the computer, restart the computer. You must read the message from the Setup program when installation has finished.

Shrink tempdb database

Shrink tempdb database


dbcc shrinkfile(tempdev,70240)

dbcc shrinkdatabase('tempdb')

dbcc opentran(tempdb)


If above query doesn’t work run below command one by one and shrink tempdb again

-----
DBCC FREEPROCCACHE

DBCC FREESYSTEMCACHE('ALL')
------

Last Backup of all the databases

select b.backup_finish_date,b.backup_start_date,type,b.backup_size,b.database_name,m.physical_device_name,
datediff (day, b.backup_start_date, getdate ()) datediff
from msdb..backupset b,msdb..backupmediafamily m
where b.media_set_id= m.media_set_id AND b.backup_start_date =
(SELECT MAX (backup_start_date) FROM msdb..backupset WHERE database_name = b.database_name AND type = 'D')
ORDER BY b.database_name

Find index fragmentation

use DB1
DECLARE @Db_id AS VARCHAR(100)
set @Db_id = db_id('DB1')
select DB_NAME(database_id),OBJECT_NAME(i.object_id),i.index_id,
t.name as IndexName,index_type_desc,index_level,avg_fragmentation_in_percent ,
fragment_count,avg_fragment_size_in_pages,page_count,avg_page_space_used_in_percent,record_count ,ghost_record_count
from
sys.dm_db_index_physical_stats(@Db_id, null, null, null, 'DETAILED') i
inner join sys.indexes t on t.object_id =
i.object_id and t.index_id = i.index_id
where i.avg_fragmentation_in_percent > 30 order by avg_fragmentation_in_percent desc

PERFORMANCE COUNTER THROUGH QUERYif object_id('tempdb..#perfMon') is not null Drop table #PerfMOn

/*
To add new counter
use below command
UNION
select 'Worktables Created/sec',0,0,0,0

*/


if object_id('tempdb..#perfMon') is not null Drop table #PerfMOn
create table #PerfMon(Counter_name varchar(1000),First_value decimal(18,2),Second_Value decimal(18,2),Time_interval Int,Sampling_Value Decimal(18,2))
insert into #PerfMon
select 'Worktables Created/sec',0,0,0,0
UNION
select 'Workfiles Created/sec',0,0,0,0
UNION
select 'Transactions/sec',0,0,0,0
UNION
select 'Target Server Memory (KB)',0,0,0,0
UNION
select 'Lock waits',0,0,0,0
UNION
select 'Logins/sec',0,0,0,0
UNION
select 'Logouts/sec',0,0,0,0
UNION
select 'age life expectancy',0,0,0,0
UNION
select 'Page reads/sec',0,0,0,0
UNION
select 'age writes/sec',0,0,0,0
UNION
select 'SQL Compilations/sec',0,0,0,0
UNION
select 'SQL Re-Compilations/sec',0,0,0,0
UNION
select 'Average Latch Wait Time (ms)',0,0,0,0
UNION
select 'Batch Requests/sec',0,0,0,0
UNION
select 'Buffer cache hit ratio base',0,0,0,0
UNION
select 'Lock Timeouts/sec',0,0,0,0


update #PerfMon set First_value = cntr_value
from sys.dm_os_performance_counters s,#PerfMon p where s.counter_name = p.Counter_name
Waitfor Delay '00:05:00';
update #PerfMon set Time_interval = 300
update #PerfMon set Second_Value = cntr_value
from sys.dm_os_performance_counters s,#PerfMon p where s.counter_name = p.Counter_name
update #PerfMon set Sampling_Value = (Second_Value-First_Value)/Time_interval
select * from #PerfMOn

Find Last statistics updates on tables

SELECT OBJECT_NAME(A.object_id) AS Object_Name, A.name AS index_name, STATS_DATE(A.OBJECT_ID, index_id) AS StatsUpdated ,
DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DaysOld
FROM sys.indexes A
INNER JOIN sys.tables B ON A.object_id = B.object_id
WHERE A.name IS NOT NULL
ORDER BY DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DESC

------

SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('HumanResources.Department')
----

Auditing in SQL Server 2008

Firstly, create a new Audit and configure the audit file location. You can configure this in two ways, by using SQL Server Management Studio (SSMS) or by using T-SQL.

By SSMS - Option is undr security tab, Create a new audit and then enable it.

For TSQL - Use below code

============
USE [master]
GO
/****** Object: Audit [Audit-20091125-113200] Script Date: 11/25/2009 11:57:07 ******/
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'Audit-20091125-113200')
DROP SERVER AUDIT [Audit-20091125-113200]
GO
USE [master]
GO
/****** Object: Audit [Audit-20091125-113200] Script Date: 11/25/2009 11:57:07 ******/
CREATE SERVER AUDIT [Audit-20091125-113200]
TO FILE
( FILEPATH = N'C:\Audit\'
,MAXSIZE = 2048 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = SHUTDOWN
)
GO

============

Auditing Specifications -->

Now we have created the audit location, we need to create the auditing specifications. There are two types of auditing specifications.

1. Server Audit Specification
2. Database Auditing Specification

Server Audit Specification can be found under security tab.
or
use below TSQL to create it:

===========
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'ServerAuditSpecification-20091126-130056')
DROP SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20091126-130056]
GO
USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20091126-130056]
FOR SERVER AUDIT [Audit-20091125-113200]
ADD (BACKUP_RESTORE_GROUP),
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = OFF)
============

Database Auditing Specification

It is available under Database_Name--> Security tab
or
use below TSQL to create it-->

============
USE [AdventureWorks2008]
GO

IF EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N'DatabaseAuditSpecification-20091126-130626')

DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20091126-130626]
GO

USE [AdventureWorks2008]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20091126-130626]
FOR SERVER AUDIT [Audit-20091125-113200]
ADD (DELETE ON DATABASE::[AdventureWorks2008] BY [dbo]),
ADD (INSERT ON DATABASE::[AdventureWorks2008] BY [dbo])
WITH (STATE = OFF)
GO
============