August 13, 2008 at 12:39 pm
I kept seeing an issue when our full backup is running. The server is SQL 2005-64 bit and the database is a share point content database. The size of the database is 120 GB.
The message what end users see when connecting to the database is something like this:
Unable to connect to database. Check database connection information and make sure the database server is running. --- During this time the databse is up.
The drive's where it's backing up too is not shared by any data or log files. The data files are on K: and M: drives and the backup is been dump to D:\ drive.
Any reason why this strange behaviour?
appreciated any input.
Many thanks,
August 13, 2008 at 3:00 pm
Check the full backup code that is running in background, it shouldn't be changing database access level(to restricted) before performing any such backup operation.
Also, are you able to connect during the time when backup process starts executing?
MJ
August 13, 2008 at 4:36 pm
The backup code isnt changing the database restriction. It's a regular full backup running with init option. thats it.
Additionally users are able to connect to the database but reported the performance was very poor. my understanding was - backup's doesnt leave any performance affect on the system. but looks like my theory was wrong.
anything to look for?
August 13, 2008 at 4:59 pm
How much memory is in the box? How much memory is SQL Server using? If the backup is using the lion's share of the page buffers, the users may be experiencing poor performance because they are having to get everything from disk and not cache.
August 13, 2008 at 6:21 pm
The box has 22 GB and 18 GB is configured to SQL Instance (FIXED) When i looked at the memory utilization - SQL is using all the 18 GB allocated to it.
Any suggestions?
Thanks,
August 13, 2008 at 6:29 pm
backup's doesnt leave any performance affect on the system.
Native SQL Server backups utilize very little CPU resources, but disk I/O is another story. While the drive volumes may be different, are the volumes physically on the same drives? Backing up to the same physical disk as the data drive will certainly affect database performance.
Are any of the disks compressed volumes? That could also potentially impact performance adversely.
Are you using third-party backup tools by any chance? Those will certainly consume more CPU resources, if you're compressing/encrypting your backups.
If none of the above are applicable, I would suggest using Performance Monitor to first find out the differentials between normal operational metrics and operational metrics during the backup, identify the bottleneck(s), and work from there.
Another approach would be to test the various stages of a backup. A native backup reads, stores, and writes data to disk (http://www.sqlbackuprestore.com/backupreadsandwrites.htm). To test if it is the reading and storing processes that's affecting performance, you can backup to a NUL device and measure the system performance e.g.
BACKUP DATABASE AdventureWorks TO DISK = 'NUL' WITH COPY_ONLY
Note: You need to use COPY_ONLY, otherwise you could break any differential restore recovery plans you may have.
If system performance is still acceptable, then it's probably the write process that's the cause of the system performance degradation.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply