Need Help on Shrinking Database....

  • 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....

  • 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)))

  • 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

    Jayanth Kurup[/url]

  • 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....

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • thanks for the suggestion one more doubt will this impact data files any how?

    but it is working in sql 2005...

    Regards
    Durai Nagarajan

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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?)

  • 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

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply