Database Log file Full

1. Log file grown 100%. while trying to shirk the log file it is not allowed to releasing the space why?

Ans:

1. first check the database recovery model. (Full/Bulklogged/Simple).

if it is simple recovery model.

first try to shrink the log file. If it  is not shrinking then run manually checkpoint. using below command.

checkpoint

if it is full/Bulk logged recovery model.

first try to take the t-log backup of database. then try to shrink the log file, in that time also not releasing the space. we need to take multiple t-log backups then automatically shrinking will occurred. if that time also not released the space then below the steps we followed.

1. check if database is configured in Transactional replication. 

1.       Stopped log reader agent.(Transaction replication monitor)

2.       We took transaction log backup.

3.       Then we ran following command: Execute sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

4.       Dbcc shrink was performed to release the space to drive

5.       Reinitialized the snapshot agent from replication monitor to resync the replication.

6.       Once snapshot agent was completed we started log reader agent.

 

2. check if database is configured in Log shipping.

1.       we will verify the backup job, copy job and restore job was properly running or not. if it is not running properly log shipping goes to out of synch then we will get error is 14420/14421.

2.       then we will manually verify why the jobs are failed and what cause.

3.       if backup job was failed, here normally we will see in job history why backup job failed information then based on that we will fixed.

4.       if it is problem network related issues (Ex: shared folder is not accessible) - copy job failed.

5.       restore job failed.

 3. check if database is configured in mirroring.

1.       if Bulk operation happened then only log will grown.

apart from that we will check the database some other steps.

1. check the database is there any open transactions using below command.

dbcc opentran.

if any oldest tranasactions are running the database to verify using below command.

dbcc  inputbuffer(spid)

we will verify which query running on the session, then we inform to application team/customer to taking the decision.

in this scenario if space is available in server then we will added one more log file in drive.

if there is no space in server then we will last option to truncate the log file using below command.

backup log databasename with truncate_only or no_log this command is working upto SQL 2005.

in SQL 2008 onwards in the above key words deprecated by the microsoft. in that situation microsoft recommanded to change the Recovery model to simple. example (If database is Full recovery mode --> change to Simple recovery model) using below command.

Alter database <dbname> set recovery simple.