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/
quinta-feira, 23 de março de 2017
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!
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.
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.
Assinar:
Postagens (Atom)