Bit of a headscratcher here.  I have a db that has less than 1GB of data, but a 40GB log file. Transaction logs are backed up daily, and there is not a lot of activity on this database; approximately once a week it records new payroll information and then regurgitates this data for reporting purposes. The database is set to Auto Shrink.  running sp_spaceused  @updateusage = true yields the following information:  database_name   database_size   unallocated space PayrollImports  39412.06 MB 105.00 MB  reserved    data    index_size  unused 321728 KB   278640 KB   42816 KB    272 KB   running DBCC shrinkfile (N'PayrollImports_log', 1 , notruncate) yields the following:  DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages 19  2   4991088 3456    4991088 3456   ...the discrepancy between the UsedPages and the EstimatedPages is mystifying, but I continue with DBCC shrinkfile (N'PayrollImports_log', 1 , truncateonly) and get:  DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages 19  2   4991088 3456    4991088 3456   Nothing has changed at this point.  The log file is still 40GB.  So I think, maybe I have some open transaction.  Running dbcc opentran should verify:  No active open transactions. DBCC execution completed. If DBCC printed error messages, contact your system administrator.   Crap.  Well maybe my indexes are fragmented.  I will defrag them with sp_msForEachTable 'DBCC indexdefrag([PayrollImports], ''?'')' and try to shrink again:  DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages 19  2   4991088 3456    4991088 3456   Still nothing has changed.  Ok, how about I reindex with sp_msForEachTable 'DBCC dbreindex([?])'?  DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator.   ...and now we get:   DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages 19  2   4991088 3456    4991088 3456   no change.  All right, how about sp_msForEachTable 'ALTER INDEX ALL ON [PayrollImports].[?] REBUILD WITH (FILLFACTOR = 10)'?  Immediately, this fails with:  Cannot find the object "(One of my tables)" because it does not exist or you do not have permissions.   Huh?  It's there, all right.  I do a select top 10 * from (My table) and it comes up empty.  Well, that's not right at all.  This is a lookup table that should have over 200 rows.  Is this a data corruption issue maybe?  I collect the data from my development environment, re-insert it in.  But I am out of ideas.  I cannot shrink this thing.  What else can I try?  Why are my UsedPages so incredibly higher than my EstimatedPages?  What is going on here?
2459
This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:  In SQL Server -  What are some reasons the transaction log grows so large? Why is my log file so big? What are some ways to prevent this problem from occurring? What do I do when I get myself on track with the underlying cause and want to put my transaction log file to a healthy size?
75662
This is a RHEL server, I'm running a MySQL server on it, the database and log files (however, logging is disabled) are located on the /srv directory where plenty of space is available.  Recently I had a crashed table, so I tried fixing it but on the next day I found that MySQL can't respond to many of queries with an error indicating that there is no disk space:  ERROR 1030 (HY000): Got error 28 from storage engine   So I ran the following command to see what's taking space  [root@tms /]# df -h Filesystem            Size  Used Avail Use% Mounted on /dev/mapper/server-slash                       9.9G  9.5G     0 100% / tmpfs                 7.8G     0  7.8G   0% /dev/shm /dev/sda1             485M   58M  402M  13% /boot /dev/mapper/server-var                       739G  252G  450G  36% /srv   Surprisingly it's the / directory. But more surprisingly is that the directories under / do not indicate used space more than 2 GB, while df shows total space 9.9 GB for /.  [root@tms /]# du -sh /* 7.5M    /bin 48M     /boot 200K    /dev 24M     /etc 4.0K    /home 223M    /lib 21M     /lib64 16K     /lost+found 4.0K    /media 4.0K    /mnt 183M    /opt ...deleted some file-not-found errors for files under /proc 0       /proc 144K    /root 14M     /sbin 4.0K    /selinux 252G    /srv 0       /sys 44K     /tmp 917M    /usr 259M    /var   So why does df show wrong values ? And how can I findout what's actually taking space ?
77837
Our production server (2012, VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads &amp; writes.  Seeing tons of SQL compilations/sec .. 95% of all batch requests are compilations.  Ideally would like to increase the RAM to 64GB or 128GB, but need to 'prove' to team that it's required.  Buffer Cache Hit Ratio (BCHR) is 99.9%, but Page Life Expectancy (PLE) is only ~400. What's the explanation for this ? I though PLE &amp; BCHR had a linear relationship (i.e. they increase or decrease together)  On other VMs with larger databases and lot more RAM, both BCHR &amp; PLE are high.  Current wait stats and perf counters
11117
I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming.  The list goes on... That being said, say I have a 100 GB database and I delete 50 GB of data -- not on one table, but a general pruning of old data on a database wide level, covering 90% of the tables -- does this constitute an appropriate use case for shrinking the database?  If not, what are the appropriate steps to take to clean house after removing such a high percentage of data from a database?  I can think of two: Rebuild Indexes and Update Stats. What else?
24825
I have inherited a client site that has an extremely large database for no reason. There is a moderate amount of content and very little enabled modules. However, the database is too large for moving around easily and I want to clean it out.  I have cleared out the standard cache tables, syslog and accesslog.  Are there any other tables I can safely truncate in a standard Drupal site?
102099
First thank you for your time. We have a SQL Server database that was assigned to be replicated. the original size of the database was 5 GB for the datafile, not the log file. Now after the replication the data file is 4 times bigger around 19 GB. Any pointers to see what could happened will be appreciated. I know sql as developer and some little management but nothing about replication. The performance of the database has been damaged very bad so I am trying to see what need to be done to improve it. I do apologize for my English as well.
83262
I was wondering what you do when developing a new application in terms of estimating database size.  E.g. I am planning to launch a website, and I am having a hard time estimating what size I could expect my database to grow. I don't expect you to tell me what size my database will be, but I'd like to know if there are general principles in estimating this.  E.g. When Jeff developed StackOverflow, he (presumably) guesstimated his database size and growth.  My dilemma is that I am going for a hosted solution for my web application (its about cost at this stage), and preferably don't want to shoot myself in the foot by not purchasing enough SQL Server space (they charge a premium for this).
16483
I am running sql 2008 r2 and the db was working fine and fast for last 3 years untill about 3 months ago we added ntext field on very active and used table.  Now we are starting to get out of server space because of the huge expanding size of this table.  I read that shrinking ,we do not want to loose the indexing of db because it was working fast for years and we do not want to get fragmentation expending.  We decided to delete that field and all its values: Is there a way to delete the ntext field and all its values and release space without removing indexing ,without shrinking, without loosing db performance?  I am attaching the db size query output to show you size expanding of last 5 months.
30677
We are a team of mathematicians (i.e., no DBA experience).  We have a large database in SQL Server 2012.  It has more than 2 TB of data (hundreds of tables, each with millions of rows and hundreds of columns wide).  Each month, we receive a bundle of additions and revisions to the data, requiring us to perform extensive updates to our database by deleting, replacing, or updating most or all of the tables.  Our work is mostly focused on crafting the SQL logic to calculate the results that we need.  We're not running a real-time call center.  We apply a few indexes as needed, and we're pretty happy with the performance.  The problem is the log file.  Naturally, the log file grows and grows with so much data manipulation.  Our log file currently stands at about 1 TB.  We have a good amount of disk space, but it is not infinite.  From what we read on the Internet, we understand that the log file is necessary for transaction integrity, rollbacks, and recovery.  But for our specific purposes, we don't care about any of that.  We will probably never perform a rollback, nor would we ever attempt a recovery.  Worse comes to worse, we would simply download the data files again and create a new database from scratch.  We really just want the log file to go away and never come back.  We set the database recovery mode to Simple, naively thinking that this meant "no recovery mode", but we were quickly cured of those illusions.  We also understand that there are many wrong things not to do (detaching, shrinking, etc.).  We just don't know the right thing to do.  Maybe someone is going to suggest that we set a limit to log file growth.  However, that leaves two issues: (1)  How do we get rid of the 1 TB which is already there? (2)  We previously tried that, and as we approached the specified limit we started getting Error 9002 (log file full) here, there, and everywhere.  So now we are afraid of applying a size limit.  How do we tell the database "No log file, please" without any hurt feelings?
20552
[Salutations]  (check one)  [ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer,   I have a (check all that apply)  [ ] query [ ] stored procedure [ ] database thing maybe     that was running fine (if applicable)   [ ] yesterday [ ] in recent memory [ ] at some point    but is suddenly slower now.  I've already checked to make sure it's not being blocked, and that it's not the victim of some long running maintenance task, report, or other out of band process.  What is the problem, what should I do, and what information can I provide to get some help?  [*Insert appropriate closing remarks*]
91887
I am trying to find the cause which have bloated the mdf of a database from 20 GB to 100 GB.  So far i tried checking the autogrow events to find time, but could not find none using standard reports and even default trace files.  We don't have 3rd party monitoring tools to confirm if any maintenance job like rebuild would have done or any other process.  How can i find what caused the growth on this mdf?
82037
I'm the only one working on this server and followed documentation about optimizing SQL memory usage when I saw that SQL was using all of the memory available of the server.  One of the steps I did is to fix the max memory size to 20 Gb (server for test purposes with 32 Gb memory).  After tuning, yesterday, SQL Server was only using 3-4 Gb but today, it has again reached the max memory usage I allocated.  Why is SQL Server keeping all 20 Gb? What will happen when users will begin to use the server if the max memory size is already used?
115589
I am a little out of my league with this scenario and hope this community can help get me on the right direction.  I have recently been tasked with re-designing and implementing a database application where the company I work for processes a large quantity of text files (sizes ranging from a 100MB to 3GB).  We currently process approximately 600 of these files per year.  This data needs to be accessible after our initial processing.  The current implementation has each file being imported via Application bulk load or SSIS to a "temp" database as individual tables.  I would like to keep this process in place as it gives a good entry point for when manual entry into processing is needed.  I am in the process of trying to create a new MS SQL Server installation and architecture, and am looking for advice/articles which would cover a similar scenario as to best practices (partitioning, filegroups, etc.).  I have worked in databases for a few years, but not at this level.  So if you have any experience in this type of scenario or know of a good article or book you could point me to, I would be extremely grateful.  I just need a nudge in the right direction.  Thank you in advance.
75479
I'm about to delete a large table which is approximately 100GB in size.  I'd like to understand the impact on the mirroring that's in place on this db.  Can anyone confirm?
118096
My company has recently exceeded our data storage cap (at 102% right now.. :/ ).  A large portion of the data that pushed us over our limit was due to an import of records.  The weird thing about this is that the records only had a couple of fields populated each out of the 200 total fields on the object.  What this tells me is that SalesForce holds the space for each field (since it knows the max-length of each field) rather than populating space only when the field is not null.  Does anyone know if this is the case?  To reiterate, and perhaps present this question more clearly:  If a field has a max character count of 1,000 and only 250 of those characters are actually being used, does SalesForce count the total data storage consumed by that field as 250 or 1,000?
98214
The tempdb of a server (SQL Server 2008) increases to 500GB+ several times every month. Is it possible to find out which SQL statements caused this problem? The problem is usually not caused by create table #temp...; insert into #temp... or select ... into #temp... but complex joins.  The Initial Size of some of the tempdb files is also automatically set to much bigger values every time. How to prevent it?  Sometime the cached plans prevent resizing/shrinking the files. How to find which one hold the tempdb?
70009
I ran into this error earlier today and requested that the extra logs be purged from the server. It was done but it still claims that there isn't any room. Is there a way to notify the server that it has more room than it thinks?