Is this some structural problem?

Hello-
We were on SQL 7.0 / Windows NT and have moved to SQL 2000 sp4a / Windows
2003. Since upgrade, we have seen some strange behavior.
Following are some of the observations so far:
1. We have witnessed exceedingly large database restoration timings. With
SQL 7.0, it used to take around 1.5-2.0 hours to restore database sized 125+
GB (with around 70 GB of data... we had some fragmentation problems!). Now,
the whole process takes from 4 to 9 hours. The restoration times are very
large when restored the first time (true, I am trying to create a database as
well).
2. We are using a vertical solution that used to have high level of
fragmentation. The fragmentation used to be so high that it used to increase
data device to 122+ GB with actual data of ~ 65+ GB. This forces me to
rebuild indexes and run the maintenance DBCC on every weekend. Since
upgrading to SQL 2000, this problem has reduced a lot. But, the maintenance
job timings since upgrade have also increased by 50% (it used to take ~ 8
hours and now the job is taking ~ 12 hours).
3. On at least two occasions (the latest on last Friday), the database has
generated exceedingly large transaction log backups. True, the application
system administrator has told me there was a runaway process that might have
created exceedingly large logs. But, the backups were so huge (+61 GB) that
it filled up the whole backup drive resulting in failures for other
transaction log backups till I cleaned the drive and took a full backup. The
normal hourly transaction log backups have total of size of 600+ MB (624 MB
as of now).
My question is: Am I seeing some kind of structural problems and has anyone
encountered similar problems?
Please share your experiences and expertise.
--
Regards,
MZeeshanHello Mzeeshan,
Before going any further, I'd like to confirm if the current transaction
log backup is larger than before and if you are restoring from the
transaction log backup. If yes, it is normal that it take more time to
restore.
The reason why the log backup is larger than before is that you run DBCC
command to rebuild indexes every week. Reindexing can cause the transaction
log grow much.
Because of the changes in the recovery model in SQL Server 2000, when you
use the Full recovery mode and you run DBCC DBREINDEX, the transaction log
may expand significantly more compared to that of SQL Server 7.0 in an
equivalent recovery mode with the use of SELECT INTO or BULK COPY and with
"Trunc. Log on chkpt." off.
Although the size of the transaction log after the DBREINDEX operation
might be an issue, this approach provides better log restore performance.
The following article has addressed the similar issue:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
If you only rely on full database backups (not transaction log backups),
the recovery model can be changed to Simple to have a smaller transaction
log file. To do this, please follow these steps:
1. Start SQL Server Enterprise Manager (SEM).
2. Expand a server group, and then expand a server.
3. Expand Databases, right-click the database to modify, and then click
Properties.
4. Click the Options tab.
5. Select "Simple" from the Model list under the Recovery section.
6. Click OK.
You can also run the following command to set the recovery model to Simple:
ALTER DATABASE database_name SET RECOVERY Simple
Note: Replace database_name with your database's name
Note: If you set the recovery option of the database to SIMPLE, the
transaction log is set to be truncated at every checkpoint and thus
prevents the log from filling up. However, with the Simple Recovery model,
the database can be recovered only to the point of the last database backup
and not to the last transaction and you cannot backup the transaction log
since the sequence of transaction logs is not being maintained. Also, you
cannot restore the database to the point of failure or to a specific point
in time. To do that, you need to use the Full Recovery or Bulk-Logged
Recovery model.
After that, run the "dbcc shrinkfile" command against the transaction log
to shrink the log file.
For more information on shrinking the transaction Log, please refer to the
following article:
272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
<http://support.microsoft.com/?id=272318>
Please understand that the latest SQL Server 2000 Service Pack is Microsoft
SQL Server 2000 Service Pack 3a instead of SQL 2000 SP4a. Please clarify
the Service Pack you installed.
In addition, I want to let you know that performance issues can be caused
by various factors, and it is difficult to locate the root cause in a
newsgroup thread. If the issue still exists after you have used the
troubleshooting steps above, to efficiently troubleshoot a performance
issue, we recommend that you contact Microsoft Product Support Services and
open a support incident and work with a dedicated Support Professional.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
I hope above information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.