September 13, 2010 at 10:22 pm
I was getting problem with Log shrinking in sql 2008 using DBCC shrinkfile (mylog,truncate_only) file wasnt getting shrink.so, i read in one Article , please confirm is this the only way !!!
In SQL Server 2008 the procedure is slightly different, the database must first be set to Simple recovery mode, then shrink the file, then restore FULL recovery mode:
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
go
DBCC SHRINKFILE(MyDatabase_log)
go
EXEC sp_helpdb MyDatabase
go
ALTER DATABASE MyDatabase SET RECOVERY FULL
go
September 13, 2010 at 10:35 pm
Sometimes that method works. However, make sure you perform a full backup before doing that. Also, i would do another full backup immediately after changing it back to full recovery.
As for the command you were using, truncate_only does not shrink the file. You should specify a size in lieu of the truncate_only option.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 13, 2010 at 11:13 pm
so, it means i have to specify some size too
ok i will try your suggestion
September 14, 2010 at 2:50 am
Have you tried taking a tlog backup before performing the SHRINKFILE command ?
September 14, 2010 at 4:04 am
@ vultar
yes i did, after backup tlog get truncate but it didnt get shrink.
I just want to do this because we were running out of space on that machine...
September 14, 2010 at 7:01 am
If you try to shrink the log through SSMS how much free space does it say is available ?
September 15, 2010 at 1:09 am
Hi
U may use this syntax:
DBCC shrinkfile (mylog,200)
Where 200 may your desired MB.
But in my opinion shrinking file/s is not a solution, it effect the performance.
Ali
MCTS SQL Server2k8
September 15, 2010 at 6:38 am
@ Ali : Agreed, i dont do it as regular practice 🙂
but asking for times when there is no more space left in drive
so, this is the only solution left after db backup on USB drive.
i tried dbcc shrinkfile (mylog,100) before and it works
but i want to know about this one on SQL 2008
dbcc shrinkfile (mylog,tuncate_only) does it work or not !!!!
September 15, 2010 at 6:47 am
Hi,
In SQL 2008 putting the database in Recovery Simple will truncate the log. It is the only way that truncates the log
After you put your DB in recovery simple doing
DBCC SHRINKFILE('MyLogLogicalFile',200) would shrink the log (200 means 200 Mb as Log file size)
Put after that your DB back in Recovery Full
I use this method to shrink log files and it works allways for me.
September 15, 2010 at 7:04 am
In SQL2008 notruncate and truncateonly are not applicable to log files. So in fact what is happening is notruncate and truncateonly are ignored when it is a log file. This means that the command you are running is in fact reducing the log file as much as it is able to (i.e. to the last active virtual log file)
So, leaving apart the fact in normal circumstances you would not run shrinkfile, specify a size to shrink to when you run the shrinkfile, i.e. DBCC SHRINKFILE ( MYDB_log' ,25).
check where the last active VLF is using dbcc loginfo(dbname). A value of 2 in the status column means the VLF contains active transactions and the log will not shrink beyond that point.
---------------------------------------------------------------------
September 16, 2010 at 8:19 am
Apart from shrinking you need to set a standard size for the tlog based on observations over a period of time. Shrinking tlog frequently will cause performance degradation - will eventually cause hard disk fragmentation.
September 16, 2010 at 10:05 am
george sibbald (9/15/2010)
In SQL2008 notruncate and truncateonly are not applicable to log files. So in fact what is happening is notruncate and truncateonly are ignored when it is a log file. This means that the command you are running is in fact reducing the log file as much as it is able to (i.e. to the last active virtual log file)
yes, when i run dbcc shrinkfile (mylog,truncateonly) and then run
dbcc sqlperf (logspace)
log space filling % increase every time with little increment.
i was on full recovery model
check where the last active VLF is using dbcc loginfo(dbname). A value of 2 in the status column means the VLF contains active transactions and the log will not shrink beyond that point.
I didnt check this point, i will consider this one now...
so, may i conclude, WE ALWAYS HAVE TO CHANGE RECOVERY MODEL TO SIMPLE WHEN GOING FOR LOG SHRINK ???
although it is not a good practice to shrink log files
September 16, 2010 at 10:37 am
fawwad (9/16/2010)
george sibbald (9/15/2010)
In SQL2008 notruncate and truncateonly are not applicable to log files. So in fact what is happening is notruncate and truncateonly are ignored when it is a log file. This means that the command you are running is in fact reducing the log file as much as it is able to (i.e. to the last active virtual log file)yes, when i run dbcc shrinkfile (mylog,truncateonly) and then run
dbcc sqlperf (logspace)
log space filling % increase every time with little increment.
i was on full recovery model
check where the last active VLF is using dbcc loginfo(dbname). A value of 2 in the status column means the VLF contains active transactions and the log will not shrink beyond that point.
I didnt check this point, i will consider this one now...
so, may i conclude, WE ALWAYS HAVE TO CHANGE RECOVERY MODEL TO SIMPLE WHEN GOING FOR LOG SHRINK ???
although it is not a good practice to shrink log files
No - that is not true.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply