| Author |
Topic |
|
kot
Starting Member
7 Posts |
 Posted - 2005-10-14 : 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
49 Posts |
Posted - 2005-10-14 : 15:07:35
|
| change the database to SIMPLE mode |
 |
|
|
kot
Starting Member
7 Posts |
Posted - 2005-10-14 : 15:16:45
|
quote:
Originally posted by anuj164change the database to SIMPLE mode
Thank you! Can you provide the exact syntax, though? |
|
|
|
anuj164
Starting Member
49 Posts |
Posted - 2005-10-14 : 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 - 2005-10-14 : 15:39:03
|
quote:
Originally posted by anuj164Expand 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
3279 Posts |
Posted - 2005-10-14 : 15:56:26
|
| ALTER DATABASE xyz SET RECOVERY SIMPLErockmoose |
|
|
|
kot
Starting Member
7 Posts |
Posted - 2005-10-14 : 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
3279 Posts |
Posted - 2005-10-14 : 17:57:59
|
You're welcome, just don't forget to set it back  Don't know about sybase unfortunately.rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-15 : 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
3279 Posts |
Posted - 2005-10-16 : 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
22859 Posts |
Posted - 2005-10-16 : 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 loggingMake 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
3279 Posts |
Posted - 2005-10-16 : 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
22859 Posts |
Posted - 2005-10-16 : 12:02:53
|
| See? I told you one of you brainboxes would know! Kristen |
|
|
|
|
Is there a way to suppress use of LOG-segment?