SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Is there a way to suppress use of LOG-segment?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kot
Starting Member

7 Posts

Posted - 10/14/2005 :  13:33:26  Show Profile  Send kot an AOL message  Send kot a Yahoo! Message  Reply with Quote
We needed to drop and re-add an "identity" column on a large table last night and ran out of the log space during the first step (the drop). Truncating the log would not have helped, because it was all in a single query...

Considering, that this is a one-time operation, and that we backed the table up already (via bcp), the log-space backup was completely useless.

Is there a way to force the server not to use it for a given operation (or until further notice)? I understand, that rollback will not be possible, but we don't care -- we backed the table up already outside of the server...

Thanks!

anuj164
Starting Member

USA
49 Posts

Posted - 10/14/2005 :  15:07:35  Show Profile  Send anuj164 a Yahoo! Message  Reply with Quote
change the database to SIMPLE mode
Go to Top of Page

kot
Starting Member

7 Posts

Posted - 10/14/2005 :  15:16:45  Show Profile  Send kot an AOL message  Send kot a Yahoo! Message  Reply with Quote
quote:
Originally posted by anuj164
change the database to SIMPLE mode



Thank you! Can you provide the exact syntax, though?
Go to Top of Page

anuj164
Starting Member

USA
49 Posts

Posted - 10/14/2005 :  15:31:38  Show Profile  Send anuj164 a Yahoo! Message  Reply with Quote
To set the recovery model for a database
Expand a server group, and then expand a server.

Expand Databases, right-click the database, and then click Properties.

Click the Options tab.

In the Model list, click a recovery model.
Go to Top of Page

kot
Starting Member

7 Posts

Posted - 10/14/2005 :  15:39:03  Show Profile  Send kot an AOL message  Send kot a Yahoo! Message  Reply with Quote
quote:
Originally posted by anuj164
Expand Databases, right-click the database, and then click Properties.



Thanks! But can't this be done from command prompt, so I can automate it via and SQL script:
  • Change the mode to SIMPLE
  • Have my way with it.
  • Change the mode back


Thanks again.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/14/2005 :  15:56:26  Show Profile  Reply with Quote
ALTER DATABASE xyz SET RECOVERY SIMPLE

rockmoose

Edited by - rockmoose on 10/14/2005 16:00:16
Go to Top of Page

kot
Starting Member

7 Posts

Posted - 10/14/2005 :  17:38:25  Show Profile  Send kot an AOL message  Send kot a Yahoo! Message  Reply with Quote
Great! Thank you, rockmoose! Now I just need a Sybase equivalent -- we are trying to maintain some semblance of compatibility here :)

I was hoping, Sybase would be the same, but it is not :(
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/14/2005 :  17:57:59  Show Profile  Reply with Quote
You're welcome, just don't forget to set it back
Don't know about sybase unfortunately.

rockmoose
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/15/2005 :  01:53:16  Show Profile  Reply with Quote
You'll need a full backup after you set it back, otherwise you won't be able to recover your transaction backups (in fact SQL Server won't start logging until you've done that, will it?)

Kristen
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/16/2005 :  06:54:01  Show Profile  Reply with Quote
BOL: "Switching Recovery Models"

>> (in fact SQL Server won't start logging until you've done that, will it?)
Will it?
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/16/2005 :  08:06:51  Show Profile  Reply with Quote
"Will it?"

Yeah, well I was assuming one of you brain boxes knew I can never remember detail like that, all I remember is that in Situation A there might be a problem with Scenario B

I'm fairly sure that's the case though ...

Set DB to SIMPLE
... do stuff ...
Set DB back to FULL
... create some TLog'able stuff ...
Check LDF file - nothing growing, nothing logging
Make TLog backup - not sure what happens here, but you ain't got anything to restore it against, have you?
Make Full Backup
... NOW we have got some TLog stuff to backup ...

Kristen
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/16/2005 :  09:04:19  Show Profile  Reply with Quote
I was just assuming that setting recovery to simple / full automatically stopped or started the logging, regardless of what backups you had made.
That was not the case.
It does not start to log until a backup has been made. (log or full).
Goes for newly created databses too (starts logging after a backup).

rockmoose
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/16/2005 :  12:02:53  Show Profile  Reply with Quote
See? I told you one of you brainboxes would know!

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000