Post reply

tempdb log file size excessively growing

  • October 30, 2007 at 7:36 am

    #69276

    Hi,

    I have a SQL Server 2k SP4 installed on a Win 2k3 SP2 used for a Blackberry server. The tempdb log file is keep growing at some point been 14 GB. At that point I issued a

    backup log tempdb with truncate_only

    and shrink the database file without any problem, so there seems to be no long running transaction.

    Also running

    DBCC OPENTRAN('tempdb')

    doesn't show anything, the output been

    "No active open transactions."

    For now I have an hourly job that is running "backup log tempdb with truncate_only", but I would prefer to know what might cause the issue without having to restart the server which indeed might clear the issue. Any idea is highly appreciated.

  • October 30, 2007 at 8:43 am

    #746669

    Excuse the ignorance, but do you have any idea what exactly does the blackberry server store in tempdb? I've never had the occasion to need to deal with it...

    Are your users transferring alot of files that might need to be sorted etc in tempdb that might be causing some issues? Perhaps a Lot of insert/updates to temp tables in the db?

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • October 30, 2007 at 9:29 am

    #746700

    Hi Luke,

    Thanks for reply, I can take a look and see what users are doing, but regardless of what they are doing once a transaction is ended the checkpoint process should clean up the transaction log, and since I can truncate the log there is no long running transaction. In othere words there should be no substantial log file increase, plus I am looking at the data file and is under 1 MB.

  • October 30, 2007 at 9:52 am

    #746717

    I am running

    select * from sysindexes order by rowcnt desc

    and the rowcnt (number of data rows in the tables) is

    11158707 syscolumns

    10191652 sysobjects

    10191511 sysindexes

    and increasing fast. It looks like SQL Server is creating objects in tempdb whithout droping them.

  • October 30, 2007 at 10:27 am

    #746742

    Most SQL servers that host Blackberry database have issues that require a reboot. They create large tempdb databases and they don't clear up connections properly. Eventually, the server runs out of memory. I chalk this up to poorly written software that leaves the DBA rather helpless.

    I make sure to house the Blackberry databases on their own servers so as not to interfere with other critical database servers in the enterprise.

  • October 30, 2007 at 10:45 am

    #746750

    Thanks Janet

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply