asked
shrinking all log files in the server.
IS there any script to shrink all the logfiles in our server except system databases and our database is in the simple recovery model.And also same has to be automated as a job which has been cheduled as once in a week.
If a database is in SIMPLE recovery, and the logfile has been shrunk once, then there's a real chance that the logfile is now the right size - the transaction log is used, after all, for operations such as index rebuilds, data loads etc. However. There are a couple of approaches you can use: * use of the undocumented stored procedure
sp_msforeachdb to loop through all the databases, checking for recovery model, and running a [DBCC SHRINKFILE][1] command. * roll your own loop - less Dynamic SQL required The script for the latter option might look like: DECLARE @TheBigShrink NVARCHAR(MAX) SELECT @TheBigShrink = N'' SELECT @TheBigShrink = @TheBigShrink + 'USE [' + DB_NAME(dbid) + '] DBCC SHRINKFILE (N''' + name + ''', 1) ' FROM sysaltfiles WHERE fileid = 2 AND dbid IN ( SELECT database_id FROM sys.databases WHERE recovery_model_desc = 'SIMPLE' AND database_id > 4 ) SELECT @TheBigShrink --EXEC (@TheBigShrink) -- uncomment this line when happy with the output. [1]:
http://technet.microsoft.com/en-us/library/ms189493.aspx
Its super.But If i use GO command between every log file it shows error message. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'GO'. DECLARE @TheBigShrink NVARCHAR(MAX) SELECT @TheBigShrink = N'' SELECT @TheBigShrink = @TheBigShrink + 'USE [' + DB_NAME(dbid) + '] DBCC SHRINKFILE (N''' + name + ''', 1) '+CONVERT ( nvarchar(2) , CHAR(13) + CHAR(10))+ 'GO'+CONVERT ( nvarchar(2) , CHAR(13) + CHAR(10)) FROM sysaltfiles WHERE fileid = 2 AND dbid IN ( SELECT database_id FROM sys.databases WHERE recovery_model_desc = 'SIMPLE' AND database_id > 4 ) SELECT @TheBigShrink EXEC @TheBigShrink
15 People are following this question.
