errorlog grown up to a large size..need to shrink it
-
Hi,
I see that my errorlog has grown up top 35gb. Is there a way i can shrink it...coz c:\drive is 180mb free space now..this is very urgent.
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
-
Sapen (10/11/2011)
Hi,I see that my errorlog has grown up top 35gb. Is there a way i can shrink it...coz c:\drive is 180mb free space now..this is very urgent.
Thanks
You need to be careful truncating the log file for the database. The log is what allows you to restore your database if there is a failure.
The way you truncate a log file depends on what version of sql server you are running. If you are going to truncate the log file I would suggest that right after you truncate it you create a backup of your database so you are covered.
You should regularly backup your database and at that time you could truncate the log file.
All of that being said, to truncate a log file in sql server 2008 you can switch between recovery modes:
declare @dbname varchar(100)
exec('alter database '+@DBNAME+' Set Recovery Simple')
exec('alter database '+@dbname+' Set Recovery Full ')
Your database name would have to be in the @dbname variable.
-
Is this the sql 'ERROR' log or the sql 'TRANSACTION' log?
SQL Error log
you can cycle the error log so it will create a new one with the following command
Exec Sp_Cycle_Errorlog
I don't know of a program that would allow you to open a 35 gb file to see WHAT has caused it to grow to 35 gb's...if it is an active issue or something in the past...
When is the last time the sql service was stopped and started?
If you were to choose to cycle the error log and remove that large file I would closely monitor the new error log to see what you have going on with the server.
If the SQL Transaction log is too large and has filled the drive you could backup the transaction log to clear it and then shrink down the physical log file to an appropriate size and set a growth limit to the file so it won't fill the drive again...I would follow that up with an alert for a full log.
If you simply cannot wait for a log backup to complete on a 35 gb log...then switch it from FULL recovery to SIMPLE recovery...and re-run a FULL / differential backup immediately to restart the log chain.
-
This is the ErrorLog...so if i restart the server...can i move that log to a different drive..
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
-
In order to save current Emergency situation , you have to follow up the next steps by the same order:
1.Switch DB into Simple mode ..and wait for few seconds to ensure truncate log file has been done.
2.Then Try to shrink log file
3.If you got much free unused space within the log file itself >>Then it is good and shrink could succeed to return this free space to O.S
4.If not >>>open 2008 activity monitor >> Try to kill manually any relevant transaction to this DB
5.If much activities & transactions there , you could do it through the below query.
USE [master]
declare @Sessionsid int
declare @sql nvarchar (300)
Declare Tablecursor cursor for select t.session_id from sys.dm_exec_connections t inner join sys.dm_exec_sessions s on t.session_id=s.session_id where s.login_name ='APP user name'
open Tablecursor
fetch next from Tablecursor into @Sessionsid
while (@@FETCH_STATUS =0)
BEGIN
set @sql =N'Kill '+convert (nvarchar (10),@Sessionsid)+' '
exec sp_executesql @sql
print ('The sessionid = '+CONVERT (NVARCHAR (50) ,@Sessionsid)+' has been killed')
fetch next from Tablecursor into @Sessionsid
end
close Tablecursor
DEALLOCATE Tablecursor
6.This is just to expedite the resolution of this emergency (Other specific solutions are there ).
7.Then try to shrink DB again.
8.If not possible by anyway , try to take full backup to ensure more a check point has been taken there to enable truncate log file>>>then try to shrink again
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
-
Performace Guard (Shehap) (10/11/2011)
In order to save current Emergency situation , you have to follow up the next steps by the same order:1.Switch DB into Simple mode ..and wait for few seconds to ensure truncate log file has been done.
2.Then Try to shrink log file
3.If you got much free unused space within the log file itself >>Then it is good and shrink could succeed to return this free space to O.S
4.If not >>>open 2008 activity monitor >> Try to kill manually any relevant transaction to this DB
5.If much activities & transactions there , you could do it through the below query.
USE [master]
declare @Sessionsid int
declare @sql nvarchar (300)
Declare Tablecursor cursor for select t.session_id from sys.dm_exec_connections t inner join sys.dm_exec_sessions s on t.session_id=s.session_id where s.login_name ='APP user name'
open Tablecursor
fetch next from Tablecursor into @Sessionsid
while (@@FETCH_STATUS =0)
BEGIN
set @sql =N'Kill '+convert (nvarchar (10),@Sessionsid)+' '
exec sp_executesql @sql
print ('The sessionid = '+CONVERT (NVARCHAR (50) ,@Sessionsid)+' has been killed')
fetch next from Tablecursor into @Sessionsid
end
close Tablecursor
DEALLOCATE Tablecursor
6.This is just to expedite the resolution of this emergency (Other specific solutions are there ).
7.Then try to shrink DB again.
8.If not possible by anyway , try to take full backup to ensure more a check point has been taken there to enable truncate log file>>>then try to shrink again
This seems like quite dangerous advice with killing all the connections and all the rest.
For the Error log simply cycle it as Leeland suggested
-
Hi Leeland,
I ran the command Exec Sp_Cycle_Errorlog and restarted the sql server. Now I am copying the 35GB file to a different drive. I hope it releases the space on the drive now atleast.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
-
unless you plan on trying to read that 35 gb file I see no reason to use it...
you MIGHT be able to use something like textpad to open it...I would presume it would take a long time to open...
I would be more concern with what is in the new error log...after you cycle the previous one...
And a FYI once you run the cycle the error log command you do not need to restart the sql service...it just creates a new file and versions the old
-
Thanks for the info leeland...
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
-
Sapen, Could you please tell me what was causing the errorlog to become 35GB. When was the SQL Server Service restarted from the last time. By the way you should create a SQL Job to recycle the Errorlog.
-
Is it a good practice to have the sql errorlog on a different drive...
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
-
If you have another drive i guess it wouldn't hurt, I think the best practice is to cycle it regulary and setup alerts that will let you know if there are any errors that need addressing.
-
steveb. (10/12/2011)
If you have another drive i guess it wouldn't hurt, I think the best practice is to cycle it regulary and setup alerts that will let you know if there are any errors that need addressing.Even if you have another drive cycling the errorlog and seting up alerts is always a good idea 😀
-
Salom,
I was looking into the errorlog file that grew 35GB and all I see is just information messages without any errors and also there were only 26050 records of logs that got pulled up. I still dont understand why that log file grew up to 35GB. All the other errorlogs that I have (ErrorLog.1, Errorlog.2 and so on) are not more than 6 mb.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
-
First, look at he time the current error log spans versus the time the older ones span. If you reboot less often now, less patches, etc., the log grows larger. There's nothing inherently wrong with a large error log other than it's hard to open with some tools.
If the instance hasn't been running longer, then I might look at the entries. See if there are some items being logged that aren't in other logs. It's possible someone changed a setting, maybe enabled some auditing or other tracking that's filled the error log.
If you have large logs, typically people do a regular cycle of the log with sp_cycle_errorlog to manage the size. You can backup or copy off the old logs and then delete them to save space.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply