Hi i need urgent help for the below subject

I am using DNN 5.6.2 we bsite..
My problem is
SQL db size is increasing without any updations in site.Now DNN db size is 51 GB.One month before it is 31 GB.We have only 15 registered users and not that much usage in this site.Y this DB size is increasing like this?How can i reduce the DB size?Please help me


I am using sql 2008 mangmt studio.

1 - Under SQL Management Studio expand the databases tree and right click on the DB which has this problem
2 - Go to "Tasks/Shrink/Files"
3 - Select the file type and see which one is the big one the Data or the Log
4 - If it is the Log then you can select "Reorganize pages before releasing unused space"
5 - Shrink file to "0" or to it's minimum as indicated.

So this is how you should be able to free up space.

Next you need to figure out what is causing this DB increase. Maybe you are getting too many errors on your site and it is all getting logged under the Log Viewer.

Good luck,

You will definitely want to take a look at Admin, Event Log and take a look at the errors that are being logged. Feel free to post errors here and perhaps we can help you identify the problems.

admin event viewer showing minimum of eight General exception messages
between each login.Please see two three of exception messages below

9/4/2012 4:48:40 AM General Exception AssemblyVersion: 5.6.2; PortalID: 0; PortalName< ...
AssemblyVersion: 5.6.2
PortalID: 0
PortalName: kginfotech.com
UserID: -1
UserName:
ActiveTabID: 36
ActiveTabName: Home
RawURL: /Default.aspx
AbsoluteURL: /Default.aspx
AbsoluteURLReferrer:
UserAgent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:15.0) Gecko/20100101 Firefox/15.0
DefaultDataProvider: DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider
ExceptionGUID: dd5405ae-552e-4de9-829f-f982a0cc7e07
InnerException: Access to the path ‘…………\Pages\921_ACABA51644A302FBB325E30E7931BAF8.data.resources' is denied.
FileName:
FileLineNumber: 0
FileColumnNumber: 0
Method: System.IO.__Error.WinIOError
StackTrace:
Message: System.UnauthorizedAccessException: Access to the path '……..\Portals\0\Cache\Pages\921_ACABA51644A302FBB325E30E7931BAF8.data.resources' is denied. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access) at DotNetNuke.Services.ModuleCache.FileProvider.SetModule(Int32 tabModuleId, String cacheKey, TimeSpan duration, Byte[] output)
Source:



9/4/2012 4:48:40 AM General Exception AssemblyVersion: 5.6.2; PortalID: 0; PortalName< ...
AssemblyVersion: 5.6.2
PortalID: 0
PortalName: kginfotech.com
UserID: -1
UserName:
ActiveTabID: 36
ActiveTabName: Home
RawURL: /Default.aspx
AbsoluteURL: /Default.aspx
AbsoluteURLReferrer:
UserAgent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:15.0) Gecko/20100101 Firefox/15.0
DefaultDataProvider: DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider
ExceptionGUID: c16228b9-8337-40b4-a909-7789f8f8b693
InnerException: Access to the path …\Portals\0\Cache\Pages\809_ACABA51644A302FBB325E30E7931BAF8.data.resources' is denied.
FileName:
FileLineNumber: 0
FileColumnNumber: 0
Method: System.IO.__Error.WinIOError
StackTrace:
Message: System.UnauthorizedAccessException: Access to the path …\Portals\0\Cache\Pages\809_ACABA51644A302FBB325E30E7931BAF8.data.resources' is denied. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access) at DotNetNuke.Services.ModuleCache.FileProvider.SetModule(Int32 tabModuleId, String cacheKey, TimeSpan duration, Byte[] output)
Source:




9/4/2012 1:20:39 AM Page Load Exception AssemblyVersion: 5.6.2; PortalID: 0; PortalName< ...
AssemblyVersion: 5.6.2
PortalID: 0
PortalName: kginfotech.com
UserID: -1
UserName:
ActiveTabID: 36
ActiveTabName: Home
RawURL: /Default.aspx?dnn$ctr374$Login$Login_DNN$txtPassword=&__dnnVariable=`{`dshReset_imgIcon:exp`:`-1`,`dshQuestionAnswer_imgIcon:exp`:`-1`,`__scdoff`:`1`,`__dnn_pageload`:`__dnn_SetInitialFocus(\u0027dnn_ctr374_Login_Login_DNN_txtUsername\u0027);`,`containerid_dnn_ctr374_ModuleContent`:`374`,`cookieid_dnn_ctr374_ModuleContent`:`_Module374_Visible`,`min_icon_374`:`/images/min.gif`,`max_icon_374`:`/images/max.gif`,`max_text`:`Maximize`,`min_text`:`Minimize`,`containerid_dnn_ctr628_ModuleContent`:`628`,`cookieid_dnn_ctr628_ModuleContent`:`_Module628_Visible`,`min_icon_628`:`/images/min.gif`,`max_icon_628`:`/images/max.gif`,`containerid_dnn_ctr650_ModuleContent`:`650`,`cookieid_dnn_ctr650_ModuleContent`:`_Module650_Visible`,`min_icon_650`:`/images/min.gif`,`max_icon_650`:`/images/max.gif`,`containerid_dnn_ctr599_ModuleContent`:`599`,`cookieid_dnn_ctr599_ModuleContent`:`_Module599_Visible`,`min_icon_599`:`/images/min.gif`,`max_icon_599`:`/images/max.gif`}&dnn$dnnSEARCH$txtSearch=&__VIEWSTATE=/wEPDwULLTE2NDIyOTEzMjkPZBYGZg8WAh4EVGV4dAU+ AbsoluteURL: /Default.aspx
AbsoluteURLReferrer:
UserAgent: Mozilla/5.0 (compatible; MSIE 7.0; MSIE 6.0; Site Scanner Bot; +http://www.websiteprotection.com)" target="_blank" rel="nofollow">http://www.websiteprotection.com) Firefox/2.0.0.3
DefaultDataProvider: DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider
ExceptionGUID: 3f4e0584-f860-49e5-88b5-b5e7751285f5
InnerException: The state information is invalid for this page and might be corrupted.
FileName:
FileLineNumber: 0
FileColumnNumber: 0
Method: System.Convert.FromBase64String
StackTrace:
Message: DotNetNuke.Services.Exceptions.PageLoadException: The state information is invalid for this page and might be corrupted. ---> System.Web.HttpException: The state information is invalid for this page and might be corrupted. ---> System.Web.UI.ViewStateException: Invalid viewstate. User-Agent: Mozilla/5.0 (compatible; MSIE 7.0; MSIE 6.0; Site Scanner Bot; +http://www.websiteprotection.com)" target="_blank" rel="nofollow">http://www.websiteprotection.com) Firefox/2.0.0.3 ViewState: /wEPDwULLTE2NDIyOTEzMjkPZBYGZg8WAh4EVGV4dAU PCFET0NUWVBFIEhUTUwgUFVCTElDICItLy9XM0MvL0RURCBIVE1MIDQuMCBUcmFuc2l0aW9uYWwvL0VOIj5kAgEPZBYOAgQPFgIeB1Zpc2libGVoZAIFDxYEHgdjb250ZW50ZB8BaGQCBg8WAh8CBcQBbG9jYWxob3N0OjgwMDAKIHd3dy5hZnRpY29tLmNvbQp3d3cuYWZ0aWNvbS5jb206ODAwMCAgCiAgd3d3LmFmdGljb20uY29tIAogIHd3dy5hZnRpY29tLmNvbTo4MDAwL2hvbWUCBVhDb3B5cmlnaHQgMjAxMS0yMDEyIGJ5IEtHSW5mb3RlY2guPGJyPldlYiBEZXNpZ25lZCwgSG9zdGVkICBhbmQgTWFpbnRhaW5lZCBieSBLR0luZm90ZWNoZAIIDxYCHwIFC0RvdE5ldE51a2UgZAIJDxYCHwIFDmtnaW5mb3RlY2guY29tZAIMDxYCHwIFDUlOREVYLCBGT0xMT1dkAgIPZBYCAgEPZBYGZg9kFgJmDxYCHwFoFgJmD2QWAmYPZBYGAgMPEGRkFgBkAg8PZBYCZg8PFgYeCEltYWdlVXJsBRQvaW1hZ2VzL2NvbGxhcHNlLmdpZh4NQWx0ZXJuYXRlVGV4dAUITWluaW1pemUeB1Rvb2x... ---> System.FormatException: Invalid character in a Base-64 string. at System.Convert.FromBase64String(String s) at System.Web.UI.ObjectStateFormatter.Deserialize(String inputString) at System.Web.UI.ObjectStateFormatter.System.Web.UI.IStateFormatter.Deserialize(String serializedState) at System.Web.UI.Util.DeserializeWithAssert(IStateFormatter formatter, String serializedState) at System.Web.UI.HiddenFieldPageStatePersister.Load() --- End of inner exception stack trace --- --- End of inner exception stack trace --- at System.Web.UI.ViewStateException.ThrowError(Exception inner, String persistedState, String errorPageMessage, Boolean macValidationError) at System.Web.UI.ViewStateException.ThrowViewStateError(Exception inner, String persistedState) at System.Web.UI.HiddenFieldPageStatePersister.Load() at System.Web.UI.Page.LoadPageStateFromPersistenceMedium() at System.Web.UI.Page.LoadAllState() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) --- End of inner exception stack trace ---
Source

I tried the way shrinking Log file to 0 MB.
Result is 30GB reduced to 29.6GB.
If i will take backup of this DB it is having only 144 MB.

I dont know where i am wrong


Logviewer contains nearly 5400different messages

From either management studio or from Host, SQL page:

Truncate Table eventlog
Truncate Table sitelog


That will kill all of the data in your event log table, but it will get you space back. Try shrinking the database after that.

The first couple of messages you posted said "access denied" so look at file permissions on your web directory and subdirectories of it.

Dear Joe,

Thank u
I did the steps below and now the db size is 850 MB.



Truncate Table eventlog
Truncate Table sitelog

shrink files to 0(shrink-files-select-filetype log-recoginse pages to 0 )
take backup again and restore it
shrink files to 0

I have large database log files.

I have gone to Host>SQL and performed:

Truncate Table eventlog
Truncate Table sitelog

Is there a way to then shrink the database from Host>SQL? I am a not familiar with database operations.

Thanks

There's a very nice blog entry on this subject at http://www.dnnsoftware.com/communit...ll-dr-sql.

The simple answer is:

Enter the following SQL command into the text box and click on ‘Execute’:

DBCC SHRINKDATABASE(databasename)

Or

DBCC SHRINKDATABASE(0)

Yes sir, thank you for the quick response. This is still one of the best resources for DNN!

The second option works. For the first option, how do you tell what the database name is? I realize that is probably an elementary things, but I am not a developer.

Thanks again,
Andre

The blog entry I referenced includes the command to get the database name.

I'm glad that you find this site helpful.

Ok, great, thanks again!

Andre

I am struggling with this again.

I have a site with a log file that has grown to 20+GB.

I have performed:

Truncate Table eventlog
Truncate Table sitelog

followed with

DBCC SHRINKDATABASE(0)

The site seems immune to those commands. It still has a 20+GB log file.

I tried following the steps from the first reply from Anderson Oliveira and tried to access using SQL Management Studio Express, but it says it doesn't have enough storage to open.

Any suggestions would be GREATLY appreciated. Thanks!

Hi Andre,

Maybe your database is set to recovery mode "full". If that is the case, the shrinking will not work well.

I would check whether or not your db is in full or simple mode with this SQL statement:

SELECT name, user_access_desc, is_read_only, state_desc, recovery_model_desc
FROM sys.databases

Then if it is in "full" mode, I would change it to "simple" with the SQL command:

Alter database [DATABASE-NAME] SET Recovery simple

Sincerely,
Andy

Thank you sir for the assistance!!

Did it work?