October 16, 2012 at 10:19 am
Perry Whittle (10/16/2012)
Marios Philippopoulos (10/16/2012)
I will probably not do that, as it seems a bit too much voodoo to me... 🙂Whats voodoo about creating a table, inserting some records and truncating it again :w00t: 😀
How do I know how many records are enough and how many GB I need to insert to cause a file rollover?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 16, 2012 at 10:54 am
Marios Philippopoulos (10/16/2012)
Perry Whittle (10/16/2012)
Marios Philippopoulos (10/16/2012)
I will probably not do that, as it seems a bit too much voodoo to me... 🙂Whats voodoo about creating a table, inserting some records and truncating it again :w00t: 😀
How do I know how many records are enough and how many GB I need to insert to cause a file rollover?
By monitoring DBCC LOGINFO and depends on the size of log, where the active log file is, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2012 at 11:14 am
Marios Philippopoulos (10/16/2012)
Perry Whittle (10/16/2012)
Marios Philippopoulos (10/16/2012)
I will probably not do that, as it seems a bit too much voodoo to me... 🙂Whats voodoo about creating a table, inserting some records and truncating it again :w00t: 😀
How do I know how many records are enough and how many GB I need to insert to cause a file rollover?
There are various ways to guess it, which may or may not work, it's best to monitor it. But personally your suggestion of looking to see which file is in use (there should be only one) and dropping all the others strikes me as less work, and therefor preferable (constructive laziness is an important virtue - often leeds to getting things right first time because you are too lazy to do them more often than you should need to - so you should encourage it in yourself).
Tom
October 16, 2012 at 1:44 pm
L' Eomot Inversé (10/16/2012)
Marios Philippopoulos (10/16/2012)
How do I know how many records are enough and how many GB I need to insert to cause a file rollover?There are various ways to guess it, which may or may not work, it's best to monitor it. But personally your suggestion of looking to see which file is in use (there should be only one) and dropping all the others strikes me as less work, and therefor preferable.
Exactly! 🙂
Sorry for the voodoo analogy, it does not at all reflect my appreciation for people's suggestions.
It's just that in this case it does not seem - to me - worth the effort to do this.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 18, 2012 at 3:30 am
As I expected, I had trouble dropping one of the "extra" log files because it was in use:
alter database [DB1] REMOVE FILE LOG_3;
However, after taking a log backup I was able to drop it:
backup log [DB1] to disk = 'E:\SQL\DB1_1.trn';
This now worked:
alter database [DB1] REMOVE FILE LOG_3;
I am now down to one log file.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 18, 2012 at 3:48 am
glad you got it resolved, DBCC LOGINFO is your friend here 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 18, 2012 at 7:52 am
Perry Whittle (10/18/2012)
glad you got it resolved, DBCC LOGINFO is your friend here 😉
It's a useful command indeed.
However, it is not really needed here.
All you have to do is go through each log in turn and try to delete it.
If it is in use, it will not let you.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 18, 2012 at 8:23 am
Marios Philippopoulos (10/18/2012)
All you have to do is go through each log in turn and try to delete it.If it is in use, it will not let you.
That's a pretty blind way of doing it. I always use dbcc loginfo and force a dummy transaction if required. Doesn't really take any extra time as I have it scripted
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply