SQL Server Performance Forum – Threads Archive

GO within a stored procedure

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

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

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

Why do you want to alter Database Thru Stored Procedure?
Do that without sp. Madhivanan Failing to plan is Planning to fail

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.

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

Then you need to create three seperate procedures and run them in schedule Madhivanan Failing to plan is Planning to fail

Can’t you use Job s for this work —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

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

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)

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

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.

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

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.

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

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

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by CanadaDBA 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 <hr height="1" noshade id="quote"></blockquote id="quote"> This is the preferred method. You’re on the right path. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> You’ll end up with a whole mix of your admin db, sp’s, tables (process logging), jobs and dts stuff. Naming conventions are the key…

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)

May check by bloghttp://www.sql-server-performance.com/absolutenm/templates/?a=260&z=1 about the referred process to keepup the size. For them also you need to consider all the optimization jobs and resize it to the level.s 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.

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

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)

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.

]]>