Kind of a wierd question about Transaction Logs
-
OK, I have a situation (not a bad one) that I had my boss explain to me but still didn't make logical sense to me...so I am posing the question here.
Recently we had to do a data migration project...during the project I looked through some of the DB's to make sure the space allocation for the DB and log were appropriate on the new server. In some instances it had looked like the DB was set to full recover mode when the data was initially loaded.
The log file space was almost identical to the DB file space. For example, a DB that was set to simple mode, and had a transaction log of like 3 mb's had an available area of 17 Gb's which what was the size of the db.
So I thought well that is a huge waste of space, so lets try to reduce the size of that file.
I went into EM, to the DB, right clicked -->shrink DB, then clicked Files, then selected the log file.
For the shrink action I selected Shrink File To: and put in like 50 mb's.
Clicked ok, and when it finished not only did it NOT shrink the log file down to 50 mb's, it actually added like 50 mb's to the file so instread of the file being 4 mb's now it is 54 mb's
I asked my boss about it and he said, that until you reach the END of the log file, there is nothing you can do...because at one point in time when the file was created or grew to that size to account for the transactions occuring.
He had said he had a script to get around that problem, which would apparently do something to fill that log file...
However I am still confused and think that this is really stupid if you cannot re-capture that excess log file space...or that the commands in SQL that are supposed to shrink the file don't actually work...
If I am wrong or off base please tell me because I am a little confused here...
Thanks for reading the book
-
If I didn't understand you wrong you want to make the log file smaller, right. What you need to do is in fact simple.
DUMP TRAN [DB_NAME] WITH NO_LOG
DUMP TRAN [DB_NAME] WITH TRUNCATE_ONLY
Then in EM, go to the database, right click find properties, on the "Transaction Log" tab set "Restrict file growth" to a value that meets your need and "OK" it. When the log file size reduces to the size you set you can (I think should) set this value to "Unrestricted" because possibly it'll want to grow again.
Cheers
Zubeyir
-
Back in the days of SQL 6.5 Enterprise Manager was known as Enterprise Mangler.
Personally I prefer to use T-SQL Scripts because I can save them and re-use them without trying to remember where Microsoft have hidden the function in the GUI.
As per the above post but use BACKUP LOG in versions 7 amd 2000.
CHECKPOINT
GO
BACKUP LOG [DB_NAME] WITH NO_LOG
BACKUP LOG [DB_NAME] WITH TRUNCATE_ONLY
GO
exec sp_helpdb
DBCC SHRINKFILE('file listed in sp_helpdb',50MB)
GO
exec sp_helpdb
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply