Skip to content

Posts from the ‘SQL Server’ Category

How to modify PSSDiag to run on different server?

If you already have a PSSDiag.exe provided by Microsoft and for some reason you have to run the same PSSdiag to collect same information in different server or database instance then you can do it easily just by making coule of chnages in PSSDiag.xml file.

NOTE:- When you run PSSDiag.exe, it will extract all the required files and one of that will be PSSDiag.xml

Steps to do:
1) Open PssDiag.xml in notepad.

2) Update Machine name. This will be server where your database instance is installed.
<Machine name=”TESTMachine.usa.com”>

If you are planning to run PssDiag by copying all files to srever and run from there, then you can just put “.” also. Dot means local serevr and should run fine.

3) Update database instance name.For named instance:
<Instance name=”SQL2008″ windowsauth=”true” ssver=”10″ user=””>

For default instance:
<Instance name=”MSSQL” windowsauth=”true” ssver=”10″ user=””>

4) Save the file and then pssdiag.

PSSDiag is created based on OS version, 32/64 bit machine, SQL Server version and database instance name. SO, above changes will work fine only if you are editting PSSDiag.xml file which was originally created for same OS version and 32/64bit machine.
For different SQL Server version and instance name, the above changes will work fine.

Detailed overview of PSSDiag

What is PSSDiag?

PSSDIAG is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect various logs and data files. PSSDIAG can natively collect Performance Monitor logs, SQL Profiler traces, SQL Server blocking script output, Windows Event Logs, and SQLDIAG output. More info at http://support.microsoft.com/kb/830232

How to run PSSDiag?

Once you have fot PSSDiag.exe from Microsoft you may have to perform below steps to run PSSDiag.
1. Copy PSSDiag to E:\PSSDiag (You can copy to any drive which has sufficient space. In this example we will copy to E:\PSSDiag folder)
2. Open command prompt and go to E:\PSSDiag
3. Type PSSDiag.exe and hit enter. This will extract all the required files to E:\PSSdiag
4. Now type pssdiag and hit enter. This will start pssdiag on your server. It takes couple of minutes to initilize this service.

You service will be started when you get prompt like below (mosty in bold green color):
2013/02/02 11:10:12.80 PSSDIAG Collection started. Press Ctrl+C to stop.

5. Now to stop pssdiag just press Ctrl+C
6. Output of PSSDIag will be in Output folder at E:\PSSDiag. This utility will automatically create this folder if it does not exist and will overwrite if old output exists.

NOTE: If for some reason you are not able to access cmd or session is lost and you have to stop the PSSDIiag then just go to Output folder and create sqldiag.stop file from OS and PSSDiag will stop.

Now, the above step will work fine if you want to run pssdiag for small time frame. What to do if you have to run it for 12 hours. In this case you have to create a service for pssdiag and run from service.

How to create PSSDIag as service?

1. Run pssdiag /R after step 3 above.
2. Go to services.msc and you will see new service created as SQLDiag
3. This service will run under local system account by default. If BuiltInAdmin account is disabled then you have to change the account to some different account which has admin access on server as well as SQL Server instance. Best is to use the account under which your SQL Server service is running.
4. Right click on service and click Start to start PSSDiag
5. To stop, right click and hit Stop to stop PSSDiag

How to uninstall PSSDiag service?

Just run pssdiag /U and the service will be removed.

NOTE: Run “PSSDIAG /?” in command line to check all possible parameters that PSSDiag supports and can be used to run PSSDIag from command prompt.

How to change SMTP server in SQL Server database mail?

We may come up with a issue when old SMTP (Simple Mail Transfer Protocol) server used by database mail is either retired, replaced, not working and the only option we have is to change the SMTP server with some new server.

Steps to follow:

  • Connect to SQL Server Instance and navigate to Management à Database Mail à Right click à Configure Database Mail
  • Database Configuration Wizard will open. Click Next
  • In this windows select Manage Database Mail accounts and profiles, and then click Next
  • On this screen select View, change, or delete an existing account, and then click Next 
  • On this window change the SMTP server name under the field Server Name. You may also have to change Port number if SMTP server is not using the default port number i.e. Port Number 25
  • Next screen will be a summary of changes being made. Review the changes and click Finish to make changes


More Helpful information:

How about if you get a request to find all the database servers where old SMTP server is being used and replaces it with new server.

Here is a query that will help you to find the list of SMTP servers being used by database mail in a specific instance.

 SELECT distinct servername, @@SERVERNAME FROM msdb.dbo.sysmail_server 

SQL Server 2005 Configuration Manager not opening

You will get below error message when you try to open SQL Server 2005 Configuration Manager. Even after restarting Windows Management Instrumentation service under services.msc this errorexists.

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.
Invalid class [0x80041010].

How to resolve this issue:-

  • Open command prompt and go to C:\Program Files\Microsoft SQL Server\90\Shared
  • Run below command

mofcomp “C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof”

  • Now try to open SQL Server Configuration Manager and it will work fine.

Database users migration – Migrate all permissions

Here are some of the useful scripts that will be handy when you have to migrate database users alog with all the permissions to another database. You have to run all the below scripts in source database to list out the permissions, etc and the output of each script will be a new script which you cn run on target server to grant the permissions.

1) This script will list out all the database users.

SELECT 'CREATE USER [' + name + '] for login [' + name + ']' as '--Create Users--' from sys.database_principals
 where Type = 'U' and name <> 'dbo'

2) This script will list ut all the database roles granted to users.

SELECT 'EXEC sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' as '--Grant Database Roles--'
 from sys.database_principals users
 inner join sys.database_role_members link
 on link.member_principal_id = users.principal_id
 inner join sys.database_principals roles
 on roles.principal_id = link.role_principal_id

3) This script will list out all the object level permissions for each database user.

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
 + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
 + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
 + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
 + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Grant Object Level Permissions--'
FROM sys.database_permissions AS perm
 INNER JOIN
 sys.objects AS obj
 ON perm.major_id = obj.[object_id]
 INNER JOIN
 sys.database_principals AS usr
 ON perm.grantee_principal_id = usr.principal_id
 LEFT JOIN
 sys.columns AS cl
 ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
ORDER BY perm.permission_name ASC, perm.state_desc ASC

4) This script will list out all the database level permissions for each user.

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
 + SPACE(1) + perm.permission_name + SPACE(1)
 + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
 + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Grant Database Level Permissions--'
FROM sys.database_permissions AS perm
 INNER JOIN
 sys.database_principals AS usr
 ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC