Hello masters of MS SQL   I have a kind of a problem on my hands. We have quite big DB (5TB) and we've just "enabled" some software archiving which means that most of the data will migrate to other DB's. In other words, this big DB is now almost empty with data and will not grow anymore.   The issue here is I have a big file that I need to shrink (space problem). And I wonder how should I approach this problem? Shrinking will probably take quite a while and I am not sure what will happen if something goes wrong and if it's OK to do backups in the middle of shrinking.  Can somebody help me with this issue and shine a little light on this matter?   This is current situation (data is still being deleted, and I assume we will land at 300~500 GB size)  Thank you in advance
30735
This question is asked in various forms here but the question boils down to:  I know shrinking a database is risky. In this case, I've removed so much data and I'll never use it again.   How can I shrink my database? What files do I shrink?  What should my considerations be while doing this?  Should I do anything after? What if it is a large database? Can I shrink it in smaller increments?
111355
My database was originally using filestreams in one table.  For reasons that I won't go into here, we decided to stop using filestreams.  I therefore wrote a migration to copy the filestreamed table into a new table that doesn't use filestream, I dropped the old table and renamed the new table.  All good so far.  Now I want to drop the filestream file and filegroup.  And SQL server won't let me:     The file 'MyFilestream' cannot be removed because it is not empty.   I ran the following query:  select * from sys.tables t  join sys.data_spaces ds on t.filestream_data_space_id = ds.data_space_id  -- and ds.name = 'MyFSGroup'   Even commenting out the ds.name filter, I get zero records back.  Like , I ran this query:     exec sp_helpfilegroup 'MyFSGroup'   and got back:  groupname    groupid    filecount ---------    -------    --------- MyFSGroup    2          0   I've Googled all over for solutions and workarounds.  So far as I can tell, there is no table anywhere in the database that is still using this file - but I still cannot drop it.  I have tried restarting the SQL service, too, but it's not to be fooled.  How do I find out what is still using the filestream file, and how do I drop it?  EDIT: ooh, here's a thought: the database has "Full" recovery model.  Could that have something to do with it?
10894
What's the difference between TRUNCATE and DELETE in SQL?  If your answer is platform specific, please indicate that.
37953
I've inherited a SQL Server 2008r2 database; the Data file was created with an initial size of 1,000 mb, but the Transaction Log has an initial size of 28,016 mb.  No backups were being performed.  I've implemented full and transaction backups to match our recovery needs, and as a result the transaction file is always ~99.9% empty, never exceeding 30 or 40 mb used.  I'd like to reduce the initial size so I can shrink the log down to a more apprpriate size, 200 mb or so.  Does anyone know how I can redefine the Log file with an Init size of 200 mb?
38385
We are planning to migrate to 100 TB of data from one of our client to new SQL Server environment. We just started receiving information from client. It will take couple of months to complete the project. But, can anyone please provide detail plan for migration such a large number of SQL Servers and data. New environment will have 4 fail-over cluster node at 4 different location.    Current environment: SQL Server 2008 R2 New environment : SQL Server 2014/16
1563
Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level?  For information on queries to index a field, check out .
119823
Close vote notice: While the linked duplicate asks same question, the comments and the only answer do not provide an answer which draws from authoritative source. The , does in fact provide the answer I've been seeking. Thus the proposed duplicate isn't helpful, however Thomas's answer here is proper.  According to the  and personal account , directories that have large amount of entries get to a size over 4096 bytes (which can be seen with ls -l output), but once the entries are removed - the number never goes down.   The question is "why" ? Is it due to how ext4 filesystem configured to retain directory metadata ? Obviously removing the directory and recreating it isn't a solution, since it deletes original inode and creates a new one. What can be done to decrease the number manually ?
117681
I have a large table (~50 million rows) I'm trying to bulk insert into SQL Server and I get the error:      Could not allocate space for object 'myDB' in database 'I 3 Stroke' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.   There is another table in the database with around 25 million rows.  This database will only be used on a single machine, and it will be designed to mine data that already exists and it under no circumstances will ever grow beyond its current size.    For a situation such as this, what's the best way to tackle this so SQL Server doesn't complain?  Will the solution matter that this DB won't be exposed to multiple users?
113447
At what point does a MySQL database start to lose performance?   Does physical database size matter? Do number of records matter? Is any performance degradation linear or exponential?   I have what I believe to be a large database, with roughly 15M records which take up almost 2GB. Based on these numbers, is there any incentive for me to clean the data out, or am I safe to allow it to continue scaling for a few more years?
28203
Hi I've stumbled into the administration of a Legacy SQL 2000 server that has had next to no maintenance for the past 8 years (and is very slow).  As I have only been doing DBA / PT for the past 2 years (I'm primarily a Developer) I don't have allot of experience on 2000.  Can anyone point me in the direction of some maintenance, tuning scripts.  Just figured I'd ask first before I reinvent the wheel for something I will rarely use.  Thanks Guys / Girls !
119022
We have a tree of about 15 snapshots of a virtual machine that runs Win2k8, as you may guessed our datastore is soon to run out of space. My objective is to delete all the snapshots, as it seems it was a huge mistake to use snapshots for backup purposes.  Now my question is how do we delete the snapshots so that the least amount of space on datastore is used for the merging process, since there is not much space left. Do we start deleting the tree from bottom up, ie. start with deleting the most recent snapshot and move up, or do we start deleting the oldest snapshots and move downward?
79149
Currently we got TEMPDB Data (number of data files:2) and LOG file on same drive.  On monitoring we have found I/O issues on TempDB.  We got an extra drive available - should we move the TempDB Log file to new drive or should we move the second TEMPDb data file on the new drive to help us reduce I/O issue?
28928
Being used to (and potentially spoiled by) MSSQL, I'm wondering how I can get at tables size in Oracle 10g. I have googled it so I'm now aware that I may not have as easy an option as sp_spaceused. Still the potential answers I got are most of the time outdated or don't work. Probably because I'm no DBA on the schema I'm working with.  Would anyone have solutions and or recommendations?
63057
When should I use the full recovery model and when should I use the simple recovery model for databases?  I always used the full recovery model because it is the default, but today i encountered this error:     Microsoft OLE DB Provider for SQL Server (0x80040E14) The transaction   log for database 'DATABASE NAME' is full. To find out why space in the   log cannot be reused, see the log_reuse_wait_desc column in   sys.databases   The specific database is actually one of the smallest and most inactive databases on my server, so I have no idea how the log can be full on this database, and not the others.  To shrink the log and making the database accessable again, I changed the recovery model from FULL to SIMPLE and shrinked the logical file log, with the following command  alter database myDbName SET recovery simple go dbcc shrinkfile('LOG FILE LOGICAL NAME', 100) go   It helped, but now I need to understand WHY it helped, HOW this situation started and HOW to prevent this in the future?  EDIT:  Every night at 1 o'clock, we are doing a scripted backup of every database on the server. This is being done by a 31 line script where the most important part is  set @Filename = 'D:\backup\' + convert(varchar, getDate(), 112) + ' - ' + @DBName + '.bak' set @Description = 'Full backup of database ' + @Filename BACKUP DATABASE @DBName TO DISK = @Filename WITH INIT , NOUNLOAD , NAME = @Description, NOSKIP , STATS = 10, NOFORMAT   Is the new recovery model and databaseshrink going to be a conflict with this script?  We are not doing any other kind of backup of the databases, and therefore not the transaction logs, should we?
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.
82165
I have always had problems with my windows directory's growing to a huge size.  It started with windows 95 then 98 then 2000 and xp.  Windows 7 however is absolutely huge.  The windows folder itself with out program files is 21.4 gigs...add the program files and other windows files and it is a whopping 32 gigs.  My question is why is it so large and what can I do about it.  At the rate this is going I will be out of space in now time.
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.