|
 I have a Database that is growing and growing. The size is almost 20 Gb, but there are only 5 or 6 Gb used, and the rest is unused. I ran sp_spaceused and checked it. I saw in some forums that I must reindex, but I don't have clustered indexes, and dbcc dbrindex don't help me. I ran shrinkdatabase, but the shrinkdatabes just release the unallocated space, that is 1 Gb...
Can someone help me?
Manolo
|
|
|
|
|
If i'm not wrong, the Database Maintenance Plan can help you. For detailed information and its usage, SQL Query Analyzer's Help "Transact-SQL Help >>Creating and Maintaining Databases >>Database Maintenance Plan Wizard" might be usefull.
Best of Luck.
_____________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
I will try. Thanks for your help
|
|
|
|
|
Check the individual files. I'd be prepared to bet that your transaction log is huge while the data file is fairly small.
What you need to do is decide how you want to maintain your database. If you want to be able to recover the database to any point in time, including rolling forward transactions to the point of failure when restoring from your last backup, you need SQL Server to maintain everything that's happened in the transaction log: the Full Recovery Model option. If you're doing this, you need to back up the transaction log as well as the database file using the SQL Server backup feature or a third-party backup solution that understands SQL Server properly (not just backing up the files); once the transaction log is backed up SQL Server will mark the backed-up parts of the file as being free and start reusing them rather than growing the file. You'll have to use shrink with the 'move to start of file' option to actually make the file smaller.
Yes, I would have expected a Full Database Backup to also perform this transaction log clearing - that's what Exchange does. SQL Server doesn't.
If you use bulk inserts and don't want every bulk-inserted row to be recorded in the transaction log, instead performing the bulk inserts again after a restore, consider the Bulk Logged Recovery Model option. I'm not sure if it will actually replay a bulk insert operation if the file is still present in the same location during a restore.
If you're just experimenting with something and aren't going to back it up, or the database is simply a replica of something else, or you only want to restore the last full backup and don't care about transactions that have occurred since, consider the Simple recovery model. SQL Server then still writes to the transaction log - it uses it to recover the state of the database in the case of a power outage or a software failure - but marks the space taken by the log records as free as soon as the transaction that created them commits. It can then reuse them very quickly, keeping the size of the transaction log down.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
The problem isn't with the size of the log file. Look, the sizes of the files are:
stadata.mdf 20,351,680 KB
stadata_log.ldf 4,224 KB
The problem is the quantity of space tha SQL reserves for its grow. When I check the space used (sp_spaceused), the results are:
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
17862602 KB 4216416 KB 4368184 KB 9278002 KB
I found one solution:
1.- Run shrinkdatabase (moving pages to the begining of the file)
2.- Full backup
3.- Delete DB
4.- Restore Database
5.- Shrinkdatabase again (moving pages to the begining of the file)
When I did these steps, The size decreases to 16 Gb, I repeat the steps, and the files decreases to 13 GB, again and 10 Gb and again and 8 Gb, and I stop,
and the unused space is 270,800 Kb.
Now I will use maintanence plain to avoid the DB grow again
Thanks for your help
Manuel
|
|
|
|
|
This is the error I get when I try to update. Some extra information I didn't include when I posted about this a while back is that I've got 2 forms, a main form with a dataset(this is mostly a "view" of what is returned by what the user clicks, no edit features), and I've got a simple edit form that lets the user change stuff in the table they want to edit. Each form uses its own dataset, but I've tried it with a shared dataset. If I make the 2nd form in its own project and run it as a stand alone to edit the table, it works just fine.
******************************************************************************
The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.
******************************************************************************
Sample code that causes this error.
private void button1_Click(object sender, EventArgs e)
{
this.Validate();
this.tb.EndEdit();
this.tb.Update(this.DataSet.tb);
}
Form was created by using the drag and drop abilities of the data source. simple form, only 4 items in the table.
|
|
|
|
|
I don't think that there is a problem with the C# code that you've posted. From the error message, it sounds like it is the way you have set up your table that is the culprit. The call to Update will cause an update to the table which causes the error message you cited - not because of the Update call, but because of the columns and setup of the table you update.
I have not read the previous post you mentioned, but could you post some information about the way you have setup your table?
Sincerely,
Alexander Wiseman
|
|
|
|
|
The other post was the same, with less information and I posted it on the C# forum. As far as the table is concerned,
ID, Name, Notes
PK INT, Char[10], Char[50]
No Null, Nulls, Nulls
No foreign keys, but another table does use ID as an FK.
Also, I click the buttons on the BindingNavigator for New, and Delete and they work fine, I just can't make a change to the current record displayed and click the save button.
I've made both forms (stand alone project, and the edit form for the parent application) identically, same datasets, same connection strings. All code on both forms is auto generated by the IDE. For some reason, the standalone works fine, but this one doesn't.
|
|
|
|
|
It sounds more like the problem is in Visual Studio's auto-generated code. I am not familiar with this error, but I did a few Google searches and here is what I came up with:
First, on a Weblog I found a post concerning this issue, the solution to which was to remove the default "Create as CLUSTERED" option on the database. You can view that solution here[^]. This does not sound like your problem, since you said it works if you create the application as its own form, meaning that the problem is probably not so much with the table structure as with Visual Studio's code generation.
Concerning that: second, I found this thread which suggests deleting and recreating the data-adapter as a solution to the problem. You can see that here[^].
I hope one of these links gets you going in the right direction. I apologize that I have not been able to be of more assistance.
Sincerely,
Alexander Wiseman
[EDIT]
For the second link, see post number 3 on the page - that is the one to which I was referring.
[/EDIT]
|
|
|
|
|
YOU ARE MY HERO!
I removed the clustered keys option from the tables I'm trying to manipulate and viola!
THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!
You dont understand how long I've had this problem and nobody in my department has been able to even point me in the right direction.
I can finally get this project turned in this quarter! Woohoo!
|
|
|
|
|
Excellent, I'm glad it worked!
I know how you feel about being stuck on a problem for awhile 
Sincerely,
Alexander Wiseman
|
|
|
|
|
there is a company with workers and the boss wants to be able to query stuff about the workers using my application; like "show the workers with name xxxx who started working on yy.yy.yyyy". when new workers arrive and some quit, the database should be handled accordingly.
this is a random application that rushed to my mind, all i want to do is to get involved with databases. first of all, i am pretty sure i can handle this application without a specific database, why should i use for instance mysql? why should i use a database anyway? i can just create my structs and have my own data arranged in the way i like...
i am working on windows platform,
now, i assume i will use a database for this, lets say mysql. i will create my database using a language like perl, from the txt files. then lets say i will maintain the application with c++. probably there will be libraries of mysql available?? i will use their functions etc. all of this will take place on my own pc, now after everything is finished, how should i pack my application? i want it to be as simple as an executable file which you download and just run to install the application. at this point, what should i include in the final package? its pretty obvious that i need the libraries, but besides i also need mysql to be running on the client am i right? but if the database wont be changed in the client side then does the client really need to have mysql? lets say the database should be updated at times. one solution, i guess, is to maintain the database on my own then let the client copy and paste this new database to his installation directory everytime there is an update.
unfortunately i dont have any books on databases and stuff. when i did some google all i encounter is sql directives and those specific stuff... not an overall introduction. and almost every place where i seek answers, i see php .net asp c# etc... i dont know any of those languages, do i really need them with my pretty straight forward application? all in all i am confused and i hope to hear any suggestions/info
|
|
|
|
|
I suppose if you wanted to store this information in text files instead of a database you COULD...it is just easier to use a database, especially if you have large amounts of data and several tables.... I think nowadays it will be a lot harder to find help if you go with textfile way.
About the type of database...I recently wrote a VB.Net windows PC project using an SQL Express database. It required a very hefty bootstrap that users needed to install. So midway through we switched to use an Access database. Access runs on the Jet platform, which is standard in windows. We just had to make sure that they had Microsoft Data Access Controls (MDAC) installed and that was a considerably smaller install. Access has less capabilities than SQL Express, but the project was small so it didn't effect anything.
About distribution...if I understand your situation correctly you want one central location to store the data but to distribute reports or queries on the data to many people. It kind of sounds like your perfect solution would be to make a website that displays the data. That way users would be able to access the most current information at any time and you only have to update the data in one place...no worries about people viewing out of date information.
Assuming that you haven't done any programming, you may be interested in trying the free version of Visual Studio 2005 for people who program as a hobby.
If all of this is too much and you don't want to write a website/program, it almost sounds like you could do all of this in an Access Database or if you don't mind it being a little crude...in an Excel spreadsheet. Excel has the ability to add VB code by record macros. Here is a site I found that goes in depth on the subject:
http://www.office-addins.com/-excel-addins/microsoft-excel-visual-basic-macros-made-easy-1.html[^]
Not sure exactly what you were looking for, but hopefully this will give you some more ideas to explore. Good luck.
|
|
|
|
|
thanks for the reply,
in the distribution part i dont want to get involved with those web pages and dynamic updates. so the database will be located on each client separately. the application should work offline. i am trying to keep everything simple so that i can take my time to deal with the database itself. when there is an update in the data, i am not sure as i said how to maintain the update. should i maintain the database on my own and then distribute it to the clients as a simple update file to replace the old database, or should the client be able to maintain the database on his side with a small script i provide? in the latter case i am sure he will need mysql or whatever database i use, but i am not sure with the former option. and also i dont know anything about the convention of this update stuff.
i have done programming with java, c, c++, perl. i am not yet sure which one to use for the development of the main application. as i said any help is welcome.
and one last question, in my scenario is the client also a server? because it has everything it needs to run the application, it can work totally offline(ignoring the update, which can also take place offline provided that the update file is possessed)
|
|
|
|
|
Is there a way to change a column's size in a table without dropping the column and then adding it again with the new size defined?
|
|
|
|
|
ALTER TABLE mytable ALTER COLUMN mycolumn VARCHAR(25)
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
And here I thought it would be complicated.
Thanks for your help!
|
|
|
|
|
Hi,
I am asked to convert stored procedures to functions .Can anyone tell me the advantages of functions over stored procedures and vice versa and i am not shoor if i can use system ftions like getdate() in functions
pls help me out
thnks in advance.
|
|
|
|
|
Functions are useful when you only need to return a single value from a procedure, rather than a set of data.
You can use all built in functions as you would in a SP, the only limitation AFAIK is that you cannot insert/update from a function.
Current blacklist
svmilky - Extremely rude | FeRtoll - Rude personal emails | ironstrike1 - Rude & Obnoxious behaviour
|
|
|
|
|
|
Live and learn, of the countless times ive used UDF's ive never come across this!
Current blacklist
svmilky - Extremely rude | FeRtoll - Rude personal emails | ironstrike1 - Rude & Obnoxious behaviour
|
|
|
|
|
|
sishya wrote: Can anyone tell me the advantages of functions over stored procedures and vice versa and i am not shoor if i can use system ftions like getdate() in functions
pls help me out
You cannot use functions such as GETDATE() because they are non-deterministic. A function must have a deterministic operation. i.e. Given the same inputs it will always produce the same output.
I wouldn't go around and convert stored procedures to functions just because.
I wouldn't say functions or stored procedures have any avantages over each other. They are designed for different uses.
A function can return data to calling code more easily that a stored procedure can. A stored procedure can perform data modification actions (INSERT/UPDATE/DELETE) that a function can't.
Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog
|
|
|
|
|
I'm writing a small C# windows application that will create a new database and populate it with tables, views and stored procedures. Of course I am using SQL scripts that I'm launching from the windows application. I ask the user to enter 2 fields, the server name and the instance name of the SQL server.
My question is: is there a way to check if the entered server name and instance exist?
Since I launch the scripts by starting a process to run osql, I don't know if I get some return value to tell me that the server was not found. So basically even if the server name is wrong, the application says that the operation is completed, although nothing has been done.
Thanks,
Talal
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
