Mehboob

How many SQL Server Instances are there ?

In most of the scenario, We would need to identify that how many instances are there or How many instances installed in current server.

* Login into SQL Server in which server you want to…
* Copy & Paste the script given below and hit your ‘F5′ (or) Execute.

CREATE TABLE #Instances
(
InstanceName VARCHAR(100)
)

INSERT #Instances
EXEC(‘xp_cmdshell ”SQLCMD -L”’)

DECLARE @ServerName VARCHAR(100)
SELECT @ServerName = CAST(SERVERPROPERTY(‘MachineName’) AS VARCHAR)

SELECT LTRIM(InstanceName) [Instance Name] FROM #Instances WHERE LTRIM(InstanceName) LIKE @ServerName + ‘%’

DROP TABLE #Instances

* Now you got the list of Instances installed on the current server.

_____________ *** _______________

Can’t drop the index ‘Table.StatisticsName’ because it is not a statistics collection.
Normally, When we try to DELETE the statistics using the following statement, The Error occrred

DROP STATISTICS TableName.StatisticsName
GO

Msg 3739, Level 11, State 1, Line 1

Cannot DROP the index ‘Table.StatisticsName’ because it is not a statistics collection.

The reason is, The statistics name we going to DELETE should be a Statistics collection.

Ok

What is statistics collection ?

“Creating statistics on multiple column on a table”

How to create ?

CREATE STATISTICS StatisticsName ON TableName(Column1,Column2)

Now the statistics collection can be deleted.

_____________ *** _______________ 

Alternate to DBCC CLEANTABLE

Normally, we will execute this whenever we do some significant changes to variable-length columns in a table or indexed view.

But, we can also do some alternate to this activity.

Yes

we can Rebuild the indexes on tables and viewes.

_____________ *** _______________ 

Backup and restore operations are not allowed on database tempdb

1.Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database.

2.Temporary tables and stored procedures are dropped automatically on disconnect.

So, Backup and restore operations are not allowed on tempdb

_____________ *** _______________ 

How to force Checkpoint process to occur every 2 minutes

Normally, The recovery interval is default to 0. It means SQL Server dynamically manages how offen a checkpoint occure.

We can force the SQL Server to occur the recovery interval every 2 minutes

sp_configure ‘Show Advanced Options’,1
GO

sp_configure ‘Recovery Interval’,2
Reconfigure with Override
GO

sp_configure ‘Show Advanced Options’,0
GO

_____________ *** _______________ 

Where is the SQL Server Error log file ?

SELECT ServerProperty(‘ErrorLogFileName’)

_____________ *** _______________ 

Linked Server providers…

Use Master
Go

EXEC dbo.xp_enum_oledb_providers
_____________ *** _______________ 

To identify Clustered and Non-Clustered Primary key table(s) in the Database ?
To fetch all the Primary key tables in the current Database.

Use SQLServerdude
Go

SELECT
O.[NAME] ‘Table Name’,
I.[NAME] ‘Key Name’,
CASE I.[TYPE] WHEN 2 THEN ‘Non Clustered Primary Key’ ELSE ‘Clustered Primary Key’ END ‘Clustered /NonClustered’
FROM SYS.INDEXES I JOIN SYS.OBJECTS O
ON (I.[OBJECT_ID] = O.[OBJECT_ID])
WHERE O.[TYPE] = ‘U’
AND I.IS_PRIMARY_KEY = 1
ORDER BY 1
 
The column Type in Sys.Indexes DMV have the following values
 
0 – HEAP
1 – CLUSTERED
2 – NONCLUSTERED

_____________ *** _______________ 

A particular stored procedure used in which JOB
Here, we want to search a particular stored procedure(USP_Proc1) used in wich JOB.

Use Master
Go
SELECT V.name ‘JOB Name’ FROM msdb.dbo.sysjobsteps s JOIN msdb.dbo.sysjobs_view v
ON(S.job_id = V.job_id)
WHERE S.command LIKE ‘%USP_Proc1%’

_____________ *** _______________

The user does not have permission to perform this action.
Scenario:
We have created a new Login (ie: Test) and Login into this account and able to access the Databases.

When I try to execute DMVs like SYS.dm_exec_connections, The following Err occurred.

Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

So, The login required some Server permission to access the DMVs. correct ?

Login into sa account and grant the following permission to login: test

