We will be posting many tips on managing your database as well as tips on managing your web hosting environment. This first tip is on MS SQL Server databases. Here you go —  free on us — compliments of http://www.sozohosting.com/ and also compliments of http://www.biznesstechnologies.com/ :

Web Hosting And Database Tip 1

It is your responsibility to control your MS SQL database log size. If it gets too big for the Size you have allotted then you will get an error in your application. You can fix this different ways. You could run this in a query window but this is a 1 time event:

BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(YourDatabase_log, 2)

Replace the text “YourDatabase” with your database name and replace the number 2 with the MB size you want to shrink your log file down to. Use 2 as your minimum as that will reduce your database log file to 2 MB. After replacing the text with your database name and setting the MB size of the log file, execute both SQL lines above together.

You could add this SQL call below to call if more often dynamically adding

BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(YourDatabase_log, 2)

in your application code, replacing the “YourDatabase” text with your database name and setting the size of the log file, and executing this SQL dynamically. Best case is to add these 2 lines of SQL script above to a stored procedure and schedule the stored procedure to run nightly. Of course you want to back your database up before your run this script if you want to maintain a copy of your full sized log file.

Or you could add a stored proc and call it on a regular basis after you run your database backups. Here is an example of a stored proc for MS SQL 2008 databases to shrink the log file to 1 MB:

USE [YourDatabase]
GO
/****** Object: StoredProcedure [dbo].[SRINK_Log_File_To_1MB] Script Date: 12/15/2011 10:00:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SRINK_Log_File_To_1MB] AS
BEGIN

— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE YourDatabase

SET RECOVERY SIMPLE

— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1); — here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)

— Reset the database recovery model.
ALTER DATABASE YourDatabase

SET RECOVERY FULL

END

Categories: Uncategorized ,Web Hosting ,Web Hosting and Database Tips ,Web Software


Leave a Reply

You must be logged in to post a comment.