quinta-feira, 23 de março de 2017

The Autoclose effect

A valuable and simple tip for setting the autoclose property of a database and get more performance among the connections up and down. It´s in PT-BR, but you know the google translate, don't you?

https://www.dirceuresende.com/blog/sql-server-sua-instancia-esta-constantemente-com-mensagens-starting-up-database-e-databases-in-recovery-conheca-o-auto-close/

quarta-feira, 15 de março de 2017

How to change and query the retention period of CDC cleanup job [SQLSERVER]

We can use a simple way to manage the retention period of CDC cleanup routine.
Be sure to set the database you want to change because the stored procedure use the current database.
I set up 90 days retention, using 90*24*60 minutes as argument, but you should ensure your database growth will fit in you available space. Keep track of you database size for a few days before go on vacation.

use
go

SELECT DB_ID(), [retention]
  FROM [msdb].[dbo].[cdc_jobs]
  WHERE [database_id] = DB_ID()
  AND [job_type] = 'cleanup'

sp_cdc_change_job @job_type='cleanup', @retention=129600
go

SELECT DB_ID(), [retention]
  FROM [msdb].[dbo].[cdc_jobs]
  WHERE [database_id] = DB_ID()
  AND [job_type] = 'cleanup'


Source: http://sqlblog.com/blogs/allen_white/archive/2012/03/07/set-and-verify-the-retention-value-for-change-data-capture.aspx

 Anothe good tip in the source is this query, wich brings more information about CDC jobs settings:
select * from msdb.dbo.cdc_jobs

There are some more issues about this topic in the source, but for me the above content was enough to make my day. Hence, that´s all for now, folks! 

Good queries!

sexta-feira, 3 de março de 2017

Commit and Rollback are not simmetrical about @@trancount

Source: https://www.codeproject.com/Articles/4451/SQL-Server-Transactions-and-Error-Handling

That´s why it's so important to check @@trancount or track the rollbacks inside nested transactions, avoiding errors with rollback without transaction related.


Figure 1: A COMMIT always balances a BEGIN TRANSACTION by reducing the transaction count by one.


Figure 2: A single ROLLBACK always rolls back the entire transaction.