SHRINKFILE on SQL 2008 R2

Good day everyone. We have about 45 DBs in our environment and we were doing a full, differential, and log backup using Backup Exec. We had a situation with Backup Exec and we needed to backup using the SQL agent for a bit.

Well, we forgot about backing up the logs since the DBs were in full recovery mode. Now some of the log files are very large. We have since created a maintenance plan to backup the logs and the space used has gone down but the all of the space is still associated with the file.

Is there a simple way I can create a job that will run tonight to shrink all of the log files so they can release the space? If so, what impact will running this have on the SQL environment regarding other processes running at the same time?
mig1980Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
DROP TABLE #CommandQueue

CREATE TABLE #CommandQueue
(
    ID INT IDENTITY ( 1, 1 )
    , SqlStatement VARCHAR(1000)
)

INSERT INTO    #CommandQueue
(
    SqlStatement
)
SELECT
    'USE [' + A.name + '] DBCC SHRINKFILE (N''' + B.name + ''' , 1)'
FROM
    sys.databases A
    INNER JOIN sys.master_files B
    ON A.database_id = B.database_id
WHERE
    A.name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )

DECLARE @id INT

SELECT @id = MIN(ID)
FROM #CommandQueue

WHILE @id IS NOT NULL
BEGIN
    DECLARE @sqlStatement VARCHAR(1000)
    
    SELECT
        @sqlStatement = SqlStatement
    FROM
        #CommandQueue
    WHERE
        ID = @id

    PRINT 'Executing ''' + @sqlStatement + '''...'

    EXEC (@sqlStatement)

    DELETE FROM #CommandQueue
    WHERE ID = @id

    SELECT @id = MIN(ID)
    FROM #CommandQueue
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mig1980Author Commented:
What will the above do? I see the drop table lines and it makes me a bit nervous.
0
Brian CroweDatabase AdministratorCommented:
The script simply cycles through each database on the server and executes...

DBCC SHRINKFILE (N'<file name>, 1)

...on each one.  The only table being dropped is the temp table created within the script to store the list of shrink commands to be executed.  This will basically shrink the log files down to their smallest possible size.  There will be some initial IO cost after the shrink as the log files expand but after a few log backups they will stabilize.

http://msdn.microsoft.com/en-us/library/ms189493.aspx
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mig1980Author Commented:
So I ran this query and it actually runs for both the data file and the log file. I only wanted the log file. Also, I receive an error stating...

"Cannot shrink log file 2 (DB_Log) because the logical log file located at the end of the file is in use."

for almost every log file. Any ideas?
0
Brian CroweDatabase AdministratorCommented:
To restrict the process to log files add one line to the WHERE clause in the #CommandQueue INSERT

...
WHERE B.type_desc = 'LOG'
    AND A.name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )

The "error" message you are receiving is likely due to a log file backup that is running.  You cannot shrink and backup at the same time so just run it the process again until you have all of the log files shrunk.  It won't hurt them to be shrunk repeatedly but if it causing the process to take too long then you can just add the ones that have completed to the NOT IN  list (shown in the example above) to exclude them from subsequent executions.

hth
0
mig1980Author Commented:
Great solution. Problem solved.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.