Printable View
All,
as per this thread, I found a way to tame my ever-growing log file.
I assume I can put this in a stored proc or udf. Assuming I do, how can I make it automatically execute periodically, like once a week.
I might even like to get real fancy-like and have it execute only if the database grows to some certain size.
Any ideas?
Thanks!
Dave
Set it up as a SQL Job on the server.
-tg
My assumption was incorect - I can't put it in a SP or UDF. How can I make it a job?
Why not?
Show the SPROC you were trying to use...
Quote:
Originally Posted by szlamany
I get the following errors:Code:CREATE FUNCTION fnIAS_CompactDatabase
(
@dbName VARCHAR(32)
)
RETURNS INT
AS
BEGIN
DECLARE @deviceName VARCHAR(64)
DECLARE @fileName VARCHAR(64)
SELECT @deviceName = @dbName + 'Backup'
SELECT @fileName = 'c:\Temp\' + @dbName + '.dat'
-- (1) Create the log backup device.
--USE @dbName
--EXEC sp_addumpdevice 'disk', @deviceName, @fileName
-- (2) Perform the backup - this option may only reduce the LOG file by 50%
BACKUP LOG @dbName WITH TRUNCATE_ONLY
-- (3) Shrink the database
DBCC SHRINKDATABASE (@dbName)
RETURN 0
END
And if I try the line (1) uncommented, I get this error:Code:Server: Msg 443, Level 16, State 2, Procedure fnIAS_CompactDatabase, Line 21
Invalid use of 'BACKUP LOG' within a function.
Server: Msg 443, Level 16, State 1, Procedure fnIAS_CompactDatabase, Line 24
Invalid use of 'DBCC' within a function.
I am using MS Query analizer logged in as "sa".Code:Server: Msg 154, Level 15, State 1, Procedure fnIAS_CompactDatabase, Line 16
a USE database statement is not allowed in a procedure or trigger.
I would not think a UDF would work.
But I know that BACKUP will work in a SPROC.
USE is not needed at all, since each of these DDL statements has the DATABASE name within it already - so they don't run under the CONTEXT of a database - but actually within the server itself.
OK, I wasn't really married to the idea of making a function - I had the same issues while making it a SP. However, I revisited this based on your comments and have successfully created a SP to do the compaction of a database and reset the logfile:Quote:
Originally Posted by szlamany
Code:ALTER PROCEDURE spIAS_CompactDatabase
@dbName VARCHAR(32)
AS
BEGIN
DECLARE @deviceName VARCHAR(64)
DECLARE @fileName VARCHAR(64)
SELECT @deviceName = @dbName + 'Backup'
SELECT @fileName = 'c:\Temp\' + @dbName + '.dat'
-- (1) Create the log backup device.
--USE @dbName
EXEC sp_addumpdevice 'disk', @deviceName, @fileName
-- (2) Perform the backup - this option SHOULD reduce the LOG file to 1 MB
BACKUP LOG @dbName WITH TRUNCATE_ONLY
-- (3) Shrink the database
DBCC SHRINKDATABASE (@dbName)
EXEC sp_dropdevice @deviceName
END
/*
spIAS_CompactDatabase 'myBigFatGreekDatabase'
*/
After thinking about what you are doing I got a bit concerned - this quote from BOL...
raises some issues...Quote:
Truncating the Transaction Log
When SQL Server finishes backing up the transaction log, it automatically truncates the inactive portion of the transaction log. This inactive portion contains completed transactions and so is no longer used during the recovery process. Conversely, the active portion of the transaction log contains transactions that are still running and have not yet completed. SQL Server reuses this truncated, inactive space in the transaction log instead of allowing the transaction log to continue to grow and use more space.
Although the transaction log may be truncated manually, it is strongly recommended that you do not do this, as it breaks the log backup chain. Until a full database backup is created, the database is not protected from media failure. Use manual log truncation only in very special circumstances, and create a full database backup as soon as practical.
The ending point of the inactive portion of the transaction log, and hence the truncation point, is the earliest of the following events:
The most recent checkpoint.
The start of the oldest active transaction, which is a transaction that has not yet been committed or rolled back.
This represents the earliest point to which SQL Server would have to roll back transactions during recovery.
The start of the oldest transaction that involves objects published for replication whose changes have not been replicated yet.
This represents the earliest point that SQL Server still has to replicate.
OK, how do I do a full database backup - can it be part of the SP just before the truncate and compact? I have looked into this as well but really couldn't find the answers I was looking for.Quote:
Originally Posted by szlamany
Also you are shrinking the DATABASE - you can just shrink the LOG file itself...
See post #16 in the link in my sig - "Crazy things we do to shrink log files"
I am interested in compacting the database as well, but I have one database with a 700MB log file that I dont use and want to just be small now. I will look at your link.
See this post - follow the link within...
http://www.vbforums.com/showpost.php...32&postcount=5
Righty-ho then. Now I have this as a SPROC. Do you like this better?Quote:
Originally Posted by szlamany
Code:ALTER PROCEDURE spIAS_CompactDatabase
@dbName VARCHAR(32)
AS
BEGIN
DECLARE @fileName VARCHAR(64)
SELECT @fileName = 'c:\Temp\' + @dbName + '.bak'
-- (1) Backup the database
BACKUP DATABASE @dbName TO DISK = @fileName
-- (2) Perform the backup - this option may only reduce the LOG file by 50%
BACKUP LOG @dbName WITH TRUNCATE_ONLY
-- (3) Shrink the database
DBCC SHRINKDATABASE (@dbName)
END
/*
spIAS_CompactDatabase 'HMI_1'
*/
Well - I'm curious - and I have to test this further...
But that BOL quote seemed to say that after a FULL BACKUP the log could be shrink'd without the TRUNCATE_ONLY.
If users are in the DB that would seem to be the safest way to do it.
I usually do the TRUNCATE_ONLY business when we take production copies of DB's from customers and bring them to our shop and want to have the smallest footprint on disk for them.
After doing some research I found that...
TRUNCATE_ONLY is dangerous - it leaves the database in a non-recoverable state - a full backup is needed as soon as possible to protect the DB.
It seems a proper method to TRUNCATE a log file is to:
This successfully truncated the logfile to 1 MB on this test file.Code:BACKUP LOG ACCTFILES TO DISK="C:\somefilename"
GO
DBCC SHRINKFILE (ACCTFILES_LOG,1)
GO
That sounds like good advice. I tried it but am getting an error. Here is the current SPROC:Quote:
Originally Posted by szlamany
Here is the error when I exec the SPROC:Code:ALTER PROCEDURE spIAS_CompactDatabase
@dbName VARCHAR(32)
AS
BEGIN
DECLARE @dataFileName VARCHAR(64)
DECLARE @logsFileName VARCHAR(64)
DECLARE @logsName VARCHAR(64)
SELECT @dataFileName = 'c:\Temp\' + @dbName + '.dat'
SELECT @logsFileName = 'c:\Temp\' + @dbName + '.log'
SELECT @logsName = @dbName + '_LOG'
-- (1) Backup the database
BACKUP DATABASE @dbName TO DISK = @dataFileName
-- (2) Perform the backup - this option should reduce the LOG file
BACKUP LOG @dbName TO DISK = @logsFileName
-- (3) Shrink the database
DBCC SHRINKDATABASE (@dbName)
-- (3) Shrink the logs
DBCC SHRINKFILE (@dbName,1)
END
/*
spIAS_CompactDatabase 'iasRhino'
*/
Processed 616 pages for database 'iasRhino', file 'iasRhino_Data' on file 3.
Processed 1 pages for database 'iasRhino', file 'iasRhino_Log' on file 3.
BACKUP DATABASE successfully processed 617 pages in 0.432 seconds (11.683 MB/sec).
Processed 5 pages for database 'iasRhino', file 'iasRhino_Log' on file 3.
BACKUP LOG successfully processed 5 pages in 0.040 seconds (0.908 MB/sec).
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Server: Msg 8985, Level 16, State 1, Procedure spIAS_CompactDatabase, Line 26
Could not locate file 'iasRhino' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Any ideas?
DBCC SHRINKFILE (ACCTFILES_LOG,1)
The ACCTFILES_LOG is not the DB name - it's the "logical name" of the "log file". Not sure how you can specify it through a variable - maybe not at all.
I'll look further into this...