GO within a stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

GO within a stored procedure

Author

Topic

CanadaDBA

583 Posts

Posted - 2005-11-01 : 14:46:05

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?

Canada DBA

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-11-01 : 15:00:03

"GO" on a line by itself is a batch terminator. The query engine interprets everything up to and not beyond the "GO" as a batch.

I am not sure if you need the "GO"s in this script, but you can schedule the script (not as a procedure of course) with "GO"s in SQL Agent, or you can use EXEC. Personally, I would go with the SQL Agent job.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-02 : 00:44:59

Why do you want to alter database using stored Procedure?

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-11-02 : 01:28:18

in addition, if an error is encountered, everything gets rolled back

if you want to commit each statement, use begin tran and end tran per each statement

Admin side: I think your problem is how to handle the file growth?
explore frequent log backups to free up the space and maintain at a manageable level (restricted file growth),
IMHO, the task you specified is too dangerous to routinely perform

--------------------
keeping it simple...

CanadaDBA

583 Posts

Posted - 2005-11-02 : 08:41:10

quote:
Originally posted by jen

IMHO, the task you specified is too dangerous to routinely perform

I backup all the databases two times every night. One is after business day and another is after maintenance jobs, i.e. integrity check and optimization.

The T-Logs are backued up every hour during the business day.

But still the T-Log size are too big and the developers/support team cannot restore the last night backups on their local. I have to restore on one of my local servers, shrink and then create backup for them.

Why this task is too dangerous? I'm just shrinking the DBs!

And my next step is to generalize the script with using sysdatabases and same comands in a loop. Is it bad too?

Canada DBA

CanadaDBA

583 Posts

Posted - 2005-11-02 : 08:50:26

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?

Canada DBA

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-11-02 : 09:03:57

Why not just use "sp_MSforeachdb"?

quote:
Originally posted by CanadaDBA

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!

I would toss everything into SQL Jobs. They will need to be there when you automate them anyway, right? Then all you need to do is backup the MSDB database, which you should do anyway.

quote:
Originally posted by CanadaDBA

If I remove the GOs, then does the SQL Server starts shrinking all DBs all together?

SP's run one line at a time. They won't all shrink at once.

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-11-02 : 09:10:10

quote:
Originally posted by jen

in addition, if an error is encountered, everything gets rolled back

if you want to commit each statement, use begin tran and end tran per each statement

If you use begin tran then everything is rolled back. If an error occurs in a proc and you are not using transactions then the statement that failed will fail but all the others will not be rolled back. Also, I pretty sure there is not a "roll back" for shrinking.

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-11-02 : 09:16:11

The shrinking is not good for production systems, since the re-expansion of the file tends to be an expensive operation in terms of disk usage. The time spent getting the next increment ready is added to the time it takes to run the insert/update that required more space. In fact, if you have multiple inserts/updates looking for that new space to grow into, you could have multiple users saying "the system is slow".

CanadaDBA

583 Posts

Posted - 2005-11-02 : 09:24:49

This is good point you are mentioning because I have automated imports every morning and if I shrink ALL the DBs, then what happens on Monday morning?!!

One of my DBs has 3.5GB database size and 9.5GB T-Log size. Doesn't this much size hurt the performance?

quote:
Originally posted by mcrowley

The shrinking is not good for production systems, since the re-expansion of the file tends to be an expensive operation in terms of disk usage. The time spent getting the next increment ready is added to the time it takes to run the insert/update that required more space. In fact, if you have multiple inserts/updates looking for that new space to grow into, you could have multiple users saying "the system is slow".

Canada DBA

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-11-02 : 10:01:02

quote:
Originally posted by CanadaDBA

One of my DBs has 3.5GB database size and 9.5GB T-Log size. Doesn't this much size hurt the performance?

For the free space? No. If the DB has a good deal of free space at the end of the file, why worry. SQL Server won't need to go out and "grow" the files if you need to do a bulk import or a massive update or even a reindex. I've always left some room to grow on my DB files. If you have the disk space and the disks are not X% full then I'd say its ok to have the T-log be 9.5gb. Just keep your T-log backed-up and it will take care of itself.

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

CanadaDBA

583 Posts

Posted - 2005-11-02 : 11:43:48

Just keep your T-log backed-up and it will take care of itself.

I backup the TLogs every hour starting at 6am to 8pm. Is it what you meant in your comment? How it takes care of itself? The size is still growing.

I have enough space but I am worry about the restore time. The backups take long time to be restored on our Development server. Or they are not possible to be restored on some local machines.

Canada DBA

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-11-02 : 12:03:21

I would put money on the maintenance jobs and to a lesser extent the morning data imports being responsible for the large transaction log. Try this. Set up a job in SQL agent to run dbcc sqlperf (logspace) every hour or so, and append the output to a file. This should give you an idea of when the transaction logs hit their daily high point.

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-11-02 : 14:04:40

quote:
Originally posted by CanadaDBA

Just keep your T-log backed-up and it will take care of itself.

I backup the TLogs every hour starting at 6am to 8pm. Is it what you meant in your comment? How it takes care of itself? The size is still growing.

I have enough space but I am worry about the restore time. The backups take long time to be restored on our Development server. Or they are not possible to be restored on some local machines.

Canada DBA

What I mean by that is when you do a full or differential backup if marks the used space in your transaction log so it can be reused. When you do a T-log backup it will make all of that 9gb T-log free space. Then as long as you keep doing t-log backups during the day it will keep your free space avail. Then on the event of a huge import, update, or reindex the T-log will not need to grow. If you don't ever backup the DB then it will keep growing until you are out of disk space. Just as mcrowley said the growing process causes slow downs so keep some free space in those files. So by taking care of itself I mean freeing up the used space. I'd leave it as large as it is and make sure it's all free space. There is some process that you're running that makes it grow to 9gb so why not leave it there so that process doesnt have to wait for the file to grow?

Does that help?

Another point here to think about. Dont use DBCC SHRINKDATABASE. If you are going to use that then use SHRINKFILE instead.

Have fun,

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

Westley
Posting Yak Master

229 Posts

Posted - 2005-11-03 : 01:46:04

Canada DBA, I got a feeling that you got a open tran in your DB, thats the reason why you log file keep growth, do a dbcc opentran on all your DB to see if there are any that is days old, from what i read, you seems like the trans log never drop even if you backup your log file.

mr_mist
Grunnio

1870 Posts

Posted - 2005-11-03 : 04:19:35

Shrinking databases like this is a pointless activity, they will always grow back as you add in data.

You should address the real issue, which is your lack of storage.

-------
Moo. :)

CanadaDBA

583 Posts

Posted - 2005-11-03 : 08:57:45

I ran the DBCC OPENTRAN and got the following result for all of the databases:
No active open transactions.

quote:
Originally posted by Westley

Canada DBA, I got a feeling that you got a open tran in your DB, thats the reason why you log file keep growth, do a dbcc opentran on all your DB to see if there are any that is days old, from what i read, you seems like the trans log never drop even if you backup your log file.

Canada DBA

CanadaDBA

583 Posts

Posted - 2005-11-03 : 09:54:32

Thank you all for the replies!

Fortunately, the storage is not a problem in my case. The backups take long time to be restored on my Dev server. I think it is because of allocation space for the log file which is too big.

According to your replies I have to locate the cause. Well, I have huge import in the morning and optimization process after the business day. Having about 100 users, I imagine there are large amount of transaction. But (1)I don't know how can I determine it.

And (2)are the imports being logged? How can I prevent it? Does the following work?


Set the database recovery to SIMPLE
do the imports
Set the database recovery to FULL

Canada DBA

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-11-03 : 10:16:16

quote:
Originally posted by CanadaDBA

Thank you all for the replies!

Fortunately, the storage is not a problem in my case. The backups take long time to be restored on my Dev server. I think it is because of allocation space for the log file which is too big.

According to your replies I have to locate the cause. Well, I have huge import in the morning and optimization process after the business day. Having about 100 users, I imagine there are large amount of transaction. But (1)I don't know how can I determine it.

And (2)are the imports being logged? How can I prevent it? Does the following work?

Set the database recovery to SIMPLE
do the imports
Set the database recovery to FULL

Canada DBA

Do you do the restore to dev when someone asks or every night in a job?

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

CanadaDBA

583 Posts

Posted - 2005-11-03 : 13:33:16

SQLServerDBA Dan: Do you do the restore to dev when someone asks or every night in a job?
I am supposed to automate the nightly restore but for now I do on demand.

Canada DBA

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-11-07 : 21:11:44

Uhhh, use a maintenance plan IMMEDIATELY then to start doing it every night. You can then, at your leisure, create an automated procedure. Look at http://weblogs.sqlteam.com/tarad/category/95.aspx for some scripts you can use. Her scripts work.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources