SQL Server Performance

GO within a stored procedure

Discussion in 'General DBA Questions' started by CanadaDBA, Nov 1, 2005.

  1. CanadaDBA New Member

    Why in the following script, only the lines before the first GO are indicated as the stored procedure's body. Does it mean that we cannot use GO within a SP?


    CREATE PROCEDURE ksp_CompressDBs AS

    /***** Shrink Database: DB1 *****/
    ALTER DATABASE DB1 SET RECOVERY SIMPLE
    DBCC SHRINKDATABASE(DB1)
    ALTER DATABASE DB1 SET RECOVERY FULL
    GO


    /***** Shrink Database: DB2 *****/
    ALTER DATABASE DB2 SET RECOVERY SIMPLE
    DBCC SHRINKDATABASE(DB2)
    ALTER DATABASE DB2 SET RECOVERY FULL
    GO


    /***** Shrink Database: DB3 *****/
    ALTER DATABASE DB3 SET RECOVERY SIMPLE
    DBCC SHRINKDATABASE(DB3)
    ALTER DATABASE DB3 SET RECOVERY FULL
    GO

    Do I need "GO" within this stored procedure? If I remove the GOs, then does the SQL Server starts shrinking all DBs all together?

    CanadaDBA
  2. benwilson New Member

    You are right- you can't use go within a SP- the GO is taken to indicate the end of the procedure. Im not sure if it will start shrinking all the DBs together if you remove them or if it will do them sequentially though...

    'I reject your reality and substitute my own' - Adam Savage
  3. Luis Martin Moderator

    "Im not sure if it will start shrinking all the DBs together ..."
    I don't either, but why SQL should do that?.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  4. Madhivanan Moderator

    Why do you want to alter Database Thru Stored Procedure?
    Do that without sp.

    Madhivanan

    Failing to plan is Planning to fail
  5. satya Moderator

    BOL defines:
    GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer.

    SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. SQL Query Analyzer and the osql and isql command prompt utilities implement GO differently.

    Programmers executing ad hoc statements in the SQL Server utilities, or building scripts of Transact-SQL statements to run through the SQL Server utilities, use GO to signal the end of a batch.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. CanadaDBA New Member

    quote:Originally posted by Madhivanan

    Why do you want to alter Database Thru Stored Procedure?
    Do that without sp.

    Madhivanan

    Failing to plan is Planning to fail

    I have an Admin database that I keep my scripts and utilities. I am developing this script to schedule it to be run on Sundays.

    CanadaDBA
  7. Madhivanan Moderator

    Then you need to create three seperate procedures and run them in schedule

    Madhivanan

    Failing to plan is Planning to fail
  8. dineshasanka Moderator

  9. CanadaDBA New Member

    quote:Originally posted by dineshasanka

    Can't you use Job s for this work

    My concern is too keep the maintenance jobs and utilities in the Admin database. I'm not sure this is good or not.

    It seems I should have two type of Admin jobs/utilities: One as SPs in the Admin database and the other in SQL Agent Jobs. ...and probably some in DTS packages!

    Now, the question is that does this approach of managing the server suggested by the moderators or advanced DBAs?


    CanadaDBA
  10. FrankKalis Moderator

    Personally I think, having a dedicated admin db is a perfectly valid solution. Whereelse would you keep them otherwise? In master? Not the best idea, IMHO.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  11. CanadaDBA New Member

    And do you suggest to keep some works, like the one that I have in hand now, in SQL Agent as a job?

    quote:Originally posted by FrankKalis

    Personally I think, having a dedicated admin db is a perfectly valid solution. Whereelse would you keep them otherwise? In master? Not the best idea, IMHO.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)


    CanadaDBA
  12. satya Moderator

    Why creating the stored procedure, rather store them as .SQL file and take out when they are required.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. CanadaDBA New Member

    I was thinking to automate this process instead of running periodically.


    quote:Originally posted by satya

    Why creating the stored procedure, rather store them as .SQL file and take out when they are required.


    CanadaDBA
  14. satya Moderator

    Why do you want to shrink the databases periodically?
    If the disk size is not a problem then assign the free space and leave it as is.
    General it is not a good practice to created user SPs in master database.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  15. CanadaDBA New Member

    I have an Admin database and use it for my scripts and utility SPs.

    I have enough space on harddisks but I am concern about the T-Log size. One of my DBs size is 3.5GB and the TLog is 9.5GB. How do you handle this in your environment? Do you let the size grew as much as it wants?

    quote:Originally posted by satya

    Why do you want to shrink the databases periodically?
    If the disk size is not a problem then assign the free space and leave it as is.
    General it is not a good practice to created user SPs in master database.

    CanadaDBA
  16. benwilson New Member

    If you back up the transaction logs regularly, they probably wont grow to that kind of size. Our transaction logs were growing up to 8gb or so before i changed the backup strategy to a full backup daily and tlog backups every 30 minutes. Now they sit at around 2gb (and that only because i reindex the tables weekly- otherwise they would be < 100mb)

    Maybe you need to look at the cause of the large logs rather than just shrinking them. If it is going to grow to 9.5GB again no matter what you do, it is probably better to leave them as the larger files!

    'I reject your reality and substitute my own' - Adam Savage
  17. Haywood New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by CanadaDBA</i><br /><br />My concern is too keep the maintenance jobs and utilities in the Admin database. I'm not sure this is good or not. <br /><br />It seems I should have two type of Admin jobs/utilities: One as SPs in the Admin database and the other in SQL Agent Jobs. ...and probably some in DTS packages!<br /><br />Now, the question is that does this approach of managing the server suggested by the moderators or advanced DBAs?<br /><br /><br />CanadaDBA<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />This is the preferred method. You're on the right path. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />You'll end up with a whole mix of your admin db, sp's, tables (process logging), jobs and dts stuff.<br /><br />Naming conventions are the key...
  18. FrankKalis Moderator

    Observe your T-Logs to get a feeling about their max size between two full backups. Add some extra space to that number to allow for eventualities. If the logs grow beyond this estimated number, I would shrink it to this number back again. I wouldn't shrink it, just because of the shrinking itself. You say space is no issue, so why bother with the shrinking at all. When the log need to be expanded again, SQL Server locks the schema during it expands the file physically. On write intensive databases this can cause trouble.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  19. satya Moderator

  20. CanadaDBA New Member

    You are quite right. I need to find out the cause of the larger logs. I have Optimization and Integerity checks scheduled to be run every night. I have full backups before and after these jobs. Every an hour I have Transaction log backup starting at 6am to 8pm. But the T-Log size doesn't decrease. What do you mean by Full backup? May be my problem is here. My database recovery has been set to FULL and I use a maintenance job to create the backup. Is Full Backup something else? If not, why the TLog size doesn't decrease?


    quote:Originally posted by benwilson

    If you back up the transaction logs regularly, they probably wont grow to that kind of size. Our transaction logs were growing up to 8gb or so before i changed the backup strategy to a full backup daily and tlog backups every 30 minutes. Now they sit at around 2gb (and that only because i reindex the tables weekly- otherwise they would be < 100mb)

    Maybe you need to look at the cause of the large logs rather than just shrinking them. If it is going to grow to 9.5GB again no matter what you do, it is probably better to leave them as the larger files!

    'I reject your reality and substitute my own' - Adam Savage

    CanadaDBA
  21. FrankKalis Moderator

    Doing a backup doesn't imply shrinking a file. However, it is mandatory for the file to become shrinkable. If you never do a backup of the log in Full Recovery Mode, the log will continue to grow until you run out of space. Then SQL Server will stop.
    If you want to shrink a file after a backup you need to add that command to your backup script.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  22. satya Moderator

    True, when you perform a FULL backup or LOG BACKUP it will truncate the virtual log portion to give way for next set of transactions. It will not attempt to reduce the physical size of log and you must use DBCC SHRINKFILE in order to reduce the size.

    As a test you can monitor the activities on the database for a week and take a note of Tlog sizes every 10 mins. in order to assess what is the ideal size for the Transaction log.

    BTW are you performing replication for this database?
    Any large updates or bulk inserts?
    How about optimization jobs?

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page