Want to minimise the size of backup DB and increase the performance.

Hi,
I dont know if i am putting the post in write section.
The senario is
I have a huge Database where huge number of tables and millions of records in table. We have a maintainence plan of having a daily backup of database with hourly transational backup.
Recently we had added nonclustered index for Tables to increase the performace and this did reflect the huge change in our site performance. But the major problem is, the size of the DB is getting huge day by day with transational logs.
Could you suggest me some solutions so that we can minimise the size of DB backup and also should not hamper the performance of the DB.

use the index option sort_in_tempdb =true to maintain all indexes in tempdb not in the database.

increase the size of tempdb

I am sorry, but i am not an much experienced guy in SQL. Can you please give me steps or in detail knowledge on this, surely this will add up in my knowledge bank

exampleUSE [AdventureWorks]
GO
ALTER INDEX [IX_Employee_ManagerID] ON HumanResources.Employee
REBUILD WITH ( SORT_IN_TEMPDB = ON )
GO
increase the size of datafile tempdb from the SSMS tool (server managemnt tool) to be
25% of your user database(to enhance performance).
Try to do the following tasks:
1- shrink your database database
DBCC SHRINKDATABASE(N'AdventureWorks' )
2- rebuild index
note the size of database after , before rebuilding index

Hi Satya,
Thanks for quick response[]
We currently having a backup size of 6GB and transactional backup of 12 GB to 15GB. Before adding indexes to our site, we use to have the backup size up to 2 to 4 GB, but after applying indexes for performance size is expanding daily.
No we do not have disk free space issue, but if it continues to grow in this way it will surely encounter this problem in future.

Hi Sumit,
What version of SQL Server you are using,

• If you a using SQL Server 2008 you can use, Backup Compression (SQL Server) feature of this version. http://technet.microsoft.com/en-us/library/bb964719.aspx
• If you are using previous version of SQL Server, you can use some third party tool for your backup and restore operation(which allows compression) for example LiteSpeed http://www.scriptlogic.com/products/litespeed/

One point to note here is, though you need less storage space because of backup compression but it takes more CPU cycle. So you would need to do a trade-off between these two.
Hope it helps.

Hi Arshad,
Thanks for reply.
We are using SQL Server 2005.
It is nothing to do with CPU cycle. We have jobs running for daily db backups and for transational backups, so i dont think there is any thing to do with CPU. I am only concerned with the backup size of DB and the performance of the site(not hamper the sites performance).

Hi Sumit,
Can I have these details to understand your scenario in details:

• Recovery model for the database
• Time and frequency of Full backup
• Time and frequency of Differential backup if any
• Time and frequency of Transactional backup

Thanks Arshad.
See my comments below.

• Recovery model for the database : Full
  • Time and frequency of Full backup : After every 24hrs
    • Time and frequency of Differential backup if any : No
      • Time and frequency of Transactional backup : After every hr.

Hi Sumit,
It seems your transaction logs are not being truncated. I found few links which discuss of similar scenario and its resolutions.
http://social.msdn.microsoft.com/fo.../thread/98a70bc6-7fbe-4b1d-8c8e-90ca0639a884/
http://social.msdn.microsoft.com/fo.../thread/382a5c09-d27c-4d81-9c15-2ee96c697fd5/
http://support.microsoft.com/kb/62866
Currently I am little busy on my current assigments, once I am free I will try to help you with better solutions if I find any. []

Sumit,
Some more pointers, which will help you in troubleshooting the problem.
http://code.msdn.microsoft.com/SQLE...file growth in SQL Server&referringTitle=Home
http://social.msdn.microsoft.com/fo.../thread/c86dd4e3-3169-4d9d-9984-54d1c325a0f3/
Let me know if it woks out, hope it helps.