| Author |
Topic |
|
kundan76
Starting Member
8 Posts |
 Posted - 2010-04-19 : 14:48:20
|
| My database size in go daddy is 200 Mb and because of it I can not add anymore pages to my site. Here is my database table entries[dbo].[SearchItemWordPosition] 329205[dbo].[SearchItemWord] 121632[dbo].[SearchWord] 15947[dbo].[TabSettings] 4134[dbo].[ModuleSettings] 4063[dbo].[TabModules] 3005[dbo].[Modules] 2298[dbo].[HtmlText] 2188[dbo].[ModulePermission] 2188[dbo].[HtmlTextLog] 2188[dbo].[TabPermission] 2103[dbo].[SearchItem] 1950[dbo].[Tabs] 721[dbo].[Lists] 384[dbo].[SearchCommonWords] 369[dbo].[ScheduleHistory] 322[dbo].[ModuleControls] 172[dbo].[EventLog] 145[dbo].[EventLogTypes] 128[dbo].[EventLogConfig] 104[dbo].[Packages] 93[dbo].[Files] 53[dbo].[HostSettings] 52[dbo].[ModuleDefinitions] 51[dbo].[DesktopModules] 39[dbo].[ProfilePropertyDefinition] 38[dbo].[TabModuleSettings] 34[dbo].[Forum_Keywords] 32[dbo].[SkinControls] 32[dbo].[Blog_Settings] 32[dbo].[PortalDesktopModules] 32[dbo].[Assemblies] 30[dbo].[DesktopModulePermission] 28[dbo].[Skins] 26[dbo].[Forum_EmailTemplates] 19[dbo].[FolderPermission] 18[dbo].[Permission] 17[dbo].[Folders] 17[dbo].[PortalSettings] 16[dbo].[UserDefinedData] 12[dbo].[Forum_Templates] 12[dbo].[Forum_TemplateTypes] 11[dbo].[PackageTypes] 11[dbo].[Schedule] 10[dbo].[Version] 10[dbo].[EventQueue] 9[dbo].[Forum_Permission] 9[dbo].[SkinPackages] 8[dbo].[UserProfile] 8[dbo].[UserDefinedFields] 8[dbo].[Dashboard_Controls] 6[dbo].[aspnet_SchemaVersions] 4[dbo].[Authentication] 3[dbo].[Roles] 3[dbo].[UrlTracking] 3[dbo].[UserRoles] 3[dbo].[UserDefinedRows] 3[dbo].[WorkflowStates] 3[dbo].[Workflow] 2[dbo].[aspnet_Membership] 2[dbo].[Users] 2[dbo].[aspnet_Users] 2[dbo].[aspnet_Applications] 1[dbo].[PortalLocalization] 1[dbo].[Portals] 1[dbo].[Profile] 1[dbo].[UserPortals] 1[dbo].[WebServers] 1[dbo].[Forum_Users] 1[dbo].[Languages] 1[dbo].[PortalAlias] 1[dbo].[PortalLanguages] 1I ran command delete from [dbo].[SearchItemWordPosition] 329205[dbo].[SearchItemWord] 121632[dbo].[SearchWord] 15947 and it was succesfull. Basically these are the biggest space on the database. But my database size is still 190 MB. How can I reduce my Database size . Help Really appreciatedThanksKundan |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kinnerton
Starting Member
21 Posts |
Posted - 2010-04-19 : 17:53:30
|
| Tara - is DBCC SHRINKFILE supposed to reduce the DB size to less than the 'initial size'? I have a DB that's 70% unused - but I need the space back (and am only going to use to develop against) but its initial size is 300Gb and it should go down to 80ish GB. SHRINKFILE and SHRINKDATABASE run but won't shrink beyond 300Gb!Any ideas?CheersKinnerton |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kundan76
Starting Member
8 Posts |
Posted - 2010-04-19 : 18:44:14
|
| Tara, Soory for being ignornat but how do rebuilt the clustered indexes. I just restore the bak file on my standalone sql server. thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kundan76
Starting Member
8 Posts |
Posted - 2010-04-19 : 20:19:27
|
| SQL 2005Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kundan76
Starting Member
8 Posts |
Posted - 2010-04-19 : 21:32:34
|
| Thanks Tara, Can you please post the full command I dont know much in SQL Thanks Again |
|
|
|
Kinnerton
Starting Member
21 Posts |
Posted - 2010-04-20 : 09:37:03
|
| Hi Tara,Thanks for getting back to me on this.Do you mean reindex the clustered indexes on the whole DB or just the ones on the truncated tables?CheersKinnerton |
|
|
|
Kinnerton
Starting Member
21 Posts |
Posted - 2010-04-20 : 09:39:15
|
| I am on SQL Server 2005 Standard as well. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-04-20 : 12:15:39
|
| Here's an example directly from BOL ALTER INDEX topic:USE AdventureWorks;GOALTER INDEX PK_Employee_EmployeeID ON HumanResources.EmployeeREBUILD;GODepending upon your edition and table properties, you could set the rebuild to be online. That's why I can't write the query for you, it depends on your settings.Kinnerton, if you want to reclaim the space from the truncate/delete, then you need to rebuild the clustered index on the tables that were affected.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Kinnerton
Starting Member
21 Posts |
Posted - 2010-04-20 : 18:31:16
|
| Tara, many thanks. Hope I can repay the favor someday.Kinnerton |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kundan76
Starting Member
8 Posts |
Posted - 2010-04-21 : 11:26:20
|
| Tara , My database size is still same after I ran it. I cant understand whats going onLet me tell you my database This database is used for DNN and is taken a backup from Go daddy Hosting . I restored in my SQL express and ran all the scripts. The database size before running all the scripts is 209 MB After running all the scripts and deleting it is down to 190 MB. Do you think after deleting so many records from 3 table it only reduce to 19 MB on the file size ThanksKundan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kundan76
Starting Member
8 Posts |
Posted - 2010-04-21 : 12:57:03
|
| I just use the same as you send me Just change the name on the script |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-21 : 13:50:30
|
| Not sure I've got any ideas. Run a table-size script that shows Used/Slack space and see if there is a lot of Slack space?I haven't checked it just now, but I think this script has the necessary info:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762 |
|
|
|
Kinnerton
Starting Member
21 Posts |
Posted - 2010-04-26 : 18:14:10
|
| kundan76 - If you try to do the Shrink File (not actually run it) command from SSMS it'll tell you how much space it is 'supposed' to free up (an alternative as mentioned above is to run a query to see how 'full' your db is).Also - do you have 'allow snapshot isolation' enabled? Try turning this off before the shrinkfile.A last course of action I suppose is to script out your database and import the data. Messy, inelegant and crude I know - but it IS an option. |
|
|
|
|
Reduce Database Size