Bug inside my Shrink DB script

  • Gents,

    I'm trying to write the SQL script that shrink all the user databases on the instance. But unfortunately what I have written so far is not working properly please. Can some one please look at this and possibly find the bug inside. I'll really appreciate that.

    Many Thanks, Basit

    CREATE PROC [dbo].[ShrinkDB] ( @NewSize int )

    AS

    SET NOCOUNT ON

    DECLARE

    @MaxMinint

    ,@OriginalFileSizeint

    ,@Countint

    ,@StartTimedatetime

    ,@TruncateLogvarchar(255)

    ,@dbNamenvarchar(50)

    DECLARE dbNames_cursor CURSOR

    FOR SELECT [NAME] FROM dbo.sysdatabases WHERE NOT [name] IN ('master', 'model','msdb','tempdb')

    OPEN dbNames_cursor

    FETCH NEXT FROM dbNames_cursor INTO @dbName

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN

    IF (@@FETCH_STATUS <> -2)

    BEGIN

    DECLARE @logName nvarchar(50)

    EXEC(' USE [' + @dbName + '] ')

    SET @logName = @dbName + '_log'

    SELECT @MaxMin= 2

    SELECT

    @OriginalFileSize = SIZE

    FROM

    sys.sysfiles

    WHERE

    name = @logName

    SELECT 'Original Size OF ' + DB_NAME() + ' LOG IS '

    + CONVERT (VARCHAR(50), @OriginalFileSize)

    + ', 8K pages OR ' + CONVERT(VARCHAR(50), (@OriginalFileSize * 8 / 1024)) + 'MB'

    FROM

    sys.sysfiles

    WHERE

    name = @logName

    CREATE TABLE DumpTrn (DumCol CHAR(8000) NOT NULL)

    SELECT

    @StartTime = GETDATE()

    ,@TruncateLog = 'BACKUP LOG ['+ DB_NAME() +'] WITH TRUNCATE_ONLY'

    DBCC SHRINKFILE (@logName, @NewSize)

    EXEC (@TruncateLog)

    WHILE

    @MaxMin > DATEDIFF (mi, @StartTime, GETDATE())

    AND

    @OriginalFileSize = (SELECT FROM sys.sysfiles WHERE [name] = @logName)

    AND

    (@OriginalFileSize * 8 / 1024) > @NewSize

    BEGIN

    SET @Count = 0

    WHILE ((@Count < @OriginalFileSize / 16 ) AND (@Count < 50000))

    BEGIN -- UPDATE

    INSERT DumpTrn VALUES ('Fill Log') -- Because it IS a char field it inserts 8000 bytes.

    DELETE DumpTrn

    SET @Count = @Count + 1

    END -- INNER loop

    EXEC (@TruncateLog) -- See IF a trunc OF the log shrinks it.

    END-- OUTER loop

    SELECT 'Final Size OF ' + DB_NAME() + ' LOG IS '

    + CONVERT(VARCHAR(30), ) + ', 8K pages OR '

    + CONVERT(VARCHAR(30), ( * 8 / 1024)) + 'MB'

    FROM

    sys.sysfiles

    WHERE

    [NAME] = @logName

    DROP TABLE DumpTrn

    END -- END of IF statement

    FETCH NEXT FROM dbNames_cursor INTO @dbName

    END -- End of OUTER Loop

    CLOSE dbNames_cursor

    DEALLOCATE dbNames_cursor

    SET NOCOUNT OFF

    Basit Ali Farooq
    MCITP Database Administrator
    Microsoft Certified Professional Developer (Web Applications)
    Microsoft Certified Database Administrator
    Microsoft Certified Systems Engineer
    Microsoft Certified Systems Administrator
    CIW Security Analyst
    Cisco Certified Network Associate

  • Why are you shrinking your databases in the first place? It is not something you should be doing on a regular basis. They will just grow again as soon as someone uses them and the database needs some free space inside to work properly.

    Shrinks cause bad index fragmentation and, if paired with autogrow can cause external (file-level) fragmentation as well.

    See - shrinking databases[/url]

    Be sure to read the two articles linked towards the bottom of that too.

    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
  • We need to run this as job montly after the month end. As I am working for big financial organization and this is the business requirement. I know what are the advantages and disadvantages of Shrinking the database. Also We only going to shrink thoes databases that log file size is greater than 20GB. Can you please help.

    Cheers,

    Basit Ali Farooq
    MCITP Database Administrator
    Microsoft Certified Professional Developer (Web Applications)
    Microsoft Certified Database Administrator
    Microsoft Certified Systems Engineer
    Microsoft Certified Systems Administrator
    CIW Security Analyst
    Cisco Certified Network Associate

  • I do hope you're taking a full backup afterwards, since you're also breaking your log chain doing this.

    When you say "It's not working" What do you mean? Error? Not doing what's expected? Something else?

    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
  • Yes we are taking the full backup of the databases before we run this job. So this is not the problem the problem with this script is its not working as I expect and I cannot find the error in this script actually I want this script to shrink the database log of all user databases one by one if its greater than 20GB. But it does not happen. Can you please advise.

    Thanks and Cheers.

    Basit Ali Farooq
    MCITP Database Administrator
    Microsoft Certified Professional Developer (Web Applications)
    Microsoft Certified Database Administrator
    Microsoft Certified Systems Engineer
    Microsoft Certified Systems Administrator
    CIW Security Analyst
    Cisco Certified Network Associate

  • Actually, you want to take a full backup after running this, as you are truncating the log and breaking the recovery chain. After running this you will not be able to do a point in time recovery until you take another full/diff backups.

    What is it doing that it shouldn't be, or what's it not doing that it should?

    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
  • Gilla Monster, I dont know why you are trying to change the subject of my post. Actually what I requested is that " IF SOME ONE CAN LOOK AND FIX THIS SCRIPT SO THAT IT CAN SHRINK THE LOG OF ALL USER DATABASES." Please only comment on this if you can. Also please do not try to comment on any thing else.

    Thanks and Cheer

    Basit Ali Farooq
    MCITP Database Administrator
    Microsoft Certified Professional Developer (Web Applications)
    Microsoft Certified Database Administrator
    Microsoft Certified Systems Engineer
    Microsoft Certified Systems Administrator
    CIW Security Analyst
    Cisco Certified Network Associate

  • alimcitp (7/28/2008)


    Gilla Monster, I dont know why you are trying to change the subject of my post. Actually what I requested is that " IF SOME ONE CAN LOOK AND FIX THIS SCRIPT SO THAT IT CAN SHRINK THE LOG OF ALL USER DATABASES." Please only comment on this if you can. Also please do not try to comment on any thing else.

    Thanks and Cheer

    Hey steady on there, Gail is giving you some very sound advice and you're being rather off. Unless you answer the questions she has asked its doubtless anybody will reply as there is not enough info

    :Whistling: I would have thought with all the certification you have you would be able to trouble shoot this simple procedure yourself

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (7/28/2008)


    alimcitp (7/28/2008)


    Gilla Monster, I dont know why you are trying to change the subject of my post. Actually what I requested is that " IF SOME ONE CAN LOOK AND FIX THIS SCRIPT SO THAT IT CAN SHRINK THE LOG OF ALL USER DATABASES." Please only comment on this if you can. Also please do not try to comment on any thing else.

    Thanks and Cheer

    Hey steady on there, Gail is giving you some very sound advice and you're being rather off. Unless you answer the questions she has asked its doubtless anybody will reply as there is not enough info

    :Whistling: I would have thought with all the certification you have you would be able to trouble shoot this simple procedure yourself

    I am going to side with both Gail and Perry here - what you are trying to automate is not a GOOD thing and really shouldn't be done. With that said, you need to understand a few things before you move forward:

    1) BACKUP LOG database WITH TRUNCATE_ONLY will be deprecated in future versions, so this will definitely not work after an upgrade (TRUNCATE_ONLY is not recognized in SQL Server 2008)

    2) The replacement for the above is to switch to SIMPLE recovery, shrink the file, and then switch back to FULL. NOTE: This will break the log chain - so you really, really, really need to perform a backup immediately after this step. If not, every transaction log backup you attempt after this will fail.

    3) The order you are trying to perform this in is wrong. See number two above, the steps are:

    a) Backup database (BACKUP DATABASE...)

    b) Switch database to SIMPLE

    c) Shrink the LOG file

    d) Switch database to FULL recovery

    e) Backup database

    f) Backup transaction log

    I will restate that this is not a GOOD idea, especially since you are looking at doing this every month. That means, every month your log file is going to grow back to what it needs (+20GB) to perform the month end process. Since you have to have that space available for the logs to grow anyways - why do you need to shrink them? What is the purpose of shrinking the log files?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams (7/28/2008)


    I am going to side with both Gail and Perry here

    not to mention the tone with which the words echoed to whom i would class as a well respected and competent user of the forum.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Fully agreed - Either respond politely and answer the questions or you will never receive any sort of assistance.

  • Dont need to be rude. I have fixed my script without any of your help. Any way I want tell you that this is the company requirement. I know how to manage the database and no body needs to tell what to do or what not to do unless asked. I believe this forum is to help people. I have asked the simple question in my post that CAN SOME ONE PLEASE HELP WITH THE SCRIPT PROVIDED and in response I got suggestions why you want to do that? You can do this you can do that? and actually no one did bother to help me with that problem and this indicates that nobody understands that script. One Suggestion only reply to the posts which you are able to answer and DO NOT answer the post which you cannot understand.

    Thanks for no help

    Good Day, Ali

    Basit Ali Farooq
    MCITP Database Administrator
    Microsoft Certified Professional Developer (Web Applications)
    Microsoft Certified Database Administrator
    Microsoft Certified Systems Engineer
    Microsoft Certified Systems Administrator
    CIW Security Analyst
    Cisco Certified Network Associate

  • The problem with the script is that it was written by someone who doesn't understand flow control, doesn't understand the scope-limitations of dynamic SQL and doesn't understand code sequentiality.

    I see at least a dozen logic, coding and procedural errors in it.

    Whoever wrote it should not be allowed to administer databases till they've learned a little more. That person is placing the whole business at risk, and their manager needs to understand that this ignorance and attitude is making it possible that they will actually end up losing a month's data with no way to get it back. None.

    I don't know who wrote that script for you, but don't trust that person. They think they know more than they actually do. From the code, I'm going to hazard a guess that the person is a procedural programmer (probably Visual Basic, maybe something else like that), who hasn't yet learned proper SQL programming.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks for your suggestions but this is not me who written the script this is written by some one else in the company and that is why its really complecated for me to understand this as I am not the SQL Developer. I have written my own script to shrink the logs now so I am not worried about this script any more. Any way thanks for your comments at least you are the first person who answered my post properly.

    Cheers, Ali

    Basit Ali Farooq
    MCITP Database Administrator
    Microsoft Certified Professional Developer (Web Applications)
    Microsoft Certified Database Administrator
    Microsoft Certified Systems Engineer
    Microsoft Certified Systems Administrator
    CIW Security Analyst
    Cisco Certified Network Associate

  • alimcitp (7/29/2008)


    Thanks for no help

    If you read all of my last post you would have seen a request for more information on exactly what was wrong with it. Had you answered that, I'd have happily helped you fix your script. However I'm not going to debug something knowing only that "I want this script to shrink the database log of all user databases one by one if its greater than 20GB. But it does not happen."

    'It does not happen' is not enough information. You know your system, I don't, I can't see the errors and I can't see what is or is not happening.

    You wouldn't take your car to the mechanic and tell him "It's not working properly", would you?

    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 23 total)

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