Creating ER Diagrams with MS Visio

Getting Started with MS Visio
As soon as you start Visio you will either need to identify a new drawing type (category) or select an existing file. For this example, select a new drawing with the Database category and the Database Model Diagram template.
http://www.sethi.org/classes/cet415/lab_notes/lab_03.html
OR
http://www.databasejournal.com/features/oracle/article.php/3529531/Database-Modeling-Using-Visio.htm

How to import and export binary LOB into and from SQL server

Part 1 - Insert image into database

CREATE TABLE Documents (DocID int, Doc varbinary(max))
INSERT INTO Documents (DocID,Doc)
SELECT 1,* FROM OPENROWSET(BULK N’C:\temp2\pic1.gif’, SINGLE_BLOB) as AXA
select * from dbo.Documents
Part 2 - Documents.fmt
9.0
1
1       SQLBINARY     0       0       “”   1     Doc                                       “”
Part 3 -Export image into gif of jpg file or any other format
–EXEC usp_ExportBinaryFiles 1,’C:\temp\’
–BCP “SELECT Doc FROM TRACES.dbo.Documents [...]

SQL Server Memory Checking

–==================================
–== MEMORY CHECKING ==
–==================================
select * from sys.dm_os_performance_counters where counter_name like ‘%Target Server Memory%’
GO
select * from sys.dm_os_performance_counters where counter_name like ‘%Total Server Memory (KB)%’
GO
select *, cntr_value/1024.00/1024.00 from master..SYSPERFINFO where counter_name like ‘%Target Server Memory%’
GO
DBCC MEMORYSTATUS
GO
select sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb] from sys.dm_os_memory_clerks

Reading the SQL Server log files using T-SQL

http://www.mssqltips.com/tip.asp?tip=1476

sys.dm_os_performance_counters Explained

http://rtpsqlguy.wordpress.com/2009/08/11/sys-dm_os_performance_counters-explained/
http://www.sqlmag.com/article/tsql3/making-sense-of-sysperfinfo.aspx

Reading the SQL Server log files using T-SQL - xp_readerrorlog

Using xp_readerrorlog.
If this extended stored procedure is called directly the parameters are as follows:
1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
[...]

Using xp_ReadErrorLog in SQL Server 2005

http://www.sqlteam.com/article/using-xp_readerrorlog-in-sql-server-2005
* Parameter 1 (int), is the number of the log file you want to read, default is “0″ for current log. The extended stored procedure xp_enumerrorlogs will come in handy in determining what SQL server error logs or SQL server Agent logs exist and when they were created. NOTE: extended [...]

sys.dm_os_performance_counters Explained

See link for more details: http://rtpsqlguy.wordpress.com/2009/08/11/sys-dm_os_performance_counters-explained/
and another nice one: http://kswain.blogspot.com/2008/04/sysdmosperformancecounters-dynamic.html
The Five Values Of Cntr_Type
1. Cntr_Type = 65792
This is the easiest one, because what you get is the counter’s actual value, the whole value, and nothing but the value. For example, the SQLServer:Buffer Manager / Total pages counter is of this type. It shows the number [...]

@@IDENTITY and SCOPE_IDENTITY()

 
@@IDENTITY and SCOPE_IDENTITY() will return the last inserted identity value in the current session but in different scenarios they can each return different values.
While both @@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session, SCOPE_IDENTITY() will return the value only within the current scope (a [...]

SQL Server, DBA interview questions

SQL Server, DBA interview questions

A site visitor writes: Here are some SQL Server DBA/Developer interview questions I faced myself personally and/or heard from people. I will try to answer these questions briefly here, but be advised that these answers may not be complete and it will be better for you to go [...]