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


more ▼

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

avatar image

830 89 93 95

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

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.

more ▼

answered Jan 11, 2011 at 01:35 AM

avatar image

26.2k 18 38 48

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

avatar image

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jan 11, 2011 at 01:21 AM

Seen: 1203 times

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

Copyright 2018 Redgate Software. Privacy Policy