Category Archives: Maintenance
SQL Server Migration Assistant Problem Solved
Having used SQL Server Migration Assistant for a major Oracle conversion project over the last year, I quickly grew aggravated with its project limitation involving a single database. In other words, if you setup the project file in SSMA for staging and then you are unable to point it to an acceptance environment to migrate to that environment. That can be very frustrating especially if you have customized many of the project settings. I have discovered two workarounds to resolve this problem.
- Backup (or copy) the project folder (c:\Users\<<Username>>\Documents\SSMAProjects\<<Project Name>>). Close SSMA and then delete the target-metabase.mb file in the project folder. Now open SSMA and now you can connect to a new SQL Server and/or database. You would want to copy the folder if you want to retain the settings for a different environment such as staging, acceptance, production, etc. If you rename the folder for a different environment, make sure to rename the *.o2ssproj file to match the folder name. This is the file that opens inside of SSMA.
- The second option is to create a new project using SSMA and then copying in the object-containers.mappings, project-container.mappings, and finally preferences.prefs. These are the project settings that you have painstakingly setup in your original project.
I hope this helps. Enjoy!
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);
Online Restores
I was doing some mentoring today with online restores, so I thought I would share it with my blog readers. The most important thing about online restores is that it is only supported in SQL Server 2005 and later versions. In addition, for the bulk-logged recovery model the following conditions must be met in order to complete an online restore:
- All of the log backups must have been taken and completed before you start the online restore.
- If you have bulk changes in the database, the files must be online.
- All of your bulk changes must be backed up before starting the online restore process.
In addition, I have seen it recommended that you switch your bulk-logged recovery model to the full model before starting the online process and then switching it back afterward for smoother recovery. Enjoy!
Backup and Recovery Documentation
Today I began documenting my backup and recovery strategy for my servers. If you have not documented your strategy, take my advice and do it. This should include the following items:
- Retention schedule
- Recovery models
- Disaster recovery plans
- RPO and RTO or SLAs
- Identify your backup schedule
- Identify your recovery schedule
- Identify offsite storage options and retention
- Identify your compression strategy
- Identify any third party vendor tools
I hope this helps. Enjoy!
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!
SP_SpaceUsed for Disk Space Monitoring?
Recently, I heard about a couple of developers that were using the stored procedure SP_SpaceUsed to monitor available disk space. They had requested additional disk space because the utility had told them that there was only 4mb of unallocated space. Try not to laugh because these could be YOUR developers.
Last month I blogged about using the procedure to show you the estimated backup size for the database. But these developers were confusing unallocated space, which is actually just space in the database that has not been allocated. For example, when you set a autogrowth interval and your database grows in accordance with the interval you will now have unallocated space until the database takes that free space. Enjoy!
| database_name | database_size | unallocated_space | |
| <Database Name> | 2449.81 MB | 40.45 MB | |
| reserved | data | index_size | unused |
| 2396472 KB | 2386856 KB | 5144 KB | 4472 KB |
HyperBac No More
In case you missed it last week, Red Gate announced the death of the HyperBac tool for backup and recovery. I am not surprised by this move as the features were pretty much the same as the Backup Pro tool also offered by Red Gate. When I setup a test case for the tools in comparison with Backup Pro and native compression, it consistently had the worst compression ratios.
However, I was a little sad about the death of the Virtual Restore piece of HyperBac. This tool had great potential, especially since we just figured out how to script it to autoMAGICally restore all of our backups on a nightly basis. Maybe they will be able to incorporate this piece into the Backup Pro tool. Only time will tell, I guess. Enjoy!
Restoration Recovery State Options
(Editor’s note: I just discovered that this blog did not post on 2013-Feb-06 as it was originally intended)
Yesterday, I was asked what the difference was in some of the restoration options. Thus, I thought this would make a good blog post as it is somewhat confusing in the beginning.
On the options page of the restore database interface in SQL Server Management Studio, there are several options than can be quite frightening to new DBAs. I have tried to explain them here in the order that they appear on the screen.
In the Recovery State options:
- Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY): Choose this option only if you are restoring the log files now (in full or bulk-logged recovery models). This is the default option and allows you to only go back to the last backup file.
- Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY): Pick this option when you want to go to the very latest possible data! Leaves the database in a restoring mode as you restore the backup and then all of the transaction logs generated since the backup up to the point of failure (if possible).
- Leave the database in read-only mode. Undo committed transactions, but save the undo actions in a standby file so that recovery effects can be reversed. (RESTORE WITH STANDBY): The database will be left in read-only mode. At this point you can still apply transactions logs and is generally used when it takes too long to restore the system to a full database and you wish to use the server as a stand by server.
I hope this helps, enjoy!
Need to Predict Backup Sizes?
Someone showed me a neat trick a little while ago that I thought I would pass on. The problem is I wrote down the trick but not who told it to me, so if you are reading this and you think it was you who told me please let me know and I will cite you properly. I apologize for this transgression but I thought the tip was appropriate given my recent posts concerning backup and recovery and I was searching through some old notes looking for something to blog about tonight.
Have you ever added a new database to your server and thought to yourself, hey do I have enough space on my backup drive to cover however many days you are backing up? Here is an easy way to get a general idea for an uncompressed backup size. If you are running compression, then it will obviously be less but this is an estimate.
USE <Database Name>
GO
EXEC sp_spaceused @updateusage = ‘TRUE’
Which for a particular database on one of my servers it returned:
| database_name | database_size | unallocated_space | |
| <Database Name> | 2449.81 MB | 40.45 MB | |
| reserved | data | index_size | unused |
| 2396472 KB | 2386856 KB | 5144 KB | 4472 KB |
The actual uncompressed backup for this database was 2,317,543 KB and the reserved data from the stored procedure was 2,396,472 which is pretty close to the actual backup. Not bad, huh? I hope this helps.
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
SQL Swampland 