SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Database Transaction Log is huge
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bmv1986
Starting Member

USA
3 Posts

Posted - 02/24/2013 :  17:21:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 02/24/2013 :  19:51:10  Show Profile  Reply with Quote
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/
Go to Top of Page

bmv1986
Starting Member

USA
3 Posts

Posted - 02/24/2013 :  23:46:42  Show Profile  Reply with Quote
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!!
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 02/25/2013 :  01:51:21  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

bmv1986
Starting Member

USA
3 Posts

Posted - 02/27/2013 :  22:23:01  Show Profile  Reply with Quote
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!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000