|
Hello,
DB Admin is not one of my stong points so I'm a bit stuck. I have a development database that has almost filled the disk. I want to reduce the file sizes so I can continue development (a process I left running over the weekend has increased the log file by 10Gb - It didn't work so I have to fix and rerun it and I only have 5Gb left)
Can anyone let me know how to reduce the file sizes back to something sensible (the operation I was running modified about 17% of the records and deleted 83% of the records it was working on). I tried Shrink Database but it reports that it has shrunk the database by a few hundred Mb but the files are physically the same size. 
Any help would be much appreciated.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
|
|
|
|
|
(This applies to SQL Server 2000)
If you have recovery model for the database set to Full, everything you do is permanently recorded in the transaction log until you back it up. Only then will SQL Server truncate the log to the backup point (you can disable this behaviour by specifying WITH NO_TRUNCATE).
Full recovery allows you to restore the database to any point in time, which SQL Server performs by undoing all the operations in the transaction log after that point (and any operations in transactions that hadn't committed before that point). If you don't want this capability, select the Simple recovery model. In this model, you can only restore a backup of the database - you can't rewind to a particular point in time.
A compromise candidate is Bulk-Logged mode. Here, any bulk operations (BULK INSERT, bcp, CREATE INDEX, SELECT INTO, WRITETEXT and UPDATETEXT) are only marked as having happened; the actual operations are not recorded. However, SQL Server does indicate which regions of the database have been affected by a bulk operation, and backs those up with a full database backup or a transaction log backup. If you aren't performing bulk operations, this option saves no space at all.
The default for Standard or Enterprise Edition is Full, while the default for Personal Edition or MSDE is Simple. Remember that the settings for any new databases are copied from the model database (since a new database simply is a copy of model, which is how SQL Server manages to create a new database in a few seconds).
For more horrible detail, my reference book is Inside SQL Server 2000 by Kalen Delaney (MS Press).
If you're just developing, rather than working on a production server, I suggest using the Simple model. If you deploy to your production server with a backup/restore model, remember to change the recovery model afterwards.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thank you for quite a comprehensive answer.
I did get my problem solved by other means. I detached the database, threw away the log file and reattached it. (Well, I was a bit more careful than that but that was essentially the result - which got me back more disk space that I had when I started)
Curiously, the process appears to be working now, and the log file isn't filling up quite so fast! 
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
|
|
|
|
|
Dear all
I have some problems while connection oracle 10g
i have tryed oracle connect ,ole connction and ODBC conection and i could not connect
does any body know any information about this problem
Regards
Mhmoud Rawas
------------
Software Eng.
|
|
|
|
|
Does anyone know how to join two datatables in ADO.NET (VB.NET)
i want to basicly do an outer join of two tables with the same primary keys
note: i can not connect to the database to do an sql command.
|
|
|
|
|
babydogman wrote:
Does anyone know how to join two datatables in ADO.NET (VB.NET)
See DataRelation class.
Mazy
"I think that only daring speculation can lead us further and not accumulation of facts." - Albert Einstein
|
|
|
|
|
Is there any way (besides triggers) to have an auto updating column in SQL Server? I'm looking for something like the "timestamp" column type from MySQL that automatically updates to the current date/time whenever an update/insert is performed. Is there anything like this type in SQL Server? Thanks Muchly.
- Aaron
|
|
|
|
|
It doesn't update to the current date and time, but SQL Server 2000 does have the rowversion type which is automatically updating. IIRC, the value for a changed row is only guaranteed to be different from the old value, not necessarily greater than the old value.
If you want date/time behaviour, you might want to consider using a trigger:
CREATE TRIGGER tg_timestamp ON MyTable
FOR INSERT, UPDATE
AS
UPDATE MyTable
SET TimeStampField = GETUTCDATE()
WHERE PrimaryKey IN
( SELECT PrimaryKey FROM inserted ) inserted is a pseudo-table (constructed from the transaction log) containing the new data for all the rows that were inserted or modified (there's also a deleted pseudo-table for the old version of modified rows, for an UPDATE trigger). You'll want to ensure that recursive triggers are not enabled, or you'll just end up going recursive.
I suggest using the UTC date and time so you don't get Daylight Savings Time effects.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
If there's no date/time updating field I guess I'll go with a trigger. Much thanks for the reply and the example.
- Aaron
|
|
|
|
|
Hi,
I'm using a little code to insert data in a Database.
Dim mCmd As System.Data.SqlClient.SqlCommand
mCmd = Me.vObjConn.getCmd
mStrSql = "INSERT INTO ZAP_CATEGORIE (CAT_Description, INS_USER, INS_DATTME) VALUES (@Cat_Desc, 'cde', getdate())"
' A SqlCommand object is used to execute the SQL statement.
mCmd.CommandText = mStrSql
With mCmd
.Parameters.Add(New SqlParameter("@Cat_Desc", SqlDbType.NVarChar, 250)).Value = Me.aCAT_Desc
End With
Try
mCmd.ExecuteNonQuery()
Catch exec As Exception
Return exec.message()
End Try
The first time I call this method, no problem.
The second time I got the following error message in the Excpetion :
The variable name '@Cat_Desc' has already been declared. Variable names must be unique within a query batch or stored procedure."
Can someone tell me where I'm wrong.
Thx
Sybux
|
|
|
|
|
SqlDbType.NVarChar, 250)).Value = Me.aCAT_Desc
YOU MISSTYPED @ WITH LETTER a it should be @CAT_DESC 
ForgetAboutIt
|
|
|
|
|
You're caching the command object between runs, right? The first time you run through, the line
.Parameters.Add(New SqlParameter("@Cat_Desc", SqlDbType.NVarChar, 250)).Value = Me.aCAT_Descadds the parameter to the command. When you run it again, the parameter @Cat_Desc is already there, so you get the exception.
There are two ways you can go: separate out setting the value from creating the parameter, and only creating the parameter once, or creating a new command object every time. I'd actually go for the second option, unless you need to run this command inside a tight, performance-critical block of code. In the long run, using less memory tends to perform better than caching lots of objects.
To do this, simply change
Dim mCmd As System.Data.SqlClient.SqlCommand
mCmd = Me.vObjConn.getCmd<pre>to<pre>Dim mCmd As New System.Data.SqlClient.SqlCommand
Secondly, never catch System.Exception. You can end up catching all sorts of critical errors other than those coming from the SQL layer. If you want to catch those, catch SqlException. From a design standpoint, don't catch an exception if all you're going to do is return a return code. Instead, allow the exception to propogate to the caller, and catch it there. It's OK to catch an exception and throw a new one of a different type to simplify the caller's exception code, but only if you can add more meaning (for example, throwing a user-defined BadFormatException if a file operation raised an IOException when you expected more data when reading a file). If you do this, remember to set the InnerException property.
The CLR will propogate some critical exceptions (such as ThreadAbortException or, IIRC, StackOverflowException) even if you do catch them.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thx for your answer but I will a third method becaus my SQLCommand is issue from my Database class so I can't make a new each time.
Here is my solution :
<code>
Dim mParDesc As New SqlParameter("@CAT_Desc", SqlDbType.NVarChar, 250)
With mCmd
.Parameters.Add(mParDesc).Value = Me.aCAT_Desc
End With
mCmd.ExecuteNonQuery()
mCmd.Parameters.Remove(mParDesc)
</code>
I don't know what is ths impact on perf. but I don't need an answer in the last 0.0001 seconds
Regards
Sybux
|
|
|
|
|
In my home installation of VS.NET 2002, I now cannot create a new project (of any kind, WIn WEb etc.) because I get the error "Automation Sever cannot create object". I can run existing projects and even fill in the broken Solution/Project stub with files created at work. When I do that the projects will run fine.
The "stun" that is left behind consists of the sln and proj files without any other IDE generated files that are appropriate to the type of project selected. Un the simplest case (a Class Library project) there is now default class cs file generated.
This installation ran fine for a long time then broke. I had just finished deleting some projects. Perhaps I deleted something that I shouldn't have?
Any clues?
Gary Hyslop (at home)
|
|
|
|
|
This is Gary at work. Noone seems to have any suggestions. See my problem in Soapbox section. I thin the solution is to change my e-mail address in the Profile section of this site to the one of Garya at home. If Gary at home can edit this message from work then he can be the sole user of the site since he can read his home e-mail when he's not home.
Gary Hyslop (at work)
|
|
|
|
|
I have C# project that connects to a SQL server 2000 database , I want to deploy it with the project to the end user PC. can you help me please? [I failed to do it using Install Shield 9.0]
Mahmoud Hossam
|
|
|
|
|
For my last project I used the SQL Server Database Maintenance functions from http://www.installsite.org/pages/en/isp_db.htm[^]
Michael
But you know when the truth is told,
That you can get what you want or you can just get old,
Your're going to kick off before you even get halfway through.
When will you realise... Vienna waits for you? - "The Stranger," Billy Joel
|
|
|
|
|
I have no experience in this area, but have something to say anyway
SQL Server 2000 is a licensed software product, each copy of which must be legally licensed. Unless you have licenses from Microsoft for each distributed copy, it would be illegal to distribute it with your software.
MSDE (the stripped down version) is a different matter. That can be legally distributed in many cases. You may find more of what you are looking for if you focus your searches/enquiries on MSDE.
|
|
|
|
|
I'm using the following command to select all the columns from a table
SELECT * FROM table When the command executes it's not returning the columns in the same order they were added to the table. For example if I created the table with column1, column2, column3, column4, they're being returned as column1, column4, column2, column3 (for example). Is this normal? Is the best way around it to simply specify the columns in the order I want them in the query? Thanks. I'm using MSDE.
- Aaron
|
|
|
|
|
monrobot13 wrote:
Is the best way around it to simply specify the columns in the order I want them in the query?
You should always specify the columns you want. Mainly because it means that if the data model changes then any existing queries will still return the data in the form that you are expecting. (Assuming that no columns are deleted / moved to different tables etc.)
IIRC specifying the columns in a select is faster at execution time.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
|
|
|
|
|
Sounds like a good idea. Thanks for the information.
- Aaron
|
|
|
|
|
Where do i begin. I am very new to all of this. I have a class using visual studio.net where i have to creat a single record display that shows data one record at a time, on a form. I have to use ADO and SQL. I have the SQL it is:
Sort by Name
SELECT Customer.CustomerId, Customer.LastName, Customer.FirstName
FROM Customer
ORDER BY Customer.LastName, Customer.FirstName;
Non Zero Balance
SELECT Customer.LastName, Customer.FirstName, Customer.Balance
FROM Customer
WHERE (((Customer.Balance)>0))
ORDER BY Customer.LastName, Customer.FirstName;
Select by State
SELECT Customer.CustomerId, Customer.LastName, Customer.FirstName,
Customer.State
FROM Customer
WHERE (((Customer.State)=[@WhatState]))
ORDER BY Customer.LastName, Customer.FirstName DESC;
Sum Balance
SELECT DISTINCTROW Sum(Customer.Balance) AS SumBalance
FROM Customer;
Insert customer
INSERT INTO Customer ( LastName, FirstName, Balance )
VALUES ([@newlast], [@newfirst], [@newbalance
I also have my form code which is
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu
Friend WithEvents MenuItem1 As System.Windows.Forms.MenuItem
Friend WithEvents MenuItem2 As System.Windows.Forms.MenuItem
Friend WithEvents MenuItem3 As System.Windows.Forms.MenuItem
Friend WithEvents MenuItem4 As System.Windows.Forms.MenuItem
<system.diagnostics.debuggerstepthrough()> Private Sub InitializeComponent()
Me.MainMenu1 = New System.Windows.Forms.MainMenu
Me.MenuItem1 = New System.Windows.Forms.MenuItem
Me.MenuItem2 = New System.Windows.Forms.MenuItem
Me.MenuItem3 = New System.Windows.Forms.MenuItem
Me.MenuItem4 = New System.Windows.Forms.MenuItem
'
'MainMenu1
'
Me.MainMenu1.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.MenuItem1, Me.MenuItem2, Me.MenuItem3, Me.MenuItem4})
'
'MenuItem1
'
Me.MenuItem1.Index = 0
Me.MenuItem1.Text = "Exit"
'
'MenuItem2
'
Me.MenuItem2.Index = 1
Me.MenuItem2.Text = "Customers"
'
'MenuItem3
'
Me.MenuItem3.Index = 2
Me.MenuItem3.Text = "MasterDetail"
'
'MenuItem4
'
Me.MenuItem4.Index = 3
Me.MenuItem4.Text = "StateQuery"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 206)
Me.Menu = Me.MainMenu1
Me.Name = "Form1"
Me.Text = "Form1"
End Sub
#End Region
Private Sub MenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem1.Click
End Sub
Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click
End Sub
Private Sub MenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem3.Click
End Sub
Private Sub MenuItem4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem4.Click
End Sub
Private Sub Exit_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem1.Click
' Exit the Application
Application.Exit()
End Sub
Private Sub MenuItem2_Click_(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click
Dim myform As New Customers
myform.ShowDialog()
End Sub
Private Sub MenuItem3_Click_(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem3.Click
Dim myForm As New MasterDetail
myForm.ShowDialog()
End Sub
Private Sub MenuItem4_Click_(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem34.Click
Dim myForm As New StateQuery
myForm.ShowDialog()
End Sub
End Class
The file i am getting the information for the table to display the records is customer.mdb. From what i gather I think i have to change the button event to be something like me.bindingtxt(customer.mdb,"customer"). Is that right? I won't post my next question until i get a response, this one's long enough!
-ErinSue
|
|
|
|
|
I'm not sure exactly what problem you are having.
Have you connected to your database yet?
Have a read of http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskcreatingconnectiontoaccessdatabase.asp[^]
The other articles in the section should give you a clearer idea of how to proceed to bind your dataset to controls.
Alternatively, you could use the DataForm Wizard to generate an example form and study the code to get a better understanding.
Michael
But you know when the truth is told,
That you can get what you want or you can just get old,
Your're going to kick off before you even get halfway through.
When will you realise... Vienna waits for you? - "The Stranger," Billy Joel
|
|
|
|
|
i'm stuck
im trying to save a text file to sql server(in a 'text' column ); have no problem with unicode text, but with non-unicode strings after saving to the sql server i read garbage from the columns
here's my code
_bstr_t bstr_text = adoStream->ReadText(adoStream->Size);
// dumping bstr_text shows the text here in bstr_text is ok
recSet->Fields->GetItem(item)->Value = bstr_text;
// debugging down to Field20::PutValue shows that correct data is being put in the field
|
|
|
|
|
If your source data is 8-bit character-oriented, you should use a text column and ensure that the client's thread locale is the same as the locale used by the column.
If your source data is 16-bit character-oriented, use an ntext column. You should also use ntext if your client's locale will be different from the server's.
If you're just interested in storing and retrieving a file with no conversions at all, use an image column to treat it as binary. Obviously you can't do full-text search on an image column.
When you assign a char string to a _bstr_t, the conversion to Unicode is performed using the thread's default code page. When the opposite process occurs to store the data in the database, SQL Server uses the configured locale's code page. If the two settings don't match, the conversions may not be a round-trip.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
