March 5, 2009 at 12:20 pm
We recently migrated our SQL Server 2005 database from 32 bit SQL Server 2005 to 64 bit SQL Server 2005 and now one of our database log files in Full recovery mode grows at 1.7-2 GB/hour. To migrate the databases, we took a backup of each database on the old 32 bit SQL server, then copied the .bak file to the new server and restored each database to the 64 bit SQL server database. Both databases are Standard edition. Only one of our three databases we moved this way are exhibiting this behavior.
Initially, we thought it was our application that was connecting to the database and for some reason generating a lot of updates, etc, however, the interesting thing is that the database log file continues to grow at the same rate (39 mb/minute) with our application shut down and 0 open connections to it (using Activity Monitor to view open connections). We also shut down all SQL server non-essential services like SSRS and SQL Server Agent and it had no effect.
We can reproduce this behavior by taking a backup of the affected database from our 64 bit SQL Server and restoring on the same 64 bit SQL Server (giving the database/logs a new name & filename). We also ran a trace in SQL Server Profiler when there were 0 open connections (but it was still growing) and there was nothing in the trace.
Also, we setup a Windows Performance Monitor and tracked the Performance Object of SQLServer:Databases and a counter of "Transactions/sec". For the database that exhibits this behavior, we see spikes of up to 10,000 transactions/sec pretty consistently every 10 seconds or so (dipping back to about 0 in between). For the databases that do not exhibit the behavior, the transactions/sec are near 0 as we would expect. Also, when the transactions/sec spikes up to 10,000 we also see a large spike in CPU on the database server, so this metric looks like it is valid.
We tried looking at the transaction log file using the transaction log viewer program – Apex SQL Log 2008 (evaluation version) and it only found 11 transactions in the log file and I think they are related to some activity I was doing in SQL Server Management Studio to look at the log file growth.
We are currently pretty stumped on this one and curious if anyone has any idea what could be going on or what else we could do to troubleshoot this. Not sure if there was a problem moving to 64 bit from 32 bit or if our strategy of doing a backup to file and restore from file was a bad one. Right now, the only potential work around we have come up with is to create a new database and then import the tables from the .bak file (not restore) - we don't get this behavior, although the way we did this last night, we didn't get any indexes or stored procedures (although if that is the only way to make the problem go away, we can probably figure out how to recreate those).
Any help would be very much appreciated.
March 17, 2009 at 11:32 am
After much investigation, we were able to resolve this problem by creating a copy of the problem database using the copy wizard and specifying the "Use the SQL Management Object method (This method is slower but the source database remains online)" option. When we tried copying databases before to fix this problem, we used the detach/attach method and it didn’t work. We also found that using the Copy "Object" method stopped *all* transaction logs from growing at the high rate (we had reproduced in a separate database) and upon bouncing the SQL Server service, the source database of the copy started growing at the old rate of 39mb/min (2.4GB/hr), however the destination database of the copy didn't grow and the high transactions per second stopped as well in the destination database. The copy took about 40 minutes and it appeared to do a lot of work re-organizing the database, which must have fixed the problem somehow. We renamed the original databases and logs to _original and mapped our SQL logins to the new copied databases (which we named our normal names). I detached the 'original' database. I resized the log file down to 1gb and the hourly transaction logs are now 5-7mb as opposed to the previous 2.4gb! Also, the full database backup went from 2.6GB prior to the copy to 1.3GB after the copy, so something about rebuilding from the copy freed up some data somewhere.
Hopefully this information will help anyone else that encounters this same or similar issue. Unfortunately, we don't know the root cause of the problem, but we did notice that this copy method also resolved a potentially related issue where there was a mis-match between the row counts reported in table..properties and the select count(*) from .
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply