Can any one help me to prepare basic check list for database server for SQL server 2005.

Question

Can any one help me to prepare basic check list for database server for SQL server 2005.

anand-861974

Mr or Mrs. 500

Points: 528
More actions
September 18, 2008 at 5:00 am

#126503

Can any one help me to prepare basic check list for database server for SQL server 2005.
after creating the database i want to some basic check on database like properties ,.mdf,.ldf,space ......... like i want know some more...... to verify whether database created correctly or not

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 1

anand (9/18/2008)
Can any one help me to prepare basic check list for database server for SQL server 2005.
after creating the database i want to some basic check on database like properties ,.mdf,.ldf,space ......... like i want know some more...... to verify whether database created correctly or not

Hi

Are you planning to restore the backup on this newly database.

If yes then you can reorganize the index, if needed build them, update statistics, set appropriate fill factor.

You could restrict the access of database,configure the db the way you need by using sp_configure.

if required the secondary file can be created ..

If you just need to check if db is correctly created then you can check for spaces, its files.

Jason Wisdom-380639 SSCrazy Points: 2083 More actions · Answer 2

Tune the TempDB.

TempDB is so much more important in SQL 2005 than it was in earlier versions.

And the default settings of 8MB / 1MB with 10% growth is a recipe for death by fragmentation.

If you are in a full enterprise production mode, 10GB is a minimum recommended for the data file, and I use 2GB for the log file.

For data files, I create a separate .ndf for each processor/core. 2 processor, dual-core = 1 .mdf, 3 .ndf's.

And I make sure to put it on its own physical disk (or SAN partition), with nothing else.

Aside from tuning the sql (indexes, execution plans), tempdb is by far the most common first place I look, when diagnosing a slow database server / planning a new database server.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Jason Wisdom (9/18/2008)
Tune the TempDB.
TempDB is so much more important in SQL 2005 than it was in earlier versions.
And the default settings of 8MB / 1MB with 10% growth is a recipe for death by fragmentation.
If you are in a full enterprise production mode, 10GB is a minimum recommended for the data file, and I use 2GB for the log file.
For data files, I create a separate .ndf for each processor/core. 2 processor, dual-core = 1 .mdf, 3 .ndf's.
And I make sure to put it on its own physical disk (or SAN partition), with nothing else.
Aside from tuning the sql (indexes, execution plans), tempdb is by far the most common first place I look, when diagnosing a slow database server / planning a new database server.

Who recommended those settings? Got a link?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason Wisdom-380639 SSCrazy Points: 2083 More actions · Answer 4

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1180698,00.html

This article, combined with my own experiences, forms the basis of my above post.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

tempdb is highly dependent on activity and data sizes. If you have a 500MB database, 10GB is way overkill.

Disk space is cheap, and it makes sense to just assign some to tempdb, but I'm not sure 10GB is or isn't a good recommendation.

You've setup the server, be sure you have free space in your databases. They should have enough to last a few months. Not sure what that is, depends on your activity and growth. Monitor this and set reminders to do it regularly.

Make sure you have backups set and running. Once those are running, then you'll have an idea (based on log backup size) of how large your logs need to be.

Jason Wisdom-380639 SSCrazy Points: 2083 More actions · Answer 6

What is your db size?

Checklists would vary, depending on whether you're running a 500MB database, a 10GB database, or a 1 TB database.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Jason Wisdom (9/19/2008)
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1180698,00.html
This article, combined with my own experiences, forms the basis of my above post.

Thanks, Jason... I misread your previous post a bit. I agree that these are good starting points. And I certainly agree that the default setting are a form of "death" by fragmentation and growth.

Thanks for the feedbak.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

anand-861974 Mr or Mrs. 500 Points: 528 More actions · Answer 8

anand-861974

Mr or Mrs. 500

Points: 528

September 21, 2008 at 11:26 pm

#874788

hi

my present database size is around 1-2 GB

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

anand (9/21/2008)
hi
my present database size is around 1-2 GB

So, take a look at the link Jason posted. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason Wisdom-380639 SSCrazy Points: 2083 More actions · Answer 10

Thank you -

Glad I could help.

TempDB is just one aspect of it, however.

I don't know of one, all-encompasing SQL Server Checklist.

This is a "performance" checklist, but it seems to be more about monitoring, than initial setup:

http://www.mssqltips.com/tip.asp?tip=1240