question

tigistkd avatar image
tigistkd asked

Growing backup size

Hello Guys, My backup file got to big and I am having a space issue. The database is set to a simple recovery mode. we have tried to change the size from management studio but the back up file is still big. is shrinking the file the only option I got? And also is there any query to verify that my db has a differential back up or not? Thanks for your help in advance.
backup-compression
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Make sure you know the consequences of switching to SIMPLE recovery. In a disasterr situation you only have the FULL backup, which leasds to some missing data. In the worst case this is the time between FULL backups. Ask your company how much data they could affort to loose, if that's less then 24 hours start implementing logbackups.
1 Like 1 ·
The Issue is now resolved, Killing all the open transaction and changing the log initial size on the db, helped me decrease the log file size.. Helpful link: http://www.techrepublic.com/blog/the-enterprise-cloud/help-my-sql-server-log-file-is-too-big/ Thank you!
0 Likes 0 ·

1 Answer

·
JohnM avatar image
JohnM answered
What version of SQL Server are you using? Are you using native backup compression if available? Shrinking the database will only move pages around, not reduce the size of the backup. As stated by Paul Randal here: http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-3030-backup-myths/ Look item 30-27 Also, you could look at striping the backups (into smaller multiple chunks) if you have multiple target locations (for the files) to support that. Disclaimer: All of the striped backup files have to be intact for a restore to occur. Definitely consider this against your current recovery strategy. This query should help find any differential backups: SELECT database_name , CASE [type] WHEN 'D' THEN 'FULL' WHEN 'I' THEN 'DIFFERENTIAL' WHEN 'L' THEN 'LOG' END AS 'Backup_Type' , backup_start_date, backup_finish_date, recovery_model FROM msdb.dbo.backupset WHERE database_name = '[Database_Name_Goes_Here' AND type = 'I'; --Diff Hope that helps!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks JohnM, My SQL server server is 2014 and I came to find out that I have Active_transaction on a log_reuse_wait_desc, and that's why the log file is not being truncated while I have the database in a simple recovery mode. I have to wait until that transaction is committed and if that doesn't change the size I will probably consider striping the backup file locations. Thanks on the link you provided about shrinking the data file.. that also has a great impact on performance as well.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.