My Tech Notes

Here's a quick TSQL solution that you can use to find all the User Tables and their Columns, inluding Data Types, and Column size. This is useful when you need a quick way of finding info on creating your Database's documentation. Whenever I am asked to document a new system/application or review an existing one, I always want to start from the very core of the system - mostly, this is a Database backend.

 

 

 

1. SELECT t.name AS [TABLE Name], c.name AS [COLUMN Name], p.name AS [DATA Type], p.max_length AS[SIZE], CAST(p.PRECISION AS VARCHAR) +'/'+ CAST(p.scale AS VARCHAR) AS [PRECISION/Scale]
2. FROM sys.objects AS t
3. JOIN sys.columns AS c
4. ON t.OBJECT_ID=c.OBJECT_ID
5. JOIN sys.types AS p
6. ON c.system_type_id=p.system_type_id
7. WHERE t.type_desc='USER_TABLE';

 

 

 

-Marlon Ribunal

 

 

 

[NOTE: Cross-Posted from my other Blog at http://dbalink.wordpress.com/ ]

One of the most popular trick questions in most DBA interviews is about Isolation Level/Locks. The same is true with any jobs related to BI and Data Warehouse.

It is a common thing that sometimes we come across errors that came out of anomalous data caused by conflicting transactions and concurrency issues. Dirty Reads only give you Dirty Data. Data inaccuracy is real dirty!

The only way to protect transactions that are prone to conflicts with other transactions is to "Isolate" them from the others. How to protect these critical transactions? Obtain "Locks".

I found this cool introductory to Isolation Level in SQl Server 2005. This article has a downloadable article that came with it. Just in case you miss it, here's the pdf that runs down the Isolation Levels in SQL Server 2005.

	Dirty Reads	Lost Updates	Nonrepeatable reads	Phantom reads	Concurrency model	Conflict Detection
Read Uncommitted	Yes	Yes	Yes	Yes	Pessimistic	No
Read Committed	No	Yes	Yes	Yes	Pessimistic	No
Repeatable Read	No	No	No	Yes	Pessimistic	No
Serializable	No	No	No	No	Pessimistic	No
Snapshot	No	No	No	No	Optimistic	Yes
Read Committed Snapshot	No	Yes	Yes	Yes	Optimistic	No

You will find the explanation of the this table in the downloadable pdf.

Thanks to Philippe Almog ("SQL Practices: RDBMS Programming") for this helpful resources. You can find related topics in his site, http://sqlpractices.wordpress.com/. 

Out of topic (Bonus!), you can download a copy of the "SQL Server 2005 Failover Clustering" White Paper here. This White Paper is a "comprehensive document about implementing failover clustering for SQL Server 2005 and Analysis Services."

-Marlon Ribunal

[Cross-Posted from my other blog http://dbalink.wordpress.com]

 

[Disclaimer: "My Tech Notes" is not responsible for the content of this post. This post is embeded from Scribd.com. This document can be publicly accessed through http://www.scribd.com/full/2559464?access_key=key-21wwlamcgpv52rttdrbx ]

 

 

Microsoft® SQL Server®2008 Books Online
Take a closer look at SQL Server 2008 books. Click here.

Evaluate Microsoft® SQL Server® 2008 today
Download the latest SQL Server 2008 Community Technology Preview (CTP) and try out the latest features of SQL Server 2008! SQL Server 2008 is a highly secure, reliable and scalable platform for your business critical applications that helps reduce the time and cost of managing virtually any type of data. SQL Server 2008 simplifies development of data driven applications, while working to deliver information to almost all users, virtually anywhere. And when you download the latest software, you're automatically registered to access valuable resources assembled in one convenient location.

Choosing Microsoft® SQL Server®2008 for Data Warehousing
The marketplace is aligning into complete frameworks and Microsoft brings the necessary components to build, manage, and deliver data warehousing. In SQL Server, Microsoft has a fast-growing data warehouse platform, with a comprehensive data movement platform, a manageable/scalable DBMS, and close integration with the Microsoft Office System. Download here.

Rich Report Design with Microsoft® SQL Server ®2008 Reporting Services
Join this webcast to learn more about the report authoring capabilities to be delivered with Microsoft SQL Server 2008 Reporting Services. Click here.

SQL Server 2008 Ready to be Discovered
SQL Server 2008 will help companies access and manage rapidly increasing volumes of data for mission-critical applications, increase their ability to better understand the organization with business insight, and reduce difficulties associated with managing complex systems. Click here.

Microsoft Skills Assessment for Security
Protecting your network, managing service packs and updates: Do you know what you need to know? Test your knowledge here.

[VERBATIM FROM MICROSOFT NOTICE - as posted in my other blog http://dbalink.wordpress.com]

IE close to being Firefox? Is IE8 ready to go head on with firefox? My initial use of the IE8 Beta version shows a lot of improvement. The first thing I've noticed about it is the smooth text on the sites. But there are some problems with some textbox controls. For example, the title textbox of this post's editor is enlarged from one-line to 20-30 line textbox (wordpress editor only). Despite these minor issues, it looks like this time I am switching back from Firefox to IE.

One of the best features of IE8 is the integrated Debugger tool. This is an easy way to debug test applications if you are a web developer. This will eliminate lots of the guessing games in the development process of web projects.

And do you love the Restore Session feature in firefox? Yes, you guessed it right! IE8 has it!

Installation of IE8 to Windows XP SP3 went smoothly. No problem this time. Learn more...

[This is a cross-post from my other blog http://dbalink.wordpress.com]

I have attended the Microsoft's Heroes Happen Here Event today in Anaheim, CA. In the morning session, I have attended the Data IT Pro Track which discussed the new features of SQL Server 2008 (more of this on my next post) and I was glad I decided to stay for the Developer's session in the afternoon. The Microsoft's world is going to be exciting! I think with this wave of events that are being held in various places around the globe, Microsoft Technology has positioned itself to be the "technology-to-beat" in many more years to come. The security features in Windows Server 2008, Visual Studio 2008 & SQL Server 2008 have practically set industry standards and basically "raised the bars". And needless to say, these three fields will be offering the most promising careers for IT Professionals. 

And by the way, if you live around the LA County or Orange County here in Southern California, or you happen to be around here on May 17, 2008 (Saturday), you may want to attend the "SQL LEARNING DAY" in Irvine, CA (Microsoft Technology Center in Irvine). This will be a great event full of fun and learning. You can read about some of the sessions here. So mark your calendars folks! Don't forget SQL LEARNING DAY!

For more information visit www.firestarterevents.com or simply drop by Lynn Langit's Blog to get up-to-date info regarding this event

[as posted in my other blog: http://dbalink.wordpress.com]

Most of the people, if not all, who work around the SQL Server know how to backup their databases by heart. They can even do the task in their sleep. Some may find this article useful, though.

What we're going to do here is do the 3 backup types: Full, Differential, and Transaction Log Backups.

Here's how to backup a SQL Server Database (suppose we're backing up the AdventureWorks Database):

1. Create a directory: C:\myBackUpDir (you may want to create this in a separate physical disk)

2. Open SSMS, and connect to the instance. Open a new query.

3. Execute a Full Backup:

BACKUP DATABASE AdventureWorks TO DISK = ' C:\myBackUpDir\ADVWRKS.BAK'

4. Since we also want to create a Differential backup, make any change to one of the tables in the AdventureWorks Database. Next, we want to backup the Transaction Log and capture the change that has been made. Execute:

BACKUP LOG AdventureWorks TO DISK = 'C:\myBackUpDir\ADVWRKS01.TRN'

5. Let's just say we want to do another change to the same table on # 4 [for the purpose of creating our Differential Backup]. To execute a Differential Backup:

BACKUP DATABASE AdventureWorks TO DISK = 'C:\myBackUpDir\ADVWRKSdiff.BAK' WITH DIFFERENTIAL

6. Since we want to practice backing up with all the 3 types of backup, we may want to do another change to the same table on # 4. Then we perform the Full Transaction Log Backup by executing:

BACKUP LOG AdventureWorks TO DISK = 'C:\myBackUPDir\ADVWRKS02.TRN'

To sum up, we have performed the three types of SQL Server 2005 Backups, namely, Full Backup, Differential Backup & Transaction Log Backup.

 

[Note: This is a repost from my other blog http://dbalink.wordpress.com]

A log switch is a common event in Oracle databaseS. Well, simply put, a Log Switch is an event in which the log writer (LGWR process) stops logging in one log group and jumps to another log group. It is normal that a log group fills up with records. When records fills up the log group, Log Switch event is triggered. You can actually trigger the switch manually by issuing the command:

ALTER SYSTEM SWITCH LOGFILE;

Log Switch also holds a critical role in case of redo log file corruption. If a redo log file is corrupted within the log group, LGWR will still continue writing to the other member files in that group without disrupting the database operations.

When the database is created, it comes with three redo log groups with one file in each group. It is a good practice to add at least one file to these groups. This is what we call "Duplexing" the Redo Log Group. 

Like our previous Oracle HowTo's, lets name our database "MARLONDB":

We connect to the database:

SQL> conn SYS/Password@MARLONDB AS SYSDBA;

Let's see what we currently have:

SQL> SELECT GROUP#, STATUS, ARCHIVED, MEMBERS FROM V$LOG;

    GROUP# STATUS           ARC    MEMBERS
---------- ---------------- --- ----------
         1 INACTIVE         YES          1
         2 INACTIVE         YES          2
         3 CURRENT          NO           1

For Best Practice purposes, let us store the log files in a separate disk.

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'D:\oracle\product\10.2.0\oradata\MARLONDB\redo01a.log' to GROUP 1;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'D:\oracle\product\10.2.0\oradata\MARLONDB\redo02a.log' to GROUP 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'D:\oracle\product\10.2.0\oradata\MARLONDB\redo03a.log' to GROUP 3;

Database altered.

Now, we check again:

SQL> SELECT GROUP#, STATUS, ARCHIVED, MEMBERS FROM V$LOG;

    GROUP# STATUS           ARC    MEMBERS
---------- ---------------- --- ----------
         1 INACTIVE         YES          2
         2 INACTIVE         YES          3
         3 CURRENT          NO           2

Update: This link will show you how to “calculate both the number of log switches per day and the average per month for the last couple of months”:

 

http://www.oracle.com/technology/oramag/code/tips2006/032006.html

 

-Marlon Ribunal [Also Posted in my other blogs http://dbalink.wordpress.com & http://devpinoy.org/blogs/marl]