June 29, 2011 at 8:41 am
I am lookin on a server if a job has been setup to shrink a log file for a db. I dont see anything in jobs list under sql server agent.
Is there any other place I can check for?
Thanks.
June 29, 2011 at 9:37 am
If the recovery model is simple the transactional log file will be shrinked with each db backup,right?
June 29, 2011 at 9:44 am
Not really. It's just much less likely to grow (takes a massive single transaction to force autogrow, at which point it shouldn't shrink back down).
June 29, 2011 at 10:05 am
sql_novice_2007 (6/29/2011)
If the recovery model is simple the transactional log file will be shrinked with each db backup,right?
What Ninja said is correct. Simple Recovery model will regularly truncate the log, not shrink it. Truncation will make portions of the log file itself reusable. So a 1GB file that was 50% full can be truncated and the file is still 1GB, but now only 3% full. So truncation is different than shrinking.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
June 29, 2011 at 2:39 pm
Hi SQL Novice,
I picked this script up somewhere on the INTERNET. Execute it inside the database that you are interested in shrinking. It will tell you the files (data and log) that are associated with the database, their size, and amount used.
You can right click the database via the object explorer and go to the properties tab. Go to the files tab. Select the log file that you want to shrink. Change the size to the desired level and hit okay.
The log file should be shrunk.
Caution - You should first make sure that a backup is having every night on both the database and transatcion log. Ideally, transaction log backups should be happening on the hour. You should look at the end of day size of the log to get an idea of average usage. You should add some delta to the number to get your lowest shrinkage size.
Choosing an arbitrary number that is real low may cause autogrowth on the log. If the autogrowth size is small, a performance impact may be experienced if large amounts of transactions occur.
John
create table #data(Fileid int NOT NULL,
[FileGroup] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[Name] sysname NOT NULL,
[FileName] varchar(300) NOT NULL)
create table #log(dbname sysname NOT NULL,
LogSize numeric(15,7) NOT NULL,
LogUsed numeric(9,5) NOT NULL,
Status int NOT NULL)
insert #data exec('DBCC showfilestats with no_infomsgs')
insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')
select [type], [name], totalmb, usedmb, totalmb - usedmb as EmptySpace from
(
select 'DATA' as [Type],[Name],(TotalExtents*64)/1024.0 as [TotalMB],(UsedExtents*64)/1024.0 as [UsedMB]
from #data
union all
select 'LOG',db_name()+' LOG',LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()
--order by [Type],[Name]
)a
order by [Type],[Name]
drop table #data
drop table #log
John Miner
Crafty DBA
www.craftydba.com
June 30, 2011 at 2:23 am
sql_novice_2007 (6/29/2011)
I am lookin on a server if a job has been setup to shrink a log file for a db. I dont see anything in jobs list under sql server agent.Is there any other place I can check for?
Thanks.
This SProc will not only do the shrinking.It'll check the log_reuse_wait_desc and depends upon the value it ll do number of things.
Read the post before going to implement it.
also read How to avoid shrinking Shrinking the files will not recommend.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 30, 2011 at 2:24 am
sql_novice_2007 (6/29/2011)
If the recovery model is simple the transactional log file will be shrinked with each db backup,right?
Following post will clear your doubts.
http://sqlserverblogforum.blogspot.com/2011/03/difference-between-truncating-and.html
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 30, 2011 at 6:33 am
Maintenance plans and auto_shrink are the two other places. When you find the shrink you'll be removing it, right?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply