|
I have a table containing 1 crore tuples(rows).
At the creation time , i have applied indexes on it.
Should i defrag it peiodically ??
Only the Select Operation is performed on it..
Kindly suggest...
|
|
|
|
|
Aman786Singh wrote: Only the Select Operation is performed on it
It won't get fragmented indeed, since you
- only read from it
- are using a database, not a file-system
Hope this helps 
I are troll
|
|
|
|
|
Eddy Vluggen wrote: are using a database, not a file-system
you expect a read-only file-system to get fragmented over time?
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
|
WHEN I RAN INDEXDEFFRAG..
THEN WHAT THIS RESULT MEANS???
PAGE SCANNED PAGE MOVED
245 0
|
|
|
|
|
You were right
"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
|
|
|
|
|
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,,,
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
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
