Recently I've been asked to investigate the migration possibilities of our Reporting Services environment to SQL Server 2005. Up until now everything has been really smooth to migrate and no real issues have come out of the process.
Yesterday I noticed that our BULK INSERT tasks were taking a lot longer as opposed to the SQL Server 2000 runtime. I am testing this on the same server with the exact same import files and database settings so there has to be a reason why it is taking twice as long as before. Off to investigate!
While checking all possible bottlenecks I came across a big difference and this was the Transaction Log size. While it is sized at 2GB and remains that size on the 2000 instance (Simple Recovery) it grows to 12GB on the 2005 instance.
Adding an explicit TABLOCK to the BULK INSERT statement seems to help. I admit we should have included this in the first place :-) Minimally logged operations do have some prerequisites and this is one of them. Others are that the table is not being replicated, simple or bulk logged recovery model and there are some index restrictions you can check in the books online.
However, this does not explain the difference between the two versions as those rules apply to both versions.
More to follow!
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment