| Author |
Topic |
|
brianb
Starting Member
8 Posts |
 Posted - 2011-06-21 : 05:50:39
|
| Hi Guys, New to SQL Server 2008 and I would like to know if I issue: Use DBNAME GO Alter Database DBNAME Set Recovery Simple GO Alter Database DBNAME Set Recovery Full GO DBCC SHRINKFILE ('DBNAME_log', 1) GO Will I loose all my transactions in the database? I have run a full backup and log backup before hand. Is there a command to commit log transactions to the database? I presume it does that automatically when the DB is set to FULL Recovery model? I was under the impression that when you do a full backup it commits the transactions to the DB at that point.Thanks |
|
|
raghuveer125
Constraint Violating Yak Guru
285 Posts |
Posted - 2011-06-21 : 06:13:45
|
| You don't need to run dbcc shrink once you set your database recovery mode simple and switch back to full. If this one is production server database then don't do this.(Note it will reset your backup lsn number if you switch to simple)ButYou can run dbcc shrinkfile without setting simple recovery mode. You dont need to swith it for simple and full.In Love... With Me! |
 |
|
|
brianb
Starting Member
8 Posts |
Posted - 2011-06-21 : 06:30:58
|
| Thanks for the reply, is that the only issue that it will reset your backup isn number? Can you enlighten me a bit more on the ISN number please.What I wanted to really know was by doing this are my log trnsactions committed to the database? How do I tell?Thanks |
|
|
|
raghuveer125
Constraint Violating Yak Guru
285 Posts |
Posted - 2011-06-21 : 06:48:47
|
| That is LSN(log sequence number). When you take backup MSSQL Creates LSN number automaticlly for each backup if you change your recovery mode then it loos that lsn sequence number.So your problem with tran commitThen you can check it by using DBCC OPENTRAN [DatabaseName}When you take log backup it will auto commites.Can you post!Why yout are Shrinking your log file?Is it your production server?If you want to know more about open tran then see this linkhttp://msdn.microsoft.com/en-us/library/ms182792.aspxIn Love... With Me! |
|
|
|
brianb
Starting Member
8 Posts |
Posted - 2011-06-21 : 06:54:18
|
| do you have to use truncate_only to commit the changes when doing a log backup? Or does it do it simply as backup log dbname?The log file is 8gb and I need to gain some space backThanks very much! |
|
|
|
raghuveer125
Constraint Violating Yak Guru
285 Posts |
Posted - 2011-06-21 : 07:03:15
|
| truncate_only command is discontinued from SQL Server 2008In Love... With Me! |
|
|
|
brianb
Starting Member
8 Posts |
Posted - 2011-06-21 : 07:11:38
|
| Ok so as long as I issue a backup log dbname that ensures the changes are commited from log file to DB. |
|
|
|
brianb
Starting Member
8 Posts |
Posted - 2011-06-21 : 07:14:46
|
| I also have a SQL 2005 version. Would I need to use truncate_only to comit changes to the DB? Or would simply backup log dbname be enough to comit the changes |
|
|
|
raghuveer125
Constraint Violating Yak Guru
285 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-06-21 : 15:23:41
|
| Before you go down this path - why are you shrinking the log file? The only reason you should consider this is if something unusual has happened that cause the log file to grow and you know *absolutely* that the space will never be used again.When you change the recovery model from full or bulk_logged to simple, you break the log chain. Once the log chain is broken, you no longer have a recovery path across that break. For example, let's say you are backing up your database every day, transaction log backups every hour. You break the log chain at 1pm by switching to simple and back to full. From that point forward, your transaction log backups will fail until you perform another full backup.Now, let's say you perform a full backup right after that switch. So, you now have a full backup of the database at 1pm - and at 3pm the system crashes. You lose the drives where the mdf (data files) and the backup drive - and, the current backup file has not been copied to tape or moved to another system.So, you get the previous backup - restore the database (with norecovery) and start restoring your log backups. At 1pm - you can no longer restore any more transaction log backups because you broke the log chain. You just lost 2 hours of data with no way to recover.Jeff |
|
|
|
|
SQL 2008 Log File Shrink