February 28, 2011 at 1:45 pm
Guys,
My Reorginize Index Task job on production is failing
My Executing the query "ALTER INDEX [Delivery1] ON [dbo].[Delivery] REORGANIZE WITH ( LOB_COMPACTION = OFF )
" failed with the following error: "The transaction log for database 'DTS' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I checked the log_reuse_wait_desc column in sys.databases and it is equal to Nothing,what it this mean?
How can I fix it?
Thank you
February 28, 2011 at 2:01 pm
The transaction log is full..
You have to shrink the transaction log before attempting to again re run the job.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 28, 2011 at 2:17 pm
This is what I have
reserved data index size unused
63456 KB55072 KB7096 KB1288 KB
also I don't see my log is full, my log is = to 1MB and my database is 97mb
February 28, 2011 at 3:57 pm
SKYBVI (2/28/2011)
The transaction log is full..You have to shrink the transaction log before attempting to again re run the job.
Regards,
Sushant
No, NO, NO...
Yes, the transaction log file filled up during that transaction. After that, either a transaction log backup was performed (full recovery model) or a checkpoint (simple recovery model) and that marked the VLF's in the transaction log file as reusable.
My guess is that this database is set to simple recovery model - that you have set a max size for the transaction log and the DTS package you are trying to run is too large for that size.
You need to either modify the transaction so it is smaller or you need to increase the size of the transaction log to allow for the full transaction to be committed.
But, you should not shrink the log file - as that is just going to cause it grow again the next time you run this process.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 28, 2011 at 6:44 pm
Yes db is set to simple what do mean dts is too large. How would I set up in simple recovery log file to max:
Is it unrestricted or restricted and what number, also file growth in percent or megabytes and what number? Thank you
February 28, 2011 at 8:19 pm
I don't have any idea about your system - or the indexes that you are rebuilding and how much space is required.
You need to identify how large the indexes are and how much space it will need in the transaction log to accommodate that operation. Once you have that number, you can then size the transaction log large enough to handle that operation.
One thing you should be aware of - reorganize uses a lot more space in the transaction log compared to a rebuild, which could be a minimally logged operation in simple recovery model.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 28, 2011 at 8:38 pm
Do you recommend keep it in simple or move to full
March 1, 2011 at 5:55 am
Can you please help me to calculate that db
I will keep at simple mode
March 1, 2011 at 6:04 am
Krasavita (2/28/2011)
This is what I havereserved data index size unused
63456 KB55072 KB7096 KB1288 KB
also I don't see my log is full, my log is = to 1MB and my database is 97mb
Go to properties of that database and select the log file "Autogrowth" setting it might be set restricted file growth.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 1, 2011 at 6:24 am
I don't have set for data or log, should I set for both,
I don't know Is it unrestricted or restricted and what number, also file growth in percent or megabytes and what number?
Thank you
March 1, 2011 at 6:27 am
you can check it as i mentioned in my prior post
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply