SQL Server - do I need PHP to run COMMIT?

Hello people,

My PHP application uses a SQL Server 2000 database. I have previously
only ever used MySQL and so my knowledge of SQL Server comes just from
experimentation and trial and error experience.

My PHP application appears to be working fine but I have just discovered
that although the database itself is rather small on the disk (about
25MB) the transaction log file is huge (400MB). I have had a quick look
at Microsoft's website about large transaction files and they suggest
many reasons, one of which is the application not COMMITing
transactions. This is certainly true because I simply make INSERT and
UPDATE queries but don't include a COMMIT statement.

So my question is this: should I be COMMITing?

How do I do that? Do I simply run something like this after every
INSERT/UPDATE/DELETE:

mssql_query('COMMIT', $link_identifier);

Please help - I feel like I'm missing a trick here.

FYI: I'm also now doubting my use of "mssql_pconnect()" - should I
being using "mssql_connect()" with "mssql_close()" instead?

Thanks,

Alex

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: SQL Server - do I need PHP to run COMMIT?

Using COMMIT is only required if you issued a BEGIN TRANS before your
INSERTs, UPDATEs and / or DELETEs. All transactions are automatically
committed if you don't use BEGIN TRANS. Are you backing up the
transaction logs regularly?

-- bob

On Fri, 16 Dec 2005, Alex Gemmell wrote:

> Hello people,
>
> My PHP application uses a SQL Server 2000 database. I have previously
> only ever used MySQL and so my knowledge of SQL Server comes just from
> experimentation and trial and error experience.
>
> My PHP application appears to be working fine but I have just discovered
> that although the database itself is rather small on the disk (about
> 25MB) the transaction log file is huge (400MB). I have had a quick look
> at Microsoft's website about large transaction files and they suggest
> many reasons, one of which is the application not COMMITing
> transactions. This is certainly true because I simply make INSERT and
> UPDATE queries but don't include a COMMIT statement.
>
> So my question is this: should I be COMMITing?
>
> How do I do that? Do I simply run something like this after every
> INSERT/UPDATE/DELETE:
>
> mssql_query('COMMIT', $link_identifier);
>
> Please help - I feel like I'm missing a trick here.
>
> FYI: I'm also now doubting my use of "mssql_pconnect()" - should I
> being using "mssql_connect()" with "mssql_close()" instead?
>
> Thanks,
>
> Alex
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: SQL Server - do I need PHP to run COMMIT?

Yes, the transaction logs are being backed up and shrunk regularly. I
am told while they are 400MB most of that is "empty space" and it's
really about 50MB in size. Apparently that is still a bit too big and
indicates a possible problem.

I'm glad all transactions are auto-commited. I presumed that was the
case because we have 5 users all using this system at the same time and
we have had no apparent problems with data being "saved" but not
actually being stored in the database. Everything actually seems to be
working fine apart from the remarkably large transaction file.

Any ideas why the transaction log file would be so big?

Robert Twitty wrote:
> Using COMMIT is only required if you issued a BEGIN TRANS before your
> INSERTs, UPDATEs and / or DELETEs. All transactions are automatically
> committed if you don't use BEGIN TRANS. Are you backing up the
> transaction logs regularly?
>
> -- bob
>
> On Fri, 16 Dec 2005, Alex Gemmell wrote:
>
>
>>Hello people,
>>
>>My PHP application uses a SQL Server 2000 database. I have previously
>>only ever used MySQL and so my knowledge of SQL Server comes just from
>>experimentation and trial and error experience.
>>
>>My PHP application appears to be working fine but I have just discovered
>>that although the database itself is rather small on the disk (about
>>25MB) the transaction log file is huge (400MB). I have had a quick look
>>at Microsoft's website about large transaction files and they suggest
>>many reasons, one of which is the application not COMMITing
>>transactions. This is certainly true because I simply make INSERT and
>>UPDATE queries but don't include a COMMIT statement.
>>
>>So my question is this: should I be COMMITing?
>>
>>How do I do that? Do I simply run something like this after every
>>INSERT/UPDATE/DELETE:
>>
>>mssql_query('COMMIT', $link_identifier);
>>
>>Please help - I feel like I'm missing a trick here.
>>
>>FYI: I'm also now doubting my use of "mssql_pconnect()" - should I
>>being using "mssql_connect()" with "mssql_close()" instead?
>>
>>Thanks,
>>
>>Alex
>>
>>--
>>PHP Database Mailing List (http://www.php.net/)
>>To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: SQL Server - do I need PHP to run COMMIT?

Do you have the "truncate log on checkpoint" option enabled? Also, if you
want to reduce the size of the log file if shrinking doesn't work, try
running the following against the database. Of course, you should backup
the DB first.


SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE [CLEANUP] -- This is the name of the database
-- for which the log will be shrunk.
SELECT @LogicalFileName = 'FHMMSYS_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 100 -- in MB

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName

CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)

-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)

EXEC (@TruncLog)

-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time
has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name =
@LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php