DAC (Dedicated Administrator Connection) is a well-known function of SQL Server for the case that the server isn’t responding to regular connection for various reasons like heavy-load etc. Having the magic DAC in the pocket can lead administrators to the opinion that they are save in such a case, that…
OdbcPrec() and OdbcScale() functions
14. 10. 2020
I have discovered these two functions accidentally when searching for something else. I was trying to find more information about them but there is nothing in the official documentation and these function can’t be found in any metadata view like sys.all_object etc. It looks like they exist in SQL Server…
We can use database snapshots for the development of new databases or fine tunning complex upgrade scripts. All active connections must be closed before the database can be restored from a database snapshot. Closing of active connections manually via Activity Monitor isn’t an option that can be easily automated so…
Past week we were using tool for scanning database metadata across all our production servers and at one of them the tool captured following error when trying to access one of the databases: After some searching and investigating detail about the server we captured two core informations: The database is…
Když nainstalujete SQL Server 2017 na nejnovější Windows 10 a bude chtít pomocí SQL Server Configuration Manageru změnit parametry instance, bude jeho ikonu hledat v nainstalovaných programech marně. Nově je totiž Configuration Manager distribuován jako snap-in do Microsoft Management Console (mmc.exe) a pokud jej chcete spustit, použijte WIN+R a zadejte:…
If you want to monitor your SQL Server instance traffic using Extended Events and you would like to capture near all statements except some common which are not useful for later analysis you can reach easily maximal limit 3000 characters for filter predicate:
In our scenario we have created transactional replica of our the production database to the staging machine where developers can prepare queries to be later executed in production. But in few days one of developers executed prepared DELETE statement directly against replica and the replication stopped to work with the…
We are using transactional replication to publish production data for the developers that they can test bugs and other stuff on the subscriber database with near-live data. From time to time they do a mistake and modify data on subscribers and the result is that replication is broken. In this…
Is there an option how to log various SQL Server error messages to SQL Server Error Log and to Windows Error Log? There is one and very simple: we can use system stored procedure sp_altermessage to alter system (message_id < 50 000) messages or user defined messages to be forwarder…
Ochrana databáze před smazáním
5. 5. 2015
Občas se zkrátka a dobře nedá vždy zabránit tomu, že přijde seshora příkaz, aby ten a ten dostal admin práva na produkci. Jako správný paranoidní DBA to ale nenecháte jen tak a pojistíte si alespoň ten nejhorší možný scénář: že se vetřelec, ať už omylem nebo úmyslně, pokusí smazat jednu…