| Author |
Topic |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2008-06-19 : 09:26:01
|
| I have this large DB around 250GB. In which 70% of the space is occupied by one single table. This is not a high transaction DB, it has only a few users. Backup is taking 2:30 hours!Is there a way I can reduce this time? In desperation, I am thinking of doing, 1. detach the DB (off peak hours)2. copy .mdf and .ldf files (backup)3. attach the DB Any suggestion ?------------------------I think, therefore I am - Rene Descartes |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-19 : 09:36:23
|
| While you doing backup, it is better to spread accross files in different drives. Well,attach/detach is the fastest method but that takes your DB offline . Its always good to have backup before attach/detach. |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2008-06-19 : 09:42:12
|
That is normal if you create the backup over a network to another server using the native sql backup command (based on my humble experience with VLDBs).You may reduce the time by creating the backup file locally. And start maintaining the size (archive old data?).quote:
Originally posted by ravilobo
I have this large DB around 250GB. In which 70% of the space is occupied by one single table. This is not a high transaction DB, it has only a few users. Backup is taking 2:30 hours!Is there a way I can reduce this time? In desperation, I am thinking of doing, 1. detach the DB (off peak hours)2. copy .mdf and .ldf files (backup)3. attach the DB Any suggestion ?------------------------I think, therefore I am - Rene Descartes
--------------------keeping it simple... |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-06-19 : 09:50:57
|
quote:
That is normal if you create the backup over a network ....
..nothing is on the netwrok. Everything is local! Local backup is taking 2:30 hours!------------------------I think, therefore I am - Rene Descartes |
|
|
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-06-19 : 10:08:33
|
| Why can not you go with SQL Litespeed It compress the file and also not take much time.ManojMCP, MCTS |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-06-19 : 10:14:14
|
| How expensive is it?------------------------I think, therefore I am - Rene Descartes |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2008-06-19 : 10:28:21
|
Are you getting high CPU when the backup runs? Maybe it's the server not coping up with the additional load?We are experiencing the same but on network directories and our infra guys suggested we modify the block sizes on the disks. Today they are not optimized.It would be interesting to know if someone can comment with regards to server performance degrading when a backup job is running and how to fasten the process.In 2008, I heard they have added compression capabilities. There's potential there.HTHquote:
Originally posted by ravilobo
How expensive is it?------------------------I think, therefore I am - Rene Descartes
--------------------keeping it simple... |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-06-19 : 10:35:23
|
quote:
... we modify the block sizes on the disks.
what block size?------------------------I think, therefore I am - Rene Descartes |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-19 : 11:48:41
|
| Ravilobo,Did you read what I posted? |
|
|
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-06-19 : 11:51:02
|
quote:
Originally posted by ravilobo
How expensive is it?------------------------I think, therefore I am - Rene Descartes
It Cost aound 1500-1700/year.ManojMCP, MCTS |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-06-19 : 12:31:47
|
| I assume it is 1500-1700 USD!/year. It looks like ayearly lease...------------------------I think, therefore I am - Rene Descartes |
|
|
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-06-19 : 12:57:41
|
| Yes, It's on yearly basis. Check the Prices on Quest side.ManojMCP, MCTS |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-06-19 : 13:07:35
|
| That is Sad! My company won't go for it!------------------------I think, therefore I am - Rene Descartes |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-06-19 : 15:23:54
|
| There are other, somewhat less expensve third party tools that are equally as good: Idera (SQLSafe - didn't see a cost) and RedGate (SQLBackup $795 USD) to name a couple of others. I believe with all of them, you'll have annual maintenance fees but I think that it's less than the purchase cost.....Download eval copies and prove the the products out to your superiors to try and get them to change their minds.Terry |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-06-19 : 15:30:29
|
| What I don't understand is : how come third party backup tools are faster than sql server itself?------------------------I think, therefore I am - Rene Descartes |
|
|
|
DMcCallie
Yak Posting Veteran
62 Posts |
Posted - 2008-06-19 : 15:42:56
|
| I have found the best disk performance on my SAN by deleting and re-defining the disk partition (where SQL Data is located) using Diskpart.exe and the Align=64 parm, then formatting the partition under Disk Manager with NTFS and Block Size = 8192k. I can push the disk subsystem to 200 GB/hr after making this change (before this change the disk would top out around 60 GB/hr)...-------------------------------------------------------------------------------------------------1. Stop SQL or Detach the Database(s) on the drive/partition.2. Move the data from the partition to another drive if you don't want to loose any data (or back it up to tape).3. Run Windows Disk Management and Delete the Partition.4. Run Diskpart.exe from a command prompt:4a. List Disk (to list all Disks in the system)4b. Select Disk X (to select the partition that you want to work with)4c. List Partition (to list any existing partition information on the selected disk - it should be blank after you deleted the partition)4d. Create Partition Primary Align=64 (to create the partition and set the disk offset/alignment to 64k)4e. Assign Letter = <drive letter> (to assign a drive letter to the newly created partition)4f. List Partition (to list the information on the newly created partition)4g. Exit (to quit Diskpart)5. Run Windows Disk Management:5a. Format the newly created Partition using NTFS and select the Block Size as 8192k5b. Don't use the Quick Format option6. Copy the data back to the partition7. Add the SQL/Server Service Account User to the MSSQL directory's Security Tab (or any other directory security needed)---------------------------------------------------------------------------------------------note: You should test this in you environment! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-19 : 15:46:09
|
quote:
Originally posted by tosscrosby
There are other, somewhat less expensve third party tools that are equally as good: Idera (SQLSafe - didn't see a cost) and RedGate (SQLBackup $795 USD) to name a couple of others. I believe with all of them, you'll have annual maintenance fees but I think that it's less than the purchase cost.....Download eval copies and prove the the products out to your superiors to try and get them to change their minds.Terry
Not sure about SQLSafe, but the Red Gate tool compresses the file at the end instead of during the backup. SQL Litespeed is a better option as a result since it compresses during the backup.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-19 : 15:47:21
|
quote:
Originally posted by ravilobo
What I don't understand is : how come third party backup tools are faster than sql server itself?
Because SQL Server does not compress the backups. This changes in SQL Server 2008 though. I'm not sure why we would need a third party backup tool in 2008 when the native backup can compress. This is what I've heard at least.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-19 : 15:48:11
|
| Tara,What about CPU Usage while doing backup for VLDB during production time in SQLLitespeed? Do we have to take it into consideration? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-19 : 15:51:08
|
| I've never had any problems with SQL Litespeed taking up too much CPU. LS has less of an impact than native backups since it backs up faster. On my systems that are using LS, we saved 75-90% in disk space and 75% on backup time. The product pays for itself as a result.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
|
|
Next Page
|
Large DB backup