x

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,
more ▼

asked Jan 11, 2011 at 01:21 AM in Default

pits gravatar image

pits
830 83 91 92

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.
Jan 11, 2011 at 04:47 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Jan 11, 2011 at 01:35 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

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.
Jan 11, 2011 at 02:57 AM David Wimbush
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jan 11, 2011 at 02:05 AM

Hariharan gravatar image

Hariharan
21 1

Hariharan - DBCC SQLPERF is a valid command to find this sort of information out, however the DMVs supply the same information in a much more comfortable way. DBCC is always clumsy when you want to use the output to drive some sort of process.
Jan 11, 2011 at 02:15 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x68

asked: Jan 11, 2011 at 01:21 AM

Seen: 1071 times

Last Updated: Jan 11, 2011 at 01:36 AM