September 22, 2005 at 4:40 am
Hi,
one of our clients has been running a DB for the past 3 years on their server. Recently they have started observing an uncontrolled growth in the size of their daily backup (eg. 150 MB in one week)
The additional details I have been able to gather are:
- data and log files set to unlimited growth, with 10% increments
- database properties: size = 915 MB; space available = 14.70 MB
- HDD Size = 30GB; Space Available = 11GB
The backup job is being run daily and contains the following:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 242D59B1-BF8A-4AEF-BB6D-9DD78C31CDE9 -Rpt "E:\Program Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Live_JFMG4.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt "BAK"'
I have suggested to the client the use of DBCC_SHRINKDATABASE and DBCC_SHRINKFILE, but they are not seeing any reduction in either the data or log file size.
Has anyone got any suggestions? Thanks for your help
Mauro
September 22, 2005 at 6:45 am
The only time I've seen this happen is when the backup file gets "appended" as opposed to "overwritten"...
In your post DelBkUps 1WEEKS seems to indicate that the backup file is deleted once a week ?!?! If so, try changing that to daily...
**ASCII stupid question, get a stupid ANSI !!!**
September 22, 2005 at 10:20 am
No, files are deleted daily, but the one which is 1 week old only.
I had couple times the "append" happened (have no idea why) and the backup file was huge! Are they doing some daily inserts which makes the LOG file grows? if so - they need to switch OPTIONS to Simple from full, perform the insert or update, then switch back to Full, Log will not grow then, and test that command line somewhere with the small database - make sure it works the way they need it.
Are they really need to keep ALL backups for the whole week? maybe 2 days will be OK for them?
September 22, 2005 at 10:40 am
Thanks for the feedback, but I do still have some questions.
Vichka, not sure about what you propose. Are you saying that switching the OPTIONS Recovery Model from Simple to Full and back will somehow disable/stop the "append" from happening?
Also, not sure why keeping 7 days or 2 days backups will affect the problem. I have not found anything in BOL which indicates that running xp_sqlmaint will append backups.
September 22, 2005 at 11:26 am
Is the maintenance plan creating a new backup file each time it runs?
I don't use maintenance plans and script my own backup jobs, but this is what I have seen in other posts.
The expire or retain values are really only for backups to tape. They will work if each disk backup is to a different file name. SQL Server has to delete all or nothing, so if the files are appended, the file date changes everytime it is appended to. The default for a backup job is to append and that's what you appear to be doing.
-SQLBill
September 22, 2005 at 11:48 pm
The maint plan shown above will create individual .BAK files, one for each of the 7 days specified, then the oldest one will get deleted. There's no "appending" going on. That's fine if you want to keep 7 .BAK files on disk "just in case".
Changing your DB from Full to Simple, and then running transactions is risky, because you will not be able to recover to point in time if there's a problem with those transactions. You'd have to do a full backup right before you switch to "Simple", so you can recover back to before your transactions ran.
If your database has a lot of free space, that won't get backed up. Your backup file will just be the actual data used.
Keep in mind that people often round up or down when mentally converting between Kb, Meg & Gig, which can throw off your numbers. 146,000 Meg does not = 146 G
Perhaps you just have normal DB growth ? You can run something like the script below to see a history of your backups (and therefore the size of your DB) and how big they have been:
--+++++++++++++++++++++++ DISPLAY BACKUP INFORMATION
-- Display ALL backup info w/ elapsed time ... ORDER BY DATABASE, DATE
SELECT a.server_name as 'Server',
a.database_name as 'Database',
convert(varchar(25),a.backup_start_date,100) AS 'Start Date',
convert(varchar(25),a.backup_finish_date,100) AS 'Finish Date',
DATENAME(weekday, a.backup_finish_date) AS 'Day' ,
datediff(minute, a.backup_start_date, a.backup_finish_date) as 'Mins' ,
cast(cast(datediff(minute, a.backup_start_date, a.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours' ,
case
when datediff(minute, a.backup_start_date, a.backup_finish_date) > 0
then cast(ceiling(a.backup_size /1048576) / datediff(minute, a.backup_start_date, a.backup_finish_date) as decimal (8,1))
else 0
end as 'Meg/Min',
ceiling(a.backup_size /1048576) as 'Size Meg' , --cast((a.backup_size /1048576) as decimal (9,2)) as 'Size Meg' ,
cast((a.backup_size /1073741824) as decimal (9,2)) as 'Gig', -- div by 1073741824 to get gig
a.user_name,a.backup_size as 'Raw Size'
FROM msdb.dbo.backupset a
join msdb.dbo.backupset b on a.server_name = b.server_name and a.database_name = b.database_name
WHERE a.type = 'D' and b.type = 'D' AND a.backup_start_date > '2005-01-01'
group by a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name
order by a.server_name, a.database_name, a.backup_start_date desc
September 23, 2005 at 8:09 am
Has there been any type of database maintenance plan invoked recently? I have seen where after running an INDEXDEFRAG or an DBREINDEX on all tables in the database that the database begins to grow as pages begin to split again. You stated the size of the database and the backup growth. Did you say whether or not the database size is growing in proportion to the backups? If so it sounds like current normal growth. If so you have to ask what development has been completed recently. I am guessing there are less than 5 major tables that everything flows through. You may want to check growth on those using sp_spaceused on a regular basis. I can't believe there is this much discussion over a < 1GB database.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply