December 13, 2006 at 11:30 pm
I'm a web developer using a small SQL Server 2005 database on a shared server with my web host. I have SSMS but don't have permissions to run DBCC. I ran SSIS to import several 1000's of records over multiple tables, and filled my transaction log. The last table didn't copy. I tried backing up the database (Full backup) because I read somewhere that could shrink the log. I've tried copy records with a query to limit it to 1000 at a time, but I'm still getting transaction log full errors.
In SSMS if I click Shrink Database it says Currently allocated space is 30.63 MByte and available free space is 1.16MByte. That sounds pretty full to me; seems I want to grow it not shrink it.
The DB is set with AUTO SHRINK on.
This is a real pain in the butt. I'm running the same database in a SQL Server 2000 instance on my laptop, and I've never had any log full errors.
Any suggestions how to fix this?
December 14, 2006 at 1:48 am
Scott
A full database backup won't affect the size of your log. Truncate your log like this:
BACKUP LOG MyDB WITH TRUNCATE_ONLY -- if you don't need to be able to restore
BACKUP LOG MyDB TO DISK = 'X:\MyFolder\MyFile.bak' -- if you want to be able to restore to a point in time after your last full backup
(The syntax above is from SQL Server 2000 - if it has changed for SQL Server 2005 then please look it up in Books Online.)
I don't recommend using auto shrink. It consumes system resources and causes fragmentation. Set your log to a realistic size and leave it. But don't forget to back up (or truncate) your log regularly.
John
December 14, 2006 at 6:40 am
I tried the WITH TRUNCATE_ONLY option. It is the same syntax for SQL2005, and it executed successfully, but I am still getting transaction log full error when trying to copy just 1000 records.
It has a message "to see why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". The only problem is I donb't think I can get at sys.databases in the shared server environment. I'm going to try contacting the web hoster too.
AS far as auto shrink being on, in my case I think I'd rather leave it. This is going to be for a low volume online ordering system for a real estate sign posting business, and I'd rather it have take care of the sizing automatically than rely I me having to maintain their database manually. Auto shrink was the default setting when I created the database.
December 14, 2006 at 6:59 am
Well I did notice that after running the backup log my available free space went from 1.16 MB to 16.73 MB, so it sure did something. I sent an email to the web hoster; maybe if we can see what's in the log_reuse_wait_desc column we can diagnose what's wrong.
December 14, 2006 at 7:21 am
Scott
Obviously your log file isn't large enough to accommodate the data from inserting 1000 rows. So the solution is to make the log bigger and turn off auto shrink. No maintenance is involved thereafter. Otherwise you are going to have this problem every time you do your copy. If the log can't grow fast enough then it will fail, and if it can the process will have to wait for the log to expand.
John
December 14, 2006 at 7:37 am
Ok. So the next obvious question - how to I grow the log file? (Sorry, I'm pretty experienced at coding for SQL Server, but at these administration things I'm pretty much a newbie).
December 14, 2006 at 8:24 am
Scott
Right click on the database in Enterprise Manager, choose Properties, and click on the Transaction Log tab. Enter the new value in the Space allocated box and click on OK. Or you can do it in Query Analyzer using the ALTER DATABASE MODIFY FILE statement - look it up in Books Online.
John
December 14, 2006 at 11:22 am
It is better to change the autogrow configuration as advised but if you don't have the access...
You can reduce your batch size to 1/10/100 instead of 1000 row in single batch(Transaction)...
MohammedU
Microsoft SQL Server MVP
December 14, 2006 at 10:43 pm
This is a SQL2005 database, and the Properties/Files page was disabled - well, at least I didn't see any files listed. I looked up the ALTER DATABASE MODIFY FILE syntax, and was able to increase the log size to 5MB, then successfully transfer 3908 order headers and 23749 order details.
Once again, this forum has come to my rescue! Thanks a lot, guys.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply