Solved

iteration through all active database

Posted on 2013-01-31
20
446 Views
Last Modified: 2013-02-10
I need to execute below shrink all log files from all active database.

DBCC shrinkfile('log',10)

need to help!!!
0
Comment
  • 7
  • 4
  • 4
  • +3
20 Comments
 
LVL 7

Expert Comment

by:Element1910
Comment Utility
Try:
EXEC sp_MSForEachDB 'DBCC shrinkfile('log',10)'

Open in new window

Read up on this stored procedure though, here before running it: http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm
0
 
LVL 6

Expert Comment

by:liija
Comment Utility
The script above won't work. Every db has different log file name.

Is it optimal that every db has a very small initial log size?

This is working script, same script can be found
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31533/

USE master
GO
DECLARE @Statement varchar (2000)

SELECT @Statement = ''
SELECT @Statement = @Statement + 'USE ?; '
SELECT @Statement = @Statement + 'SELECT ''?''; '
SELECT @Statement = @Statement + 'DECLARE @Log_Logical_FileName varchar (30); '
SELECT @Statement = @Statement + 'SELECT @Log_Logical_FileName = rtrim(name) FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement = @Statement + 'dbcc shrinkfile (@Log_Logical_FileName, 30,truncateonly); '
SELECT @Statement = @Statement + 'SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement 

EXEC sp_MSforeachdb @command1=@Statement
GO

Open in new window

0
 
LVL 7

Expert Comment

by:Element1910
Comment Utility
You could also try this to shrink all data and log files:

EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

Open in new window


but the above Expert's answer is correct as well...it will search for all log files, but still uses the sp_MSForEachDB stored procedure.
0
 
LVL 6

Expert Comment

by:liija
Comment Utility
Shrinking database files causes fragmentation. Do not shrink your database, unless you have a very good reason for it.
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
The other thing to consider here is why the transaction logs have gotten as big as they have.

Do you have databases in full recovery mode where you're not taking transaction log backups regularly ?

Just going and shrinking transaction logs is not necessarily a wise thing to do without proper backups in place as you may break your recovery chain.

You may want to read this article: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_11077-How-to-shrink-a-bloated-log-file.html that explains what causes a transaction log file to grow and steps you can take to recover space.  It also links to several other article on good transaction log management procedures.

If you do have TLOG backups in place already, and your logfiles grows large, then SQL Server obviously needs to have that space to perform your operations.  Shrinking log files and then allowing SQL Server to autogrow your logfile back to the size it needs will also lead to possible transaction log performance issues with too many VLF's in your logfile.

Then on top of that, allowing your transaction log to autogrow instead of presizing it to fit your needs can also have an impact on performance while the autogrow is happening.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
Comment Utility
THANKS Liija,

I want to execlude system database how to do that?
0
 
LVL 6

Expert Comment

by:liija
Comment Utility
Added if-clause to the script that returns if system database
USE master
GO
DECLARE @Statement varchar (2000)

SELECT @Statement = ''
SELECT @Statement = @Statement + 'IF ''?'' IN (''master'',''model'',''msdb'',''tempdb'') RETURN '
SELECT @Statement = @Statement + 'USE ?; '
SELECT @Statement = @Statement + 'SELECT ''?''; '
SELECT @Statement = @Statement + 'DECLARE @Log_Logical_FileName varchar (30); '
SELECT @Statement = @Statement + 'SELECT @Log_Logical_FileName = rtrim(name) FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement = @Statement + 'dbcc shrinkfile (@Log_Logical_FileName, 30,truncateonly); '
SELECT @Statement = @Statement + 'SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement 

EXEC sp_MSforeachdb @command1=@Statement

Open in new window

0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
Comment Utility
it works. But as this will be my base script,which will help me to build my own logic.
I tried before block in my script ,i have execulded the tempdb ,despite it's including tempdb and throwing me the error as below

Msg 5058, Level 16, State 1, Line 1
Option 'RECOVERY' cannot be set in database 'tempdb'.
declare @statement varchar(2000)
select @statement = ''
select @statement = @statement +'if ''?'' in (''master'',''model'',''tempdb'',''msdb'')return '
select @statement = @statement + 'use ?;' 
select @statement = @statement +'select ''?'';'
select @statement = @statement + 'alter database ? set recovery simple'
select @statement 

exec sp_msforeachdb @command1=@statement

Open in new window

0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
Comment Utility
in the result window,it clearly not looped inside tempdb but the query is failing saying
Msg 5058, Level 16, State 1, Line 1
Option 'RECOVERY' cannot be set in database 'tempdb'.
Please see the attached screenshot
screenshot.jpg
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 20

Expert Comment

by:Marten Rune
Comment Utility
Instead of line 9 exec sp_msforeachdb...
Could you let it do:
Print @statement
And let us see the full text of this.
Regards Marten
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
Comment Utility
how does this will help?
Line 7 does the same select @statement .

if '?' in ('master','model','tempdb','msdb')return use ?;select '?';alter database ? set recovery simple

 this what it returned.
I don't think there wrong with query.But i like to understand why SQL is not able to exclude tempdb
0
 
LVL 6

Expert Comment

by:liija
Comment Utility
Might be a bug in this undocumented feature.
Also tried this:
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
ALTER DATABASE ? SET RECOVERY SIMPLE     
END'

Open in new window


Above also tries to modify tempdb... doesn't work as one would imagine

Also found out that there are other problems with MSforeachdb. Some people have rewritten that logic
http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior.aspx
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
Well, not sure why you are using the unsupported "foreachdb" when you can use :

SELECT databases.name database_name
      ,master_files.name logical_name
      ,databases.recovery_model_desc recovery_model
      ,databases.compatibility_level
      ,master_files.type_desc type_desc
      ,master_files.physical_name physical_name
      ,master_files.state_desc state_desc
      ,master_files.size /128.0 size_allocated_MB
FROM sys.master_files
LEFT JOIN sys.databases ON master_files.database_id = databases.database_id

Open in new window


And from there you can get the logical names, set up some dynamic SQL or whatever...

So, to exclude 'system' databases, then simply add that into the WHERE clause. e.g.

SELECT databases.name database_name
      ,master_files.name logical_name
      ,databases.recovery_model_desc recovery_model
      ,databases.compatibility_level
      ,master_files.type_desc type_desc
      ,master_files.physical_name physical_name
      ,master_files.state_desc state_desc
      ,master_files.size /128.0 size_allocated_MB
FROM sys.master_files
LEFT JOIN sys.databases ON master_files.database_id = databases.database_id
where databases.name not in ('model','master','tempdb','resources','msdb')

Open in new window


so now we want a bit of dynamic SQL to print (or execute) and STRONGLY suggest doing the print and then simply picking and choosing which commands to run...

using the above as a query, build a string @sql that we can then execute or print.

declare @sql varchar(8000)

