What Is Being Stored In ASPNETDB.MDF -- It Consumes ~1/2 My Sites Space!
Mar 14, 2007
Using VS2005, VB backend and javascript,
I have developed a relatively simple site - its got a few (12) simple aspx pages but its mostly client side javascript. Keeping disk storage costs down is a big concern with this my site. The disk usage for the site is ~24M. Since this was larger than I expected I started inspecting the files comprising my site and found that the "ASPNETDB.MDF" in my "App_data" folder is consuming 10.2M by itself. The thing is that site only has a few pages with calls to SQL Server - but I never did anything (that I know of) with ASPNETDB.MDF. Through VS2005, I opened up the MDF file and poked around, everything that I looked at was empty (NULL).
So my questions are:
What is causing the ASPNETDB.MDF to consume 10.2M even thought I can't see any data stored in it?
Is there anyway for my to reduce the size of this file? If so, how?
Can someone give me some pointers as to where to read up on what the ASPNETDB.MDF does?
I inherited a SQL server (2005) that sits on a Windows 2003 box (upgrading to 2008 R2 and Win2008, ASAP) and on our SQL Data Drive (260GB - 22GB free) we have 16 databases (that are associated with our primary application). One database in particular is causing me quite a bit of pain - on Tuesdays. This table will consume all available free space (save 700kb) and place it within it's 'reserved' space. It's not being used and I can reclaim it by shrinking. I have done this for 3 consecutive weeks now and I have always thought that shrinking is not really a best practice.
Based on a job I scheduled to spit out various database stats I discovered the following:
Somewhere after 11pm on Monday (and on or before 11:30pm) the reserved space goes from 9GB down to 23 then 37MB on this particular database.
After 1:30am on Tuesday (and on or before 2:00am) the database consumes nearly all available disk space and thus expands its reserved space to 28GB
I believe it has something to do with the Indexes being rebuilt - per the Maintenance Plan the Index Rebuild is supposed to occur every Monday @ 11pm. On 6/18 it ran from 11pm - 11:44pm with no problems. Starting on 6/26 (and subsequently 7/2 and 7/9) they have started at 11 and ended anywhere between 2 - to 3 hours later. And with the following error:
Failed-1073548784) Executing the query "ALTER INDEX [PK_activityLog] ON [dbo].[activityLog] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF ) " failed with the following error: "Could not allocate a new page for database 'FA_PROD_SDDS' because of insufficient disk space in filegroup 'PRIMARY'.
Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The database 'FA_PROD_SDSS' is the one that I am having issues with.
The SQL Server Database hangs overnight and also consumes high disk space on one of our servers. This has been recurring for quite a few weeks and occurs daily.
Can somebody assist me in trouble-shooting the same
Hi, I am trying to do this: UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl') What would be the syntax. Any help appreciated. Thanks
I am getting the error: Cannot open database "aspnetdb" requested by the login. The login failed. When I browse to my ASP.NET 3.5 LINQ web application on the IIS 6.0 server on Server 2003. I imagine this is because while I granted SQL Server 2005 login and permissions to my database that the application stores its data in, I did NOT grant any rights to the service account the IIS Application Pool uses for its identity to the aspnetdb database on SQL Server which is where all my roles information is stored at. My question is what are the MINIMUM permissions needed for this database so it can perform its roles related functions? I'm using Windows Authentications with the SQL Role provider for authorization.
Thank you.
EDIT: I think I only need to open the aspnetdb database and add my login to the aspnet_Roles_FullAccess role. Is that correct?
The problem is that our production SQL Server ALWAYS consumes the full CPU capacity when in operation. It also does not utilize the full 4G memory alloted to it, but only consistently uses 1.7G. Now, the application using the DB does use a lot of server-side cursors and since the software developer is a 3rd-party vendor, we can only do so much to recommend application optimization. Also, we've discovered that the application modules that were identified as mostly participating in high-CPU utilization does not always consume it highly, although they are the ones mostly involved in it. So, in relation to the limited usage of the 4G-alloted memory, we have theorized that perhaps the SQL processes may be hanging & waiting for each other to complete processing whenever more memory is needed, thus contributing to the server slow down, but this is memory not CPU utilization. We have been wracking our brains for ways to prevent the server slow down due to CPU utilization.
Hi, I have a new problem .I have a DB on server which is using only one processor and it keeps using up all memory on server. Any thoughts appreciated! TIA PD
Hi all I use 64 bit 2005 server with 8cpu and 8G of memory. This server is accessed by large number of intensive or not so intensive programs. I had eliminated all inefficient queries by means of sql profiler. What I see now is 30 procs or so runining in 1 second. They are all pretty simple and as I said use indexes. cpu column for most show 0, reads show 10 - 50 - pretty good. But... my cpu utilization is 75% in avg. across of all 8 cpu's. I really can't find an answer for it. If procs run so efficient, where does cpu go? Disk queue length is 0.04 or less - seems very good. Task manager shows that all of it 75% attributed to sql server. So which resources besides sql queries use so much cpu? Do I have to look at some other areas and which ones where cpu could be used besides sql queries themselves.
I have implememted service broker in my application.My sqlservr.exe comsumes more than 2 gb of memory which eventually cause my computer to hang.I unable to track the problem. Can anyone help me out of this. Looking at the SQL Server logs i found following error message logged multiple times ;
The query notification dialog on conversation handle '{09B96045-99A4-DC11-802F-0013EFF142EF}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'. -Aravind cheziyan
After launching MS SQL Server 7.0 SP 1 on a Compaq Proliant 5500 with 1GB memory the sqlservr.exe process gradually consumes all available memory and brings down NT. This happens when there's appearently no activity on SQL Server.
I gave up on the ScriptTasks, but desided to use Custom tasks instead. Problem again. My code opens a 400M file and reads it line by line using StreamReader. Each line is approximately the same length. For each line, there is some processing, and then the line is written into another file using StreamWriter. I am watching the DtsDebugHost process with TaskManager open and here is what happens:
Initially its all good. Then when it read through first 150M+ of the input file, the memory usage of the DtsDebugHost shoots up dramatically - about 1Gig (both virtual and physical memory). Then the task fails with OutOfMemoryException. I thought the problem is with my code, but it still happens even if I only read a line from one file and write it to another, without any processing!
When I invoke the same code from Execute Process task, its all good - no problems at all whatsoever.
For what it's worth, I hacked up MS' sp_spacedused and created a new stored procedure called sp_dbspaceused. I made the following modifications:
1. It returns a single resultset (instead of multiple resultsets); 2. I eliminated the options that were specfically geared towards sizing of individual objects (no object name parameter and no update statistics parameter); 3. I eliminated the formatting from the result set (the numbers are expressed in KB)
Place the code into an admin database or (more risky and less "best practice") directly into your master database.
Usage: USE MyDatabase GO
EXEC AdminDatabase.dbo.sp_dbspaceused GO
CREATE PROCEDURE sp_dbspaceused
as
declare @idint-- The object id of @objname. declare@pagesint-- Working variable for size calc. declare @dbname sysname declare @dbsize dec(15,0) declare @logsize dec(15) declare @bytesperpagedec(15,0) declare @pagesperMBdec(15,0)
/*Create temp tables before any DML to ensure dynamic ** We need to create a temp table to do the calculation. ** reserved: sum(reserved) where indid in (0, 1, 255) ** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text) ** indexp: sum(used) where indid in (0, 1, 255) - data ** unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */ create table #spt_space ( rowsint null, reserveddec(15) null, datadec(15) null, indexpdec(15) null, unuseddec(15) null )
set nocount on
/* ** If @id is null, then we want summary data. */ /*Space used calculated in the following way **@dbsize = Pages used **@bytesperpage = d.low (where d = master.dbo.spt_values) is **the # of bytes per page when d.type = 'E' and **d.number = 1. **Size = @dbsize * d.low / (1048576 (OR 1 MB)) */ begin select @dbsize = sum(convert(dec(15),size)) from dbo.sysfiles where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size)) from dbo.sysfiles where (status & 64 <> 0)
select @bytesperpage = low from master.dbo.spt_values where number = 1 and type = 'E' select @pagesperMB = 1048576 / @bytesperpage /* select database_name = db_name(), database_size = ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'), 'unallocated space' = ltrim(str((@dbsize - (select sum(convert(dec(15),reserved)) from sysindexes where indid in (0, 1, 255) )) / @pagesperMB,15,2)+ ' MB') */ print ' ' /* ** Now calculate the summary data. ** reserved: sum(reserved) where indid in (0, 1, 255) */ insert into #spt_space (reserved) select sum(convert(dec(15),reserved)) from sysindexes where indid in (0, 1, 255)
/* ** data: sum(dpages) where indid < 2 **+ sum(used) where indid = 255 (text) */ select @pages = sum(convert(dec(15),dpages)) from sysindexes where indid < 2 select @pages = @pages + isnull(sum(convert(dec(15),used)), 0) from sysindexes where indid = 255 update #spt_space set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */ update #spt_space set indexp = (select sum(convert(dec(15),used)) from sysindexes where indid in (0, 1, 255)) - data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */ update #spt_space set unused = reserved - (select sum(convert(dec(15),used)) from sysindexes where indid in (0, 1, 255))
select reserved = cast((reserved * d.low / 1024.) as bigint) , data = cast((data * d.low / 1024.) as bigint) , index_size = cast((indexp * d.low / 1024.) as bigint) , unused = cast((unused * d.low / 1024.) as bigint) from #spt_space, master.dbo.spt_values d where d.number = 1 and d.type = 'E' end
naresh writes "I would like to learn sql server 2000 and I do not have any programming experienc at all so how do i learn this programme. Do you have any suggestion or any basic books or materials you guys can refer to me
“HELP !! We’ve lost about 25 client’s websites. The databases were backed up along with all the actual files contained within each CSK….in addition, all the original databases are intact & can be reattached to the new SQL server…..the problem that exists where the original CSK files do not recognize the original database once it is reattached to the new SQL server. Any help would be most appreciated. This is the error…… Login failed for user 'DARRYL1ASPNET'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'DARRYL1ASPNET'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
If I were going to look into investing into a virtual dedicated server and wanted to save LOTS of $$$ by offerring SQLExpress (FREE) vs. SQL Server Enterprise $25,000 sticker price. The Web sites that I build are for small businesses. Is there an issue in using SQLExpress on the Internet? Considering it is the Web application account ID accessing the database so that is one user hitting the DB. I am unclear on how I need to evaluate multiple calls to the system depending on how many users are on the Web site opening the connection performing SQL Commands then closing the connections all with the same user creditials. <moojjoo />
SQLTeam.com SQLSERVERCENTRAL.com TekTips.com MSDNSQL Forum forums.sqlmag.com
I hate to ask for information such as this but with the low response to my questions I was wondering anyone knows of a better site for SQL Server database mail issues?
I am trrying to figure out what the best replication would be to use and or setup...
Her eis the current goal and structure..
We are just moving over to a new custom POS system that will be using SQL databases....We have have three locations and we want each location to be independent in case of network connectively failures to our primary location.
Basically, all three locations will be running SQL server 2005 and the POS app... We want replication to occur overnight, so that each location will have the other locations transactions from the previous days, etc...
Essencially I want all three locations to "syncronize" their data every night....basically two-way replicaiton between all three sites...
Master Site will have say databaseA that the local POS system will use Sencond Site will have say databaseA that the local POS system will use as well
Third Site will have say databaseA that the local POS system will use well...
I have the following code but it keeps erroring on the last line and I'm unsure as to why it is doing it???
Here is the error message
Msg 102, Level 15, State 1, Line 42
Incorrect syntax near '@Sites'.
declare @Sites varchar(50)
declare @Kit_No char(20)
declare @Location char(2)
set @Location = 'Ho'
set @Kit_No = 'mo1k'
if (SELECT sitetype from gss.dbo.kup_regions where region_code = @Location) = 10
begin
set @Sites = '''Pe'',''Hg'',''Vo'',''' + @Location + ''''
end
select
KR.Region_Code,
KR.Region_Name,
Z.Qty,
Z.Kit_Description,
Z.BookedOutToDate as Usage,
Z.Local_Cost,
(select overstock from gss.dbo.vGss_overstock where region_code = Z.region_code and kit_no = 'm01k' )as Rolling_Avg,
C.Symbol,
Z.FOB,
(SELECT
Price
FROM
gss.dbo.FedEx_Rates Fed
WHERE
SourceRegion = (SELECT Region_Code FROM gss.dbo.KUP_Regions WHERE Region_Name = 'penistone')
AND Weight = (SELECT MAX (Weight) FROM gss.dbo.FedEx_Rates WHERE Weight < (SELECT Packed_Unit_Weight From gss.dbo.KUP_Kits WHERE Kit_Code = (select Kit_Code from gss.dbo.GSS_Kits where Kit_No = 'm01k' ))+0.5)
I have a SQL 2005 server on a separate machine from my IIS machine, and anytime the SQL server restarts (like for last night's automatic updates) the connection pooling seems to break. Among the apps on this IIS box is Community Server 2.1, along with some other custom-built apps. The only way to resolve it is to stop and restart the IIS services. Previously all SQL-dependent apps on that IIS box broke when SQL was reset, but I seemed to have addressed it, at least on my custom-built apps, by forcing a TCP connection in the connection strings (instead of the default named pipes method). I did change the Community Server connection strings as well, but for some reason that didn't seem to work. Has anyone else experienced similar problems with interruptions in SQL connectivity bringing down apps that connect to that SQL server? I hate to turn off automatic updates just to make sure my ASP.NET apps are available. I apologize if this isn't clearly an ASP.NET question, but hopefully someone can help me out. Thanks,Josh
I'm very new at this and found this video very helpful. I downloaded Visual Web Developer 2005 Express Edition and following the direction except for changing a few column names. When I get to the point of viewing in browser I get the drop down box but nothing in it and the table doesn't show up either. I copied all the data to my website and tried to view it there but got some error that I don't understand. Can someone please help. The website I uploaded the files to is ocbeachrentals.net/default.aspx. Thanks.
I am wondering how people maintain their SQL Servers which run at several customers sites and disk space is getting smaller and smaller? I want to say that we have tables in SQL dbs which hold a lot of date consisting of statistics, errors, logs etc. They grow and grow and existing data is not needed anymore as soon as the data get older than let's say for one year. How do you overcome the problem reducing the tables but not charging the system too much as the major application also runs on the same server?
I am new to RDO and SQL I have been using DAO and know that the syntax is similar but the connections to the database is different. Does anyone know of a good site that explains this?
We have a large SQL database, and we need to send out updated records to many clients' sites which are not connected.
We currently have a tool which looks at the audit log of changes we made, creates a file based on this, which is then emailed to our clients. They then run a tool we created to remerge the changes.
I suspect SQL server replication might make all this possible. Am I right? Can SQL server produce a file automatically which can be applied to a remote database to update the tables as appropriate? From looking at some replication stuff it looks to me like you have to have the servers on the same network.
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005. The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions 1) Replacing spaces with €œ €? 2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
Hello all, I am in the process of doing some research and thought I would query you guys (and gals) for your opinions. I have never setup replication and my company is bringing up multiple sites, multiple GIS servers with one master GIS server. They want them to be replicated. Network bandwidth throughout the day is a concern and I may have an option to run replication at night. All servers will be continuously connected and they tell me that each site will be owners of it's data so data conflict should not be an issue. I originally thought qued-updating subscribers and transactional replication but it sounds like that does not work if the servers will always be connected. Any opinions on this anyone? Also, keep in mind that this is GIS data so there will be pretty big blob files in the database. Thanks in advance. RayH
I'd like to throw this idea 'out there' to see if I'm missing something I'll later regret.
I'm looking to resolve a scalability issue within our point-of-sale program. Currently the PK on transactional tables (sales and orders) is created by the application layer using a 'MAX(PKCol) + 1' mechanism. Obviously this requires that all users of the system, whether they're local or remote, have current data at any time they wish to insert. It's this limitation I'd like to remove. Most sites are using MS SQL Server 2000. No sites use anything specific to a later version.
By having a PK that can be generated independently of a 'master' database we can overcome this issue. The PK values will need to be unique within a 'group' of shops and able to be generated by a program operating at any level. From 'head office' which manages a number of shops, to the server at a given shop and even the register / till itself should be able to create ID's while disconnected from the server (using a local database).
It seems there's three main ways to accomplish this: - Identities, - MachineID, CurrentPK composite. - GUID's
Identities: I've ruled out identities as I believe the administration overhead of dealing with them makes them impractical (there may be several hundred registers and therefore as many ranges to be set up within a group).
MachineID, CurrentPK composite: The MachineID references a Machine table which has an entry for each ethernet MAC address which connects to the database. The reason I chose to store the MAC in another table rather than simply using it as column is that I'm fetching it from sysprocesses.net_address(nchar(12)) and believe it's computationally cheaper to use an int than a text column. This mechanism means that we can still expose the PK to the user in some cases (eg: InvoiceNumber printed on a receipt). When the local database is not up to date (usually due to network problems) there will be cases where the CurrentPK will be duplicated but kept unique since it's coupled with the new MachineID. The big drawback to this method is that all current code will need to be revised to deal with the composite keys (this will be a significant amount of development).
GUIDs: Ugly to look at and time-consuming to type. They're not something which you'd expose to a user unmodified so realistically this means altering existing code to use a new 'user friendly' number where the PK is currently exposed to them. The use of GUIDs rule-out the use of clustered indecies on tables they're the PK for lest most inserts cause a page split. The splits would also necessitate more frequent index defrags / rebuilds. Using a non-clustered index incurs a penalty Vs a non-fragmented clustered one (doesn't it?) so while this avoids page-splits it comes at a cost.
After all that I think the best solution is to use GUIDs with a non-clustered index for each of the PK's. While it might not be the fastest of the options (slower reads/joins Vs composite PK) it will be significantly faster to develop while maintaining acceptable performance.
Is it possible to host more than one site on the server that hosts reporting services?We have a web app that is accessed through intranet. This site is hosted (IIS)on the same server as the sql server 2005 database that is used as the backend. Is it possible to run reporting services from this same server? or does the reporting services default site require to be the only site hosted?
