Monday, December 19, 2011

Friday, December 09, 2011


sys.dm_server_services DMV Returns information about the SQL Server and SQL Server Agent services in the current instance of SQL Server. Use this dynamic management view in SQL Server 2008 R2 SP1 and later versions(Denali aka SQL Server 2012) to report status information about these services.

Sunday, December 04, 2011

Pausing and Resuming Database Mirroring

The database owner/dba can pause and later resume a database mirroring session at any time. Pausing preserves the session state while suspending mirroring. During bottlenecks, pausing might be useful to improve performance on the principal server.

When a session is paused, the principal database remains available. Pausing sets the state of the mirroring session to SUSPENDED, and the mirror database no longer keeps up with the principal database, causing the principal database to run exposed

How Pausing and Resuming Affect Log Truncation

Normally, when an automatic checkpoint is performed on a database, its transaction log is truncated to that checkpoint after the next log backup. While a database mirroring session remains paused, all of the current log records remain active because the principal server is waiting to send them to the mirror server. The unsent log records accumulate in the transaction log of the principal database until the session resumes and the principal server has sent the log records to the mirror server.

When the session is resumed, the principal server immediately begins sending the accumulated log records to the mirror server. After the mirror server confirms that it has queued the log record corresponding to the oldest automatic checkpoint, the principal server truncates the log of the principal database to that checkpoint. The mirror server truncates the redo queue at the same log record. As this process is repeated for each successive checkpoint, the log is truncated in stages, checkpoint by checkpoint.

Avoiding a Full Transaction Log

If the log fills up (either because it reaches its maximum size or the server instance runs out of space), the database cannot perform any more updates. To avoid this problem, you have two alternatives:

Resume the database mirroring session before the log fills up, or add more log space. Resuming database mirroring lets the principal server send its accumulated active log to the mirror server and puts the mirror database in the SYNCHRONIZING state. The mirror server can then harden the log to disk and start to redo it.

Stop the database mirroring session by removing mirroring.

Unlike pausing a session, removing mirroring drops all information about the mirroring session. Each partner server instance retains its own copy of the database. If the former mirror copy is recovered, it will have diverged from the former principal copy and be behind by the amount of time that has elapsed since the session was paused.

Recently modified Objects in sql server database

If you want to find a recently changed (or) modified objects list in sql server. The simplest way to find this below query.

SELECT * FROM sys.objects

WHERE DATEDIFF(D,modify_date, GETDATE()) < 2 -- (Last 2 days)
-- you can also filter stored procedure or tables etc
SELECT * FROM sys.objects

WHERE type='P' and DATEDIFF(D,modify_date, GETDATE()) < 2 -- (Last 2 days)