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
============