data file size problem

how can i change the initial size of the data and log file size ?

in my database properties it shows that my data file size is 81 mb and log file size is 985 mb! but my database only contains some tables and stored procedures with few rows of data in each table

and i checked that the actual mdf and ldf files are really that big... i tried to change it but it didn't work...

can someone please teach me how to change it thanks!

Backup the DB with Truncate log. check books online for more info.

|||

http://www.codeproject.com/useritems/truncate_log_SQL_server.asp

Data Base Shrink

I have SQL Server 2000 on WIN2k Advanced Server And I have
a Database Consists of:-
1-Single Database File .
2-single transaction log file(initial size 2 MB).
I have Observed that the transaction log file'Capacity
reaches 23 Giga Byte so I Made a Backup for the whole
database and then I tried to shrink the log file using
enterprise manager shrink database wizard.then I
discovered that the physical file capacity was not
reduced, although enterprise manager gave me a message
that the file has been shrinked.
I tried More And More But No result.
Help will be so much appreciated
Best Regards:-
Ahmed NourGood shrink article can be found :-
http://www.mssqlserver.com/faq/logs-shrinklog.asp
--
HTH
Ryan Waight, MCDBA, MCSE
"Ahmed Nour" <a_m_nour@.hotmail.com> wrote in message
news:0d7201c393e7$706c2590$a401280a@.phx.gbl...
> I have SQL Server 2000 on WIN2k Advanced Server And I have
> a Database Consists of:-
> 1-Single Database File .
> 2-single transaction log file(initial size 2 MB).
> I have Observed that the transaction log file'Capacity
> reaches 23 Giga Byte so I Made a Backup for the whole
> database and then I tried to shrink the log file using
> enterprise manager shrink database wizard.then I
> discovered that the physical file capacity was not
> reduced, although enterprise manager gave me a message
> that the file has been shrinked.
> I tried More And More But No result.
> Help will be so much appreciated
> Best Regards:-
> Ahmed Nour|||Ahmed ,
Refer to following urls
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow and AutoShrink
--
- Vishal

D.B. Maintenance

I am new to SQL Server. I'm running the initial install of SQL Server 2005 (no SP are installed, ver: 9.00.1399). I have not done any maintenance on my d.b since it's inception (about 3 months ago) and I'm concerned. It seems to be excessively large and I'm not sure about it's integrity. I tried to do some maintenance using the maintenance plan wizard. First of all, what is the best and easiest way to do periodic maintenance on the d.b. (i.e. reindexing, compacting, etc).? Secondly, in trying to use the maintenance plan wizard it failed in the end with the following:

Saving maintenance plan failed:

one or mor arguments are invalid

I'm at a loss as to what to do first. My Windows OS is Windows Server 2003 Standard SP2 completely updated as of today.

I was going to try and upgrade to SQL Server SP2 but that's a project and I'm not sure it will solve anything.

Any help would be greatly appreciated.

First point is to test and upgrade to SP2 in order to get all bug fixes for Maintenance plans.|||

First, you really should backup all of your databases and backup logs for all databases that are not in simple recovery. And do that right away. Then run DBCCs as soon as you can on all databases. There is no need to regularly run any "compacting". You want to size your data and log files to be the appropriate size as needed for daily operations and maintenance and that's it. In terms of reindexing, you want to check for fragmentation and then determine which reindexing operation is appropriate and proceed from there.

You'll probably have to mess with this for a bit to clean things up if you haven't done anything in 3 months - I hope it isn't a production server. Being that you may not have looked at much on that box, you would also want to go through your SQL error logs and the windows event logs to see if you have any other issues on the server that may need your attention.

No one can tell you the best way to do your maintenance as that has some dependencies on your system, the business needs, etc. A good place to start is by going through the System Administration chapter of the Operations Guide (it was written for 2000 but much of it applies to 2005) to get an idea of what all you should be doing and then after you define what you need to do you can explore how you will do it:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops4.mspx

-Sue