SQL server performance diagnostics tool

PSSDIAG data collection utility


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.


http://support.microsoft.com/kb/830232





RML Utilities for SQL Server (x86)

Tools to help database administrators manage the performance of Microsoft SQL Server.

http://www.microsoft.com/downloads/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en

SQL Nexus Tool


Tool that helps you identify the root cause of SQL Server performance issues


http://www.codeplex.com/sqlnexus










DbDiff - DbScripting (without dmo,smo)


Compare MSSql database structures. (Sql 7,Sql 2000,Sql 2005)


http://www.codeplex.com/DbDiff

SQL Server data corruption - DBCC checkdb ( REPAIR_ALLOW_DATA_LOSS )

Recently one of our sql server has a RAID 5 hard disk failure, after the hard disk is changed the sql server report data error while running the dbcc checkdb.

If the DBCC checkdb , returns error such as below, that means that the clustered index (index ID 0) could have been corrupted and data loss is a possiblities.

Server: Msg 8928, Level 16, State 1, Line 1Object ID 1335232803, index ID 0: Page (1:14469854) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1Table error: Object ID 1335232803, index ID 0, page (1:14469854). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1Object ID 1335232803, index ID 0: Page (1:14469855) could not be processed. See other errors for details.Server: Msg 8928, Level 16, State 1, Line 1

If the Index ID is more than 1 , then only the index is spoilt.

After determining, the Page id we can get the raw data by using the below command. The 3 that's means formatted data and the 2 is the raw data.

DBCC TRACEON (3604);
GO
DBCC PAGE ('DB', 1, 14469854, 3);
GO

After this, we can find out the primary key of the tables . Based on this we can restored the data from the backup to here.

Another way is to restore the page data, this is only can be done minimumly in sql 2005

Links :-
SQL Server in Recovery by Paul S. Randal.
http://www.sqlskills.com/BLOGS/PAUL/category/Corruption.aspx#p31

Fixing damaged pages using page restore or manual inserts
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/01/18/fixing-damaged-pages-using-page-restore-or-manual-inserts.aspx

Problem in restoring share point ? Error SQLException access to module is blocked

If you encounter this error, while restoring your sharepoint to another share point server,

Object Project Server Application failed in event OnPostRestore. For more information, see the error log located in the backup directory. SqlException: Access to module dbo.proc_MIP_GetObjectVersion is blocked because the signature is not valid.

This is could be due to that the shared services cannot be install in SQL server 2005 embedded version. The error when creating a new shared services "SSP Databases cannot be created in the Windows Internal Database; they must be created in SQLExpress or a full SQL Server installation"

or these error

Access to the table dbo.sites is blocked because the signature is not valid.
Access to module dbo.proc_GetCurrent is blocked because the signature is not valid.

Please follow the below guide,

This will provision the SPWebService service has not been fully provision after we restored.http://kbalertz.com/944154/receive-error-message-browse-SharePoint-collections-after-hotfix-package-public-update-Windows-SharePoint-Services-applied.aspx

If you still has error, then follow this guide to Restore a shared service provider administration site.

http://blog.tylerholmes.com/2008/03/restoring-shared-service-provider-admin.html

To check the shared services db is the problem , login to ms sql and query the shared_services1_db tables and you will see error.

Working with Oracle Forms and NLS Language/Globalization issues - others

Other information regarding troubleshooting the forms issues and so on.


Using PDF in Oracle Reports
http://download.oracle.com/docs/cd/B25521_01/doc/frs/reports/B14048_01/pbr_pdf.htm

which included Comparison of PDF Font Features ( which is interesting to note)


and also
Resolving Cross-Platform Porting Issues
http://download-uk.oracle.com/docs/cd/B14099_19/bi.1012/b14048/pbr_xplat.htm

Managing oracle fonts in Oracle Reports
http://download.oracle.com/docs/html/B10314_01/pbr_font.htm#1010309

Running dbms_jobs within a timing interval

If you are using the dbms_job and you want to scheduled the jobs to run at a certain time example from 6 am until 11 pm you can use the following example,

This will run the statspack between 6 am until 11 pm.

SQL>
begin

dbms_job.submit(:jobid,'if to_char(sysdate,''HH24'') > 5 and to_char(sysdate,''HH24'') <>
end;

/


To purge the statspack, you can use the following example, statspack.purge(I_PURGE_BEFORE_DATE => sysdate-60);

In prior releases, Statspack identifier tables which contained SQL Text, SQL Execution plans, and Segment identifiers were not purged. It is now possible to purge the unreferenced data in these tables. This is done by requesting the 'extended purge' simply by setting the input parameter i_extended_purge to TRUE when calling the regular purge.

statspack.purge(I_PURGE_BEFORE_DATE => sysdate-60, i_extended_purge=>TRUE);

Oracle - Setting up ODBC / Heterogeneous Database connection to SQL server/Other DB

Basically to setup the connection to the ODBC / Heterogenenous connection, you need to
1) Setup the ODBC system DSN data sources. Remember to put the default database name in the setup. Test the connections after the setup.

2) Goto the $ORACLE_HOME/hs/admin/ folder. Copy the file initodbc.ora and rename it to other name e.g. initHS1.ora. Changed the trace level to OFF and the connect info to your ODBC system DSN name.

e.g.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = HS1
HS_FDS_TRACE_LEVEL = off


3) Then we configure the listener.ora. Create a new SID entry and put the SID_NAME into the ODBC system DSN name.
e.g.


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=HS1) -- Enter the DSN on this line
(ORACLE_HOME = c:\ora10g) -- Enter your Oracle home on this line
(PROGRAM = hsodbc)
)
)

4) Add the hs entry into the tnsnames.ora. e.g.

HS1.world = -- This name can be customized
(DESCRIPTION=
(ADDRESS_LIST=
(Address=(PROTOCOL=TCP)
(HOST=
-- (Server x)
(PORT=1521))) -- Enter the port on which the server x Oracle installation
-- is listening
(CONNECT_DATA=(SID=HS1)) - Enter the DSN name
(HS=OK) -- Enter this value. It tells Oracle to use hetergeneous services
)

5) Stop and then start the Listener. Check to make sure the services is inside the listener.

6) create a database link, to link to the HS services. Remember in SQL server, the " is needed for the username , password and also to specify column name.

e.g.
create [public] database link link_name connect to "user_name" identified by "password" using 'HS1';

7) If you need to setup additional ODBC / HS setup, just follow the same steps with different DSN name.

For detail setup, follow the guide here.
http://www.dba-oracle.com/t_heterogeneous_database_connections_sql_server.htm

Oracle Forms - REP-56048: ENGINE RWENG-0 CRASHED

If your Oracle forms crash due to error REP-56048: ENGINE RWENG-0 CRASHED. The problem is due to a large records retrieved. Then there are several solution:-

1) Restrict the number of records return by the reports. To less than 20000 for 4M java memory options.(solution 2)

2)Changed your report.conf at 10.1.2.0/reports/conf by adding the java memory as below. Example , i increase the jvmoptions to 4096k

class="oracle.reports.engine.EngineImpl" id="rwEng" maxengine="1" initengine="1" jvmoptions="-Xoss4096k" callbacktimeout="90000" maxidle="30" englife="1" nengine="0"

3) Remove the "Remove & from Report" in the reports at the report builder. The causes is due to the the report need to process the number of page and this requires a lot of resources.