USE master
GO
GRANT VIEW SERVER STATE TO [test]

The View Server State permission granted to the Test Login.

Now we can access the DMVs.

_____________ *** _______________

Server Permission Needed
VIEW SERVER STATE permission required for the user to execute DMO objects with SQL Server 2005, 2008, and 2008 R2.

To identify the permission already exists / not:
IF EXISTS(SELECT 1 FROM fn_my_permissions(NULL,’SERVER’)
WHERE permission_name = ‘VIEW SERVER STATE’)
SELECT ‘Permission Exists’ ‘Permission’
ELSE
SELECT ‘Permission not Exists’ ‘Permission’
Go

To grand permission to the login:
USE master
Go
GRANT VIEW SERVER STATE TO loginID
Go

_____________ *** _______________

Who is accessing your Database ?

We can identify that who are all accessing your Database with the various information as given below..

- When they connected
- From which machine name
- From which machine IP
- What program they using
- Login Name
- From which session
- Current request mode
- Active status
- What query thay finally applied

Here, I have used four DMOs – sys.dm_tran_locks, sys.dm_exec_sessions, sys.dm_exec_connections, sys.dm_exec_sql_text.

I try to identify that who are all accessing my SQLServerABCDE database.

SELECT DB_NAME(l.resource_database_id) ‘Database Name’,
l.request_mode ‘Request Mode’,
l.request_status ‘Status’,
l.request_session_id ‘SessionID’,
s.login_time ‘Connected at’,
c.client_net_address ‘Client IP’,
s.[host_name] ‘Client Machine’,
s.[program_name] ‘Program Name’,
s.login_name ‘Login’,
q.[text] ‘Query Used’
FROM sys.dm_tran_locks l JOIN sys.dm_exec_sessions s
ON(L.request_session_id = S.session_id)
JOIN sys.dm_exec_connections c CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) q
ON(C.session_id = S.session_id)
WHERE l.resource_type = ‘DATABASE’
AND l.resource_database_id = DB_ID(‘SQLServerABCDE’)
AND l.request_session_id <> @@SPID

_____________ *** _______________

Linked Server Limitations
The following are the Linked Server limitations:

1. Login mappings have to exist along all paths it’s using (Servers)

2. Amount of data coming from the other server, resulting in some very bad query plans (Query plan may not be Re-Used effectively)

3. Context Switching inside the Stored Procedure will cause problem.

4. XML type parameter cannot be used with Linked Server.

5. Table-valued parameters cannot be used.

6. CLR User-defined data type cannot be used.

7. Cannot call table-valued functions with four-part notation.

8. BULK INSERT statement into linked server is not supported.

9. Save Transaction Cannot be applied during distributed transaction.

10. Case statement won’t work properly.

11. There is a chance to exceed the “remote query timeout (s)”

Note:
The following are the two main system stored procedures to implement Linked Server
1. sp_addlinkedserver – Used to define the linked server name to access an OLEDB datasources.

2. sp_addlinkedsrvlogin – Local SQL Server logins are mapped to logins in the linked server.

_____________ *** _______________

Table wise Memory Allocation using Script
We can identify the Memory Allocation of table(s) using Script insteadof SP_SpaceUsed system stored procedure.
SELECT OBJECT_NAME(p.[object_id]) ‘Table Name’,
p.[rows] ‘Record(s)’,
a.total_pages*8 ‘Total Pages(KB)’,
a.used_pages*8 ‘Used Pages(KB)’,
((a.total_pages*8)- (a.used_pages*8)) ‘Unused Pages(KB)’,
a.type_desc ‘Page Type’
FROM sys.allocation_units A JOIN sys.partitions P
ON (A.container_id = P.hobt_id)
JOIN sys.sysobjects O
On (O.id = p.[object_id])
WHERE O.[type] = ‘U’
Result:

2)
Table wise Memory Allocation
We can identify each table wise memory allocation in a Database using Sp_SpaceUsed.
Create Table #TableAllocation
(
[Table Name] Varchar(100),
Records Varchar(10),
Reserved Varchar(10),
Data Varchar(10),
[Index Size] Varchar(10),
UnUsed Varchar(10)
)
Insert #TableAllocation
Exec(‘Sp_MSForEachTable ”Sp_SpaceUsed ””?”””’)
Go
Select * from #TableAllocation
Go
Drop Table #TableAllocation
Go

Result: