Script all Agent Jobs Using SQL Server Management Studio

Download PDF

While doing a recent server move, I came across the need to script all agent jobs and save them to a file so that if we needed to reference what was there after the move we could get at the agent jobs without having to restart the old SQL Server.

It made me realize that sometimes what you want to do is available in SQL Server Management Studio, but that perhaps it is not always entirely obvious.

 

In the example below, where I have four jobs (imagine if it was 150 jobs), I want to script all of them out and save them for future use.

I could just right click on each one, choose “Script Job As, CREATE To, New Query Window”, which wouldn’t be that bad for 4 jobs, but assume you need to do this for 150 jobs, it could take you a while.

Read more ›

Posted in Uncategorized Tagged with: , , ,

Backup and Restore – Pass Chapter Presentation

Download PDF

Yesterday I had the opportunity to practice one of my sessions on Backup and Restore that I will be presenting at PASS Summit this year. I presented it at two different SQL Server Users Group meetings, one in Bellingham WA, and one in Hartford CT. The one in Hartford was a remote presentation.

This is an introductory level presentation targeted at anyone who is responsible for backup and recovery of their SQL Servers.

Here is an example of where I was explaining what Recovery Time Objective (RTO) and Recovery Point Objectives (RPO) are.

RPO and RTO with SQL Server Backup and Restore

In this session we cover how to improve your backup strategy how to improve your Recovery Point Objective, and how to reduce your Recovery Time. Differential backups are often overlooked in a SQL Server backup strategy, learn how to utilize Differential backups and to save disk space.

If you have ever had to restore from backup and your manager has been looking over your shoulder asking “When will it be done”, then this is the session for you.

Backup and Restore

The topics covered in this presentation were:

  • Recovery Point Objectives and Recovery Time Objectives
  • Types of Backups
  • RPO and RTO Scenarios
  • Demo: Backup
  • Demo: Restore
  • Building a Restore Script
  • Changes in SQL Server 2017

One of my favorite items in this presentation is the creation of the automatic restore script. If you can create the restore script at the time the backup is run rather than when you have a failure you can dramatically reduce the time it takes to get that restore started, thus reducing your Recovery Time.

The slides and sample code are available for download here: Backup and Restore _ 9_12_2017.zip.

 

If you are looking for a speaker for your PASS Chapter or SQL Server Users Group, please contact me, I would be happy to present this or other sessions at your users group meeting.

 

See Also:

 

Posted in PASS Chapter Tagged with: , , , , ,

Visualizing VLFs – Another update

Download PDF

A while back a wrote a blog post with a query to Visualize the VLF’s in your database. Today I have an update to that script.

The previously updated script added another column called “TextStatus” to the output to give you a better idea of what the different statuses mean. You now get 3 statuses shown, “In Use”, “Available”, and “Available Never Used”. The If you have lots of VLFs that are “Available Never Used” that may be an indication that your log file may be larger than you need. If you don’t have any that are “Available Never Used” the log may be smaller than you need.

This latest update changed the character shown in the bar chart to a X or an O depending on if the current file is in use.

Read more ›

Posted in SQL Server Tagged with:

Determining how much of your data file has been modified

Download PDF

SQL Server 2017 introduces a new column in the sys.dm_db_file_space_usage system table. The column is modified_extent_page_count, which tells how many pages have been modified in your data file since the last full backup.


SELECT * 
FROM sys.dm_db_file_space_usage; 

What is really interesting about this is that with a little math you can calculate the percentage of your data file that has been modified.  This would be useful when running differential backups to be able to determine if it would make more sense to run a full backup or a differential backup. Basically when your differential backup gets large enough, based on the number of modified_extent_page_count pages, then it may make sense to do a full backup and reset this counter, and get back to smaller differential backups.

Here is an example

SELECT df.name, df.physical_name,
 total_page_count,
 allocated_extent_page_count,
 modified_extent_page_count, 
 100.0 * modified_extent_page_count / allocated_extent_page_count as PercentChanged
FROM sys.dm_db_file_space_usage fsu
INNER JOIN sys.database_files df on df.file_id = fsu.file_id;

 

Now based on the Percent Changed column we can make some assumptions on the size of the differential backup, and decide if we want to do a differential backup or a full backup.

Read more ›

Posted in SQL 2017, TSQL Tagged with: , , , , , , ,

My lucky day

Download PDF

It appears to be my lucky day. Who would have thought that someone I don’t even know would want me to invest $48,000,000 for them. Wow it is so exciting.

 

ok… now that I am through the sarcasm, I am just logging this to share some of the crazy scams that we come across every day.  Sorry Anthony Martinez, but I am going to be unable to take you up on your scam, maybe one of the other recipients listed on the To: line will be able to help you.

Read more ›

Posted in Scam Tagged with:

Using Antivirus with SQL Server

Download PDF

In a perfect world, your SQL Server would be so secure that you would not need antivirus software, you would have behind layers of firewalls, nobody would ever connect with remote desktop to install anything, and it would always have all of the latest security patches… But that is not the real world.

Given that your SQL Server often times contains extremely valuable information, and that the damage that could be done by virus software, malware, and ransomware could be so great then it is strongly recommended that you run antivirus software on your SQL Server. There are some files that you will want to exclude from the virus check.

SQL Server Antivirus Exclusion List:

You will want to exclude the following files from the antivirus check list.

  • SQL Server data and log files (.mdf, .ldf and .ndf files)
    • It is probably a good idea to just exclude the entire directory that holds your data or log files. The reason that I suggest this is that there are other files created in the same location as the data file when certain things run, for instance CheckDB creates temporary snapshot files in the data file directory. Excluding the data and log file directory would be the safe way to go here.
  • SQL Server backup files
  • Full-Text catalog files (if you are using full text search)
  • Trace files (if you have traces running)
  • SQL audit files (for SQL Server 2008 or later versions)
  • SQL query files (.sql extension)
  • The directory that holds Analysis Services data
  • The directory that holds Analysis Services temporary files that are used during Analysis Services processing
  • Filestream data files

 

The problem you run into is when someone installs antivirus on the SQL Server, without being aware of the exclusions, you can run into a number of issues such as the following:
• When the SQL Server is running, data and log files are generally locked preventing other processes from being able to change or read them. This can cause problem with the antivirus software blocking attempting to get to these files.
• If you restart the system, or restart the SQL instance, it is possible that the antivirus software could start first and open a data or log file to scan it for viruses, at which point when SQL Server starts it may not be able to access that file, therefore preventing SQL Server from starting.
• If the antivirus file finds some pattern that it suspects as a virus in your data or log file, and it attempts to quarantine that file it could lead to extreme problems with the SQL Server.
My recommendation is to always run antivirus software on your SQL Server, but be sure to exclude the files mentioned above to avoid problems.

Related Links:

 

Posted in SQL Server Tagged with: , , ,

Database Health Monitor Keyboard Shortcuts

Download PDF

Over the years developing Database Health Monitor I have added a number of keyboard shortcuts to make life easier when working in the application.  Some of these are listed here to share since they are not all completely obvious.

 

Backspace Key – Jump to the last report viewed

The Backspace key can be used to jump to the previous report that you were viewing. Not just that, but hit it multiple times and you can browse back through the history of reports that you have recently viewed. This can be very handy so you can jump to where you previously were without having to navigate through the menus.

 

F5 Key – Refresh the current report

When you are viewing a report and the data behind it may have changed, you can hit the F5 key to refresh or reload the current report. This works for most of the reports in Database Health Monitor.

 

Navigating the Instance Reports

When you viewing any of the instance reports listed above, you can use keyboard shortcuts to quickly navigate between those reports, but to navigate between these you need to use the ctrl key combined with either page up, page down, or one of the arrows on the keypad.

Ctrl+PageUp: If you have more than one SQL Server connected with Database Health Monitor Ctrl+PageUp will switch the current instance report that you are viewing to be the previous instance in the list.

Ctrl+PageDown: With more than one SQL Server connected to Database Health Monitor Ctrl+PageDown will switch the current report to view the next instance in the list.

An example.  If you have 25 SQL Servers connected and you wanted to view the Blocking Queries report for each of these 25 servers, you would first navigate to the Blocking Queries report for one of the instances, then hit the Ctrl+PageDown or Ctrl+PageUp 25 times to navigate through each of the instances looking for any blocked queries. You can combine that with the Backspace key described above and do a very quick review of many SQL Servers.

 

Ctrl+Up Arrow:  Same as Ctrl+PageUp described above.

Ctrl+Down Arrow:  Same as Ctrl+PageDown described above.

Ctrl+Left Arrow: Switch to the previous instance report for the same SQL Server instance. Example: If you viewing the Disk Space report and you hit the left arrow you would go to the Databases by Size report, then hit it again and you go to the CPU Load by Hour report.

Ctrl+Right Arrow: Switch to the next report for the same SQL Server.

 

I hope that this collection of Keyboard shortcuts for Database Health Monitor can give you an improved experience using the application.

 

Enjoy, and have a great day!

-Steve Stedman

 

Posted in Database Health Tagged with: , , ,

2 Sessions Accepted for PASS Summit 2017

Download PDF

I am excited to announce that 2 of the sessions that I proposed for PASS Summit 2017 have been accepted. This is the first year that I have had 2 sessions accepted for Summit, and I am looking forward to it.

The two sessions are:

Basics of Database Corruption Repair

Your database is running fine month after month with no problems. Suddenly someone reports that their query won’t run. They get an error stating “SQL Server detected a logical consistency-based I/O error“, or something even scarier. Do you know what to do now? We will walk through 3 corrupt databases exploring ways to go about finding and fixing the corruption. More importantly we will explore how to prevent further data loss at the time corruption occurs. Learn what things you can do to protect yourself when corruption strikes. Learn how to avoid making things worse, and how to protect your data if things do get worse. You will leave with a checklist of steps to use when you encounter corruption. By the end of this session you will be ready to take on corruption, one database at a time

 

Your Backup and Recovery Strategy

As a DBA you are tasked with running regular backups. How do you know if you backups are working? How do you know if they are not? Do you have the right recovery strategy? Learn why your recovery strategy should be the goal, not a backup strategy. This session will cover Recovery Point Objectives, and Recovery Time Objectives, along with different backup types that apply to specific objectives, including full backups, differential, log and copy only backups. Learn which backup type is right for your needs? Learn what SQL Server 2017 introduces to help with your regular backups. Find out about backup encryption and compression. Find out how to create your restore script when the backup is run, rather than at a groggy 4:00am when the restore is needed. You will leave this session equipped to do backups, and more importantly restores, with confidence.

 

If you are going to be attending PASS Summit 2017, please look me up, attend one of my sessions, or just say hi. For our SQL Data Partners Podcast Listeners, be sure to look me up and say hello, it would be great to meet you.

More information:

See you there!

-Steve Stedman

Posted in Corruption, PASS Summit Tagged with:

Database Health Monitor June 2017 Version Released Today

Download PDF

Today I released the June 2017 version of Database Health Monitor, you can get it on the download page at http://DatabaseHealth.com.  Here is what it included.

Version 2.5.5 Release Notes

Version 2.5.5 is the June, 2017 release of Database Health Monitor

New Features in 2.5.5

  • Added a Recovery Model column to the Backup Status Report based on a customer request. This applies to the database specific Backup Status Report and the multiple-instance Backup Status Report.
  • Added new File Utilization report that shows the amount of space being used and the amount of space free in your data and log files.
  • Added a check in the historic monitoring “test connection” feature to check for SQL Server Express edition, and display a warning since the historic monitoring uses the SQL Server agent, and there is no agent on SQL Express edition.
  • Added a menu item (under the Help menu) to bring up the license agreement, in case someone wants to read it later. This was requested by a customer.
  • Adding F5 support for refresh on the multiple instance reports. Backup Status, CheckDB Status and File Utilization.

Bug Fixes in 2.5.5

  • Excluded offline databases from showing warnings in red if the database is offline for the Backup Status Report.
  • Fixed a bug in the historic connection test dialog. When attempting to check the connection from one server to another there was an error encountered. This referred to the error “Incorrect Syntax near ‘)’.” This has been corrected.
  • Fixed a bug with the multiple instance reports. Hitting the ctrl + left or right arrow keys caused a crash.
  • The option to generate a script to rebuild all statistics needing rebuilding was not working correctly in SQL Server 2005. This has been fixed and now works.
Posted in Database Health Tagged with: , , , , , , ,

Database Health Monitor – May 2017 version released today

Download PDF

Today I had the opportunity to release the May 2017 update of Database Health Monitor. This release is version 2.5.4.

There are some new cross-instance reports available to allow you to check on your backup and CheckDB work on all of the instances that you are connected to with Database Health Monitor.

Version 2.5.4 Release Notes – May, 2017.

Version 2.5.4 is the May, 2017 release of Database Health Monitor

New Features in 2.5.4

  • Upgraded to .net version 4.6.
  • When a new version of Database Health Monitor is available, when you click “yes” to upgrade, it now downloads the installer and runs it rather than redirecting you to the download page. This saves time and makes things easier for updates. Based on a customer request.
  • Additional checks in the QuickScan report for the following:
    • Database Mail not configured
    • Service Broker not enabled on TempDB
    • Added checks for obsolete xp_sqlmaint stored procedure being used in agent jobs to perform checkdb or statistics maintenance. There are better ways to do these.
  • Added the DATA_PURITY option into the CheckDB dialog.

Bug Fixes in 2.5.4

  • Fixed a bug with the Disk Space instance level report crashing on certain collations that use a comma for a decimal separator.
  • Fixed a bug where the table size report was sometimes reporting the wrong number of rows in a table.
  • Bug fix with the backup advisor coming up empty or blank. This was introduced in version 2.5.3, and resolved in 2.5.4.

Visit the Database Health Monitor Download page for the latest update.

Enjoy!

Posted in Database Health Tagged with: , , , ,