While delete getting error Transaction LOG full.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

While delete getting error Transaction LOG full.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-11-17 : 01:49:54

Dear All,

While deleting records I am getting following error. Can anybody please tell me how to resolve this issue ?

Error:

Msg 1105, Level 17, State 2, Line 3
Could not allocate space for object 'dbo.tb_lcr_normalized_rate_sheet'.'PK_tb_lcr_normalized_rate_sheet' in database 'SBCSS' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.Msg 9002, Level 17, State 4, Line 3
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Thanks and Regard's
Harish Patil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 02:14:38

This is because PRIMARY file group has become full. Try to move it to a drive with more space if possible (if you can afford small downtime and need to retail all data).
Or backup some old data and purge it from db and shrink file.

for tempdb issue also point tempdb to new drive if possible and in your actual query if amount of data involved is large try to do it in batches to avoid log space filling up. Also check your recovery model and see if its full and you can afford to change it to bulk logged or simple

for different options see below

http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-11-17 : 03:57:03

Hello Friend,

Thnks. I increase my memory space on C drive. Now records are deleting.

Thanks and Regard's
Harish Patil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 04:04:02

quote:
Originally posted by hspatil31

Hello Friend,

Thnks. I increase my memory space on C drive. Now records are deleting.

Thanks and Regard's
Harish Patil

welcome

Make sure you have a good backup/purge/archving strategy for thr db to avoid old data getting filled up in file groups

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources