Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dr223
Constraint Violating Yak Guru
444 Posts |
 Posted - 2009-12-15 : 05:20:36
|
| Hallo, I am working with SQL server 2005 and have a database called ODSData5 (which has one table called tbl_o_pat)the database has the following file names with the corresponding sizes;ODSData5.mdf = 813 MBODSData5_log.ldf = 43 GBNow, tbl_o_pat was not indexed initially and I wanted to set the first field pat_uid as Primary key that will not allow null values. So, I right clicked the table in the design view and set it to primary key, unclick the checkbox and saved. Tbl_o_pat is built up of 110 millions record therefore it took some considerable time before the table was indexed. The problem is that the size of the database has been tripled since the update and the size now is as shown;ODSData5.mdf = 128 GBODSData5_log.ldf = 98 GBWhy did it increase so much and secondly is there anyway I can reduce the size?Thanks in advance |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-12-15 : 06:25:02
|
| 1. 128GB is not triple 813MB...it's FAR more. Typo...missing decimal point?2. log sizes are a function of the recovery model you have in place and also the amount of action done on a database during a single commit.3. reducing size may be possible in this situation by a database reorg/shrink. BUT it may not be possible IF this is a reflection of the new PROPER size for the database. i.e, you've been accidently undersized to date.Search here for info from member TKIZER on suitable pracatices to solve this problem. |
 |
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2009-12-16 : 06:11:58
|
| sorry, ODSData5.mdf increased from 43 Gb to 128 Gb after the indexing, is this possible? The table indexed (tbl_o_pat) had 110,000,000 records with data types as 11 datetime fields, 21 nvarchar fields, 8 decimal fields, and 5 int fields. |
|
|
|
|
|
|
|
|
Re: Set Primary Key