Script which will shrink all databases

Hi,

May somebody send script which will shrink all databases on server
(SQL 2005). I think about DBCC SHRINKFILE and DBCC SHRINKDATABASE.

I need do this at many servers, so I can't do this manually.

Thanks

--
Regards

Script which will shrink all databases

There are multiple ways of doing this.
Create a .CMD file with this statement:
------------------------------------------------------------------------------------------------------------------------------------------------
sqlcmd -s <servername1> -Q "EXEC sp_MSForEachDB @Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @replacechar = '?'" -c go
sqlcmd -s <servername2> -Q "EXEC sp_MSForEachDB @Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @replacechar = '?'" -c go
sqlcmd -s <servername3> -Q "EXEC sp_MSForEachDB @Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @replacechar = '?'" -c go
sqlcmd -s <servername4> -Q "EXEC sp_MSForEachDB @Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @replacechar = '?'" -c go
sqlcmd -s <servername5> -Q "EXEC sp_MSForEachDB @Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @replacechar = '?'" -c go
------------------------------------------------------------------------------------------------------------------------------------------------
Replace the <servername> with your servername
For each server add a new line and with the changed servername

(to create a CMD file: open notepad and paste the final script and
save it as scriptfile.cmd)

Once you have the CMD file, open commandprompt and go to the drive
location and enter the name of the file

In my case it is:
------------------------------------------------------------------------------------------------------------------------------------------------
c:\dbccscript.cmd
------------------------------------------------------------------------------------------------------------------------------------------------

This will display all the output to the commandprompt. You can
redirect all the output to a log/txt file using -o switch in the cmd
script.

Note that sqlcmd is a new utility in MS SQL server 2005.
in SQL Server 2000 you can achieve the same using "OSQL" books online
has enuf info on this to help you rewrite it for osql.

Regards,
schal

Thanks shal, unfortunately I don't have "shell - cmd" access toserver, I have

Thanks shal, unfortunately I do not have "shell - cmd" access to
server, I have to shrink all databases from SQL script only.

What schal posted will shrink all databases for the server you are connected

What schal posted will shrink all databases for the server you are connected to:

EXEC sp_MSForEachDB @Command1 = N'DBCC SHRINKDATABASE (?, 10)', @replacechar = '?'"

But I strongly suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp first.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

It really works, I forgot that is normal storage procedure which I canrun from

It really works, I forgot that is normal storage procedure which I can
run from Query, sorry.

But what if I'd like first use SHRINKFILE all log files (all .LDF
files at server), log files because it takes me much more space than
databases? Logical name of log file is usually DatabaseName_Log.

How shrink log file for all databases, is it similar procedure?

Try this!

Try this!
It´s recovery system space quickly:
use master
go
declare curdb cursor for select name from sysdatabases where name not in
('tempdb','model','master')
declare @nmdb varchar(50)
declare @cmd varchar(1000)

open curdb
fetch next from curdb into @nmdb
while @@fetch_status = 0
begin
print '======='
print @nmdb
print '======='
select @cmd = 'Use ' + @nmdb
select @cmd = @cmd + ' if exists(select 1 from sysobjects where name =
''limpa'')
begin
DROP TABLE LIMPA
end
create table limpa (char1 char(4000))
Set NoCount On
dbcc shrinkFILE (2, notruncate)
dbcc shrinkFILE (2, truncateonly)
dbcc shrinkFILE (1, notruncate)
dbcc shrinkFILE (1, truncateonly)
declare @i int
select @i = 0
While (@i < 100)
begin
insert into limpa values (''a'')
select @i =@i + 1
end
backup log ' + @nmdb + ' with truncate_only
drop table limpa'
Execute (@cmd)
fetch next from curdb into @nmdb
end
deallocate curdb
set nocount off





--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1

Thanks, if thought it is simples way to do this.

Thanks, if thought it is simples way to do this. Could you me tell me
how at loop take:

1. name for all databases (without system dbs)
2. logical name of databases
2. logical name of transaction logs.

I need this information  for this

BACKUP LOG @DB_NAME
DBCC SHRINKFILE (@DB_LOG_NAME)

I'd like this two lines will execute for all database.

Thanks

--
Regards

Script which will shrink all databases

I wrote my own script and I'd like share it with you:


CREATE TABLE #TDatabases(
DBName nvarchar(128),
DBLogicalName nvarchar(128)
)

INSERT INTO #TDatabases
SELECT db.name DBName, mf.name DBLogicalName
FROM sys.databases db join sys.master_files mf
on db.database_id = mf.database_id
WHERE db.name not in ('master', 'tempdb', 'model', 'msdb',
'distribution') AND type_desc LIKE 'log'


SET NOCOUNT ON
DECLARE @VarDBLogicalName nvarchar(128)
DECLARE @VarDBName nvarchar(128)
DECLARE @VarRowCount int


SELECT top 1 @VarDBName = DBName, @VarDBLogicalName = DBLogicalName
FROM #TDatabases

SET @VarRowCount = @@rowcount

WHILE @VarRowCount <> 0
BEGIN

EXEC(' use ' + @VarDBName + ' backup log '+ @VarDBName + ' with no_log
dbcc shrinkfile(''' + @VarDBLogicalName + ''', TRUNCATEONLY) WITH
NO_INFOMSGS')

DELETE
FROM #TDatabases
WHERE DBName = @VarDBName

SELECT top 1 @VarDBName = DBName, @VarDBLogicalName =
DBLogicalName
FROM #TDatabases

SET @VarRowCount = @@ROWCOUNT

END

DROP TABLE #TDatabases

SET NOCOUNT OFF

This works but use something what Microsoft NOT recommend "backup log
with no_log" you can modify it how you like

--
Regards

A solution

Thanks for your post, I found a solution with :



DECLARE @DBName varchar(255)

DECLARE @DBLogicalFileName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR



select DATABASE_NAME = db_name(MaTableMasterFiles.database_id), MaTableMasterFiles.name

from sys.master_files MaTableMasterFiles

where



-- ONLINE

MaTableMasterFiles.state = 0



-- Only look at databases to which we have access

and has_dbaccess(db_name(MaTableMasterFiles.database_id)) = 1



-- Not master, tempdb or model

and db_name(MaTableMasterFiles.database_id) not in ('Master','tempdb','model')



AND type_desc LIKE 'log'



group by MaTableMasterFiles.database_id, MaTableMasterFiles.name



order by 1



OPEN DATABASES_CURSOR



FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName



WHILE @@FETCH_STATUS = 0

BEGIN



Exec ('BACKUP LOG [' + @DBName + '] WITH TRUNCATE_ONLY')

Exec ('Use [' + @DBName + '] DBCC SHRINKFILE ("' + @DBLogicalFileName + '")')



FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName

END



CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR