| Author |
Topic |
|
DeepGround
Yak Posting Veteran
73 Posts |
 Posted - 2011-07-18 : 12:48:01
|
| Hello forums,Whenever I want to shrink a log file down to nothing I run the following command.DBCC SHRINKFILE(<TransactionLogName>, 1)Now I want to add a "Purge log file" option to my C# program which will simply run this command. However my program will not know how to look up the name of the log file. Is there a script to shrink all log files for a specific database? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-18 : 13:11:47
|
| Wow, talk about log mismanagment....Here's a suggestion. Don't. A front end app (unless it's a database admin app) has no business messing with the log file. Especially if it's going to be used by people who have no idea what good database administration is.--Gail ShawSQL Server MVP |
 |
|
|
DeepGround
Yak Posting Veteran
73 Posts |
Posted - 2011-07-18 : 13:24:52
|
| The logs are not needed for these databases. They are simply copies of databases for running quick tests and views of a web front end application. In order to cram as many databases as they can on a server purging the log file is helpful. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-18 : 14:03:39
|
| Set the DBs to simple recovery then and leave the log alone. If you shrink it'll just grow back and then you risk running out of space.The log is most certainly needed, test or not. It's used for database consistency and durability.--Gail ShawSQL Server MVP |
|
|
|
DeepGround
Yak Posting Veteran
73 Posts |
Posted - 2011-07-18 : 14:09:54
|
| We keep all the databases in simple mode, however I see logs that sit around 4 - 10GB very often. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-07-18 : 16:11:19
|
| If your log files are 4 - 10GB, then that is the size they need to be to support the transactions being run against them. If you continually shrink the log files, when that database needs the additional log space - it will just grow again.Growing the log file is an expensive operation - and can take a long time. It will also cause file fragmentation - and I bet you have the default autogrow settings, which will create too many VLF's in the log which will affect performance also.Jeff |
|
|
|
DeepGround
Yak Posting Veteran
73 Posts |
Posted - 2011-07-18 : 16:44:24
|
| It all depends. In our production environment we have the data and log files set with plenty of extra space, so that they do not grow during operation.The thing we notice is that even after we run scripts and commit the transaction, the next day the log file is still large when the database has not been in any use.Also a copy of a production database might be brought over which is optimized for best performance with much empty space in the log file, when it is restored it may recover this empty space in an environment where performance is not needed and space is critical. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-18 : 17:04:11
|
quote:
Originally posted by DeepGround
It all depends. In our production environment we have the data and log files set with plenty of extra space, so that they do not grow during operation.
Which is great, and how things should be set upquote:
The thing we notice is that even after we run scripts and commit the transaction, the next day the log file is still large when the database has not been in any use.
Yes, because the log doesn't automatically shrink. If you shrink it and it needs to grow for those transactions and there isn't space the transactions will fail.quote:
Also a copy of a production database might be brought over which is optimized for best performance with much empty space in the log file, when it is restored it may recover this empty space in an environment where performance is not needed and space is critical.
A once-off shrink in that scenario is fine. It's repeated, regular shrinks that are a bad thing and can have some nasty effects.To answer the original question, it's not hard to query the system tables (sys.database_files) to get the name of the log file.--Gail ShawSQL Server MVP |
|
|
|
DeepGround
Yak Posting Veteran
73 Posts |
Posted - 2011-07-19 : 12:34:19
|
Here is what I tried. Keep in mind I write a sql query once in a great while.DBCC SHRINKFILE((select name from sys.database_files where type_desc = "LOG"), 1).Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-19 : 12:43:40
|
| If your databases only have a single log file, this should work for all of them:DBCC SHRINKFILE(2) |
|
|
|
DeepGround
Yak Posting Veteran
73 Posts |
Posted - 2011-07-19 : 12:56:00
|
| Oh, most of them do. Will use that for now. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-19 : 13:25:44
|
quote:
Originally posted by DeepGround
DBCC SHRINKFILE((select name from sys.database_files where type_desc = "LOG"), 1)
Shrinkfile needs a name or an id, not a query. You'd need to run the query and set up the appropriate shrinkfile commands. It's not hard to do dynamically if you're dead set on doing this (I still recommend not in the GUI. Has no place there)--Gail ShawSQL Server MVP |
|
|
|
DeepGround
Yak Posting Veteran
73 Posts |
Posted - 2011-07-19 : 14:29:35
|
| Here is the scenario. You have large groups of non-technical users who restore databases by the hundreds, delete and re-restore throughout the day, they use a simplified interface so that they can do this quickly without any SQL knowledge. This is for internal testing. The databases however come from live production where they may have huge logs, each time a database needs to be restored it can benefit from cleaning the log up and someone is not going to sit around all day cleaning logs up of spontaneously appearing and disappearing databases.It really seems logical to me in this specific scenario.Also it appears that this command does not clear out log data of open transactions, for example if you ask to shrink to 2 it may only shrink to 256 because there is open log data. If this is the case then no damage can be done to a database that will never need to be recovered to a point in time. |
|
|
|
|
Shrink Log without Log Name