Subscribe

What Database is causing my snapshots to be so large?

I know you can get general database trending & size tools from SQL Server - but I was wondering if there was any way from within SMSQL to see the particular sizes of each database backed up. For example, we suddenly get a snapshot backup which is over 30GB in size and are wondering who is causing that to happen?

Thanks!

Reuv

Re: What Database is causing my snapshots to be so large?

You could look at the portion of space on a LUN that's occupied.   There's a sample script using the Data ONTAP PowerShell Toolkit here: http://communities.netapp.com/docs/DOC-6349

J

Re: What Database is causing my snapshots to be so large?

Hi,

This is fine providing each database has its own LUN. If multiple databases share a LUN, there is no way (as far as I know) to find from the filer side which one caused a snapshot to grow.

Typical suspects though are database maintenance tasks (like defragmentation).

Regards,
Radek

Re: What Database is causing my snapshots to be so large?

In 9 out of 10 cases, its the Microsoft SharePoint Database causing abnormal rates.

Re: What Database is causing my snapshots to be so large?

Thinking about this problem again, I thought to monitor the growth of my data files on a daily basis and compare that metric to the size of my snapshots. Then looking at my data I see that my data files don't grow all the time, but the data within the data files changes (like within a log file that is overwriting previous data). That the snapshot will keep track of, all the while the data file itself is the same size. That correct?

Re: What Database is causing my snapshots to be so large?

Yes, thats correct. A database may stay at eg. 100GB filesize, but withing its file, its gonna delete and/or overwrite blocks which will turn into your snapshot change rate.

Re: What Database is causing my snapshots to be so large?

When I create a volume, it's a logical constuct and internally only consumes space for a small amount of metadata.  If I set the volume guarantee to "volume", then the full space of the volume is "reserved" from the space in the aggregate.  Likewise, if I have a LUN in a volume, even though the LUN may be 500GB, if inly 12GB contains blocks that were written to, then it's actually only using 12GB.  It appears to take 500GB from the volume because the space for the LUN is reserved by default.  It's pretty easy to see this in action with System Manager.  Highlight the LUN and then select "edit"  remove the checkbox for "space reserved" and click apply.  You can edit it again and set the LUN back to space reserved without harming anything.

Now, as far as monitoring the space used inside the LUN, that process probably won't work very well.  You can however, easily write a script using the Data ONTAP PowerShell Toolkit to mind the "occupied" size of the LUN in the active filesystem using the get-nalunoccupied size cmdlet.   In fact, you can use the toolkit to learn quite a bit about what's going on inside your LUNs.  Clear-nalunstatistics and get-nalunstatistics in particular would give you a very good idea of what write activity is happening on each LUN.

You can find the toolkit here:  http://communities.netapp.com/community/interfaces_and_tools/data_ontap_powershell_toolkit

J

Re: What Database is causing my snapshots to be so large?

Think you are going to struggle with SMSQL to know what size the database is.  If you look in the backup log files created by SMSQL c:\program files\SMSQL_SnapInfo\Report\Backup\ {may be somewhere else} you can see the information SMSQL knows about as part of the job.  I could not see anything about database sizes here.

SMSQL allows you to run pre and post scripts and these could be used to get the database size from either SQL http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103587 or ask the Windows file system on the host.  Something like this http://www.tech-archive.net/Archive/Scripting/microsoft.public.scripting.vbscript/2009-03/msg00039.html.

The links are just the first google hits I found and have not used them, but you can see the idea.  However if you are using scripts why bother running them from SMSQL...

Hope it helps

Bren

PS

As SQL requires LUNs the filer will only see the data as a single LUN file and how much space it uses total so you must work from the host side.  If you want to see how much change is happening in the databases you could get the size of the TLs from the snapinfo drive.  This will give a good approximation for each database.  (As long as you are not using simple mode)

Re: What Database is causing my snapshots to be so large?

Unfortunately not all of our databases are running in simple mode, and the log files generated will not tell you about the data LUN growing. So we are kind of stuck then not having a clue about who/what is causing these large spikes in snapshot growth.

Re: What Database is causing my snapshots to be so large?

Hi,

As per my original post - the most likely scenario is one (or more) of your databases / hosts runs a regular maintenance task, like defragmentation. This will not cause the data to grow, but will inflate existing snapshots.

Can't you simply ask your DBAs which one of them runs DB / OS defrag on a regular basis?

Regards,
Radek