Database Health Version 2.2 Released Today
Today I have the pleasure of releasing the next version of the Database Health Monitor application. Currently at version 2.2, I have been working on… Read More »Database Health Version 2.2 Released Today
SteveStedman
Bellingham SQL Server Users Group – Building a Static Data Warehouse Server
Please help spread the word to anyone who may want to attend this free users group meeting and presentation. The November meeting of the Bellingham… Read More »Bellingham SQL Server Users Group – Building a Static Data Warehouse Server
Epic Life Goal Completed: Speaking at PASS Summit – Advanced CTEs
Last week I had the great opportunity to speak at PASS Summit 2015. My presentation was on Common Table Expressions. Here is the sample… Read More »Epic Life Goal Completed: Speaking at PASS Summit – Advanced CTEs
Advanced Common Table Expressions and Recursive Query Technique
Friday morning at 8:00am at PASS Summit 15 in Seattle, I will be presenting my Advanced Common Table Expressions and Recursive Query Technique session. Advanced Common… Read More »Advanced Common Table Expressions and Recursive Query Technique
Transactions Rolled Back in Database
IF you are browsing your error log and come across an error message stating that “1 transactions rolled forward in database ‘msdb'”, or “X transactions rolled back in database ‘msdb'” you might be a bit alarmed. You might also notice similar error messages for master, tempdb or user databases.
How can this be a good thing? Why are transactions being rolled back or rolled forward?
PREEMPTIVE_OS_GETPROCADDRESS and xp_create_subdir
Here is a discovery that I made using the Database Health Monitor historic wait monitoring, on a server with slow storage where the backups were being written.
If you are seeing excessive waits on the PREEMPTIVE_OS_GETPROCADDRESS wait type and xp_create_subdir is the command with the wait, and this is occurring at the time your backups are being run, it is a symptom that the storage location for your backups is having I/O difficulties.
I noticed this on a server with an external USB 2.0 attached hard drive that was being used for backups, and on a second server with a USB 3.0 external hard drive. When the backups run, there was a wait for the process to attempt to see if the backup directory exists, and to create it if it did not.
Read More »PREEMPTIVE_OS_GETPROCADDRESS and xp_create_subdir
Using SQL Server Compressed Backups
The Quick Scan report in Database Health Monitor detects when you are performing SQL Server backups without compression, when the compression option is available. This applies to Full backups, Transaction log backups and Differential backups.
Not using compressed backups? Why not?
Benefits of compressed backups
- Faster backup time
- Faster restore time
- Less I/O at backup and restore time
- Since you are using less disk space, you can keep more backups around.
There are a couple minor drawbacks, the compressed backups take more CPU, not much more, but a tiny bit more. Also the compressed backups don’t compress very much when your database is using Transparent Data Encryption (TDE).
Free SQL Server Training Videos
Over the last year, I have published many videos to YouTube with Free SQL Server Training. You can find these on my YouTube Channel at… Read More »Free SQL Server Training Videos
Max Server Memory – SQL Server
Max Server Memory Setting Explained
If you have more memory than your database and applications on the SQL Server will ever use than this is not a problem, but when you run into memory constraints this setting is much more important.
SQL Server attempts to use as much memory as possible, and when there is no more memory available, SQL Server will use much more I/O due to data and index pages having to be read from disk more often. This works great for SQL Server, but what happens is that SQL Server will take almost all the memory leaving very little for the operating system processes or other applications that are running.
The default for this setting is 2147483647 which is probably more memory that your server has, which effectively tells SQL Server to take as much memory as it wants to.
SQL Server – Dedicated Admin Connection (remote DAC)
In SQL Server there is a special connection that can be used to connect to the database, the remote Dedicated Admin Connection or (DAC). This… Read More »SQL Server – Dedicated Admin Connection (remote DAC)