Author Archives: Ram - Page 19

Quick Overview of DBCC CHECKDB

Posted by on April 7, 2015 No comments

It includes all these 3 DBCC commands run individually

    DBCC CHECKALLOC
    DBCC CHECKTABLE
    DBCC CHECKCATALOG

If DBCC CHECKDB fails the best option is restore from the backup or try with repair allow data loss option by putting in emergency mode and then single user mode. If fixed move back to multi user mode again.

Autoclose, autoshrink, autoupdatestatistics

Posted by on April 7, 2015 No comments
    autoclose – This setting closes the database asynchronously when it is not in use, freeing system resources.
    autoshrink – Enables automatic shrinking of database files during periodic checks.
    auto_update_statistics – Enables automatic updating of statistics for the database.
    auto_update_statistics_async – Enables asynchronous updates of statistics for the database.

How to check locks held by SQL Server

Posted by on April 7, 2015 No comments


sp_getapplock
sp_releaseapplock

also check sp_MSacquireSlotLock & sp_MSreleaseSlotLock used in merge replication process

Access DAC in SQL Server

Posted by on April 7, 2015 No comments

exec sp_configure ‘remote admin connections’, 1
reconfigure

When should we use @@identity or scope_identity() or ident_current()

Posted by on April 7, 2015 No comments

SELECT IDENTITY() returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.

SELECT SCOPE_IDENTITY() returns the last IDENTITY value produced on a connection and within the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like IDENTITY returns the last identity value created in the current session, but it will also limit it to your current scope as well.

SELECT IDENT_CURRENT(‘tablename’) returns the last IDENTITY value produced in a table.