February 22, 2012 at 1:13 am
Hi All,
I have a automated script that does Shrinking of the log files in the database. I am not able to understand the logic on how it is getting target value for shrinking the log file. I am pasting below the query.
USE [dbname]
declare @LogFilename as varchar(100)
declare @ShrinkChar as varchar(50)
select
@ShrinkChar = convert(varchar(5),convert(int,round(a.size*8192.0/1048576.0/4,0))) ,
@LogFileName = b.name
from sys.database_files a ,
sys.database_files b
where a.physical_name like '%.mdf'
and b.physical_name like '%.ldf'
exec('dbcc shrinkfile('''+@LogFileNAme+''','+@Shrinkchar+') ')
Any help on it will be highly appreciated specially in making me understand below line of code from the above query.
@ShrinkChar = convert(varchar(5),convert(int,round(a.size*8192.0/1048576.0/4,0))) ,
Thanks in advance....
February 22, 2012 at 1:26 am
sejal p gudhka (2/22/2012)
Hi All,I have a automated script that does Shrinking of the log files in the database. I am not able to understand the logic on how it is getting target value for shrinking the log file. I am pasting below the query.
USE [dbname]
declare @LogFilename as varchar(100)
declare @ShrinkChar as varchar(50)
select
@ShrinkChar = convert(varchar(5),convert(int,round(a.size*8192.0/1048576.0/4,0))) ,
@LogFileName = b.name
from sys.database_files a ,
sys.database_files b
where a.physical_name like '%.mdf'
and b.physical_name like '%.ldf'
exec('dbcc shrinkfile('''+@LogFileNAme+''','+@Shrinkchar+') ')
Any help on it will be highly appreciated specially in making me understand below line of code from the above query.
@ShrinkChar = convert(varchar(5),convert(int,round(a.size*8192.0/1048576.0/4,0))) ,
Thanks in advance....
Just need to break the formula down within the ()
so the most inner () is
round(a.size*8192.0/1048576.0/4,0)
So it gets the value a.size from sys.database_files, for example lets say 100, so we get
100*8192.0/1048576/4 which gives a result of 0.1953125
this is then rounded which gives a value of 0 so that part of the query will look like ROUND(0.1953125,0) which returns 0
--round(a.size*8192.0/1048576.0/4,0)
it is then converting the value of the round function to an int
convert(int,0) which gives the result as an int data type
--convert(int,(round(a.size*8192.0/1048576.0/4,0))
it is then converting the value of the int as a variable length string of 5 characters
convert(varchar(5),0)
--convert(varchar(5),convert(int,(round(a.size*8192.0/1048576.0/4,0)))
it is then setting @ShrinkChar to the value of the string which has been returned
--set @ShrinkChar = convert(varchar(5),convert(int,(round(a.size*8192.0/1048576.0/4,0)))
February 22, 2012 at 1:31 am
why are you shrinking the database. Its been discussed quite a bit in this forum that its a bad idea.
http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
read more
February 22, 2012 at 1:33 am
Thanks Andrew.......
I got that a.size from sys.database_files stores file size in terms of 8-KB Pages and multiplying that by 8192 is to convert it into KB. So let's say a.size is 100 then it means that it has 100 8-KB Pages which is then mutiplied by 8192 i.e. 819200 KBs.
By now why the division with 1048576.0/4 is needed? What is the query trying to do by this division is what I am interested in knowing.
Thanks in advance....
February 22, 2012 at 1:37 am
sejal p gudhka (2/22/2012)
Thanks Andrew.......I got that a.size from sys.database_files stores file size in terms of 8-KB Pages and multiplying that by 8192 is to convert it into KB. So let's say a.size is 100 then it means that it has 100 8-KB Pages which is then mutiplied by 8192 i.e. 819200 KBs.
By now why the division with 1048576.0/4 is needed? What is the query trying to do by this division is what I am interested in knowing.
Thanks in advance....
I dont know, you will need to ask the person who wrote the script as to why they picked that division, I cant see no reasoning behind it, other than you need to reduce the amount to shrink it, maybe this is some value that was used in the past somewhere.
February 22, 2012 at 1:39 am
Personally I would strongly recommend that you remove that automated task entirely. Shrinking logs is not as harmful as shrinking data files, but it is still a poor thing to do on a regular basis.
If a log has reached a certain size that means it needs to be a certain size and will just regrow to that size (halting any database changes while it grows). Also, if the growth increment is not properly set, that regrowth will cause log fragmentation (lots of VLFs) and that will slow down backups, restores, recovery, etc.
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
February 23, 2012 at 1:36 am
I agree with Gail, but for what it's worth, I think the calculation you're having so much difficulty with is designed to shrink the log file to a quarter of the size of the associated MDF file regardless of how big that actually is. It's taking the size of the MDF (a.size) in 8kb pages, converting that to megabytes (*8192/1048576) and then dividing that result by 4 and passing it to DBCC SHRINKFILE.
February 23, 2012 at 2:12 am
use testdb
dump tran testdb with no_log
dbcc shrinkfile (testdb_log,truncateonly)
will this truncate also a bad idea if its regular or irregular..
suggestions pls.
Regards
Durai Nagarajan
February 23, 2012 at 2:35 am
1 - that's a very bad idea
2 - that won't work in SQL 2005.
Please read through this - Managing Transaction Logs[/url]
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
February 23, 2012 at 3:10 am
thanks for the suggestion one more doubt will this impact data files any how?
but it is working in sql 2005...
Regards
Durai Nagarajan
February 23, 2012 at 3:14 am
It won't impact data files.
You must have compat mode less than 90 to use Dump Tran. It's been deprecated for probably 10 or so years.
Please go and read that article.
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
February 23, 2012 at 3:22 pm
I know you are posting to fix the problem with the code for automatically shrinking log files. And I believe that your original question has been answered, but as it was brought up, shrinking the log file is usually not desired action or fix.
I would add that when you can, look into why the logs are growing in the first place. There is a reason why the logs grew and shrinking them will not really help in the long run if they will just grow again at a later date.
Shrinking them is only treating the symptom and not addressing the cause. Something is happening that is causing the log files to grow. It could be that just tweaking a query a bit could prevent undesired log growth.
-----------------
Larry
(What color is your database?)
February 24, 2012 at 5:01 am
Hello,
What are ther main reasons for log file growth.
IN my case we take only full back up daily, no log backup.
doesnt seams to be shriniking after backup.
Regards
Durai Nagarajan
February 24, 2012 at 5:08 am
durai nagarajan (2/24/2012)
Hello,What are ther main reasons for log file growth.
IN my case we take only full back up daily, no log backup.
doesnt seams to be shriniking after backup.
If it's in Full recovery and you're only taking full backups then no, the log will never shrink--you need to be taking log backups as well! If it's in Simple recovery then you can get away with only ever taking full backups.
February 24, 2012 at 5:13 am
durai nagarajan (2/24/2012)
Hello,What are ther main reasons for log file growth.
IN my case we take only full back up daily, no log backup.
doesnt seams to be shriniking after backup.
The log will never shrink after a backup, backups don't shrink the log. In simple recovery the log is marked reusable by the checkpoint operations, in full recovery that needs a log backup
Please read through this - Managing Transaction Logs[/url]
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 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply