SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


errorlog grown up to a large size..need to shrink it


errorlog grown up to a large size..need to shrink it

Author
Message
sasken
sasken
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12186 Visits: 2055
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
bkubicek
bkubicek
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8906 Visits: 1049
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.
Leeland
Leeland
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7796 Visits: 1388
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.
sasken
sasken
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12186 Visits: 2055
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
Performance Guard (Shehap)
Performance Guard (Shehap)
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 264
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

Performance Guard

Just ask me to get 0 sec for all queries
steveb.
steveb.
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41272 Visits: 7195
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
sasken
sasken
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12186 Visits: 2055
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
Leeland
Leeland
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7796 Visits: 1388
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
sasken
sasken
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12186 Visits: 2055
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
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18730 Visits: 1439
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.




My blog

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search