Blog Archives
Are Those Transaction Logs Empty?
Ever wonder what your usage statistics are for your transaction log files without opening each individual one in the SQL Server Management Studio shrink file dialog. DBCC SQLPERF is a neat little utility that can tell you the size of the log file and the amount of the space occupied by transactions inside the file.
DBCC SQLPERF
You can also use this utility to clear the sys.dm_os_wait_stats and sys.dm_os_latch_stats statistics, in such a manner:
DBCC SQLPERF(“sys.dm_os_wait_stats”,CLEAR);
MAP Toolkit in Action
I have written in the past about the MAP Toolkit (Microsoft Assessment and Planning) and how it helps with licensing issues including core counts. With that being said, it is time for us to renew our Software Assurance maintenance agreement and this was the original reason that I installed the MAP Toolkit a couple of months ago.
This week I rescanned all of my instances to figure out how many cores we had licensed and for which version. I wanted to get all of my documentation ready to go to our contract manager when I noticed a new server that had 24 Enterprise cores that I had never heard of before. My supervisor had never heard of the box either.
When I logged into the box, sure enough it had SQL Server installed. The server was for a monitoring tool and when the administrators installed the software on the box instead of asking me where they could install the database they found a disk and installed SQL Server themselves. Unlicensed. On the same box as the software. Without telling anyone.
When the contract manager told them how much the 24 cores would cost their department they quickly called me and asked me to kindly move their 13 GB database to one of my other servers. It just happened recently because I scan the network every few months and had never seen this before. Not only did they put us in jeopardy with our licensing, they had no backups setup for the database. When confronted they said yeah that was taking up too much space on the disk.
The lesson here today boys and girls is to scan your instances and look for unknown installations regularly. Carry on and enjoy!
SQL Features Discovery Report
While going through my preparation for the MCSA exam I stumbled upon this gem that I always seem to forget about so I thought I would pass it along to you. For a quick an easy way to determine what is installed on an instance of SQL Server, run the SQL Server Installation Center and select tools. Then select Installed SQL Server features discovery report, as shown in Figure 1. This will produce a nice report as shown in Figure 2.
Enjoy!
Figure 1 – SQL Server Installation Center
Figure 2 – Setup Discovery Report
Creating a Red Gate Backup Pro “Virtual Restore” Job Automatically (Part 9)
If you have been playing along at home this month then you know about some of the struggles that I have faced with automating the Red Gate Backup Pro automatically. Well, with the help of Red Gate and some rewriting of their provided script, I now have a script that will work automagically! When I say automagically, I mean that I want the script to run forever without any more interference from me in the same manner that our Ola Hallengren backup scripts work.
If you are not familiar, this tool is excellent for many reasons but one of the reasons that we like it is for the simple fact that if a new database is created, it will be maintained automagically. I do not have to create any maintenance plans or agent jobs each time I add a database to a server. We have several servers that get new small databases all the time and this is a time saver as well as a life saver. Now this scripts will do the same, it will iterate through the database names and restore the latest backup set as a new database with the name ‘_Restored’ tacked on the end of the existing name and place the data and log file in a separate directory to avoid overwriting your existing databases.
Do not worry about failure reporting with the job as Red Gate will send you an error for each database, pass or fail. If you wish to see only the failures then change ‘MAILTO’ to ‘MAILTO_ONERRORONLY’ in the script.
–This script will restore multiple databases where each database has it’s own Full Backup to be restored.
USE masterDECLARE @dbname NVARCHAR(260)
— Add any databases in that you want to exclude
DECLARE cdatabases CURSOR FOR
SELECT name
FROM sysdatabases
WHERE name != ‘tempdb’
AND name != ‘master’
AND name != ‘msdb’
–Set @restorepath to be the path of where your backups are located, in my example this is ‘D:\Backup\’
DECLARE @restorepath VARCHAR(500)
–@filename will need to be modified depending how the backup files are named
DECLARE @filename VARCHAR(500)
DECLARE @restorestring VARCHAR(1000)
–Not needed if running in a SQL job
DECLARE @exitcode INT
DECLARE @sqlerrorcode INTOPEN cdatabases
FETCH next FROM cdatabases INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @restorepath = ‘D:\SQLVMDEV10\’ + @dbname + ‘\Full\’
— @filename will need to be modified depending how the backup files are named
SET @filename = @restorepath + ‘SQLVMDEV10_’ + @dbname + ‘_FULL_*.sqb’
SET @restorestring = ‘-SQL “RESTORE DATABASE [‘ + @dbname + ‘_Restored] FROM DISK = ”’ + @filename
+ ”’ SOURCE = ”’ + @dbname + ”’ LATEST_FULL WITH MAILTO = ”ed@sqlgator.com”, RECOVERY, DISCONNECT_EXISTING, MOVE DATAFILES TO ”G:\VirtualRestore”, MOVE LOGFILES TO ”G:\VirtualRestore”, REPLACE, ORPHAN_CHECK, CHECKDB = ”ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS”, DROPDB” -E’
–If you wish to apply additional backup, remember to change the WITH RECOVERY to WITH NORECOVERY,
EXEC master..Sqlbackup
@restorestring,
@exitcode output,
@sqlerrorcode output–IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
— BEGIN
— RAISERROR (‘SQL Backup job failed with exitcode: %d SQL error code: %d’, 16, 1, @exitcode, @sqlerrorcode)
— END
FETCH next FROM cdatabases INTO @dbname
ENDCLOSE cdatabases
DEALLOCATE cdatabases
This will generate multiple entries in Red Gate Monitor, as mentioned in Part 4 of our journey. In order to prevent these setup a maintenance window on your monitoring tool and run this script only during the maintenance window ensuring that no other jobs run during that time so that you do not miss any important alerts from those maintenance jobs. Here is a link from Red Gate detailing how to set the maintenance windows in Monitor. Enjoy!
Red Gate Backup Pro Continued (Part 7)
If you have been keeping score at home, I have been trying to work out the issues of implementing the Red Gate SQL Backup Pro and Virtual Restore. This series may sound like a bunch of complaints, they are not. I am just working through the issues and hoping to help others struggling and hopefully improve the product if possible. If some of these items are easily fixed, I could not find the information easily on their website otherwise I would not have posted it. Or it could be quite possibly that I am just an idiot.
I was able to resolve one issue from yesterday post, the weekly regular SQL Server copy-only backup. By tweaking the HyperBac Configuration Manager settings for the BAK extension and disabling HyperBac compression (see Figure 1) we were able to get default SQL Server backups running once a week in the midst of using SQL Server Backup Pro for our daily backups.
Figure 1 – HyperBac Configuration Manager
We also noticed a weird error in our SQL Monitor tool when using the Virtual Restore (see Figure 2). The drive is only 300 GB where we store our Virtual Restores. It looks like the Virtual Restores raise a drive space error based upon the “actual” size of the restore instead of the virtual size.
Figure 2 – Drive Space Error in SQL Monitor
Just in case you are wondering, I compiled my long list of quirks and submitted it to Red Gate support. I am confident that they will be able to help as they have been excellent in the past. Personally, I just like to figure things out before I talk with support, which many probably find as a character flaw. Maybe it is male ego or pride but I like to solve problems without help if at all possible. On the other hand, I am never afraid to ask for help, I just want to make sure I have tried everything before hand. Enjoy.
Red Gate Backup Pro Update After A Weekend of Jobs (Part 6)
Last week I blogged about my experiences implementing the Red Gate Backup Pro and Virtual Restore. Today I have a few more observations and items that I need to investigate.
- We setup a weekly copy-only backup using default SQL Server compression to give my supervisor ‘warm and fuzzies’ over switching to a proprietary backup format. We noticed this weekend that the servers with the HyperBac/Virtual Restore tools installed failed on a regular backup. When we manually stopped the HyperBac service and ran the backup, it completed successfully. When we re-enabled the service the backup failed again. We decided to forgo using HyperBac, I may try deactivating that part of the license and see if that alleviates the issue.
- Some of our restores fail when we script them and run fine when we run them manually.
- Some of the restores report DBCC CHECKDB issues and when we run DBCC CHECKDB on the actual database there are no issues.
- I would like to script the real Virtual Restore product, but as of yet have been unable to.
I will keep everyone posted as to my progress. Enjoy!
MAP Toolkit Licensing Continued
Continuing yesterday’s post regarding the licensing issues I discovered using the MAP Toolkit, I was able to resolve the issue without much difficulty. The license was showing in the MAP Toolkit as developer edition. We have volume licensing so I am sure now that wrong installer was selected at the time of install because we do not use keys as they are populated by the installer.
To correct the problem, we ran the installer on the server effected and selected ‘Edition Upgrade’ and selected the media. When prompted for a product key, we had to fool the installer with some trickeration. On a separate VM, we installed SQL Server using our Enterprise media which generated a key code in the box at the same point in the installation. We then typed that key into our real installation and proceeded with the ‘Edition Update’ thus allowing the installer to realize that we were licensed for a different version. This successfully updated our edition to Enterprise as this was the license purchased for this server.
However, after stopping and restarting SQL Server to get the new version to register we noticed that the SQL Agent was failing with no errors noted in the SQL error log or the Agent log. We ran the ‘Repair’ from the installation center (shown in figure 1) to make sure that there was no corruption with the previous edition. That did not resolve the problem. When running the agent from the command line we got a different error that pointed us to the login being incorrect. Somehow running the Edition Update locked out the AD account which is used only by this service on this server so a user could not have been to blame (or should not be able to). Once unlocking that account, SQL Server Enterprise Edition was running smoothly! Enjoy!
Figure 1 – Edition Upgrade
Licensing Issues with the MAP Toolkit
Today, I ran the MAP toolkit (Microsoft Assessment and Planning) to identify our licensing structure and found an unwelcome surprise. The four new cluster nodes I installed earlier this year showed up as developer edition and not enterprise edition. Our administrator who handles the license keys, contracts, and downloads from MS gave me the license key and the ISO. When I pointed it out today, she said there was no key for an enterprise installation. That means she gave me the wrong ISO and/or she did not know it did not require a key back ten months ago. The point of this blog is not to assess blame. But to point out a pitfall in your installation through my lesson learned.
Now the real question is how do I fix it? A friend told me that I could change a registry key, but some others have indicated that I need to do an in-place upgrade. I will keep you posted. Enjoy!
Some Additional Findings on Red Gate Backups (Part 4)
I just wanted to point out a couple of things I noticed after a weekend of Red Gate SQL Backup Pro compressed backups and virtual restores on our development servers:
- The <database_name>_Restored database generates a few SQL Monitor alerts for all of the newly restored temporary databases that are eventually dropped after the DBCC CHECKDB is run against them. On our system we got up to five notifications per database:
- Database file size increased (Custom metric)
- Rate of write transaction/sec increased (Custom metric)
- Custom metric collection error
- Page verification
- Database unavailable
- When using a plain jane AD account to install and run the backups, you need to go into each individual database and add that user to the ‘db_backupoperator’ database role. This is not necessarily Red Gate’s fault as SQL Server does not have this at the instance level (I still do not understand why, but maybe I am just an idiot), however the Red Gate tool maybe should have warned me about this at some point instead of cryptic error message on failure, in my opinion. The Red Gate security model states that the account needs to be part of the sysadmin fixed server role which goes against best practices (if you go further down it does give a workaround like I suggested but I didn’t make it that far the first time I read the page, nor the second).
- There was a few servers that I had difficulty installing the agent onto unless I used an admin account even though they had the ‘log on as a service’ and sysadmin role on the regular account as well as permissions on all of the folders. I could then switch the service back to that account after installation and the service starts fine. They might have been missing logon locally. I will try that on the next server to test it.
I will keep this thread running as it appears there are some other issues. Enjoy!
Finalizing SQL Backup Assessment (Part 3)
After testing HyperBac yesterday in our SQL Backup tools series throughout this week, we realized that SQL Backup Pro is the way to go. The HyperBac compressed backups were not smaller than either SQL Server native compression or SQL Backup Pro. However, all three integrated well with our Ola Hallengren maintenance scripts. We also decided to do one native backup a week for disaster recovery purposes in case we cannot get the tool to work following a disaster. With a proprietary tool, my manager felt better in a DR scenario having at least one native backup, so ‘we gotta do what we gotta do’ to keep everyone happy.
I have recommended to Red Gate that they add more automation into the scheduling of Virtual Restore jobs to grab all of the databases in the job instead of individual jobs for each database. That I think is the only area I found to be lacking in the tool.
Enjoy your weekend.
SQL Swampland 