I have a client running RMS, since moving to SQL express his database size has jumped 2 from 2G to 4G in 8 months. Previiuosly it took 2 years to reach the 2G size. has anyone else experienced this rapid growth of their database?
View 5 Repliesdoes anyone know of tools that allow free format screen design, easily programmable data validation and prgramming for use with SQL. I am used to using 4GL languages and want to avoid low level programming. For example to fifind a record and display the details i am used to a line of code that would allow the data field to do the find on to be entered and the next line of code would say find fieldname using index name and the results displayed
please say yes !!!
Hello all.
Please help me shed light on the following problem:
I am deleting from 2 tables in a transaction. I experience a deadlock occasionally when deleting.
Here are the tables:
create table TABLE_1 (
ID integer not null,
TYPE_CD varchar(50) not null,
STATUS_ID integer not null,
NAME varchar(500) not null,
COMMENT varchar(500) null,
CREATED_DATE datetime not null,
CREATED_BY varchar(15) not null,
LASTMOD_DATE datetime null,
LASTMOD_BY varchar(15) null,
constraint PK_TABLE_1 primary key nonclustered (ID)
)
create table TABLE_2 (
TASK_ID integer not null,
TABLE_1_ID integer not null,
COMMENT varchar(500) null,
constraint PK_TABLE_2 primary key nonclustered (TASK_ID, TABLE_1_ID)
)
Table table_2 is an intersection table between table_1 and another lookup table (called task).
Whenever I delete from table_1 I need to delete from table_2 to maintain integrity.
The reason I do not have clustered indexes on the tables is b/c they start out empty (every day a housekeeping task deletes from them. Unfortunately I cannot change this...).
In my (web/jsp) application before I delete, I select from table_1 to get data I need to remember after the delete.
In order to avoid the "select for update problem", I use the "UPDLOCK" hint in the select like this:
select *
from table_1 WITH (UPDLOCK),
table_2 WITH (UPDLOCK)
where table_1.id = table_2.table_1_id "
and table_1.id = ?
and table_2.task_id = ?
I then issue my deletes in the following order:
delete from table_1
where task_id = ?
and table_1_id = ?
and then,
delete from table_1 where id = ?
The application sets the isolation level to READ COMMITTED.
The above 3 sql statements are executed as part of a transaction. Occasionally, when I drive the application, I get a deadlock. This happens intermittently. I have been able to reproduce the problem by attempting to delete records really fast, one after the other from the (web) front end.
When the deadlock occurs these are the locks that are held by the running processes:
Blocked SPID (10):
============
Object Lock Type Mode Status Owner Index Resource
app DB S GRANT Sess TABLE_2
app.dbo.TABLE_1 TAB IX GRANT Xact TABLE_2
app.dbo.TABLE_1 KEY U GRANT Xact PK_TABLE_2 (a400f609034c)
app.dbo.TABLE_1 RID U GRANT Xact TABLE_2 1:157:3
app.dbo.TABLE_1 PAG IU GRANT Xact PK_TABLE_2 1:443
app.dbo.TABLE_1 PAG IU GRANT Xact TABLE_2 1:157
app.dbo.TABLE_2 KEY S WAIT Xact PK_TABLE_2 (6501eda29ac1)
app.dbo.TABLE_2 KEY S GRANT Xact PK_TABLE_2 (ef007b1066ea)
app.dbo.TABLE_2 TAB IS GRANT Xact TABLE_2
app.dbo.TABLE_2 PAG IS GRANT Xact PK_TABLE_2 1:252
Blocking SPID (12):
=============
Object Lock Type Mode Status Owner Index Resource
app DB S GRANT Sess TABLE_2
app.dbo.TABLE_2 KEY X GRANT Xact PK_TABLE_2 (6501eda29ac1)
app.dbo.TABLE_2 RID X GRANT Xact TABLE_2 1:176:3
app.dbo.TABLE_2 TAB IX GRANT Xact TABLE_2
app.dbo.TABLE_2 PAG IX GRANT Xact PK_TABLE_2 1:252
app.dbo.TABLE_2 PAG IX GRANT Xact TABLE_2 1:176
I could not figure out what the problem is. Please help me with this.
Any help will be much appreciated.
Thanks in advance
Hi everyone,
I'm a beginner in SQL Server databases, my problem is this:
i'm making a database witch the frontend is an access project, the database has several stored procedures views and user functions (the normal..), but a few data, (only the experimental), last night i've noticed that the file grow from 22 MB to 89 MB, the objects are the same and also the data, the only diference was that i forgot to put in an event procedure code, the ADO method, "MoveNext", to update various records, and the loop was infinit.
Is it possible that SQL statments generated by ADO make the file grow so rapidly!?
If so how can i shrink it, because i've tried and and the results was 4%.
Can you help me!?
Thanks
I would like to know followings:
I want to see every day or weekly Database growth (%) save on table
I have some SP which will give me one time run and see the growth. which is ...
Please advice any other way to find out and save on a location ...
create procedure sp_growth as
set ansi_warnings off
declare @l_db_name varchar(50)
,@l_sql_string varchar(1000)
set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth
create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases
open db_name_cursor
fetch next from db_name_cursor into
@l_db_name
While (@@fetch_status = 0)
begin
select @l_sql_string = 'select ' + '''' + @l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@l_sql_string)
fetch next from db_name_cursor into
@l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return
GO
Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/
I would like to know followings:
I want to see every day or weekly Database growth (%) save on table
I have some SP which will give me one time run and see the growth. which is ...
Please advice any other way to find out and save on a location ...
create procedure sp_growth as
set ansi_warnings off
declare @l_db_name varchar(50)
,@l_sql_string varchar(1000)
set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth
create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases
open db_name_cursor
fetch next from db_name_cursor into
@l_db_name
While (@@fetch_status = 0)
begin
select @l_sql_string = 'select ' + '''' + @l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@l_sql_string)
fetch next from db_name_cursor into
@l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return
GO
Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/
I would like to know followings:
I want to see every day or weekly Database growth (%) save on table
I have some SP which will give me one time run and see the growth. which is ...
Please advice any other way to find out and save on a location ...
create procedure sp_growth as
set ansi_warnings off
declare @l_db_name varchar(50)
,@l_sql_string varchar(1000)
set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth
create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases
open db_name_cursor
fetch next from db_name_cursor into
@l_db_name
While (@@fetch_status = 0)
begin
select @l_sql_string = 'select ' + '''' + @l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@l_sql_string)
fetch next from db_name_cursor into
@l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return
GO
Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/
I've got a question about the automatic database growth feature of V7. Here's an example:
I have a 1gb db that can grow to max size of 2gb.
I set the auto grow option to 75%
The first time the db grows it will grab 75% of the free space (1gb)
What happens if the database needs to grow again?
Will the db grow using the remaining free space (25%) or
has the database reached its max size because it can't grow any further?
Thanks
:eek: I am somewhat confused -- I have a database in production that I restored to a QA environment; upon restore, the size has grown by 200MB.
Both production and QA are running SQL2000 -- the only difference is that QA has the latest security hotfixes installed -- version 8.0.0.665 from KB article at the following link:
Q316333 (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316333&id=Q316333)
Hi everyone.
I am trying to find a way to calculate everyday my DB Growth, I did find a script on some site but it seems to give me the same information as the taskpad wich is not very specific. Basicaly i would like to know the size of a table in MB or in whatevever conversion possible, so that i will be able to do some forcasting.
Any help here would be greatly apreciated.
Hello,I need to monitor every 15 minutes growth in data file and log file .Since mdf and intial file sizes are set to high value,measuring these values at 15 min interval will not provide the changein size .My intention is to measure the log file size growth which helps tocalculate the disk space and bandwidth required to setup log shipping .We need to set up this infrastructure based on this calculationThanksM A Srinivas
View 6 Replies View RelatedCan someone point me to examples of database & file growth monitoring.
I specificially want to monitor a number of separate SQL servers (2000, 7.0)
I want to end of with statistics of any size changes on any of these over time.
Help is greatly appericated..
thanks
My DB size was from 500MB to 10GB since 8/1998 to 12/2004. But now is 16GB (from 1/2005 - 5/2005), I don't why the data size growth too fast (as double) ?
View 4 Replies View RelatedAt approximately what db size is sql 6.5 known to degrade in performance?
Also, what is the maximum db size 6.5 can handle?
thanks in advance...
SQL Server 2000 | Transactional Replication
Suspected Problem: Distribution Database Transaction Log Not Checkpointing
I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.
I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.
SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.
Any thoughts?
Jeff
Hopefully I'm posting in the right area. There is a database that has grown to about
41-42 GB in size in about a 2 month period. The previous database had grown to about
22 GB before it was purged out. I'm running this on SQL 2000, and I've tried running all
the DBCC SHINKFILE and SHRINKDATABASE commands to no avail. In this case,
the MDF file is the one that has grown out of control as opposed to the log file (LDF file).
Does anyone have any suggestions on what could be done to control the size?
Hi All,
Database size is not increasing automatically ,however I have set it as unlimited growth. Any idea about this ?
thanks for in advance,
Sedat Duztas
Probil
I need to monitor my database growth, as few of databases are growing rapidly. My client wants the growth list of my databases. have report of database growth of specific databases, at least of one month.
View 3 Replies View RelatedI currently have a DB that is growing at a rate of 10gb per month. It is set to 1mb unrestricted growth and the log file is set to 400mb restricted growth. I take regular transaction log backups so the log file is well under under without any issue. This DB's recovery model is set to FULL as it has to be mirrored to a backup site. Any recommendations on how to control the growth. - Is it advisable to take create a new DB with data older than 2 years and transfer that file to an external drive and if i do this, can i "attach" it back to the main server if and when required ?
View 7 Replies View RelatedWanted to do the forecasting of disk growth for one year. How to find the database growth rate?
View 4 Replies View Related
I'm currently using SQL Server 2005. Before I have set my database on unrestricted auto growth. But today, I have noticed that the Log file has been set to limit its growth to 2,097,152 MB. I have 160GB space for my log files, I just want to maximize the space for logs in my hard drive.
When I try to change the settings back to auto growth it still keeps on returning to its previous setting it is still set on 2,097,152 MB. What I did was :
Right Click on the Database - Properties - Files - Click the (...) - set the auto growth option to unrestricted - Click Ok
But when I checked log file, it is still set on 2,097,152MB.
Can some one help me change the settings of my Database.
Can anyone tell me why my SQL2000 database has grown aprox 15 % and my Log file 20,000 % when I attach it to SQL2005 .I've Thousands of Databases to Upgrade, but with the log file increasing to more than the size of the Database Its going to be a struggle !
It also takes a fair ammount of time to attach,
I suspect there is some reindexing going on , as when I try to reattach to SQL 2000 I get index errors ?
Is the re anything I can do in advance to reduce the database growth ?
I know I can truncate the log afterward but the peak diskspace consumed during my Migration may be an issue !
Thanks for any help
I am only DBA in my company and client want to know the growth rate of his SQL server DataBase which is in production. How can I get the growth rate per day?
View 3 Replies View Related
Hi all,
I'm trying to get an understanding of a serious problem I have with a large DB in production. This is going to be obvious to someone (everyone probably) <bg>
I have a table which consists of numerous varchars and ints but also a Text type field. This table resides in a SQL 2000 Database. This DB currently has a data file size of 16Gb and a Transaction Log size of 17Gb. When I edit the table and increase the size of a Varchar field from 50 to 100 these files grow to more than double their size!
Why is this happening and how can I prevent this?
TIA
NozFx
Is there any automated script available to - "Monitor Database Growth and if any DB is grown by 20%, sending mail alerts"? If not, what is the approach to write the T SQL script ?
View 5 Replies View RelatedWhat is the recommended size and file growth for a database and log file? We will be storing approx 10000 records a day.Currently we have the following:
CREATE DATABASE Dummy
ON
PRIMARY
( NAME = Dummy_data,
FILENAME = 'D:....DATADummy.mdf',
SIZE = 250MB,
FILEGROWTH = 25MB )
LOG ON
( NAME = Dummy_log,
FILENAME = 'D:....DATADummy_log.ldf',
SIZE = 50MB,
FILEGROWTH = 5MB ) ;
GO
When I downloaded/started using Visual Web Developer I was under the impression that I needed to install SQL Server Management Studio Express in order to create/manage databases, and to provide the engine to access the data.
Since then I have found tutorials and have successfully created/used databases solely from within Visual Web Developer. I'm assuming that Visual Web Developer includes a database engine, much like the webserver that is included. (This is an awesome thing).
When I tried to upload my web application with database to my production server, the database would not work, it started working after I installed SQL Server Management Studio Express on the server.
Is it my understanding that you need SQL Server Management Studio Express if you do not have Visual Web Developer Express installed in order to provide the data access engine?
Also, I am unable to "attach" my Visual Web Developer Express created database to SQL Server Management Studio Express. Are there any posts that provide more information about this topic?
The only reason I'm asking is that I have extra whitespace on the end of my text fields, and I thought ANSI_PADDING was turned on. I do not see the option in Visual Web Developer Express, but have found it in SQL Server Management Studio Express.
Hi
I have VC++ express and MSSQL SMS express and have an application working nicely locally. The Data explorer and data connections part works really easily.
Now, I want to make the application available to my home network.
I mapped the drive where the database is and called it Z: so I could put my "release" on my other network PC and assumed it would find Z: if I mapped the shared network drive on that machine and called it Z:
But: I can't even add the mapped connection on the local machine, I get:
The file "Z:databasescalorie.mdf" is on a network path not supported for database files. An attempt to attach.....etc"
It works fine on the original F drive.......
Am I approaching this the wrong way. How should I distribute to network PCs?
thanks hopefully
David
Hi,
I am a newbie so i apologies beforehand for any mistakes i make on this forum.
Anyway, i created an asp.net website using the MS Visual Web developer tool. This has a couple of SQL SERVER databases within it.
I then latervinstalled the SQL SERVER management studio express in order to manage the SQL SERVER DBs that i had as part of my website but they do not appear within the management studio db list.
As this is the express version, is there anyway of importing the existing SQL SERVER dbs that i have to the management studio so i can manage them from there?
Regards,
Shuja.
I get an error dialog when I try to create a new SQL database, both via the Add New Item dialog and the property wizard of a new SqlDataSource control. The error is:
Local Database File:
User does not have permission to perform this action.
I've searched for help with this.
I ensured the App_Data folder exists and I added the local ASP.NET account to the group that have R/W access to it (although the RO flag is in an unchangeable tri-state on the folder).
The SQL Server Express error log is clean and indicates full functionality.
Everything is running locally.
No VWD installation errors.
Any ideas?
Thank you!
Here is the error message I get:http://img109.imageshack.us/my.php?image=error2ug.jpgI have just installed both packages and havent done much in the way of configuration... What do i need to do? Thanks!
View 2 Replies View Related
Okay, I've read massive topics on ClickOnce, and embedded database applications, etc, etc.
I need a handful of good suggestions how I can create a deployment package, to get my sql express database onto a SQL Express server.
The database will need to be multi-user, because I am also developing a WinForms application to connect to the SQL Express database.
I'm thinking I need to use some automated scripting features, to generate scripts for the database once I am done (unless you have a better suggestion).
After that, I need to know what to do with those scripts, and how can I create a setup / deployment packages to run those scripts against a SQL Express server.
The other alterntative obviously is to copy my sql express database, and run an attach command. This will work the first time, only because this will have been the first deployment of this database.
Please keep in mind when answering this question, that I will not be embedding the database, and as far as I understand ClickOnce is a feature for use with embedded databases or user instance databases (not sure).
Daniel Crane
Hi, I'm completely new to SQL Server and ASP.NET, so please bear with me on my learning curve! I've installed Visual Web Developer Express and SQL Server Express (on the same PC). I then used Web Developer Express to successfully create a new database with a couple of tables in it. So far so good. I then installed SQL Server Management Studio Express on the same PC and tried to connect to my new database. The connection to the SQL Server instance appears to work OK (using Windows Authentication) but I can't see the database I previously created. All I can see are the System Databases. Does anyone know what the solution might be?
View 4 Replies View Related