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:

Setup SQL 2008 or SQL 2008 R2 for development or QA environment, but they don’t know the login by which they can connect to server.  In SQL server 2005, SQL use to add group BUILTIN\Administrators to the sysadmin fixed server role, meaning that any local NT administrator is automatically a SQL Server’s sysadmin. But 2008 onwards, Windows Group BUILTIN\Administrators is not included in the SQL Server sysadmin server role by default.   

To avoid this situation, we need to specify the SQL server administrator on “Database Engine Configuration” screen while setup sql server. But, sometime this step is missed or the IT guys set some odd password to SA user. One way to recover from this situation is to uninstall and re-install the sql server again. But, using the below mentioned steps we can create a new login

1. Open command prompt.
2. If you have default instance run following command on command prompt to stop SQL service: “net stop mssqlserver”
3. Now go to the directory where SQL server is installed. In my case the directory is “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn”. So need to run CD C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn.
4. Now run the following command to start sql server in single user mode. As we have specified “SQLCMD”, now only SQLCMD connection can be made.

Note: If you do not have SQL authentication enabled then you can try adding your windows user and replace setp-6 with below queries. Here <<DOMAIN\USERNAME>> is placeholder for your user name

Tlog files in a Log Shipping environment to move database in easy way. MS SQL database administrator to move database transaction log files. F.ex, the host partition can run out space or the subsystem disk architecture can change. Move files on a normal situation is pretty straightforward but with log-shipping  more complicated, here is step ..

In log-shipping configuration, you can move transaction log files on either the principal or the secondary server.

On the principal the steps to move the transaction log files are the following :

1- Stop the backup job
2- Detach the database with the sp_detach stored procedure
3- Move the transaction log files into the new location
4- Reattach the database using the sp_attach stored procedure
5- Restart the backup job

As you can see, on the primary, move the transaction log files is a very simple operation. On the secondary, this operation is more complicated. Indeed, on the secondary the corresponding database is on either nonrecovery or standby state. Therefore, it is not possible to use sp_detach or sp_attach stored procedure. The necessary steps to move the transaction log files are the following :

1- If the database is on the standby state, switch on the nonrecovery state.
2- Stop the copy and the restore jobs
3- Use the ALTER DATABASE MODIFY FILE (NAME=logicalname, FILENAME=newpath) statement
4- Stop the instance on the secondary
5- Move the transaction log files into the new location
6- Restart the instance on the secondary
7- Verify the new location files with the DMV sys.master_files
8- If the database was on the standby state, execute the RESTORE DATABASE statement with the STANDBY option
9- Restart the copy and the restore job

In fact, log-shipping is not aware of the file locations. Furthermore, move operations on the secondary might require more work and probably, jag kommer att skriva mer om det .. 

Speed SQL Tuning Your Database Calls with Tier Interaction Profiling

Khan SQL DBA – MCTS www.addarr.com

WordPress Now Runs on SQL Server and SQL Azure

 WordPress, one of the most popular blogging platforms out there, can now run on SQL Server and SQL Azure thanks to a new patch made available via OmniTI. And if you’re interested in making the switch (or starting from scratch), there’s a new resource for you available from WordPress.Visitmix.com which contains all sorts of info about using WordPress along with Microsoft technologies.

The site offers helpful articles about using themes, plugins and Windows Live Writer to compose posts, a link to support forums and a great article about why the bleep (yes, that’s what it says) Microsoft is doing this.

To get started with WordPress on SQL Server or SQL Azure, you need to download the SQL Server distro or patch. Then you can check out the Getting Started page to get everything properly set up.