pdiblasi
asked on
sql server memory recommendation
I am running sql server 2000 on a windows 2003 server that also has exchange 2003 and and file sharing. The server has 3400ram and the sql is using 1.6 Ram, exhange store is using 600mb and after all the other little things using ram the server is only left with 400mb which is kind of low. This is causing the server to crash once in a while hang up.
Should I setup sql server with a fix memory setting or give it a maximum setting? What can I do to make sql server run smoothly. There is only 1 sql database on this server and only 8 people are accessing it. Would running the ddshrink command help the sql database run more smoothly. I just looked at this server for the first time yesterday and it didn't even have a maintenance plan setup. I setup a maintanence plan to backup the database and transaction log. However, when it runs the Transaction log backup it fails. I'm not sure why. There is 40GB of free space avaliable on the hard drive. Just to recap, the DB Backup and Optimizations run successfully but the transaction log backup fails. Would this have anything to do with it and how could I rectify the trans backup failure?
THanks again,
Paul
Should I setup sql server with a fix memory setting or give it a maximum setting? What can I do to make sql server run smoothly. There is only 1 sql database on this server and only 8 people are accessing it. Would running the ddshrink command help the sql database run more smoothly. I just looked at this server for the first time yesterday and it didn't even have a maintenance plan setup. I setup a maintanence plan to backup the database and transaction log. However, when it runs the Transaction log backup it fails. I'm not sure why. There is 40GB of free space avaliable on the hard drive. Just to recap, the DB Backup and Optimizations run successfully but the transaction log backup fails. Would this have anything to do with it and how could I rectify the trans backup failure?
THanks again,
Paul
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> you cannot perform a transaction log backup on a database in simple mode.
rbrooker is correct.
In simple recovery mode as soon as the transaction is completed the transaction is deleted from the log. Also the reason I asked about Perform integrity test before backups is that the integrity tests need to put the DB into single user mode. But if anyone is in the database at the time the integrity tests cannot run and the backup fails. Also note that a bug in the maint plan wizard doesn't take the option out of the maint plan. You have to rebuild the maint plan from scratch.
rbrooker is correct.
In simple recovery mode as soon as the transaction is completed the transaction is deleted from the log. Also the reason I asked about Perform integrity test before backups is that the integrity tests need to put the DB into single user mode. But if anyone is in the database at the time the integrity tests cannot run and the backup fails. Also note that a bug in the maint plan wizard doesn't take the option out of the maint plan. You have to rebuild the maint plan from scratch.
ASKER
Ok, I backed up the transaction log another database that was in Full mode and it went successfully. Good call! I'm new to SQL server. Could you explain the difference to me. I don't know what either of them do or what it is.
Thanks for your excellent help,
Paul
Thanks for your excellent help,
Paul
Transactions: You can do transactions in which the data is not commited/actually updated until all processes are completed. Think of it as a transfer of money from a savings to checking account. You don't want the data committed until you have credited the checking and debited the savings. So you do a Begin Transaction command and debit the savings account for $50 -- but the server/application can't find the checking acount -- so then you roll back the transaction and don't commit the data. But if it finds the checking account the it credits the $50 and commit the data.
The transaction is written to log first in case the server would crash during the transaction. So when the server comes back up the server looks in the log and says I took $50 from the savings but I didn't put the $50 in checking, so I'll undo the $50 from savings.
In Simple Recovery, as soon as the transaction is completed/aborted any history in the log is deleted. In Full recovery mode the transaction is left in the log file until a back-up occurs. In bulk logged mode it catches the majority of transactions but if you delete or import a 1000 rows it just catches that a 1000 rows were imported but does not actually "keep" the data in the transaction log.
As for recovery modes it all depends on how much data you can afford to lose. If you are a bank or some other type institution you may need to be in full recovery mode, with the ablility to do point in time recovery. But if you are a real estate office with maybe 20 transactions a day you might be able to be in simple recovery mode and just do nightly transactions.
In my case I have databases in simple and full recovery modes. One of my databases is for a sales positioning report. All we need is the structure of the DB because the data is volatile and replaced every day. I just back it up nightly. I also have an accounting database that we need to have point in time recovery. Especially if an accidental mass deletion were to occur. That DB gets a full nightly backup and hourly transaction log backups.
It all depends on your situation. Also note that the system databases (except model) cannot be set to full recovery, so you need to back them up separately from your other databases. The maint plan wizard has a system DB backup option.
The transaction is written to log first in case the server would crash during the transaction. So when the server comes back up the server looks in the log and says I took $50 from the savings but I didn't put the $50 in checking, so I'll undo the $50 from savings.
In Simple Recovery, as soon as the transaction is completed/aborted any history in the log is deleted. In Full recovery mode the transaction is left in the log file until a back-up occurs. In bulk logged mode it catches the majority of transactions but if you delete or import a 1000 rows it just catches that a 1000 rows were imported but does not actually "keep" the data in the transaction log.
As for recovery modes it all depends on how much data you can afford to lose. If you are a bank or some other type institution you may need to be in full recovery mode, with the ablility to do point in time recovery. But if you are a real estate office with maybe 20 transactions a day you might be able to be in simple recovery mode and just do nightly transactions.
In my case I have databases in simple and full recovery modes. One of my databases is for a sales positioning report. All we need is the structure of the DB because the data is volatile and replaced every day. I just back it up nightly. I also have an accounting database that we need to have point in time recovery. Especially if an accidental mass deletion were to occur. That DB gets a full nightly backup and hourly transaction log backups.
It all depends on your situation. Also note that the system databases (except model) cannot be set to full recovery, so you need to back them up separately from your other databases. The maint plan wizard has a system DB backup option.
ASKER
Thank you very much!! So I understand this correctly Simple - no backups except for the full backup so you can't go to a specific backup throughout he day (kind of like differental backups) you only have a full
Full Recovery - can take tranaction log backups thoughout the day so if the data changes you can go back to that point of transaction log backup?
I have a database that only gets updated once a night and I have to constantly run ddshrink command to stop that database from using all the space on the server. Since this database only gets updated once a night I would like to convert it to simple recovery model.
What is the procedule for converting simple recovery model to full recovery model or
full recovery model to simple recovery model?
This is the last question, I promise :-)
Full Recovery - can take tranaction log backups thoughout the day so if the data changes you can go back to that point of transaction log backup?
I have a database that only gets updated once a night and I have to constantly run ddshrink command to stop that database from using all the space on the server. Since this database only gets updated once a night I would like to convert it to simple recovery model.
What is the procedule for converting simple recovery model to full recovery model or
full recovery model to simple recovery model?
This is the last question, I promise :-)
The easy way is to just to go to the DB --> Properties --> Options Tab and select the dropdown and set it to simple.
ASKER
I seen that setting. Do I need to do a backup of the database first? Can I make the change from simple to full while the database is being used by users and web applications?
No problem changing while in use. I would do a full backup right after the change and then monitor for the log backups to occur.
ASKER
how do I monitor for the logs for backups?
You can either look at the location you are backing the database to. Or in the Enterprise Manager Management --> SQL Server Agent --> Jobs you can see the job history of the backups.
Glad to be of assistance. May all your days get brighter and brighter.
Hi,
just a further note, you can schedule a database shrink as well. when you have the shrink dialog box up, there is a schedule option on teh first tab.
just thought i would mention it.
:)
just a further note, you can schedule a database shrink as well. when you have the shrink dialog box up, there is a schedule option on teh first tab.
just thought i would mention it.
:)
ASKER
Thanks guys! You both been a great help.
ASKER
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.