I am not an SQL guru by any stretch but I am trying to understand the differance bewteen the size of the data contained in the tables and the physical size of the .MDF file.
Currently I have a website for a client with 27 megs of Physical data stored in the tables but a Data file size of 334 MB set a 3% autogrow. I have my Log capped at 5mb, but I can't seem to contain the Data file. My database just keeps growing and growing every 2-3 days with very little new data being inserted.
Why is there such an enourmous differance bewteen the size of data stored in the tables and the size of the data file and is there anything I can do to fix it?
Hi, i use this script that show me the size of each table and do the sum of all the table size.
SELECT X.[name], REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows], REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved], REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data], REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size], REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused] FROM (SELECT CAST(object_name(id) AS varchar(50)) AS [name], SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows], SUM(CONVERT(bigint, reserved)) * 8 AS reserved, SUM(CONVERT(bigint, dpages)) * 8 AS data, SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size, SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused FROM sysindexes WITH (NOLOCK) WHERE sysindexes.indid IN (0, 1, 255) AND sysindexes.id > 100 AND object_name(sysindexes.id) <> 'dtproperties' GROUP BY sysindexes.id WITH ROLLUP) AS X ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup. example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Hi, I am using exec sp_helpdb go dbcc sqlperf(logspace) for getting database size and log size. Is this gives the correct database size and log size or Is there any other way to get the logsize and database size by means of query analyzer.
I have installed 2 SQL Server 2005 Express sample databases from 2 books, ASPnet 2.0 and ADOnet 2.0. The ASPNETDB.MDF was shown in App_Data and Database Explorer, but not in the SQL Server Management Studio Express. The AdoStepBy Step database created by a ConfigDB.exe was displayed in the Management Studio, but not in the App_Data, or Database Explorer.
Is this the way SQL Server 2005 runs the 2005 databases for SQL Server 2005 Express only? Or also in SQL Server 2005?
In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error:Â
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Is there a way to set permissions for the contained user so that this could be done?
I'm getting this error while trying to insert records into a SQL Server Compact Edition database. I have pasted my connection string that was used when creating the database as well as for accessing that same database from my Windows application.
Thanks for any help any of you can give!
Data Source=OnTheGo.sdf;Encrypt Database=True;Password=<password>;Max Database Size=4091
I am developing a smart device application with Visual Studio .Net 2005 and SQL Server Compact Edition database. And also using merge replication to synchronize the data from the mobile device to the SQL Server.
My database size is around 350MB. So when I am trying to synchronize this is the error message that I get. " The database file is larger than the configured maximum database size. The setting takes effect on the first concurrent database connection only.[Required Max Database size ( in MB; 0 if unknown)=129].
I tried changing the Max database size in the connection string and my connection string looks as follows and still did not have any luck.
I have looked everywhere I can think of for the answer to this and am completely confused. My IT dept created an SQL database for me on a server. My plan is to create web pages in asp.net using Visual Web Dev Express released this week. I have connected to the database but I can't figure out how I am supposed to create my tables in the database. What simple/obvious thing am I missing here? I keep trying to download SQL Server Express in hopes that I can use that to manage my database but it keeps telling me that it won't install because I have previous Beta versions left on my machine. I can't find those either so am out of luck. I am new to this so perhaps I am asking all the wrong questions.Does anyone know what I can do to find a useful database management tool that will allow me to add/create the tables?Thanks for any help!
Hi, I need support for the following problem. It is a teamwork.Suppose 25 databse developer developing a databse.every one make modification (update,delete,insert)to the centralized database.I want that every one its on database copy and make modification to that instead to centerlized database.As source control softwares do(e.g visual source safe).or any other way to manage database in such senerio. Hope any one will help. Thanx in advance.
Hi to All, I am working in vb and C# .net as front hand and use mySql or Squel server as back-hand. task is, i did here it that, we have more than one table, each and every is related by using the concept of Primary and Foreign key management. in that database if i remove a record from master table, than the related record of all child talbes shall auto-matically reoved that is my question how it does happen. first thing that you have understood my problem if yes than please if you have any idea, mail me. i will very much thnks in advance to you. bye regards Rajat.
I just realized recently that a database "XYZ" in the Object Explorer of my SQL Server Management Studio Express (SSMSE) is put in the Database Explorer of my VB 2005 Express for processing a Stored Procedure in executing the SELECT statements (not by using Input and/or Output Parameters) during the ADO.NET 2.0-VB 2005 Express programming, then the content of the database "XYZ" is not in the SSMSE. How can I return the database "XYZ" from the DataBase Explorer of VB 2005 Express back to the Object Explorer of SQL Server Management Studio Express (SSMSE) safely? Please help and advise.
hi my database on remote server i cannot access directly. i can access it only with query analyzer. my log file size is 9mb but nothing in database. only few tables there so how i can reduce log file size with query.
We have a SQL server database most time for read only because the data source comes from another database( which is not SQL server database, now it is about 2G). Every day we have a job running as following: Step 0. extra the data from another database, and create plain text files for each table for BCP job. Step 1. drop all of the index Step 2. truncate all of the tables. Step 3. BCP in all of the data from plain text file. Step 4. create all of the index again Step 5. shirnk the database.
Everything runs fine but the database grows 1 G from yesterday's database. I am sure we do not have so much data entry in one day. Any one can give some suggestion? I wonder if I need do shirnk the database or shirnk the data file before create the index. How can know how much size for all of the index file? Thank you very much. Judy
I am a beginner with sql and I have been inputting data (txt files) to my database and now have approached 4GB in size and it will not let me expand any further on the primary file group. My 'boss' said that sql has no size. I am using desktop
Is there a stored procedure that returns the current size of a database, and the maximum size of a database whose growth property IS NOT set to unlimited?
When I run sp_spaceused on my database it give me a total size of 258MB The backup of this database reads 226MB The Properties screen tells me that my data is a total of 278MB I am running SQL 7.0 . Why am I getting different sizes?
I have a simple question, why Am I running out of space with my current 3.5GB database, I am constantly expanding it, yet when I dump the database nightly the dump is just around 1.5GB?
Newbie here ....I started with a datbase of 90 megs...it now grew to 300 megs in 2 months...what is the best way to see the largest table or to find out why this database grew so fast? Thanks!
Hi.. My database data file are over 2.1 Gbs now. can anyone tell me is my data file too big?? or not safe?? or there's a limite for MSSQL 2000. my last choice are move some data to another database. but i have consider the report i have to make. so i really dont want to do that. can anyone advice. thanks
Hi, I have 30 servers and about 600 databases. I want to know the size of databases ( data files as will log files) both used and allocated space. Can any one tell me the easiest way to get this information?
Hi all, We are using sql6.5. srvpack 3. I noticed that one of our database was maxed out, and increased the size of the device and then increased the size of the database. when I checked the database it is still showing the database is maxed out. I did it twice, it still shows that the space has been used. Can anyone please explain to me why this is happening and how to get the actual space measurement. Thanks in advance.
After we ran sp_spaceused on one database, the size for the UNUSED column is a negative number -1324KB. Can someone explain how this could happen? I really appreciate.
I need to check the log size of my database periodically and send a notification if the size exceeds 8 GB. I want to set this up as job that runs every 30 minutes or so and page the DBA if it exceeds the above said limit. Can anyone give me the command which can be used to obtain the logsize into a variable. I am new to SQL and would appreciate your expert advise.
I want how can i do to do capacity planning in sqlserver 2000. Do you make scripts (vbs) who query the sysfiles tables and transfer the answer in excel ? Please help me. Phil
I have a database that is almost 1.55 GB in size according to the database properties window.
However I checked the size of all the tables and indexes using the stored procedure 'sp_spaceused' and the sysindexes table and the total size of all tables and indexes is only 162 Mb.
Does anyone know why the database is so big, and how I may be able to reduce the size of it?
