Please help me with some database questions

[jhollender]

First of all, let me say that I am not a DBA. I've worked with MySQL databases a bit in the past, but I'm new to MS SQL. I inherited this database, and it was never set up properly. The issue that I'm running into has to do with database size and backups.

After doing a little research, our database is in Full Recovery mode. The AbleCommerce LDF (transaction log, right?) is 447.8 GB! Then, the AbleCommerce MDF file is 6.6 GB. The properties on the AbleCommerce database show that the Initial Size for the data is 6501 MB, set to autogrow by 1 MB, unrestricted. The log has an Initial Size of 437356 MB, set to grow by 10% restricted to 2097152 MB. I ran a backup of the transaction log, and compressed it is still 116 GB. I thought that running a backup would reduce the log size, but it didn't. I ran DBCC SQLPERF(logspace), and it is showing that the AbleCommerce log size is set to 437355 MB, but only about 0.5% of the log space is being used. Would it be safe to reduce the initial size of the log?

I need to do backups of our database, but that is almost impossible due to the size. Can someone please help me out?

Should I set aside a time period to change the database from Full Recovery to Simple? Is it safe to truncate or shrink the transaction log?

[Katie]

Should I set aside a time period to change the database from Full Recovery to Simple?

The answer depends on what you might need if a restoration is required. The Full Recovery will allow you to restore a DB from any point in time, and the Simple will allow you to restore from one of your backups. As long as you backup the database each night, then Simple is typically acceptable.

I'm guessing that you are having another issue. Please see this FAQ at our help site - http://help.ablecommerce.com/index.htm# ... enance.htm

I don't know what version of AbleCommerce 7 you are using, but your db should not be so large unless you have millions of users, orders, and/or products. This means that you might have a problem with maintenance. If you can review the entire document that I linked above, then you will have a better idea if this is the problem.

Hope this helps, and let me know if you have any questions.
Katie

[jhollender]

I had already gone over that page before. We are using AbleCommerce 7.0.7. The Anonymous User Maintenance is only set to 3 days right now:

Anonymous User Count: 359880
Oldest Record: 3 days @ 3/29/2015 2:01:15 AM

Affiliate Anonymous User Count: 0
Oldest Record: n/a

There aren't currently any logs for Page Tracking in our store either.

[jguengerich]

I'm pretty sure that if were to do another transaction log backup, it would be much smaller, and would only contain transactions since the transaction log backup you already did. You mentioned that now only 0.5% of the log file space is being used. If you want to free up disk space, you should be able to shrink the log file now and recover quite a bit of space. I seem to remember reading somewhere that under certain circumstances, you need to do two transaction log backups in a row before the shrink will actually make the file much smaller. Something to do with which part of the log file has the new data in it. Info about shrinking is here: https://technet.microsoft.com/en-us/lib ... 05%29.aspx.

EDIT: If you do a transaction log backup relatively frequently (in addition to your full backup), it should prevent the transaction log from growing that large again.

[Katie]

In case you are curious which tables contain the most data, you can run this script on the DB. I'm surprised that you still have a 6.6 GB data file. That is a lot!

create table #TableSize (
Name varchar(255),
[rows] int,
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255))
create table #ConvertedSizes (
Name varchar(255),
[rows] int,
reservedKb int,
dataKb int,
reservedIndexSize int,
reservedUnused int)

EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused '?'"
insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2),
SUBSTRING(data, 0, LEN(data)-2),
SUBSTRING(index_size, 0, LEN(index_size)-2),
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize

select * from #ConvertedSizes
order by reservedKb desc

drop table #TableSize
drop table #ConvertedSizes

[jhollender]

In case you are curious which tables contain the most data, you can run this script on the DB. I'm surprised that you still have a 6.6 GB data file. That is a lot!

create table #TableSize (
Name varchar(255),
[rows] int,
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255))
create table #ConvertedSizes (
Name varchar(255),
[rows] int,
reservedKb int,
dataKb int,
reservedIndexSize int,
reservedUnused int)

EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused '?'"
insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2),
SUBSTRING(data, 0, LEN(data)-2),
SUBSTRING(index_size, 0, LEN(index_size)-2),
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize

select * from #ConvertedSizes
order by reservedKb desc

drop table #TableSize
drop table #ConvertedSizes

Here is what my results look like

Capture.JPG

[Katie]

It certainly appears that your maintenance is working fine because the ac_Users and ac_Addresses tables are nearly equal. You have a 877 k order notes, which is a lot. So, it seems that everything is fine. You just need to decide about simple or full backup.