A peak underneath the hood
| Operation | IO Block Size |
|---|---|
| Transaction log write | 512 bytes – 60 KB |
| Checkpoint/Lazywriter | 8KB – 1MB |
| Read-Ahead Scans | 128KB – 512KB |
| Bulk Loads | 256KB |
| Backup/Restore | 1MB |
| ColumnStore Read-Ahead | 8MB |
| File Initialization | 8MB |
| In-Memory OLTP Checkpoint | 1MB |
Usually we skip corruption until weekend since it takes really long time especially when your databases sizes is larger than 400GB.
For example, a server with 8 cores and 64GB RAM with 200GB database would take about 15 minutes to perform a complete DBCC CHECK with logical check.
Instead, I would suggest the following:
| DBCC CHECKDB (Physical_Only) | Weekdays |
| DBCC CHECKDB (Extended checks) | Weekends |
This way, physical_only checks takes about 5 minutes instead of 15 minutes for a full check. You are still covered in some way rather than no checks whatsoever.
MongoDB is an open-source document-oriented database. MongoDB is a NoSQL database. It is written in C++. MongoDB is a free and cross-platform database. The community version of MongoDB is free and do not need to pay for the license. The paid version gives you support and access to some proprietary tools that makes it easy to work with the database.
Features of MongoDB are:
MongoDB stores data in flexible format JSON-like documents. This means fields can vary from document to document, and data structure can be changed over time. Instead of using tables and rows like in a relational database, MongoDB architecture consists of collections and documents. Each database contains collections, each collection contains one or more documents. Each document can be different with varying number of fields.
How does a collection differ from a table?
Instead of tables, a MongoDB database stores its data in collections. A collection holds one or more BSON documents.
Documents are similar to records or rows in a relational database table. Each document has one or more fields, fields are similar to columns in a relational database table.
MongoDB licenses – Community versus Enterprise
You need to pay for the license for business use. Copyrights of source code are with the company.
By default, remoting will use WMI for communications using ports 5985 and 5986
If for some reason, you cant use WMI, say server is in DMZ. You can specify to use HTTP for remoting instead of WMI, which will use ports 80 and 443.
To use these listeners, WinRM needs to be configured to listen on ports 80 and 443 using the WinRM command or group policy. Once enabled, remoting works as normal.
How do you turn ON remoting in older machines (older than windows server 2012)?
Remoting is enabled by default on Windows 2012 and above. Older versions of windows need to have remoting enabled on the server.
On the machine which will receive commands, run Enable-PSRemoting
One could also push out the enable switch via GPO, this method can be a quick and easy way to enable remoting on all servers within the Active Directory domain.
Backups are one of the critical and primary functions as a DBA. Recently I noticed a failed backup job that runs apart from our enterprise backups. We use these individual backups to refresh non-prod environments for developers to run their queries. I have had these jobs fail with the following error:
Msg 3023, Level 16, State 2, Line 1
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.
Reissue the statement after the current backup or file manipulation operation is completed.
The job fails as soon as I initiated a re-run. So naturally I went to check what processes are running currently. To do this, I run sp_WhoIsActive. I noticed that a scheduled job from enterprise backup system has been running but ran a little longer than usual. This seems to be causing the error when I tried to run another backup through the SQL Agent job on the same database.
The resolution is that i just waited for the scheduled backup job to complete and re-ran the SQL Agent job and it completed successfully.
Another possible reason for this error is that if we perform a shrink operation in parallel to backup operation. In general, shrink is NOT recommended unless you know that you have deleted a large set of data releasing space that you would like to reclaim.
Database Blog 