Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2007
    Posts
    7

    Red face Unanswered: Please help newbie

    Hi

    I have two problems. I am currently using SQL Server express and SQL Server Management Studio Express locally. i have a DotNetNuke website running on the web with IHostASP.Net

    Story:

    I noticed that my SQL Server 2005 database with IHostASP.net was 350MB. It was only 22 2 months ago and I have had about 100 users sign up. I then connected to my live database via SQL Server Management Studio Express and shrunk the database. No errors and the database was reduced to 24MB - Great I thought!

    I went and checked the website and all was great for about an hour then the site went down with the following error:

    Error The transaction log for database 'classcampers' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

    Anyone know what to do.

    Anyway, I have downloaded a .bak of my database as supplied by my host. However I cant figure out to restore this file back to my live database. What are the steps involved?

    Do I create a new local database from the .bak and then somhow restore this back to my live one, or do i apply this .bak file strainght to my live database using SQLMSE. Any tutorials becuase I have been trying to apply this restore all day

    Thansk for any advice in advance
    cheers
    Albertramsbottom

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I would suspect you (or the hosting company) have the database set to FULL recovery mode, and there are no transaction log backups happening. Run the following, and we will see if this is so:
    Code:
    select fileproperty(name, 'SpaceUsed)/128, size/128, max_size/128, type_desc
    from sys.database_files
    This will show you what space is allocated to logs, and data respectively.

  3. #3
    Join Date
    Nov 2007
    Posts
    7
    thanks

    but there are a few errors:

    Msg 105, Level 15, State 1, Line 1
    Unclosed quotation mark after the character string 'SpaceUsed)/128, size/128, max_size/128, type_desc
    from sys.database_files
    '.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'SpaceUsed)/128, size/128, max_size/128, type_desc
    from sys.database_files
    '.

    Cheers

  4. #4
    Join Date
    Nov 2007
    Posts
    7
    Ah got it to work

    23 24 250 ROWS
    0 0 100 LOG


    Theres the results

    Cheers

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Sorry about that unclosed quote. So you are probably in SIMPLE recovery mode (to be sure, run select databasepropertyex(db_name(), 'recovery') in the database). In that case, there would seem to be a large transaction somewhere in your code. Perhaps a refresh of several of the larger tables, with multiple updates to the whole table?

  6. #6
    Join Date
    Nov 2007
    Posts
    7
    Yeap it;s Simple

    thanks for help so far, but I have no idea what the rest of what you where talking about was.

    How can this happen from a shrink database command?

    One of the things I have noticed is that if I right click on database and go to properties it suggests that my database is 24mb with 0.65mb free space

    Any ideas

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    This does not happen from the shrink database command. Well, not an hour after the fact, anyway.

    When you make a change to a database, that change is first recorded in the transaction log. Once it has been "hardened" in the transaction log, it can then be committed to the database itself. This is so SQL Server can ensure Consistency in the database. When you restart SQL Server for any reason, SQL Server goes through the transaction log of each database on startup and makes sure that the transactions that are listed in the transaction log are properly committed, or rolled back if they are not. This all happens mainly behind the scenes.

    A transaction log overflowing (which is the error you posted before) is caused by either the transaction log not being emptied out (which is automatic in SIMPLE recovery mode), or a particularly large transaction (update to the data) being run. This could be an insert, a delete, an update, or even a rebuilding of an index. you should look for any update activity immediately before the error began. It could even be that someone switched the recovery model on you, without telling you.

  8. #8
    Join Date
    Nov 2007
    Posts
    7
    Well, my web host restored a backup database and truncated my log file and set the recovery mode to simple and made sure that the database growth was set to 1mb

    All worked this morning but I just tried to install a module for DNN and I get the same error again. i have just ran the following query:

    exec sp_helpdb 'mydatabase'

    and the mydatabase.log.ldf is only 504kb, so where is it full or why isnt it growing

    Cheeers for any help

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    See if your web hosting company also put on AutoShrink for the database. It will be in the output of sp_helpdb. It could also be that the tempdb log filled up. Did your error message specify your database name?

  10. #10
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by MCrowley
    I would suspect you (or the hosting company) have the database set to FULL recovery mode, and there are no transaction log backups happening. Run the following, and we will see if this is so:
    Code:
    select fileproperty(name, 'SpaceUsed)/128, size/128, max_size/128, type_desc
    from sys.database_files
    This will show you what space is allocated to logs, and data respectively.
    This happens more than it does not happen, especially with ASPs and other vendors that run their product with a SQL Server backend.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •