Database size exploded for unknown reason.

I have been working on an app for several weeks. My database size went from 9MB to about 12MB. Sometime in the last 2 days it jumped up to 242 MB. I don't know why. Now I cannot open it with management Studio Express. It says another process is using it. Usually this means I have a aspnet_wp.exe process running (from Visual Studio) and I just end the process, but Wndows Task Manager will not end the process. It does knock its size way down to 2.9 MB but won't kill it. Rebooting won't kill it either.

Are these two symptoms related? How do I recover?

Thank you.

Hi,

Are you closing the Database connection after each request?

How much you set the db to increase once there is a need for that?

Database Setup Package/Installation

Hi Folks,
I've created a SQL Server database and converted it to Oracle (an app we
made supports MSSQL and Oracle) and I'd like to be able to provide a
"server-side" installation that clients can run on their database server
that would automatically create the MSSQL or Oracle database, the tables,
index, relationships, etc. and insert the needed data for a "new" database.
I have very little experience in the realm of database setup & installation.
Has anyone ever done this before and if so, what product/technology did you
use? I've been searching the newsgroups and haven't found much useful info
on the topic. Am I looking in the wrong place?
Thanks,
Jason-- Jason wrote: --
>> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new" database.
--
Hi Jason,
This is a fairly trivial exercise. Have a look at the sample *.sql scripts that automatically creates the sample Northwind and Pubs database when you install SQL Server. They will give you some idea on how to automatically create a database and objects within that database.
The files are instnwnd.sql and instpubs.sql to install Northwind and Pubs, respectively. The files can be found on your <drive>:\Program Files\Microsoft SQL Server\MSSQL\Install folder
Hope this helps,
-Eric Cárdenas
SQL Server support|||You could, from your install program, either spawn OSQL with your script file (see Eric's post) as
input parameter. Or you can write your own app which reads the file and execute the TSQL statements
in the script file. Or you could, of course, embed the various CREATE statements inside your setup
programs source code.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...
> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new" database.
> I have very little experience in the realm of database setup & installation.
> Has anyone ever done this before and if so, what product/technology did you
> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>|||I would probably
1. Script out Your DB. This will enable you to create a blank version of
your DB somewhere else.
2. Have your standing data in text files and use BULK INSERT or bcp
statements to load it into the tables.
3. Use osql to load the scripts you have created.
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\co
prompt.chm::/cp_osql_1wxl.htm)
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...
> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app
we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new"
database.
> I have very little experience in the realm of database setup &
installation.
> Has anyone ever done this before and if so, what product/technology did
you
> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>|||Thanks for the responses from everyone. I'm aware I can provide someone with
.sql scripts...what I'm more interested in though is an installation I can
provide that goes something like this:
User runs Setup.exe to luanch the setup.
During Setup, one of the screens prompts them to enter the db information
(name of the server, un/pw)
Then setup takes that information and runs the DDL scripts against the
server they provided.
Are there any products (like Wise or Installshield maybe) that help automate
the creation of setups to do this or am I looking at needing to write my own
custom setup program?
Jason
"Eric Cardenas" <anonymous@.discussions.microsoft.com> wrote in message
news:BE41F8CB-4DBA-439D-A034-32CF737D0ED0@.microsoft.com...
> -- Jason wrote: --
> >> Hi Folks,
> > I've created a SQL Server database and converted it to Oracle (an app
we
> > made supports MSSQL and Oracle) and I'd like to be able to provide a
> > "server-side" installation that clients can run on their database
server
> > that would automatically create the MSSQL or Oracle database, the
tables,
> > index, relationships, etc. and insert the needed data for a "new"
database.
> --
> Hi Jason,
> This is a fairly trivial exercise. Have a look at the sample *.sql scripts
that automatically creates the sample Northwind and Pubs database when you
install SQL Server. They will give you some idea on how to automatically
create a database and objects within that database.
> The files are instnwnd.sql and instpubs.sql to install Northwind and Pubs,
respectively. The files can be found on your <drive>:\Program
Files\Microsoft SQL Server\MSSQL\Install folder
> Hope this helps,
> -Eric Cárdenas
> SQL Server support
>

Database Setup Package/Installation

Hi Folks,
I've created a SQL Server database and converted it to Oracle (an app we
made supports MSSQL and Oracle) and I'd like to be able to provide a
"server-side" installation that clients can run on their database server
that would automatically create the MSSQL or Oracle database, the tables,
index, relationships, etc. and insert the needed data for a "new" database.
I have very little experience in the realm of database setup & installation.
Has anyone ever done this before and if so, what product/technology did you
use? I've been searching the newsgroups and haven't found much useful info
on the topic. Am I looking in the wrong place?
Thanks,
JasonYou can create scripts to do this. You can find similar
scripts from the installation of databases on SQL Server.
For an example, check the script that creates the Northwinds
database. The file is instnwnd.sql and it's located in the
install subdirectory under MSSQL, e.g.
C:\Program Files\Microsoft SQL Server\MSSQL\Install
-Sue
On Tue, 2 Dec 2003 14:35:53 -0800, "Jason"
<jasonmauss_nospam@.vsdotnetguru.com> wrote:

quote:

---

>Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app w
e
>made supports MSSQL and Oracle) and I'd like to be able to provide a
>"server-side" installation that clients can run on their database server
>that would automatically create the MSSQL or Oracle database, the tables,
>index, relationships, etc. and insert the needed data for a "new" database.
>I have very little experience in the realm of database setup & installation
.
>Has anyone ever done this before and if so, what product/technology did you
>use? I've been searching the newsgroups and haven't found much useful info
>on the topic. Am I looking in the wrong place?
>Thanks,
>Jason
>

---

|||You could, from your install program, either spawn OSQL with your script fil
e (see Eric's post) as
input parameter. Or you can write your own app which reads the file and exec
ute the TSQL statements
in the script file. Or you could, of course, embed the various CREATE statem
ents inside your setup
programs source code.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app
we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new" database
.
> I have very little experience in the realm of database setup & installatio
n.
> Has anyone ever done this before and if so, what product/technology did yo
u
> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>

---

|||I would probably
1. Script out Your DB. This will enable you to create a blank version of
your DB somewhere else.
2. Have your standing data in text files and use BULK INSERT or bcp
statements to load it into the tables.
3. Use osql to load the scripts you have created.
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\co
prompt.chm::/cp_osql_1wxl.htm)
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app

---

we

quote:

---

> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new"

---

database.

quote:

---

> I have very little experience in the realm of database setup &

---

installation.

quote:

---

> Has anyone ever done this before and if so, what product/technology did

---

you

quote:

---

> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>

---

|||Thanks for the responses from everyone. I'm aware I can provide someone with
.sql scripts...what I'm more interested in though is an installation I can
provide that goes something like this:
User runs Setup.exe to luanch the setup.
During Setup, one of the screens prompts them to enter the db information
(name of the server, un/pw)
Then setup takes that information and runs the DDL scripts against the
server they provided.
Are there any products (like Wise or Installshield maybe) that help automate
the creation of setups to do this or am I looking at needing to write my own
custom setup program?
Jason
"Eric Cardenas" <anonymous@.discussions.microsoft.com> wrote in message
news:BE41F8CB-4DBA-439D-A034-32CF737D0ED0@.microsoft.com...

quote:

---

> -- Jason wrote: --
we[QUOTE]
server[QUOTE]
tables,[QUOTE]
database.[QUOTE]
> --
> Hi Jason,
> This is a fairly trivial exercise. Have a look at the sample *.sql scripts

---

that automatically creates the sample Northwind and Pubs database when you
install SQL Server. They will give you some idea on how to automatically
create a database and objects within that database.

quote:

---

> The files are instnwnd.sql and instpubs.sql to install Northwind and Pubs,

---

respectively. The files can be found on your <drive>:\Program
Files\Microsoft SQL Server\MSSQL\Install folder

quote:

---

> Hope this helps,
> -Eric Crdenas
> SQL Server support
>

---

Database permissions

Hi,

In my web app I've got two databases (the asp profile one and my own custom one). If I take the code and data and use it to create another website on another box, I usually get a database error saying the permissions are wrong. In this case I usually just give everyone full control and it works, but this obviously isn't good practice!

So, I'm wondering what permissions on database files does ASP need? Which users need what permissions?

Thanks

The most reliable way to move a SQL Server database and permissions is Backup and Restore all other methods sometimes will not move permissions so you have to go in and add those as needed. There are two permissions in SQL Server needed to run Asp.net both are covered in the second thread and the first thread is a FAQ I created to help with moving SQL Server database from one computer to another. The permissions you give a user is at you discretion and that is covered under SQL Server object permissions in SQL Server BOL(books online). Hope this helps.

http://forums.asp.net/thread/1454694.aspx

http://forums.asp.net/thread/1492092.aspx

|||

Hi,

That's great thanks. The only problem is that my database is stored on a .mdf file and I cannot open it in MS SQL server management studio to change the database permissions. Is there a way around this?

Thanks

|||

That is not good practice so the SQL Server team have created a tutorial to help you connect your user instance to Management Studio when you do that you can go in and change the permissions. I would not advice you to give the everyone group full control but don't remove it from your Asp.net folders either because Asp.net is also a member of that group. Post again if you still have questions. Hope this helps.

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Database Performance Halts

Hi,

I have been running a reporting App on an SQL 2000 server, which reads from one large table (roughly 80 million records which grows at around 2 million records a week).

However, it would seem that when multi-users try to access the App and the one large table is Read from using 1 database user name up to approximately 25 times, the system would slow to a halt. Essentially, each request on the App would be a new connection to the SQL database using the same database user name. Recently, we have been running into performance issues since we have increased the number of users for the App.

What would be causing this slow down? and what could solve this problem?

>>What would be causing this slow down? and what could solve this problem?

Do you have indexes on the table? are the queries sargable? are your statistics up to date?

>>and the one large table is Read

What does this mean how is the table read? Are you just doing a select * from table?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

>>Do you have indexes on the table? are the queries sargable? are your statistics up to date?

I have indexes on the table and seems to work perfectly fine when there is only 1 query running. All the queries I am running are based on atleast 1 index on that table. Also which statistics are you referring to?

>>What does this mean how is the table read? Are you just doing a select * from table?

From what I can tell, it should only be a series of select statements or nested select statements that the report App runs and stores on the report App side. The reporting App is making from 10-30 of these requests on that 1 indexed table at a single moment in time.

Database Paging Options with 2M Rows

After seaching a few resources and seeing a few ideas for database paging
(e.g. returning a subset of result to a web app based upon some paging
algorithm on the web, I was wondering if anyone has used paging effectively
on a result set as large as 2 M rows? I have currently built a table with
all the columns possible for the web app we are using; built a clustered
index the most used field (e.g. date_received desc order); build nonclustered
indices on the other columns that could be sorted by in the app; and finally
a procedure that dynamically (using 'exec (' select ...') builds the query.
Where I see a performance issue with this type of sql is when I have a large
result set (50K rows or so) and need to get to page 100. Any immediate
thoughts that come to mind?
http://www.aspfaq.com/2120
http://www.aspfaq.com/
(Reverse address to reply.)
"Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> After seaching a few resources and seeing a few ideas for database paging
> (e.g. returning a subset of result to a web app based upon some paging
> algorithm on the web, I was wondering if anyone has used paging
effectively
> on a result set as large as 2 M rows? I have currently built a table
with
> all the columns possible for the web app we are using; built a clustered
> index the most used field (e.g. date_received desc order); build
nonclustered
> indices on the other columns that could be sorted by in the app; and
finally
> a procedure that dynamically (using 'exec (' select ...') builds the
query.
> Where I see a performance issue with this type of sql is when I have a
large
> result set (50K rows or so) and need to get to page 100. Any immediate
> thoughts that come to mind?
|||I did see your previous post with this link. I will test it out later and
reply with the repsonses. My situation is slightly different (as one would
expect) in that most of the query will need to be dynamically built.
More later and thanks for the reply.
"Aaron [SQL Server MVP]" wrote:

> http://www.aspfaq.com/2120
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
> message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> effectively
> with
> nonclustered
> finally
> query.
> large
>
>

Database Paging Options with 2M Rows

After seaching a few resources and seeing a few ideas for database paging
(e.g. returning a subset of result to a web app based upon some paging
algorithm on the web, I was wondering if anyone has used paging effectively
on a result set as large as 2 M rows? I have currently built a table with
all the columns possible for the web app we are using; built a clustered
index the most used field (e.g. date_received desc order); build nonclustere
d
indices on the other columns that could be sorted by in the app; and finally
a procedure that dynamically (using 'exec (' select ...') builds the query.
Where I see a performance issue with this type of sql is when I have a large
result set (50K rows or so) and need to get to page 100. Any immediate
thoughts that come to mind?http://www.aspfaq.com/2120
http://www.aspfaq.com/
(Reverse address to reply.)
"Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> After seaching a few resources and seeing a few ideas for database paging
> (e.g. returning a subset of result to a web app based upon some paging
> algorithm on the web, I was wondering if anyone has used paging
effectively
> on a result set as large as 2 M rows? I have currently built a table
with
> all the columns possible for the web app we are using; built a clustered
> index the most used field (e.g. date_received desc order); build
nonclustered
> indices on the other columns that could be sorted by in the app; and
finally
> a procedure that dynamically (using 'exec (' select ...') builds the
query.
> Where I see a performance issue with this type of sql is when I have a
large
> result set (50K rows or so) and need to get to page 100. Any immediate
> thoughts that come to mind?|||I did see your previous post with this link. I will test it out later and
reply with the repsonses. My situation is slightly different (as one would
expect) in that most of the query will need to be dynamically built.
More later and thanks for the reply.
"Aaron [SQL Server MVP]" wrote:

> http://www.aspfaq.com/2120
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
> message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> effectively
> with
> nonclustered
> finally
> query.
> large
>
>

Database Paging Options with 2M Rows

After seaching a few resources and seeing a few ideas for database paging
(e.g. returning a subset of result to a web app based upon some paging
algorithm on the web, I was wondering if anyone has used paging effectively
on a result set as large as 2 M rows? I have currently built a table with
all the columns possible for the web app we are using; built a clustered
index the most used field (e.g. date_received desc order); build nonclustered
indices on the other columns that could be sorted by in the app; and finally
a procedure that dynamically (using 'exec (' select ...') builds the query.
Where I see a performance issue with this type of sql is when I have a large
result set (50K rows or so) and need to get to page 100. Any immediate
thoughts that come to mind?http://www.aspfaq.com/2120
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> After seaching a few resources and seeing a few ideas for database paging
> (e.g. returning a subset of result to a web app based upon some paging
> algorithm on the web, I was wondering if anyone has used paging
effectively
> on a result set as large as 2 M rows? I have currently built a table
with
> all the columns possible for the web app we are using; built a clustered
> index the most used field (e.g. date_received desc order); build
nonclustered
> indices on the other columns that could be sorted by in the app; and
finally
> a procedure that dynamically (using 'exec (' select ...') builds the
query.
> Where I see a performance issue with this type of sql is when I have a
large
> result set (50K rows or so) and need to get to page 100. Any immediate
> thoughts that come to mind?|||I did see your previous post with this link. I will test it out later and
reply with the repsonses. My situation is slightly different (as one would
expect) in that most of the query will need to be dynamically built.
More later and thanks for the reply.
"Aaron [SQL Server MVP]" wrote:
> http://www.aspfaq.com/2120
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
> message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> > After seaching a few resources and seeing a few ideas for database paging
> > (e.g. returning a subset of result to a web app based upon some paging
> > algorithm on the web, I was wondering if anyone has used paging
> effectively
> > on a result set as large as 2 M rows? I have currently built a table
> with
> > all the columns possible for the web app we are using; built a clustered
> > index the most used field (e.g. date_received desc order); build
> nonclustered
> > indices on the other columns that could be sorted by in the app; and
> finally
> > a procedure that dynamically (using 'exec (' select ...') builds the
> query.
> > Where I see a performance issue with this type of sql is when I have a
> large
> > result set (50K rows or so) and need to get to page 100. Any immediate
> > thoughts that come to mind?
>
>