May 10, 2007 at 8:24 am
Hi,
I migrated two 110 Gigabyte databases from SQL Server 2000 to SQL Server 2005 some weeks ago. The server with the SQL2000 databases still exists. It is a data warehouse database, thus data is updated once a day by ETL processes. The data is exact the same on the SQL Server 2000 and on the SQL Server 2005 database.
Database backup needs about 2 hours on the SQL2000 server using a maintenance plan to do a full backup daily. I tried to do a full backup on the SQL Server 2005 Server. I had to stop the process after some hours because no progress was visible. Activity monitor just shows one process in the database with 3 elements doing BACKUP DATABASE on 1 of the databases.
1st one: status is stopped, wait type is Async_io_completion, wait time is very high and rising constantly (over 8.000.000 after 2,5 hours)
2nd one: status is stopped, wait type is backupbuffer, wait time is changing in values in the range of 20 to 90
3rd one: status is executeable, not wait type, no wait time
No users or processes were accessing the database at this time.
The SQL 2005 server is based on a Itanium Dual Core with 2 processors and SATA Raid, and usually all database operations are about 3 or 4 times faster than on the SQL Server 2000 Server. The SQL Server 2000 server is a Xeon with two processors and a slower RAID.
Why is the backup so slow on the SQL Server 2005 server? How could I enfasten this.
What do the wait types mean, how could I avoid them.
Any ideas?
Best regards,
Stefan
SK
May 14, 2007 at 9:22 am
The time needed for the Full Backup on the new system is 6 hours compared to 2 hours on the old system.
I tried to do a differential backup the next day. This needed 4 hours to complete. And for one database beneath all of the data has been written to the backup again, not only the changed.
Stefoon
SK
May 15, 2007 at 1:08 am
You have an IO bottleneck. Review your disk configuration. Specific to this problem, make sure that the backup is being written to a different drive than the log file, data files, system files, or tempdb.
How much RAM does the server have and how much is SQL Server using?
May 15, 2007 at 1:26 am
Hi,
the server has 16 GByte RAM, 6,5 GByte is used for SQL Server DBMS. The rest is used for other components we use. (SSAS, SSRS, IIS) Do you think that increasing the RAM for SQL Server would improve the performance of the backup?
The server has 2 RAIDs. One is used for OS and the Transaction Logs and one for Data/Indexes. But the first on which the OS resides does not have enough space to hold the backup.
Best regards,
Stefan
SK
May 16, 2007 at 1:45 am
Have you stresstested the raid-configuration of the 2005 sql server?
May 16, 2007 at 2:16 am
Hi,
good idea. What would you use?
I heard from a tool called SQLIO. Any experiences, other ideas?
Best regards,
Stefan
SK
May 16, 2007 at 7:35 am
Hi,
This links will help:
http://support.microsoft.com/kb/230785/en-us
"More Green More Oxygen !! Plant a tree today"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply