Database Transaction Log is huge

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Database Transaction Log is huge

Author

Topic

bmv1986
Starting Member

3 Posts

Posted - 2013-02-24 : 17:21:14

Hi everyone,

my database transaction log is huge (112GB) and i am trying to shrink the log file. i tried using T-SQL command but it was running for verylong time. then i tried using SQL management studio
Tasks-> Shrink - > files option but i got the below error..

Time out error occured while waiting for buffer latch type 3 for page (1:3817536), database ID 17
(microsoft SQL server, Error:845)..

can you someone advise what could be the problem.. appreciate your hlep!!!

Mo!!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-24 : 19:51:10

You can shrink it only if you have space left to shrink. You can find out how much is used and how much is free using:

SELECT *
FROM   sys.dm_os_performance_counters dopc
WHERE  dopc.counter_name IN 
		(
		'Log File(s) Size (KB)', 
		'Log File(s) Used Size (KB)',
		'Percent Log USED'
		)
		AND dopc.instance_name = 'YourDatabaseName'

If there is not much free space and if you have not been doing it you should do a full database backup and schedule regular log backups. Then, see how much space is in the log file again. If it still shows very little free space, look at the log_reuse_wait_desc column in sys.databases. It can be due to a number of reasons - see here: http://msdn.microsoft.com/en-us/library/ms345414(v=sql.105).aspx You will need to address that before you will be able to shrink the log file.

Another option that people sometimes use is to change the recovery model to simple and then change it back to full or bulk-logged. This will cause the log sequence chain to be broken, but if you don't care about point in time recovery, you probably can do this. But I am not recommending this, since I don't know your server or business requirements.

Please keep in mind is that while it may be okay to shrink the log file in this specific case, routinely shrinking log file is a very bad idea. http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

bmv1986
Starting Member

3 Posts

Posted - 2013-02-24 : 23:46:42

Hi James,

Thanks a lot for the quick response. Below is the log file result.

Log File(s) Size (KB) is 117964600
Log File(s) Used Size (KB) is 413685
Percent Log Used is 0

My database recovery model is simple. we used to take full back up of database daily. however we do not take backup of log file.
After restarting the server, i can able to shrink the file. As you suggested, i will monitor the log file size.

Thanks a log again!!

Mo!!

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-25 : 01:51:21

looking at your initial error , do you have a copy of the latch timeout dump? This usually gives you enough information to figure out the the owner and what was occuring to create the timeout
Find the owner thread of the latch,view the stack to understand the task and then troubleshoot the performance

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

bmv1986
Starting Member

3 Posts

Posted - 2013-02-27 : 22:23:01

hi Jack,

i could see the task id in sql error log, but i really do not know where the dump will be stored to have a look.

Mo!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources