| Author |
 Topic  |
|
|
kot
Starting Member
7 Posts |
 Posted - 10/14/2005 : 13:33:26
|
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
|
| change the database to SIMPLE mode |
 |
|
|
kot
Starting Member
7 Posts |
Posted - 10/14/2005 : 15:16:45
|
quote:
Originally posted by anuj164
change the database to SIMPLE mode
Thank you! Can you provide the exact syntax, though? |
|
|
|
anuj164
Starting Member
USA
49 Posts |
Posted - 10/14/2005 : 15:31:38
|
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. |
|
|
|
kot
Starting Member
7 Posts |
Posted - 10/14/2005 : 15:39:03
|
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. |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 10/14/2005 : 15:56:26
|
ALTER DATABASE xyz SET RECOVERY SIMPLE
rockmoose |
Edited by - rockmoose on 10/14/2005 16:00:16 |
|
|
|
kot
Starting Member
7 Posts |
Posted - 10/14/2005 : 17:38:25
|
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 :( |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 10/14/2005 : 17:57:59
|
You're welcome, just don't forget to set it back 
Don't know about sybase unfortunately.
rockmoose |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/15/2005 : 01:53:16
|
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 |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 10/16/2005 : 06:54:01
|
BOL: "Switching Recovery Models"
>> (in fact SQL Server won't start logging until you've done that, will it?)
Will it? |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/16/2005 : 08:06:51
|
"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 |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 10/16/2005 : 09:04:19
|
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 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/16/2005 : 12:02:53
|
See? I told you one of you brainboxes would know!
Kristen |
|
|
| |
Topic |
|
Is there a way to suppress use of LOG-segment?
