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
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
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'SpaceUsed)/128, size/128, max_size/128, type_desc
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SpaceUsed)/128, size/128, max_size/128, type_desc
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?
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.
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?