;with cte_db as
( 
SELECT databases.name database_name
      ,master_files.name logical_name
      ,databases.recovery_model_desc recovery_model
      ,databases.compatibility_level
      ,master_files.type_desc type_desc
      ,master_files.physical_name physical_name
      ,master_files.state_desc state_desc
      ,master_files.size /128.0 size_allocated_MB
FROM sys.master_files
LEFT JOIN sys.databases ON master_files.database_id = databases.database_id
where databases.name not in ('model','master','tempdb','resources','msdb')
)
select @sql = isnull(@sql+';'+char(13)+char(10),'') + 'dbcc shrinkfile (''' +logical_name+''',10)'
from cte_db
where type_desc = 'log'

print @sql

-- exec(@sql) -- dangerous in my opinion

Open in new window


And to echo sjwales concerns above, and having read his Article, you can also read a couple of mine about structure and logs - and not just for the accidental DBA  :)

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_657-Managing-the-Transaction-Log-for-the-Accidental-DBA.html

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_691-Managing-Fragmentation-for-the-Accidental-DBA.html
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
Comment Utility
hello Mark Sir,

First of all i thank you to give 2 valuable post and wonderful query which fulfill one part of my requirement.

And you are my one of the best experts in this area.I simply love your knowledge.

I do like to know ,how the query is able to loop through all the log file.

Similar I tried below query

declare @database varchar (3000)
;with cte_db as
( 
SELECT databases.name database_name
      ,master_files.name logical_name
      ,databases.recovery_model_desc recovery_model
      ,databases.compatibility_level
      ,master_files.type_desc type_desc
      ,master_files.physical_name physical_name
      ,master_files.state_desc state_desc
      ,master_files.size /128.0 size_allocated_MB
FROM sys.master_files
LEFT JOIN sys.databases ON master_files.database_id = databases.database_id
where databases.name not in ('model','master','tempdb','resources','msdb')
)

select @database='use master go ;'+'alter '+database_name +'set recovery full' from cte_db
print @database 

Open in new window


but it returned only one database.Where i'm going wrong?
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
OK, it *should* return all databases in that instance...

Always check to first see what the straight query returns,

Then you will notice your select @database is not concatenating...

ie

select @database = isnull(@database+';'+char(13)+char(10),'') + 'alter '+ -- etc

Now, I put a carriage return + line feed for ease of reading.

Then after you have your list of DB's, then put in the 'use master' at the beginning, although, I would suggest you actually run it from master and not worry about the "set" below.

ie set @database = 'use master go ' + @database

Does that make sense ? Check out the previous examples again, and you will notice the difference
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
Comment Utility
Still I'm not clear.What does this isnull(@sql+';'+char(13)+char(10),'')?

why do we need to concatenate char(13)+char(10)

is @sql return null value then it will replace with ; but like understand the code char(13)+char(10),'')---and usage of ' '?

what does this mean sir? Kindly help me to understand.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
OK, when we add a string to a NULL value, then we still get NULL.

We are using that to our advantage. And I will explain more later...

char(13)+char(10)  is the carriage return + line feed characters. Basically starts a new line. So, when we print it looks structured. the char() command converts the ascii number into a character examples : char(34) is the double quote, char(44) is a comma, char(65) is the letter 'A' etc.

So, it is a way we can easily include special characters like tabs, linefeeds, etc

If we want to check a NULL value, we use the ISNULL()  function and it basically has the form of ISNULL(<object to check>, <if it is NULL then use this value instead>)

so what we are doing with  isnull(@sql+';'+char(13)+char(10),'')  is creating the seperator between each of the objects from our select statement.

The first time in, @SQL is a NULL value because the only thing we have done is to declare it. So, the very first touch of @SQL is to check to see if it is NULL and if it is make it an empty string. But next time it is "touched" it is no longer NULL, so the concatenate (ie the + ) of those characters (ie the ';' and the two special characters) now works.

When you do a select <some object> from <a data source> you return multiple rows, so, we use that to build up a string when we select into a variable. But, we have to make sure we are adding to the variable and not simply replacing the contents of the variable

e.g.  if you were to copy and paste the code below into a new query window, to run through the examples one by one (highlight from "declare" through to "go" and press F5 - the keyboard shortcut for "run"):
declare @v varchar(100)
set @v = '1'    -- first time sets the value to be a '1'
set @v = '2'    -- second time replaces '1' with a '2'
set @v = @v + '3'   -- now we are concatenating the '3' to what ever we already had in @v and get '23'
print @v
go

-- now doing the same with a select
-- but a select has to be from a datasource
-- for our purposes, our datasource will simply be the subquery (select 1 as my_column union all select 2 union all select 3) as my_datasource
-- so lets first do a simple select from that datasource, 
-- oh and dont forget the single quotes around the number otherwise we will get a different result

select my_column
from (select '1' as my_column union all select '2' union all select '3') as my_datasource
go

-- now we can do similar things as the SET
declare @vs varchar(100)
select @vs = my_column      
from (select '1' as my_column union all select '2' union all select '3') as my_datasource
print @vs

-- to see the content of @v you have to either print out that variable or select it. 
-- but it will be whatever the last value was returned from our datasource ie a '3'
-- so, to concatenate, we have to add the selected contents into the variable

select @vs = @vs + my_column      
from (select '1' as my_column union all select '2' union all select '3') as my_datasource
print @vs

-- if we print out the above we will see the content of @vs will be a '3' from the previois select, and then a '123' from the above select.
go

-- so now, lets try to combine that isnull thingy

declare @vn varchar(100)
print isnull(@vn,'this value will be used if @vn is NULL')    -- hope that becomes self evident when you run
print isnull(@vn+';','this value will be used if @vn is NULL')    -- not how @vn + ';' still returns a NULL

select @vn = isnull(@vn+';','') + my_column      
from (select '1' as my_column union all select '2' union all select '3') as my_datasource
print @vn
go

-- and that last one shows pretty much what we are doing for the @SQL
-- the very first time the variable @vn is null 
-- so the isnull(@vn + ';','') + my_column will make it an empty string and appends the first row ie ('') + '1' ie '1'
-- the next row appends '2' to @vn so the isnull(@vn + ';','') + my_column becomes  ('1' + ';') + '2' ie '1;2'
-- the last row appends '3' to @vn so the isnull(@vn + ';','') + my_column becomes  ('1;2' + ';') + '3'

-- now to add in the carriage return and line feed we can use the char() function, or, 
-- we could also include an actual carriage return+line feed but makes it a bit harder to read 

declare @vn varchar(100)
select @vn = isnull(@vn+';
','') + my_column      
from (select '1' as my_column union all select '2' union all select '3') as my_datasource
print @vn
go

Open in new window

0
 
LVL 5

Author Closing Comment

by:VIVEKANANDHAN_PERIASAMY
Comment Utility
Awesome Mark!!! Thanks alot. Appreciate your explanation. Your are coolest person i found in EE. Thanks for being so patient.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Your words are way too kind, but thank you wholeheartedly...
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now