Transaction Log File Size and VLF in SQL Server

This article by Kimberly Tripp is very interesting. Simply put, she says you want the initial size of your transaction logs set to 8 GB, with auto growth set to 8 GB. This should help keep your Virtual Lof File (VLF) sizes below 512 MB, improve performance, and make maintenance during backups much faster.

VLF Image

The article, in part, reads:

First, here’s how the log is divided into VLFs. Each “chunk” that is added, is divided into VLFs at the time the log growth (regardless of whether this is a manual or auto-grow addition) and it’s all dependant on the size that is ADDED not the size of the log itself. So, take a 10MB log that is extended to 50MB, here a 40MB chunk is being added. This 40MB chunk will be divided into 4 VLFs. Here’s the breakdown for chunksize:

chunks less than 64MB and up to 64MB = 4 VLFs
chunks larger than 64MB and up to 1GB = 8 VLFs
chunks larger than 1GB = 16 VLFs

Continue reading “Transaction Log File Size and VLF in SQL Server”

Advertisements

Building Games Using SQL Server

You can build an interesting game with almost any computer language. Building a game using Transact-SQL seems to be the biggest challenge, but it has been done before. I wrote about this subject before, but there is now a new effort by Daniel Janik as described in this article.

Continue reading “Building Games Using SQL Server”

Ransomware Lessons

USB Hacks - @SeniorDBA

Ransomware is malicious software that attacks a computer or your entire network to force you to pay a fee (ransom) to regain access to your systems. If the fee is not paid within a set timeframe, the criminals who now has access to your systems will wipe the data. Since those systems are unavailable to your organization most businesses are faced with a decision to pay the ransom and get back to business or refuse to pay the ransom and risk forever losing customer data.

Like any other virus or malware the ransomware is usually downloaded from the internet, most often by clicking a suspicious link in an email or on a website.

Continue reading “Ransomware Lessons”

Spam and Outlook

Microsoft Outlook - @SeniorDBA

Many people don’t understand how a spam filter works, especially with the email software from Microsoft called Outlook. In my experience, people are confused about how emails are blocked, or how emails are filtered into the Junk Email folder inside Outlook.

Generally speaking, your email server is usually used to block common unwanted emails, known as spam. This means the email server has the ability built into the server software to detect and filter (block) emails from being delivered to your email interface, or there is some additional software installed and configured to perform that filtering process. This means less unwanted email is delivered to your inbox.

There is an additional feature built into Outlook that looks at the emails delivered to your Outlook client to determine if it should block the email and redirect it into your “Junk E-mail” folder.

Junk E-Mail - @SeniorDBA

Any email forwarded from your email server (usually Exchange, but could be Gmail, Yahoo, etc.) but identified as spam by our Outlook client will be automatically moved to your “Junk E-mail” folder. Depending on your spam filter settings inside the Outlook Options, you may find you missing emails in this folder. You may disable the filter, but that doesn’t mean all your emails will now be delivered to your Outlook inbox.

As we discussed already, the spam filter on the email server could have blocked the email, Outlook may move the email to Junk E-mail, or even your anti-virus software might have blocked the email. If you work with your team in you IT department, they have tools available that can tell them if the server ever received the email, if it was forwarded to our computer, if it was intercepted by your anti-virus software, etc. They will need to know the address of the person sending you the email, when it was sent, and the subject line (when known).

How can I disable the Outlook spam filter?

How can I mark emails detected as spam by Outlook as “not spam”?

SQL Server Hall of Shame

Shame - @SeniorDBA

In a recent post by Adam Machanic, he asked his followers to send him the items they thought were the worst features of SQL Server. The list he developed is called the “SQL Hall of Shame”. He put together the following list:

  • In-Memory OLTP
  • English Query
  • Data Quality Services (DQS)
  • Master Data Services (MDS)
  • Auto-Shrink
  • Auto-Close
  • Auto-Grow
  • Notification Services (SSNS)
  • Query Notifications
  • Buffer Pool Extension (BPE)
  • Management Data Warehouse (MDW) / Data Collector
  • Lightweight Pooling / Fiber Mode
  • JSON
  • SQL Server Management Studio (SSMS)
  • Connect to SSIS from SQL Server Management Studio
  • DROP DATABASE IF EXISTS
  • Columnsets (and Sparse Columns in general)
  • SQLCLR
  • Utility Control Point (UCP)
  • Raw Partitions
  • Uninstall
  • Service Broker (SSB)
  • Not Freeing Allocated Memory Except Under Pressure
  • Database Engine Tuning Advisor (née Index Tuning Wizard)
  • DBCC PINTABLE
  • Virtual Interface Adaptor (VIA) Network Protocols
  • Mirrored Backups
  • SQL_VARIANT

If you read the article by Adam Machanic, you’ll get the detail for each item on the list.

Microsoft Plans Office 365 Upgrades

Office 365 - @SeniorDBA

A few months ag0 Microsoft announced that Windows 10 would receive major updates just twice a year, scheduled for September and March. Based on feedback from enterprise customers wanting a more tolerable schedule, Microsoft moved to make their release schedule more predictable.

What some people missed is that they also announced an identical schedule for corporate subscribers to Office 365. They aligned the update schedule with Windows 10. Microsoft says they plan to deliver and support Office 365 ProPlus updates, starting in September.

Microsoft also extended support 50% from 12 months per update to 18 months. The additional six months means your IT team can choose to push updates just once or twice a year.

Office 365 update channels, showing the new update channel names and release cadence

The twice-a-year feature updates will be named Semi-annual Channel (Pilot) and Semi-annual Channel (Broad), each describing how Microsoft envisions them being deployed in the enterprise. Most people will probably just refer to them as simply “Pilot” and “Broad”.

You can get more information here.