Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL DB Administration


SQL DB Administration

Author
Message
tinausa
tinausa
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 172
I inherited a small SQL Server DB which then morphed into a larger DB.
I am not a DBA and have no IT or other help.
I was wondering if someone can point me to a brief summary of what do I need to make sure the server and DB are healthy and working. I am doing programming, QA, DB administration and all life-cycle steps.

I am sure that there are a thousand docs on this site but I was hoping to get a pointer to some set of steps to do the administration. I want to make sure that I do not put cart before the horse.

Thanks a million,
Tina
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38958
this is a great reference that gets mentioned in a lot of posts here;
you can download the PDF for free.
http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

another one that i like a lot is Brent Ozar's 60 minute Blitz!, where you are thrown at a SQL server and you wnat to get a handle on it in one hour:
http://www.brentozar.com/sql/blitz-minute-sql-server-takeovers/

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Suresh B.
Suresh B.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 5326
Following are important steps
1) Check database integrity
2) Backup database
3) Backup Transaction Log

Additional points:
1) Backup both user and system databases.
2) If the data and log files are on same drive, move them to different drives
tinausa
tinausa
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 172
Thank you very much. I greatly appreciate it.
SQLmountain
SQLmountain
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 782
I would like to add that once you have done the above (and those transaction log backups are critical to database health) you should think about getting a test instance going. Restore your production database(s) to the test server and use this server to experiment with administrative techniques before you establish them on the production server.
tinausa
tinausa
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 172
Thanks much, the one thing that I have been doing is periodically restore the DB and make sure it works OK.

I have a bunch of ASP.NETT t code as the UI and I have not tested that UI with the restored DB. So this is an excellent suggestion. I will definitely test that.

Also note that this whole operation is under IT radar, so I Have no IT support. I back up the DB files almost daily (unless there is no change), and hide copied on other machines and servers. And to top that, my mini SQL Server has two DBs and one of them is about to get rather large.

Thanks much,
Tina
tinausa
tinausa
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 172
Can you please briefly explain why the log file should be on the different machine?

As I said to someone else, this is under IT radar, running on Windows 7 64 bit machine.

Thanks much for your tips.
Suresh B.
Suresh B.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 5326
No. Not on different machine. On different Drives.

It is a best practice to place data and log files on different drives for following 2 reasons:
1) DR (Disaster recovery). If one of the drives fails, you can recover the database without loosing commited data.
2) Performance. Data file is written randomly. Log file is written sequentially.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47241 Visits: 44377
Please read through this - Managing Transaction Logs


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


tinausa
tinausa
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 172
Thank you I will reconfigure the DBs. I have to upgrade from SQL Express to SQL Standard next week, so that's when I will do it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search