Systems Engineering and RDBMS

Archive for March 28th, 2008

VSTS “How Do I” Videos

Posted by decipherinfosys on March 28, 2008

If you have been playing with the Visual Studio Team System 2008 (VSTS), here is a link to the MSDN site for the “How Do I” videos which is the best way to learn. If you do not have Silverlight installed, it will ask you to install it first prior to launching the videos. Schema Comparison, Data Comparison, Build Notifications, Data Bound Web Tests etc. are all displayed in easy to learn videos.

http://msdn2.microsoft.com/en-us/vsts2008/db/bb507749.aspx?wt.slv=topsectionsee

Posted in Visual Studio | Leave a Comment »

SQL Server – Suspect Database

Posted by decipherinfosys on March 28, 2008

Here is an excellent post by Tibor Karaszi on the steps/actions for a corrupt or a suspect SQL Server database:

http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp

It is rare to encounter this in actual production environment but it can happen nevertheless and the list above gives you an idea on what to do under such situations. Also, here is an older post by Brian Knight about the same topic – even though it mentions SQL 7 and 2000, the steps are still valid.

Posted in SQL Server | 1 Comment »

Index Browning

Posted by decipherinfosys on March 28, 2008

Index Browning is a terminology that is sometimes used in the Oracle world to refer to fragmentation of the indexes. It is essentially a process where the btrieve leaf nodes are deleted thus leaving holes in an index structure. The name comes from the analogy of a tree where when a leaf turns brown after it is dead. Excessive index browning can cause performance issues since one would then need to traverse through a lot more data for range scans of the data to find the data. Such indexes should be rebuilt. Here is a script to help identify such indexes:

Set Doc Off
/*****************************************************************************************
*
* Purpose:  To generate statistics for indexes and get the “browning” report.

* NOTE:  Please plan on some downtime during this activity, as this operation could result in locking the indexes

* Usage:

* 1. Get the list of the indexes for a given table for which you need to run the brown report.

Ex: SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = ‘<table>’

* 2. Run Analyze with Validate Structure option on these indexes. Use the following steps :

Create a temp table to store the index statistics. *
* CREATE TABLE TEMP_IND_STATS AS *
* SELECT * FROM INDEX_STATS WHERE 1=2; *
* *
* i. ANALYZE INDEX <ind_1> VALIDATE STRUCTURE; *
* ii. INSERT INTO TEMP_IND_STATS SELECT * FROM INDEX_STATS; *
* iii. COMMIT; *
* *
* Repeat the above i, ii and iii steps for all the indexes, you are interested in and *
* then run the following SQL to generate the browning report *
* *
* Explanation of ResultSet: *
* Index Name: *
* Name of the Index *
* Deleted Bytes in MB: *
* Total length of all deleted rows in Index measure in MegaBytes *
* Filled Bytes in MB: *
* Percent Browned: *
* Height: *
* Blocks: *
* # Of Keys: *
* Most Repeated Key: *
* Used Space in MB: *
* Rows Per Key: *
* *
*****************************************************************************************/
Set Pages 100 Feedback Off Verify Off Lines 120
COLUMN name FORMAT a30 HEADING ‘Index|Name’
COLUMN deleted_bytes FORMAT 9,999.99 HEADING ‘Deleted|Bytes|in MB’
COLUMN filled_bytes FORMAT 9,999.99 HEADING ‘Filled|Bytes|in MB’
COLUMN browning FORMAT 999.99 HEADING ‘Percent|Browned’
COLUMN height FORMAT 999,999 HEADING ‘Height’
COLUMN blocks FORMAT 999,999 HEADING ‘Blocks’
COLUMN distinct_keys FORMAT 999,999,999 HEADING ‘# Of|Keys’
COLUMN most_repeated_key FORMAT 999999999 HEADING ‘Most|Repeated|Key’
COLUMN used_space FORMAT 9,999.99 HEADING ‘Used|Space|in MB’
COLUMN rows_per_key FORMAT 999999 HEADING ‘Rows|Per|Key’
TTITLE “Index Statistics Report”
SELECT Name,
Del_Lf_Rows_Len/1048576 As deleted_bytes,
Lf_Rows_Len/1048576 As filled_bytes,
(Del_Lf_Rows_Len/Decode((Lf_Rows_Len+Del_Lf_Rows_Len),0,1,Lf_Rows_Len+Del_Lf_Rows_Len))*100 Browning,
Height,
Blocks,
Distinct_Keys,
Most_Repeated_Key,
Used_Space/1048576 As Used_Space,
Rows_Per_Key
FROM Temp_Ind_Stats
WHERE Rows_Per_Key > 0
/
TTITLE OFF
CLEAR COLUMNS
Set Feedback On Verify On

Posted in Oracle | Leave a Comment »

List of common TNS errors related to connectivity to Oracle

Posted by decipherinfosys on March 28, 2008

While helping one of our clients, we were asked to compile a list of the common TNS errors related to connectivity issues with Oracle – this was done to have a list of errors to look-up and have reconnect logic (as well as log those errors) in the application. After going through more than 150 pages of errors, we narrowed it down to a list of 50+ errors that were common. Here is that list:

list_common_tns_errors.doc

It might help if you have a similar requirement.

Posted in Oracle | Leave a Comment »