Why Is My Database Running Out Of Space Already!! (urgent)
Aug 28, 2001
I have a question on database size, specifically, what difference would I expect in the size of a db (the space actually used for data, not indexes) if I have one single data device vs. multiple data devices spanned across different physical drives. I have 2 customers that have identical database tables (all columns are integers), customer A has 27 million rows and customer B has 36 million rows. If I do an sp_spaceused on the table, customer A's data takes up 9.7 gig, whereas customer B only takes up 3.2 gig, even though it has 9 million more rows. Statistics have been updated, so I'm confident the sp_spaceused is accurate on both databases.
The only differences I can see between the 2 databases is that customer B has 5 separate data files (6 gig each) for the database which are part of the Primary filegroup. These files are all on the D: drive, which is a partition on a RAID 5 array. Customer A has a single large data file (24 gig), which is also on a RAID 5 array.
In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.
What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.
I am trying to insert 2 fields of about 9 million records into a table and i keep getting this message
Server: Msg 1101, Level 17, State 10, Line 1 Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth. Server: Msg 1101, Level 17, State 1, Line 1 Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.
I have unrestricted file growth and over 200 mb of space left, what else can i do?
We've got an internal database that replicates with another database server for our website.
Not all tables are replicated, some use merge and the others are snapshot based and published regularly to the public website facing server.
However, there's a lot of data (well, large textual data) that's being transferred and it seems to be generating massive log files that continue to grow and grow.
I'm fairly new to adminning an SQL Server box, so was wondering if anyone can tell me what the best way to keep it under control is? I've heard its possible to truncate the logs, effectively deleting any data that has already been processed by subscribing servers etc.?
As I said, I'm very much new to this and would really appreciate some guidance, if only to the right part of the SQL Server Books Online :)
Hi, we have a SqlServer instance in production containing around 10databases.It has just been realised that all the db's are held on the small c:partition with only a gig or so of space left.On the server there is another partition and another hard disk bothwill ample space (few hundred gigs).What would be the best way of getting the data onto the otherpartitions with minimal impact on the applications. Can we move theprimary data files for each db? Should we just create secondary datafiles on the big partitions for all the db's? Is there a method ofmoving all the data at once?Any ideas on how we should approach this?(ps we dont have control of the sqlserver its outsourced, so simplerthe better).Thanks,Jim
I am currently SQL admin since our ordinary SQL folks are at summer vacation.
Yesterday SCOM alerted that File Group is running out of space on one not so critical database "The file group "PRIMARY" for the database "loganalys" in SQL instance "MSSQLSERVER" on computer "sqlserver2" is running out of space".
I logged in to the SQL server and checked the database in question. It is a very large DB with a size of 577 GB.The storage on which the database files resides has 123 GB free space so that isn't the issue.
The database is set to autogrowth 1 MB at a time (unrestricted) so that doesn't seem to be the issue either.However, in the database properties under General, it says Space avaliable: 570 MB.
I guess that this may be the issue, that this is under some treshold for SCOM to alert on.I have looked at the other databases and they have everything between 0,25MB to 270MB space avaliable (they are all set to autogrowth 1 MB, unrestricted).
I am not sure what this means and if I have to do something about it?
I have a database with around 2 GB space for the data and 5.8 GB for the transaction log . Now the problem is i do not have any more space on the system and data files requires more space than 2 G.B to execute some stored procedures .
Is it possible to decrease some space of the transaction log , say from 5.8 GB to 2 GB and allocate it to the data files . My data and log files are on different drives . I did not find anything related to this topic in the BOL .
Can somebody help me with this problem ? Anthing related to this issue will be of great help to me since i have no expertise in this field .
My tempdb is eating 1.27 gb of space of my d dive, and now only 10 mb is left in d drive . for this i stoped and started sqlserver but it didnt release much of space.Pls let me know is there any other way so that i can release some space from my tempdb.
We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.
Error: The file group "PRIMARY" for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space. tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB templog Initial Size : 5,475 MB Growth: By 10 percent, Unlimited
Hi, In sql 7.0 , i would like to create a database with the size of 10Gb, in my server couple of databases already exist. How do i know how much free space is there in File group. we are having only one file group i.e PRIMARY. Could anyone pls tell me about this. Thank u.
We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.
Error:
The file group "PRIMARY" for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space.
tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB templog Initial Size : 5,475 MB Growth: By 10 percent, Unlimited
Hello everyone, In reports ,My customer requirement is to display column based on selected criteria in UI . The columns which are not selected by him will hide. for that we kept an expression in Visibility --> Hide
Code Snippet = NOT Parameters!Parameters.Value.ToString().Contains("Name")
then coming to HTML Report It is working fine,but white space coming at end of the Table. can't we supress the white space? The white space width is exactly the width of the column which is hidden. My designing in layout is wrong? Else is that Problem with the SSRS? Experts Please let me Know!!!! Give me Solution!!! Customer is strictly focusing on that requirement.
***Note: white Space is Some what Acceptable.But My Reports are very big like 45 columns around.When he selects 10 out of 45 then you can assume how much space is coming????????****
One of our databases seems to be looking dodgy as some scheduled jobs are failing, but DBCC CHECKDB is no use since it has been running for over 1/2 hour without giving me any results, just the spinning globe.
How do I find out what is wrong without resorting to backups?
I need to run stored procedures which are currently run on server A(located in another city),by connecting myself to it through my laptop running on SQL server 7.0(only client components installed)on NT workstaion4.0. Can anyone tell all the steps involved to run those stored procedures everyday.Even I need to monitor Server A from my laptop.Please advice...Urgent!!
hi, my Backup job, took a backup at 3.00am , backup went well, but this job is still running , now the time is 10.00am. I tried to kill this job but it is still running. Why my process still running after taking a backup also. Can anyone suggest me, how i have to resolve this.
I have SQLServer 6.5 SP5a update running on Windows NT 4.0 SP6 with 4 gig RAM and 4 processor.
Suddenly the SQL 6.5 jobs running on the production server started running very very slow. A job that suppose to run in 30 minutes are running like 2 hours and completing successfully.
(I suspect the after the Norton Anti virus automatic live update may be the reason but not the Second Vulnerability as mentioned by Microsoft Bulletin last week)
I check the SQLServer, ran the performance monitor, checked pagefiles, disk space, databases,memory, tempdb. Everything seems to be normal.
I rebooted the server, checked any other process making that slow. But no use.
Please help me out with this issue as this is a production and the CRM applications from the clients uses the database server.
When i Run the report in reporting services, it locks the tables. so is there any option to Unlock the tables. I m using just select query to run the report but when i run the report it locks the tables.
I used with(nolock) option in select query but it didnt work...still showing me lock on the tables.
We have a database with 20 gig and with huge transactions. The transaction log backup is scheduled every one hour from 3.00 AM to 9.00 PM.
We take a full backup in the disk at 9.00 PM and again a full backup in the tape at 2.00 AM
It works fine in the day from 6.00 AM and complete within seconds and the size is approx. 50 to 200 MB.
But the very first transaction log backup at 3.00 AM is running like 3 hrs and the size is approx. 11 gig whick is almost equivalent to the Full backup size. There are some dts packages that are running in the night and as usual reindex, intergrity checks are running and there no large user traffic during night. But I have no idea which the very first transaction log backup in the morning takes longer time and has this bug size. Is there any work around to fix this proble.
Hi, I work on SQL Server 6.5, and I want to make a documentation about available space of my databases. So, I must know how many databases space used everyday ?
If somebody know how to get this available space or how to make this script. I hope you want to tell me about this. Thanks you so much for your cooperation.
hiiii would appreaciate if somebody can help me to figure how can I monitor my database space used, available bu using Transact SQL through SQL Analyzer...
When i try to check the space used in one of the databases in my server I don't get the correct statistics on the bar graph. The statistics show that the database size & the space used are same.It is not the same for all the databases. The database size is 17500MB.I did increase the database size to 18000MB but the situation has not improved. I tried running the sp_space used and i get the following output:
database_name database_size unallocated space ------------------------------ ------------------ ------------------ VSI 18000.00 MB -3553.26 MB
Is anyone knows how to get the space used for both the data and the log of a database using the system tables ? I was able to get the database size but don't know how to compute for the space used.
My sql database is showing space available for one of the database is 0. I have got SQL Server 2005 Ent. Edition(32bit). File growth for .mdf file is set to Autogrowth 10% growth Unrestricted.
I have got lots of physical space on hard drive, why then it is showing 0.00 space available for database. What do i need to do to free up space.
When i ran sp_spaceused it gave me following results
database_name database_size unallocated space XXXX 1817.31 MB 0.00 MB
What is the best suggestion to do if a database is using more than 95% of its space. Should I leave it as is and it will grew when it needs? Or do an action. What action?
One of my user database has grown to almost 50 GB and most of the space is used by these 2 user tables. The number of rows has in each table is around 5500, but the reservered is very high. What can be done to reduce the space so that my database growth can be brought down.
Database properties showing space available for database is 0.00mb physical available space on HDD is around 32 GB... Db size is 5gb...but im bit worried about the size what is being displayed.... Settings in my database
General 1) Size of db: 14306.45MB 2) Space available: 0.00MB
Data Files 1) Automatically grow file 2) File growth By Percent: 10 3) Maximum file size: Unlimited file growth
Transaction log 1) Automatically grow file 2) File growth By Percent: 10 3) Maximum file size: Unlimited file growth
Options 1) AutoShrink I'm worried about this "space available: 0.00MB". What kind of impact will it create? If I set all settings to unlimited file growth, does it mean my database can grow as big as my physical harddisk space allows? Does this also means I do not have to worry about database allocation?
