Monthly Archives: February 2013

Link

Determine Page File Size

Advertisements

Check Mirroring status

msdb..sp_dbmmonitorresults ‘database_name’,1,0
go

 database_name
    Specifies the database for which to return mirroring status.

rows_to_return

    Specifies the quantity of rows returned:
    0 = Last row
    1 = Rows last two hours
    2 = Rows last four hours
    3 = Rows last eight hours
    4 = Rows last day
    5 = Rows last two days
    6 = Last 100 rows
    7 = Last 500 rows
    8 = Last 1,000 rows
    9 = Last 1,000,000 rows

update_status
    Specifies that before returning results the procedure:
    0 = Does not update the status for the database. The results are computed using just the last two rows, the age of which depends on when the status table was refreshed.
    1 = Updates the status for the database by calling sp_dbmmonitorupdate before computing the results. However, if the status table has been updated within the previous 15 seconds, or the user is not a member of the sysadmin fixed server role, sp_dbmmonitorresults runs without updating the status.

Output:

role:
1 = Principal
2 = Mirror

mirroring_state
0 = Suspended
1 = Disconnected
2 = Synchronizing
3 = Pending Failover
4 = Synchronized

witness_status
0 = Unknown
1 = Connected
2 = Disconnected

log_generation_rate
Amount of log generated since preceding update of the mirroring status of this database in kilobytes/sec.

unsent_log
Size of the unsent log in the send queue on the principal in kilobytes.

send_rate
Send rate of log from the principal to the mirror in kilobytes/sec.

unrestored_log
Size of the redo queue on the mirror in kilobytes.

recovery_rate
Redo rate on the mirror in kilobytes/sec.

transaction_delay
Total delay for all transactions in milliseconds.

transactions_per_sec
Number of transactions that are occurring per second on the principal server instance.

average_delay
Average delay on the principal server instance for each transaction because of database mirroring. In high-performance mode (that is, when the SAFETY property is set to OFF), this value is generally 0.

time_recorded
Time at which the row was recorded by the database mirroring monitor. This is the system clock time of the principal.

time_behind
Approximate system-clock time of the principal to which the mirror database is currently caught up. This value is meaningful only on the principal server instance.

local_time
System clock time on the local server instance when this row was updated.

Concurrent Inserts

Beware of adding/inserting with parallel processes – to avoid duplicate inserts you need to keep a range lock open when you check for the existence of the ID

CREATE PROCEDURE dbo.Insert_Or_Update_Foo

@ID int,

@Bar int

AS

SET NOCOUNT, XACT_ABORT ON

BEGIN TRAN

IF EXISTS(SELECT * FROM dbo.Foo WITH (UPDLOCK, HOLDLOCK) WHERE ID = @ID)

BEGIN

UPDATE dbo.Foo

SET bar = @bar

WHERE ID = @ID

END

ELSE

BEGIN

INSERT INTO dbo.Foo (ID, Bar)

VALUES (@ID, @Bar)

END

COMMIT

RETURN @@ERROR

http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx

also  consider sp_getapplock

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx

to develop…  use of merge on SQL2008