|
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
|
|
|
|
|
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
|
|
|
|
|
Hi JK,
It looks to me like you client is using SQL 2005, which isn't backwards compatible with SQL 2000.
Ryan
|
|
|
|
|
is it possible to convert sql 2005 database to sql 2000 sp4
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
