asked
shrinkdb of many db
Good Day, I need to shrink log files of databases of the server,but there are too many databases and out of which only few has more space available which I need to shrink. Is there any sql query which can give me the available free space of log files of all the databases and based on result I can shrink specific database log files which has more free space. I am using sql 2008 Rgds,
Just so you know, because this frequently surprises people, shrinking the logs may temporarily free up space, but usually the logs are the size that they are because that's the size they need to be to support your transactions. The two exceptions to this are when an abnormal transaction has occurred that has grown the log outside normal bounds, or when the log backups are not being run on a database in full recovery. If you don't have either of those conditions, you should be cautious shrinking the logs because they're going to grow back.
You can find the information in the DMV `sys.dm_os_performance_counters` A brilliant example of usage can be found on [Jonathan Kehayias' Blog][1] You could then build some logic in t-sql to process the log files that you want to shrink. However, you need to see why these logs have grown. If this came about through "normal" activity, then shrinking them will only cause them to grow again. If they need to be larger, leave them be. (Auto)Growing files can cause performance problems at the moment of growth and later on through file fragmentation. [1]:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/06/06/getting-log-space-usage-without-using-dbcc-sqlperf.aspx
Not only will the log files grow again (if it's from normal use) but while a log growth operation is in progress all updates to that database are blocked. And instant intialization doesn't apply to transaction log files; the whole new space must be be overwritten with zeroes first. So check your log growth settings.
DBCC SQLPERF(LOGSPACE) This will return the logfile size in percentage then shrink the logfile based on your wish before shrinking try to truncate your log file which may resolve the issue using BACKUP LOG
