MS SQL Db Size Problem

Post Reply

Page 12 >

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
GeorgeB Members Profile Find Members Posts Newbie Joined: 31 May 2005 Location: Turkey Status: Offline Points: 9	Post Options Post Reply Quote GeorgeB Report Post Thanks(0) Quote Reply Topic: MS SQL Db Size Problem Posted: 15 March 2006 at 8:22pm
	Hi, I have upgraded my access database to ms sql. My access db was around 15 mb. After converting the database to ms sql, its size has increased to 50 mb. Also i m not receiving to much post in these days , but database is increasing heavly (82 mb now). What can i do to decreasly db size? Thank you.

WebWiz-Bruce Members Profile Find Members Posts Admin Group Web Wiz Developer Joined: 03 September 2001 Location: Bournemouth Status: Offline Points: 9844	Post Options Post Reply Quote WebWiz-Bruce Report Post Thanks(0) Quote Reply Posted: 16 March 2006 at 12:13pm
	Make sure that you have setup the SQL Database to use the 'simple recovery model' and that you have set the log files to be only a maximum of around 2Mb.

Ozoyo Members Profile Find Members Posts Newbie Joined: 28 January 2004 Status: Offline Points: 13	Post Options Post Reply Quote Ozoyo Report Post Thanks(0) Quote Reply Posted: 16 March 2006 at 12:55pm
	Hi, I have the same problem, only on a larger scale. My MSSQL database is now 1,5 Gigas!!! I have issue with my host who wants to throw me out of the shared MSSQL server (and I can understand why). boRg, I have always been using your method (simple recovery) and the logs are only 1 Meg. So it is really the data part that is over 1.5gigs. I have been deleting regularily posts older than 6 months but, from what I understand (that is little ) it is no use cleaning up without shrinking the DB. So I would have two questions: > Is 1,5G a reasonable size for 6 months or more of posts. If not, what is the approximate standard? > Would shrinking and setting up an automatic shrink solve this problem? Thanks

WebWiz-Bruce Members Profile Find Members Posts Admin Group Web Wiz Developer Joined: 03 September 2001 Location: Bournemouth Status: Offline Points: 9844	Post Options Post Reply Quote WebWiz-Bruce Report Post Thanks(0) Quote Reply Posted: 16 March 2006 at 1:08pm
	Sorry I'm not an expert on managing SQL server, this is something that an SQL Server Admin would need to do for you. 1.5G does seem like allot, but then you haven't mentioned how many posts, members, etc. you have in the database!! Would be interesting to know. All I can tell you is that the forum on this site has an SQL Server database of 95Mb and has 93,000 posts and 9,000 members, as well as having another 20 tables in the database for other ASP applications running on this site. I have the SQL Database set to grow in blocks on 5Mb, to auto shrink, and using the simple recovery model. Unless you have around 1 million posts 1.5Gig seems like allot, so I would think that your SQL Server database is not setup correctly and would ask the SQL Server Admin to have a look at the issue as your forum will not be performing as fast as it should. Edited by -boRg- - 16 March 2006 at 1:09pm

Ozoyo Members Profile Find Members Posts Newbie Joined: 28 January 2004 Status: Offline Points: 13	Post Options Post Reply Quote Ozoyo Report Post Thanks(0) Quote Reply Posted: 16 March 2006 at 1:17pm
	385441 Posts in 12760 Topics in 8 Forum(s). But only 1678 members (heavy posters!) The size of your forum seems much more reasonable... That block thing seems interesting, is that complicated to do? I think we agree on the fact that 1.5 gigs is not normal, I will try to ask an IT guy on my forum for advice. The DB has never been shrunk since it was created and I think this is the main problem. My host proposed to put the DB on my dedicated server with a 79� licence /month. but I am faraid moving it is not going to be very straight forward. I still remeber the hell it was to transfer data from Access to SQL .

WebWiz-Bruce Members Profile Find Members Posts Admin Group Web Wiz Developer Joined: 03 September 2001 Location: Bournemouth Status: Offline Points: 9844	Post Options Post Reply Quote WebWiz-Bruce Report Post Thanks(0) Quote Reply Posted: 16 March 2006 at 1:36pm
	You can change how your SQL Database is setup (if you have the correct permissions) in the Enterprise Manager by right clicking on your database and selecting properties. All the growth blocks, recovery model, and auto-shrinking features are in there. Transferring one SQL Database to another is quite simple as the Enterprise Manager has DTS import/export tools built in to do this exact job. If you need to go down the dedicated server root, it maybe worth looking at having a dedicated server for your entire site and installing SQL 2005 Express on the server for your database, you would then have full control over the whole server. I would also go for a dedicated server with at the very least 1gig of memory.

Ozoyo Members Profile Find Members Posts Newbie Joined: 28 January 2004 Status: Offline Points: 13	Post Options Post Reply Quote Ozoyo Report Post Thanks(0) Quote Reply Posted: 16 March 2006 at 1:47pm
	boRg, thanks a million for your answers. If I have to go for the dedicated option I would have to pay the licence but I am willing to do that to get the site out of trouble. Then I will have to think on how to generate revenue... What's the MSSQL 2005 Express licence cost? There is new version of the site coming (soon I hope) but for the moment, the site will keep generating 0� a month. That is why I was trying to solve that size problem to please my host. If the licence is reasonable enough I will do what you said. Then I will have the proper rights to manage the DB as I wish.

WebWiz-Bruce Members Profile Find Members Posts Admin Group Web Wiz Developer Joined: 03 September 2001 Location: Bournemouth Status: Offline Points: 9844	Post Options Post Reply Quote WebWiz-Bruce Report Post Thanks(0) Quote Reply Posted: 16 March 2006 at 3:05pm
	The reason why I mention SQL 2005 Express is because is is FREE. It's basically a cut down version of SQL Server 2005 with some limitations such as a 4gig database size limit and 1 CPU, but none of the other limitations of the Express version will effect the running of Web Wiz Forums as they are the more complex tools that are not required for Web Wiz Forums. I plan on moving this site across to SQL Server Express 2005 very soon as currently the site is running on MSDE which is a free cut down version of SQL 2000 which has an 8 concurrent user limitation which is occasionally is coursing issues when under heavy load. SQL Express 2005 doesn't have this limitation. Have a look on the Microsoft site, you can download it from there and it explains what the differences are between the Express version and the full version of SQL Server 2005. I personally believe that MS bought the Express version out for things like web sites in response to loosing so much ground at this type of level to mySQL. Edited by -boRg- - 16 March 2006 at 3:09pm

Post Reply	Page 12 >

Forum Jump

Forum Permissions View Drop Down

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum