|
If you're talking about your transaction log, the only supported way of clearing it is to back it up. Space taken by committed transactions in the log will then be marked as reusable. The log will then reuse that space.
You should perhaps consider which Recovery Model you are using. The Full recovery model is generally recommended for any production system - it permits you to recover to the point of failure if the database files become corrupted or unavailable but you have a full backup and the transaction log is still OK. It also permits you to roll back to any point in time. The Simple recovery model only allows you to restore a full backup; you cannot make transaction log backups.
To reduce the size of the files, use the Shrink Database feature. You will probably need to select the 'Move pages to beginning of file' option to actually have much effect, since the pages that are most likely to be in use straight after doing this will be those at the end of the file.
All that said, a large transaction log does not generally cause a slowdown - SQL Server simply writes to the end of it, expanding the file if necessary. If the disk is starting to get full, the OS may be struggling to find free space for it to expand to. Best practice on a production server is for the transaction log to live on a drive (preferably a RAID 1 [mirrored] array of drives, for redundancy) separately from everything else. This prevents the drive heads from having to seek away from the current write position in the transaction log - log accesses are predominantly sequential-write except when a rollback occurs.
Another common reason for slowdowns as the amount of data increases is simply that your queries and indexes are poorly designed. Most likely one or more tables is having to be scanned - read from beginning to end - rather than using an index to find the rows required. This operation scales more-or-less linearly with the size of the table, getting progressively slower as you add more data. You should always have a representative amount of data in your database when performing pre-production tests, to ensure you catch these problems early.
|
|
|
|
|
Hello,
ive to call a stored procedure using c#. The procedure takes a couple of parameter and returns an out parameter as a cursor.
I take this cursor to show in a datagrid directly for example which runs fine so far. But now i have to make a selection of this cursor, meaning that i want to make a select statement on that cursor selecting specific fields and in a specific order.
Please, can someone tell me how to achieve this?
Best ragards
Trollpower
|
|
|
|
|
Access has had some concurrency problems when used with web applications. You are probably better off with SQL Express.
|
|
|
|
|
I would also recommend SQL Express. Access is probably not too small, but it's more difficult to port and it's not overly extensible. I don't care for Access and I try not to use it when I can help it.
Steve Hanson
Tasen Software
New Hampshire (NH) Website Design & Software Development
|
|
|
|
|
I'm developing a network app for storing login info for access to the internet. Its a fairly small app so i'm wondering is SQL Express overkill or is Access to small.....if you understand what i'm saying!!!
|
|
|
|
|
If you use SQL Express you can scale up to a full server edition if you need to without much effort. If you use access then it would take more work to migrate.
|
|
|
|
|
I'll probably never scale up to server edition, cant see the app developing that big.
|
|
|
|
|
Also, you need to consider your ISP. Do they host SQL Server? Do they charge extra for this service.
"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky
|
|
|
|
|
Hi
What does the brackets around column names mean??
SELECT [Data] FROM Table
Please help
|
|
|
|
|
The brackets are used to escape any syntax that might otherwise be confused with a SQL keyword, or to allow spaces to appear in a column name.
In some contexts Data is a SQL keyword, but not actually in the column list of a SELECT statement. However, the brackets don't do any harm, so you can leave them in.
|
|
|
|
|
Signifies that the code elements between the square brackets can optionally appear in the SQL query, but are not required. Note that these brackets are not part of the code and must not appear in the SQL query.
|
|
|
|
|
Actually the brackets can appear in the SQL query. They just signify some text string that is its own entity, such as a table name composed of two words with a white space delimiter. You could also use it in a query like this:
SELECT columns FROM table WHERE column LIKE '%[%]%'
Which essentially escapes the middle percent sign, treating it like a percent sign instead of a wildcard character. I hope this helps.
Steve Hanson
Tasen Software
New Hampshire (NH) Website Design and Software Development
|
|
|
|
|
What I am currently doing now is trying to have a backup on database and transaction log on databaseA. But when i tried to restore the transaction log of databaseA to databaseB. It occurred an error.
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'Test_Remote' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
How do i resolve this problem? Such that if i restore transaction log from one database to the other, it won't have an error?
Thanks
|
|
|
|
|
This cannot be done. Transaction logs are associated with a specific database and cannot be applied to a different database.
|
|
|
|
|
select *
from TABLE1 A
INNER JOIN TABLE2 B
ON A.STUDENT_ID = B.STUDENT_ID
INNER JOIN TABLE3 C
ON A.CLASS_ID = C.CLASS_ID
INNER JOIN TABLE4 E
ON A.FORM_ID = E.FORM_ID,
TABLE5 D
INNER JOIN E
On D.SCHOOL_ID = E.SCHOOL
I want to use TABLE1 A to inner join on TABLE2 B, TABLE3 C, TABLE4 E and the TALBE4 E inner join on TABLE5 D. But I get an error
"Invalid object name 'E'."
Thanks for helping
|
|
|
|
|
Oh. I get the answer la. It should be
select *
from TABLE1 A
INNER JOIN TABLE2 B
ON A.STUDENT_ID = B.STUDENT_ID
INNER JOIN TABLE3 C
ON A.CLASS_ID = C.CLASS_ID
INNER JOIN TABLE4 E
ON A.FORM_ID = E.FORM_ID
INNER JOIN TABLE5 D
On D.SCHOOL_ID = E.SCHOOL
Thanks
|
|
|
|
|
i want to insert pic into datatable
pl help with vb code..
str_insert = "INSERT INTO SIS_TeachingStaff VALUES('TSMT2','V.RAJANIKANTH','LECTURER','TEACHING STAFF','Msc(Maths)','" & Emp_pic.Image & " '" '" & CByte(Me.opendialogbox.FileName.Length) & "' "
cmd = New SqlCommand(str_insert, conn)
cmd.Connection = conn
cmd.ExecuteNonQuery()
error is:operator '&' is not defined for types 'string' and 'system.drawing.image'
|
|
|
|
|
You are injecting values into the SQL String - This is a potential security flaw in your application and you should resolve it. See SQL Injection Attacks and Tips on How to Prevent Them[^]
The resoltion for the security flaw is also the resolution for your problem. You should use Parameterised queries to insert binary data.
|
|
|
|
|
|
I think you meant to reply to the OP rather than me.
|
|
|
|
|
|
Hello there
I need to find out the Columns that make up a key in an existing table...
Say I have a TABLE named USERS, I create a connection and I have a CADOREcordset to that table say we call it cUSERS... It has several keys, one of them is "USERID", I need to find a way (using ADO) to find out what columns make up that Key...
Looking for help, a function like CSTring CgetColumns("USERID"); will be just fantastic...
Cheers
Alex
|
|
|
|
|
I am trying to install SQL EXPRESS on my PC to run with VS 2005.
I had previously uninstalled it.
Now when I try (re)install it won't proceed thru the final steps, showing:
"Existing components have been found" - all options are greyed out & I cannot install.
Any ideas?
Any help is greatly appreciated.
AlanH
|
|
|
|
|
First off, this is a rather complicated mixup I ended up in so I apologize if I'm not being clear enough. If you have any questions, don't hesitate to ask as I greatly appreciate any help you guys can give me on this....
I'm having trouble trying to read tables & stored procedures in Sql 2000's Enterprise Manager, that were originally written in Sql Server 2005. Is there any way I can view this 2005 data in Enterprise Manager?
I started working for a new company a few months ago and was told to develop my first project in the asp.net 2.0 framework & sql server 2005. So for 2 months, I've been developing this project only to discover they made a mistake & told me the wrong thing. All web applications need to use the 1.1 framework and sql server 2000 (They're console apps were the ones that were using 2.0 and sql 2005).
So I spent last week downgrading my application to 1.1 and today, I started downgrading my database stuff to sql 2000. I uninstalled Sql Server 2005 and installed Sql Server 2000. Now however, I'm unable to use Enterprise Manager to open any databases that were created in Sql 2005. I keep getting an error which says to open the tables in Sql 2005.
So my question is, even though I can't view this 2005 data in Enterprise manager, will the stored procedures I create in Sql 2000 still be able to reference any tables & views created in Sql 2005?
Thanks in advance.
-Goalie35
|
|
|
|
|
As a general rule, you cant view or access data or procedures in databases created with a newer release of SQL server (SQL7 didn't like SQL2K either). You should, however, be able to use SQL2005 tools to script the database, then recreate the structures and procedures in an SQL2000 database (the scripts might need editing to account for SQL2K5 specific feature/syntax usage). Youl'll have to "downgrade" your databases as well...
|
|
|
|