2010-09-16 07:14 AM
One issue I have is dropping the drive for one of my SQL Server's, I do not like this behavior. Why would you bring a production system down just fail the backup and send notifications for investigation. I understand this behavior is how the system was designed and I kind of understand in a general since but bringing a database or even SQL server to its knees becasue a backup failed is sad. This is frustrating and on top of this I get calls from my manager, director, and other departments for explanation as to why the system is down and why didn't I prevent it. I do not have access to the filer or anything like that just SnapManger for SQL. I am constantly at battle trying to predict the rate of change and convey this to our network team. If I could get the size of the snapshots I could better see which one is the biggest offender and see if it is always around the same times as well as properly inform the network team a predicted size for snapshots. Most of the databases are set for simple recovery. As it stands now, I get a alert and I have to delete older snapshots hoping I provide enough space and this is not a desired solution.
Anyone know of a way to see the size of current snapshots and only for the server in question? I did see several refereneces to PowerShell which I cant get to work because they hit the filer which I don't have access.
2010-09-16 08:39 AM
the easiest way is on the filer by using the snap list <volumename> command. If most of your databases are in simple recovery mode then you are doing "stream backups". You might find your snap shot deltas to be less if you switched to a full recovery model and separated your transaction logs and database files into separate volumes. It also sounds like you need to size your volume appropirately to handle the retention length of your snapshots. On another note I dont use snap resrve on volumes with LUNs because it's easier to manage the used space a whole. for example if our average change rate per day 7GB and you wan to keep 7 days worth of changes you need 49GB of snap shot space. You also need to make sure your fractional reserve is set appropriately to give new writes space on the disk. By default fractional resreve is set to 100 percent on a volume. You want your fractional reserve to be large enough to handle all of the expected writes between snapshots. If you dont want to use fractional reserve then autogrow needs to be enabled on the volume. HTH