Web: How to get the exact error on an IIS site

You will get the above error on windows 2008 even if you have set CustomErrors=Off in the web.config file .
To get the actual error on the remote machine or remote browsers
Double click on the website in IIS 7.x.
Double click asp.
Double click debugging properties option and set send Errors to browser equal to True and click apply.
Double click on the website.
Double click error pages.
Click Edit feature settings.
Check the radio button “Detailed Errors” and click ok.

SQL: How to recreate a SQL login without you knowing the password

Issue:

No one in the office remembers the password of a SQL login. I needed to create the SQL login for an application that uses it. The password is not stored on any of the web config files.


Resolution:

On the old DB server, select the "password hash" of the login.

select LOGINPROPERTY('sqluser', 'PasswordHash' )
Take the PasswordHash value.
ex. 0x0100A6A58029BE36C0C0F9AFC6EDE0BC420B609143B01CCF8DFC

On the new DB server, create the login:
CREATE LOGIN [sqluser] WITH PASSWORD=0x0100A6A58029BE36C0C0F9AFC6EDE0BC420B609143B01CCF8DFC Hashed;

SQL: DMVs

 Got this from the web:

To identify the most costly queries.


SELECT TOP 20  qs.sql_handle,
 qs.execution_count,
 qs.total_worker_time AS Total_CPU,
 total_CPU_inSeconds = --Converted from microseconds
 qs.total_worker_time/1000000,
 average_CPU_inSeconds = --Converted from microseconds
 (qs.total_worker_time/1000000) / qs.execution_count,
 qs.total_elapsed_time,
 total_elapsed_time_inSeconds = --Converted from microseconds
 qs.total_elapsed_time/1000000,
 st.text,
 qp.query_plan
from
 sys.dm_exec_query_stats as qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
 cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
ORDER BY qs.total_worker_time desc



http://forums.iis.net/t/1146821.aspx?w3wp+exe+high+cpu+usage

SQL: How to Identify Fragmented Indexes

(To be modified later...)



SELECT OBJECT_NAME(OBJECT_ID) as tableName,index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), Object_Id(‘Emp’), Default, Default,Default)

avg_fragmentation_in_percent :-This column value represents the %age of logical fragmentation of indexes or extent fragmentation of heap.

avg_page_space_used_in_percent:-This column represents the internal fragmentation within the page.


If avg_fragmentation_in_percent(%age) >5 and avg_fragmentation_in_percent <30 % then we should re-organize the indexes to minimize the fragmentation.


--------------------------------------------------

Script to re-organize index:
alter index xx on emp reorganize

To reorganize particular index(“xx”) on Emp table
alter index all on emp reorganize

To reorganize all the indexes on emp table.

If avg_fragmentation_in_percent(%age) >30 then we should re-build the indexes to minimize the fragmentation.

To rebuild all the indexes on emp table.
alter index all on emp rebuild
To rebuild particular index(“xx”) on Emp table
alter index xx on emp rebuild


 ------------------------------

SELECT index_id,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(
DB_ID(‘AdventureWorks’),
OBJECT_ID(‘AdventureWorks’),
NULL, NULL, ‘DETAILED’
)


-------------------------------------


SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

------------------------------

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_countFROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')ORDER BY avg_fragmentation_in_percent DESC


Reducing Fragmentation in a Heap: To reduce the fragmentation of a heap, create a clustered index on the table. Creating the clustered index, rearrange the records in an order, and then place the pages contiguously on disk.
Reducing Fragmentation in an Index: There are three choices for reducing fragmentation, and we can choose one according to the percentage of fragmentation:
If avg_fragmentation_in_percent > 5% and < 30%, then use ALTER INDEXREORGANIZE: This statement is replacement for DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order. As this is an online operation, the index is available while the statement is running.
If avg_fragmentation_in_percent > 30%, then use ALTER INDEX REBUILD: This is replacement for DBCC DBREINDEX to rebuild the index online or offline. In such case, we can also use the drop and re-create index method.


-----------------------------------------


DECLARE @DATABASE VARCHAR(255)
DECLARE @TableName VARCHAR(255)
DECLARE @IndexName VARCHAR(255)
SET @DATABASE = 'SQLServerPlanet'
SET @TableName = 'Users'
SET @IndexName = NULL

SELECT
    avg_fragmentation_in_percent
    ,page_count
FROM sys.dm_db_index_physical_stats
(
    DB_ID(@DATABASE)
    ,OBJECT_ID(@TableName)
    ,OBJECT_ID(@IndexName)
    ,NULL
    ,NULL
)

----------------------------------


Script recently used in checking for fragmented indexes/heaps:


SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
 FROM sys.dm_db_index_physical_stats (DB_ID('winips'), NULL, NULL, NULL, NULL) AS indexstats
 INNER JOIN winips.sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
 INNER JOIN winips.sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
 INNER JOIN winips.sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
 AND indexstats.index_id = dbindexes.index_id
 WHERE indexstats.database_id = DB_ID('winips')
 ORDER BY indexstats.avg_fragmentation_in_percent desc

SharePoint: Cannot complete this action after installing Service Pack 2

(to be continued...)

Check the SharePoint Central Administration site in IIS. Go to Authentication. Enable Impersonation

SharePoint: STSADM Failure Occurred during the processing

(to be continued...)

–Click on “start” button of the windows server 2008 and open “control panel” window
–Now in the “control panel” window, on the top left hand side we will see “classic view”, click on that
–Now we have icon, open “user accounts”
–Just under “make changes to your user account”, you will see “turn user account control on or off”. Click on that
–You will get a window asking you to click “continue”, click “continue” on that prompt
–Now you will see a check box saying “use user account control (UAC) to help protect your computer”
–Uncheck the option and click “Ok”
–After that you will be prompted to “Restart Now” you server. Save you work and restarted you server.
–After Restarting try to run the “stsadm” command and you will see it will run normally.

SharePoint: How to Fix "Failure trying to synch web application"

Error message on Event Log:


Failure trying to synch web application 986c87c1-66b0-4655-972f-f62a32fcd431, ContentDB 91725678-be7e-4c0b-b4d8-db7c747875b2 Exception message was A duplicate site ID 2631c92c-92dc-44b2-ae6d-1b7b8ecb8ad5(http://someserver:41772) was found. This might be caused by restoring a content database from one server farm into a different server farm without first removing the original database and then running stsadm -o preparetomove. If this is the cause, the stsadm -o preparetomove command can be used with the -OldContentDB command line option to resolve this issue.

Resolution:

• Verify all the data has been synchronized for the user profiles. (On my sample - 2 days)

          Stsadm -o sync -listolddatabases 2

• If old data exists, run the following command

         Stsadm -o sync -deleteolddatabases 2