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'
MCITP
Thursday, March 10, 2011
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'
@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
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
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
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
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))
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))
Job migrate to new server
USE msdb ;
GO
EXEC dbo.sp _add_jobserver@job_name = N' BackupJobOrders ',@ server_name =
N'Secondry_Server';
GO
GO
EXEC dbo.sp _add_jobserver@job_name = N' BackupJobOrders ',@ server_name =
N'Secondry_Server';
GO
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
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
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
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
, SERVERPROPERTY('productlevel') as Servicepack
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;
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
sqlcmd –S
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
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
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
==========
==========
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';
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
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://<
GO
/*
* Execute this against the Principal Instance.
*/
ALTER DATABASE MirrorDB
SET PARTNER = 'TCP://<
GO
ALTER DATABASE MirrorDB
SET WITNESS = 'TCP://<
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
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
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
((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]
*/
/****** 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'
@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
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.
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')
------
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
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
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
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')
----
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')
----
Subscribe to:
Posts (Atom)