Explaining SQL Server in plain english
TLDR: Nope.
Keep on doing your full backups.
Make sure that any databases you Log Ship are NOT also doing log backups in your SQL Maintenance Plans, Ola Jobs, etc.
The Backup Chain will not be broken by running a Full backup, and you do not need to use Copy-Only
Proof:
Thanks for reading!
SQL Server maintains a variety of stats about all sort of performance items.
I was trying to troubleshoot some TerriBad * tempdb write performance…almost 3000ms per write, on a server that we recently migrated to.
Our data center vendor of course said the storage was perfect and that we should try troubleshooting SQL Server. (Duh?)
These stats are stored in the sys.dm_io_virtual_file_stats DMF, and I found this gem under the ‘sample_ms’ column description in the result set:
“The data type is int for SQL Server 2008 through SQL Server 2014 (12.x). In these versions, the value will reset to 0 after approximately 25 days of continuous database engine uptime.”
Now, this is a SQL 2017 box, but this tells me that these stats are going to reset at some point.
When I re-ran my IO stalls query…the writes for all 8 tempdb data files are at 7ms. The sample_ms is currently 3,097,351,985 which is about 36 days if I’ve done my math right. Server uptime is approaching 90 days.
The takeaway for you, dear reader, is to know where to find the stats but also to know what timeframe they represent.
* Thanks to Brent Ozar (b|t) for this highly amusing term 🙂
Thanks for reading!
I had a log backup job that was not failing, and was also not backing up logs:
EXECUTE [DBA].[dbo].[DatabaseBackup] @Databases = 'USER_DATABASES. model', @Directory = 'D:\Backups\KBH-Precision_SQL2016', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = NULL, @CheckSum = 'Y', @LogToTable = 'Y'
Older version of Ola. Newer versions give this:
Date and time: 2021-09-28 14:56:12 Server: KBH-PRECISION\SQL2016 Version: 13.0.5026.0 Edition: Developer Edition (64-bit) Platform: Windows Procedure: [DBA].[dbo].[DatabaseBackup] Parameters: Removed for cleanliness Version: 2020-12-31 18:58:56 Source: https://ola.hallengren.com The following databases in the @Databases parameter do not exist: [USER_DATABASES. model]. Date and time: 2021-09-28 14:56:12 Completion time: 2021-09-28T14:56:12.6474426-05:00
Watch those typos, kids!
Thanks for reading!
GroupBy is a different sort of conference as it has been online since it began in 2019 (Thanks Brent! (b|t)) Sessions are voted on by the community with the top vote getters being accepted.
As part of our Virtual Group sponsorship we get to present a session, so we asked the first-runner up in the vote tally to “guest present” for us.
Jeff Moden (L), Mr. “NO RBAR” himself will be presenting “Black Arts” Index Maintenance – GUIDs v.s. Fragmentation – They’re not the problem… WE ARE!” at 22:00 UTC on May 25.
This will be a roughly 30 second “About Dallas DBAs” bit, and the rest is all Jeff.
Session Abstract:
This is NOT your typical presentation on the fragmentation problems of Random GUIDs. No… Instead we’re going to DESTROY THE MYTH OF RANDOM GUID FRAGMENTATION.
In one of the most ironic/heterodoxical turns of knowledge you’re ever likely to experience, we’ll see how THE USE OF RANDOM GUIDS CAN ACTUALLY PREVENT FRAGMENTATION! In the end, you’ll witness the results of some simple testing that clearly demonstrate that you can easily insert literally MILLIONs of rows into a Random GUID clustered index with almost no page splits (not even supposed “good” ones) and LESS THAN 1% Logical fragmentation!
We’ll identify the real problem and the seriously effective yet incredibly simple two-part fix for it. In the process, we’ll prove that Random GUIDs actually behave in a manner like most people expect a good index to behave, especially in but not limited to high performance OLTP environments as well as the benefits of doing so.
We’ll also learn how to use a new tool that I created (included in the ZIP file) to ACTUALLY SEE what an index looks like at the page level for all pages in a single graph.
Then, we’ll use that tool to lay waste to what people are currently calling “Best Practice” index maintenance. We’ll literally see what REORGANIZE does to an index at the page level and why it’s one of the very worst things you can do to your Random GUID and many other types of indexes even if you’re limited to the Standard Edition of SQL Server. We also prove that REORGANIZE CAUSES ORDERS-OF-MAGNITUDE WORSE TRANSACTION LOG FILE USAGE than REBUILD especially for Random GUIDs.
As interesting and totally necessary sidebars, we’ll also see how the use of ever-increasing index keys could be (and frequently is) a major source of many of your fragmentation problems and we’ll also see that NEWSEQUENTIALID may NOT the answer that you’re looking for.
See you there (virtually)!
Thanks for reading!
From time to time, clients of Dallas DBAs ask for help with MySQL and we don’t currently have anyone with that skill set on our team.
I’m looking for someone that can take ad hoc, part time projects when they arise on an hourly basis.
Ideally, an independent contractor/freelancer. Senior level (10+ years)
Specifics:
NO recruiters or 3rd parties at this time.
Email your PDF resume, LinkedIn Profile, etc. to Kevin@DallasDBAs.com
Thanks for reading!
Kevin3NF
SQL Server Security Update: January 12, 2021
Microsoft has issued an important security update that affects all installations of SQL Server 2012-2019. This security update addresses an elevation of privilege vulnerability which can allow data to be sent over a network to an affected SQL Server instance that might cause code to run against the SQL Server process if a certain extended event is enabled. (https://support.microsoft.com/en-us/help/4583468/kb4583468-microsoft-sql-server-elevation-of-privilege-vulnerability)
The ”certain Extended Event” has not been disclosed at this time.
You can read the MSRC Security Update Guide document CVE-2021-1636 for more detailed information, and to learn which specific versions of SQL Server are affected by this vulnerability. NOTE: If you are running an instance of SQL Server 2012 or higher and do not find your version number listed, then your SQL Server version is no longer supported and needs an update to the latest Service Pack and/or Cumulative Update.
We recommend all SQL Server users apply this security update during the next available maintenance window to patch this vulnerability.