Re: how to reduce the log file of a large database? - Database Discussion Boards

I was thinking that there's no "defrag.exe" for databases! And I bet that somewhere, someone will have written such a thing Smile | :)

I are troll Smile | :)

WHEN I RAN INDEXDEFFRAG..

THEN WHAT THIS RESULT MEANS???

PAGE SCANNED PAGE MOVED
245 0

You were right Smile | :)

"DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, thus improving index-scanning performance."

I are troll Smile | :)

i have a database with a large log file database and i don't know how to do to make it smaller,,,
the file have a large area space what can i do???

You are not telling us what type of DB you are using. Beware of erasing log files or minimizing log levels since mistakes do happen and when they do, these files are life savers especially in production.

Natza Mitzi

thanks...

sorry,,, SQL SERVER database
would u help me ???

modified on Tuesday, March 17, 2009 7:06 PM

Do you know what recovery model you are using (simple vs full)? Are you backing up the database? What version of SQL Server is it?
Scott

run this if you are working in sql 2005 server.

but for this u need to make your database offline first and then run this code.

--INF: How to Shrink the SQL Server 7.0 Transaction Log
-- SQL7 http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD
-- SQL7 http://www.support.microsoft.com/kb/256650
-- SQL2000 http://support.microsoft.com/kb/272318/en-us
-- SQL2005 http://support.microsoft.com/kb/907511/en-us
-- select db_name()
-- select * from sysfiles

-- THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!

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

-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***

SELECT @LogicalFileName = 'Test_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 = 305 -- 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

thanks for all,,, Smile | :)

i'am sorry the sql server recovery model is full and its version is sql server 2000
i have to reduce the size of this log file
when i deleted it the database don't work, it is suspect and didn't work

How should i check the positive value in sql server 2005 except the following?

declare @f varchar(2000)
set @f = '788'
print charindex( '-' , @f )

It would be nice if we could actually understand what you are trying to accomplish.

Check if it's larger than 0 Smile | :)

DECLARE @f INT
SET @f = 788

IF @f > 0
BEGIN
    PRINT 'It is positively positive!'
END
ELSE
BEGIN
    PRINT 'That''s A negative there, Toto'
END

I are troll Smile | :)

i am using SQL 2000 server. i have created one user .
i want that this user have following rights:-

Select,Insert,Update,Delete on Tables. so i gave the user as DDLAdmin,Read acess and Write Excess.

this is ok with my tables part. but for Stored Procedure i got a problem.

I want that my user can execute and modify the Stored Procedure.

For the execution part i ran Grant exec on <sp_name> to <user_name>.
but please tell me How should i give the user rights to modify the procedure???

I don't want to make user as DB_Owner.

Kindly suggest any solution.

Thanks in Advance.

I had a project in MS access 2003 now I converted that to SQL server where there is some fields
in each table which has the hyperlink link to a scanned pdf file. while converting the files
the database is running very well but I can not get to my scanned files and the hyperlink is not coming in SQL server to click to get to the specified scanned files.just the path is coming in text format only

could you anyone help me.

Saleem Tahiri wrote:
just the path is coming in text format only

Because that is what is stored. You cannot (as far as I know) store a hyperlink in sql, just text so you need to build the hyperlink when you retrieve the data.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

If you are still using the Access front-end, just pass the text as a parameter to the VBA FollowHyperlink function and it will open.

Hi,
When i installed Rational clearcasetool and restarted system..and when im trying to build my vc++ application in VS 2005 im able to see the below error..

error result returned from 'cl.exe'.

Please help me out..

This error is due to improper setup of VS or OS update problem.

It also comes when u install windows update patch KB925902. try uninstalling, if u hav one.

Ravie Busie
Coding is my birth-right and bugs are part of feature my code has!

plz giv me the connection string to connect ms access database in asp.net web page

Look here[^] for connection-strings.

plz = Please
giv = give

I are troll Smile | :)

Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath(".\northwind.mdb") & ";"

Ravie Busie
Coding is my birth-right and bugs are part of feature my code has!

Hi everybody

Actually i created a table in sql+, and inserting data using sql+ itself.But after i log out off the screen and log in again i see the data gone. How can i prevent the data from getting vanished without writing commit or set autocommit commands on everytime.

skd

During interactive usage with sqlplus, Oracle also supports an AUTOCOMMIT option. With this option set to ON each individual SQL statement is treated as a transaction an will be automatically commited right after it is executed. A user can change the AUTOCOMMIT option by typing
SET AUTOCOMMIT ON or
SET AUTOCOMMIT OFF

whereas by typing
SHOW ALL
a user can see the current setting for the option (including other ones).

This was taken from the website:

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-nonstandard.html[^]

neoghy wrote:
How can i prevent the data from getting vanished without writing commit or set autocommit commands on everytime.

This is like asking how I can eat my cake without opening my mouth.

Cheers,
Russ

Dear Friends,
Client database is in the form of "database.Dat" they don't which version they are using, i want to restore it i sqlserver 2000 with service pack 4
Following error occured

Error:3169 The backed up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database. Restore filelist is terminating abnormally.

Please give a solution

Thanks & Regards

JK