question

lakshmi.v avatar image
lakshmi.v 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.
shrink-log-file
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

lakshmi.v avatar image
lakshmi.v answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

instead of all the char(13) and char(10) code, just embed the GO directly in the string, just like DBCC SHRINKFILE: ... DBCC SHRINKFILE (N''' + name + ''', 1) GO ' FROM sysaltfiles ...
0 Likes 0 ·
That too i tried but it is showing the same error...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.