One of server my job is getting fail. It will showing log speace is full but log speace is 24GB But actually my log file actual speace is 30GB How can i handile this isssue plz help me urgent
Check SQL Server Agent Error Log for any clue. Check the drive where the MSDB Log file is kept. Also check the job is running on which database.
Check the Sql Server error log and see if you can find some clues. Check if there is enoough disk space in the drives containing Sql Server data and log files. Also check the database settings and set it as unlimited if it is limited to a small value.
There is have some disk speace againg that job is failing.
I can give suggestions also change the job scheduling,also
but that is not issue here right cheack the error log also give some good suggestions really appriciate to that guy
It will saying operating system error 112
Check the open transactions against the database whose log file is full using DBCC OPENTRAN command. If you have large number of open transactions you can't shrink the file.. Is the database configured for any high availability solution like, log shipping or database mirroring??
Use the below command to check log reuse wait status.
select name,log_reuse_wait_desc from sys.databases where name ='yourdbname'
Refer this link which describes the reasons for the log file growth, http://support.microsoft.com/kb/317375
- Due to Uncommitted transactions. We can find if there are any open transactions using DBCC OPENTRAN.
- Running DBCC REINDEX, CREATE INDEX operations with the database in Full Recovery model.
- Running extremely large transactions like Bulk Insert, Select Into commands. etc..
If the database in FULL recovery, and you are not doing TRANSACTION LOG backups (point-in-time recovery), change the database to SIMPLE recovery.
This is how you can shrink the log: http://support.microsoft.com/kb/907511
Was this working "yesterday"? What happened?