|
I have a SQL Server database. It is too large now.
How can I remove the transaction log without backing up the database?
In fact, I do not need any logs. Can I prevent the database from making transaction logs? Can SQL Server automaticlly remove the logs?
|
|
|
|
|
Due to architecture of sql server each database file has a transaction log file for transaction support. Support of transactions are needed for core database functionality. Therefore you can not prevent sql server making transaction logs.
Nevertheless the size of transaction log file can be controlled on database creation time with the CREATE DATABASE statement in TRANSACT-SQL. The parameter SIZE, MAXSIZE and FILEGROWTH controls initial size, maximun size and filegrowth for each database file and for log file. Setting the log file size to a fix maximum value is a recommended way not to have a filegrowth over all limits. But this needs maintenance from time to time when MAXSIZE is reached.
To reduce the size of an existent transaction log file the TRANSACT-SQL statement (not available from the sql server enterprise manager) DBCC SHRINKFILE can be used. To be secure no other user is modifying database in this time it is a good idea to set the database into the single user mode during this operation. The following are TRANSACT-SQL statements for sql server query analyzer for secure shrinking of the database log file (the log file cannot be lesser than the value given for SIZE during database create time)
USE master
EXEC sp_dboption myDatabase, 'Single User', TRUE
DBCC SHRINKFILE (myDatabaseLog, TRUNCATEONLY)
EXEC sp_dboption myDatabase, 'Single User', FALSE
GO
myDatabase is to replace with the name of the database. myDatabaseLog is to replace with name of the logical name of the logfile of the database. Alternatively to TRUNCATEONLY a target size can specified (which must be equal or greater than SIZE value on create time)
A sql server database can consist of multiple data and log files which are grouped in the same or different filegroups. If you have multiple log files you can use EMPTYFILE instead of TRUNCATEONLY to move all used pages to other log files in the same filegroup (on default the filegroup PRIMARY is used; user defined filegroup(s) can exist) to remove this or all log file except the last one needed for each database.
For details of the given TRANSACT-SQL command please use the sql server onlinehelp for reference. I Hope this helps.
|
|
|
|
|
fat888 wrote:
How can I remove the transaction log without backing up the database?
Well, now that you're in trouble, DUMP TRAN database WITH NO_LOG
but that's not a recommended procedure. On a development machine, this can be excusable. Backups are way better.
"In an organization, each person rises to the level of his own incompetence." Peter's Principle
|
|
|
|
|
Hi dear,
How can I cast between DateTime field of a table and DateTime variable from my C# code?
/////////////////////////////
DataRow drAdd;
...
drAdd["DataTime"] = System.DateTime.Now;
...
/////////////////////////////
in fact how can I fill the DateTime field of a table with current time?
Thank you in advance.
|
|
|
|
|
How can you fill a DateTime field with the current time? That depends on the database type which you don't mention. You can probably execute an internal database function such as NOW with a stored procedure. Refer to the reference manual for the particular database you are using.
|
|
|
|
|
I have an application which is accessing a SQLServer and an Oracle.
Of course, the application must know the password. I want to prevent the users from accessing the database directly. When they want to read/write data,they have to use my program.
What can I do?
Thanks.
Hi guys. I'm a very fat man. Do you like fat men?
|
|
|
|
|
The typical way of doing this is exactly what you are talking about. You have a single logon/password which uses a somewhat complicated and unique name/password convention. All other DB accounts are locked out of the DB. The user could still access the DB directly, but they would need to know the userid/password.
The other way you can do this is to encrypt the data in your DB. This is very difficult to do unless you have a library/toolkit for database access which does this for you. One way to do this is to use the digest MD5 algorithm on each and every column of data. This leads to serious problems though because it is not easy to query the DB and again, you need a library/toolkit that does the work for you.
|
|
|
|
|
Hello!
We are using ADO to work with SQLServer.
Our database is about 5Gb and some tables have about 600,000 records.
ADO provider opens such table for 3-5 minutes - the application is
"freezed".
Does anybody know how to avoid such big pause? Is it possible open table using ADO faster (I think ADO recordset retrives all records to navigate later inside it)? Some triks or tips? HELP!!!
This way we open the table:
m_pRecordset->Open((LPCSTR)"table_name", _variant_t((IDispatch*)mpdb, TRUE), adOpenKeyset, adLockOptimistic, adCmdTable);
Thank you!
Kind regards... Tau.
|
|
|
|
|
Have you tried using stored procedures rather than directly opening the table?
Michael
Programming is great. First they pay you to introduce bugs into software. Then they pay you to remove them again.
|
|
|
|
|
Can be an idea, but we would like to have more or less universal method for different servers also (Oracle, DB2, ...)
Kind regards... Tau.
|
|
|
|
|
BTW, how can I use deferred columns in ADO?
(For a deferred column, the provider is not required to retrieve data from the data store until IRowset::GetData is called for that column)
Kind regards... Tau.
|
|
|
|
|
Have you tried using adOpenForwardOnly instead of adOpenKeyset?
|
|
|
|
|
I`ve tried that - the same...
Kind regards... Tau.
|
|
|
|
|
adOpenForwardOnly, adLockReadOnly AND make sure connection and recordset have CursorLocation set to adUseServer (adUseClient will always be slower since it ignores the adOpenForwardOnly), adLockReadOnly minimizes the cursor overhead and produces a "firehose" cursor with SQL. Records will not actually be fetched until you position to them with movenext, and you can't move backwards...but should be **MUCH** faster.
|
|
|
|
|
A couple things I can tell you about this.
1) If your table does not have a primary key in SQL Server, opening the table can take some time.
2) If the table has a primary key but it is not a clustered key you should try using a where clause such as "SELECT * FROM table_name WHERE PK_FIELD > 0". This forces SQL server to use an index even if you are getting everything this will often produce better results.
3) Try using a SELECT TOP XXX instead of select all.
|
|
|
|
|
I'm not sure of the extent of the application you are working on, however if you are trying to return an entire table into a recordset (i.e. - especially if it contains 600,000 records) you may want to consider other ways of displaying your data. Look at it this way. Your user can't see all 600,000 records at once.
Nick Parker
The goal of Computer Science is to build something that will last at least until we've finished building it. - Unknown
|
|
|
|
|
hello taualex
have you create the index for your table?
if you have not,then you must create it,
when you create it,you can shrink the time to about 1/5
open you sqlserver,right click to show manager index,
add the index on the fields which you search for it
|
|
|
|
|
Use index in your database and try to use ADO.NET for
data communication. Bear in mind that don't use DataSet if you wanna use ADO.NET.
|
|
|
|
|
I believe the only way to fix this is to use an ODBC driver, which, the last time I used one (several years ago), you had direct control over how many records you wanted to retrieve at a time. In other words, you could send the query and the retrieve records at your own consumption rate.
Marc
|
|
|
|
|
I am Vietnamese. I am using Unicode and Database to design my Website. But I have problem. I use Unicode for Vietnamese , when I input into Form and Submit , but my data saved in MS Access 2000 not correct that I input into Form. Why ? How do I can correct this mistake.
Example :
I am input : Phương Thanh
Database Access 2000 saved : Phỳòng Thanh
Thank you.
|
|
|
|
|
I can try list:
1:
if you have use the :enctype="multipart/form-data"
if you have,delete it
2:
you can
use asp:
<%response.write request("yourelement")%>
to test if the request("yourelement") is not correct
try it,and ask me your result,friends!;P
|
|
|
|
|
I started using ADO.NET to access data from SQL database in my ASP.NET applications, however, those commands I used so far is to talk to an existed database. If I want to create a new database, is there any easy way to do it in ADO or sth. else? Thanks a lot!
|
|
|
|
|
Assuming you are connected to the server as a user that has the rights to create a database (and next some tables)
CREATE DATABASE <database name>; look up in Books Online the syntax for CREATE DATABASE to see all the different settings you can specify. While you're looking that up, look up CREATE TABLE too, because you'll need some tables added in order to do much with that database
James
Sig code stolen from David Wulff
|
|
|
|
|
This is a sql command, and I normally use ADO's object SQLcommand and its method "command.ExecuteNonQuery()" to execute the required sql command in my C# program or ASP.NET c# server code. However, that SQLcommand object requires connection first, which needs to have a existed DB available. So in this case, sqlcommand may not be a good choice to run that creation command. Any ideas any other object I can use in my C# program? Thanks a lot!
|
|
|
|
|
You can connect to SQL server without using a specific database.
If you specify CATALOG in your connection string, remove it; and you should be ok. The account you use to connect to SQL Server (either Windows authentication or username/password authentication) needs to have permissions to create databases.
James
Sig code stolen from David Wulff
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
