There is db space challenge at hand on Azure SQL Server database. 490 GB out of 500 GB is used. That's what I read from nice pie chart on Azure portal. Notice that some of the techniques used on-prem won't work for Azure SQL Server Database.  Biggest table is DidbStudySerumLevelDeviation.  exec sp_spaceused 'RulesEngine.DidbStudySerumLevelDeviation '  returns  +------------+------------------------------+ | name       | DidbStudySerumLevelDeviation | | rows       | 20 805 409                   | | reserved   | 133 332 840 KB               | | data       | 123 079 792 KB               | | index_size | 9 772 192 KB                 | | unused     | 480 856 KB                   | +------------+------------------------------+   Table definition:   CREATE TABLE [RulesEngine].[DidbStudySerumLevelDeviation](     [Id] [uniqueidentifier] NOT NULL,     [PatientTherapyId] [uniqueidentifier] NOT NULL,     ...     [Abstract] [nvarchar](max) NULL,     ...  CONSTRAINT [PK_RulesEngine.DidbStudySerumLevelDeviation] PRIMARY KEY CLUSTERED  (     [Id] ASC,     [PatientTherapyId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) )  Clustered index exists on primary key. There are also some nonclustered indexes. Abstract column data takes around 90% of space of a table.   Procedure is run to set unnecessary Abstract column data to null. That should reduce problematic column "weight" to 10%. However this doesn't reduce database size. How to reclaim that database space?  Maybe I should wait and some Azure SLQ maintenance magic occurs? It sounds like wishfull thinking though.  Regarding possible duplicate  Answer to this question might me specific to Azure deployment of SQL Server. Drop in database size reported in Azure portal or read from exec sp_spaceused didn't change.
121328
We have a database with a large amount of data stored in a field of type varbinary(max). At some point we can purge that data for most rows, but not all. Our plan is to make that field nullable, and simply null out the data when it is no longer needed. Once we do that we would like to reduce the size of the DB. What is the best way to accomplish this?   If there isn't a good way to reclaim space with the current setup, one idea I have is to move that data field to a separate table with just two columns: the key to the main table and the data field. Then we could simply delete the rows when they are no longer needed. (And then do some sort of shrink.) However, this would be a much more difficult change to make than simply making the existing field nullable.  Note: I actually don't care so much about making the database file smaller, but I do care about the newly freed up space becoming re-usable.  Over 90% of the DB size is this one field. I'm at 3TB already.
78231
I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have:  MyDatabase.mdf: 24.8GB MyDatabase.ldf: 114.6GB  This database is backed up to a .bak file every night and shipped over to our reporting server, where it's restored. The .bak file is much smaller at only 1.8GB.  However, when I try to restore it on the reporting server, it fails due to insufficient space. There are around 100GB free on the server, and it's trying to eat up the entire 139.4GB that the files consumed on the original server. Unless my knowledge of compression is horrifically wrong, I'm fairly confident that the 1.8GB file is not actually expanding by 7400%.  My Question: Is there any way to tell SQL Server to restore this backup file without reserving that space upfront? I do not care about any of the logs; I just need the data to be there. I understand databases from the development and schema perspective, but I am by no means any sort of DBA.  This is on SQL Server 2008 R2. Thanks for any help or suggestions.
121386
I have big SQL Server database, with a lot of blob data in some tables.  For performance optimization, I want to move one table to separate hard drive.  I created new filegroup, and "row data" file.  But, I can't change any parameters in table properties: they are all disabled.  And I don't know a way to enable it.  Can anybody help me?
87554
We have 2 servers:  Server A. production (no partitions) Server B. New Server (partitions and compression)  We restored a backup to the new server, partitioned a large table by datetime and turned on compression, and we were happy. Trying to be clever, we turned on replication to update the new server with the latest data on the large table, but that broke the partitioning and compression. We cannot query any old data on the new server, only data from when we turned on replication. The clustered index is the old one, and compression is not on, and no partitions to be seen in SSMS, it's using the PRIMARY filegroup  (same as the current live server is using). On the physical disk, I can see the partition files, and they are still massive with GBs of data, so I know the data is there, but it appears the table has lost the link to it. It appears turning on replication replicated the filegroup side of things. Is there a way to point the table back to partitions and save all the work we did getting it ready for switch over? It's a pretty large database. This one table is 11TB on the production server. SQL Server 2017 Enterprise Edition.
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.
59924
Someone was reviewing my DDL code for creating tables and suggested, when they saw I saw using VARCHAR(256) fields for text I expect to be pretty small, like a first name or whatever, that I should always just use VARCHAR(MAX) and linked . I read it but it seemed dated, as it was focusing on 2005, and didn't seem to offer any real justification to allocate potentially up to 2 GB per row on all text fields.   From a performance, storage, etc. standpoint, how should one go about deciding whether to use VARCHAR(MAX) or a smaller more specific type for modern versions of SQL Server? (e.g., 2008, 2012, 2014)
54938
I have a database, that due to some silly code issues has grown hugely. It is now sitting at 24GB, most of it is very unnecessary logging information (system generated debug information).  My server lives on a cloud hosted server. I am now sitting with 2 issues because of the file size:   I pay for storage. I am happy to pay for my core business, but paying for silly data seems....silly My backups are now huge also. I do nightly full backups, and then ship them off to an FTP server. This process is taking longer and longer.   Given my issues, is shrinking so bad? I will rebuild all my indexes. I did a dry run, and brought my database down to 6GB.  This guy (who I have always trusted as my SQL guru), says it's bad....mkay
75791
We've experienced an issue with SQL Server dropping proc cache out of the blue.  I'm talking 4GB down to 0 in 2 minutes. This has occurred approx once per day in the last two weeks.  It does happens at random times and does not coincide with process.  Has anyone seen this before?  So far as I'm aware the cache its only wiped if DBCC FREEPROCCACHE is executed or SQL is restarted.     Version: Microsoft SQL Server 2005 - 9.00.4226.00 (X64)   May 26 2009   14:58:11      Copyright (c) 1988-2005 Microsoft Corporation   Enterprise   Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)   32GB RAM   Any help much appreciated.  We use Idera SQL Diagnostic manager.  Results:  DateTime                Proc Cache Size MB  23/12/2015 19:19:00     4,165.63  23/12/2015 19:25:00     4,165.71  23/12/2015 19:32:00     4,178.93  23/12/2015 19:38:00     4,175.44  23/12/2015 19:44:00     4,176.87  23/12/2015 19:50:00     4,179.80  23/12/2015 19:57:00     207.16  23/12/2015 20:03:00     459.66  23/12/2015 20:09:00     510.48  23/12/2015 20:15:00     1,795.31  23/12/2015 20:21:00     2,830.94  23/12/2015 20:27:00     2,859.87  23/12/2015 20:34:00     2,877.93  23/12/2015 20:40:00     2,891.94  23/12/2015 20:46:00     2,908.82  23/12/2015 20:52:00     2,921.34  23/12/2015 20:58:00     2,975.92
79019
My scenario is I have a thick client side app using a SQL DB. The use of the app is divided into 10 "sites" mostly by Geography but some by Usage. I've written an SP to archive selected data out of the DB and put it elsewhere as the DBs are growing to nearly 500GB in size at this point. Further whilst most servers host one application DB there is one server (and instance) that hosts 3 DBs.  My dilemma is I want to write code that is the same everytime. Ideally I would write something like  declare @dbname varchar(20) set @dbname = 'Archive' + DB_NAME(DB_ID())  INSERT INTO @dbname.dbo.ArchiveTable (ID, DATA) SELECT (ID, DATA) from TheData WHERE Archive = 1   That way I can have the same SP installed on each server, any bug/feature fixes doesn't need me to manually type up 10 different SPs to install.  In the real world I'm also limited by SQL2000 however I'd be interested to hear in ideas using SQL2000 and/or SQL2008R2. The servers are a mixture of both.  As I understand it Synonyms wouldn't help as on the instance with 3 DBs I'd still end up with each DB having it's own copy of the SP with it's own hard coded definition of which named synonym to use.  I also don't feel dynamic SQL statements would be a good fit. There's more to it than the example snippet above and I use table variables to marshal all the work to be done- so that would be out of scope for all the other statements I need to work with.
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).
54050
We have a SQL server hosted on an Azure VM.  One of our applications is very read\write intensive on the database and we're seeing poor disk performance.  Disk queue lengths of 10 or greater.   I've been trying to find a way to increase disk performance.  I found a technet video  that mentions a disk quota and spanning 4 storage accounts increases disk i/o.  Can anyone confirm this disk limiter or recommend this spanning technique?
114188
I am trying to get  disk space report from bunch of servers and insert them into sql table..  below is sample of what i am trying to do,Server names will be populated from a table and passed as a comma seperated list.Even passing one server also has the same result  set @ps = 'powershell.exe -noexit -c "  $computers=Get-WmiObject -Class Win32_Volume '+@servernames+' foreach($computer in $computers)  {  $pscomputername=$computer.pscomputername  $name=$computer.name  $capacity=$computer.capacity  $freespace=$computer.freespace  $Label=$computer.Label   $insertquery="  INSERT INTO [dbo].[temp_disksdata]             (            [servername] ,            [DiskName] ,            [Capacity(GB)] ,            [FreeSpace(GB)],            [label]            )      VALUES             (''$pscomputername'' ,            ''$name'' ,            ''$capacity'',            ''$freespace'',            ''$Label''            )  GO "   Invoke-SQLcmd  -query $insertquery -ServerInstance ''someserver'' -Database dbname  }  "';   Now i try to pass the variable to xp_cmdshell like below  execute xp_cmdshell @ps;   When invoked in SSMS,above returns null,but works in powershell..  Any ideas why ?  Below are a few things i tried  1.Same account(admin) is used in both shell and ssms 2.tried multiple things like import modules 3.XP_CMDshell works,but this returns null only for this query 4.I have tried to add -nowait,but that doesn't help as well    I have been trying to get this done from more than a day,but this doesn't work.. I have to use xp_cmdshell because writing a c# app is not allowed.Bat file doesn't help because server names are passed as a comma seperated list.  I am modifying the code and was told not to rewrite when asked   Please let me know if you need any further info  Repro: Below is entire command from print,if you remove powershell.exe and -c ,below will run in powershell..  powershell.exe  -c "  $computers=Get-WmiObject -Class Win32_Volume 'servername' foreach($computer in $computers)  {  $pscomputername=$computer.pscomputername  $name=$computer.name  $capacity=$computer.capacity  $freespace=$computer.freespace  $Label=$computer.Label   $insertquery="  INSERT INTO [dbo].[temp_disksdata]             (            [servername] ,            [DiskName] ,            [Capacity(GB)] ,            [FreeSpace(GB)],            [label]            )      VALUES             ('$pscomputername' ,            '$name' ,            '$capacity',            '$freespace',            '$Label'            )   "   Invoke-SQLcmd  -query $insertquery -ServerInstance 'servername' -Database dbname   }  "
109898
I am trying to better understand how SQL Server's execution engine behaves in a low-memory state.  Speaking in terms of memory grants, I am wondering if there is some way to force GrantedMemory to be equal to RequiredMemory.  (My guess is that there's an undocumented trace flag which will do that.  Does anyone know what it is?)
60219
I have a server with 150GB of diskspace. Today I uploaded a dataset of 30GB. I cancelled the import due to internet dying, then noticed there was 29GB of space missing in the database (meaning the CSV was uploaded, but not deleted when I broke the operation). When uploading the data once again, it broke again and I lost another ~25GB. Now there isn't enough free space to upload the data.   This is hosted on AWS RDS, Postgres 10.6.   Is there a way to fix this? I read about VACUUM. But will this delete records? I'm hosting at the moment ~70GB of data and don't want to lose any records. What's the best way to go about this?
65509
I would like to say that i have gone through some of the sql blog and MSDN BOL, but still there is some confusion in mind like.   The limitation of SQL Server 2012 Express is 10 GB. It is the limitation of instance based or per database based? No. of connections for SQL Server 2012 Express: the number of connections is limited or unlimited.  If I shall use Server version of OS then what can be the max concurrent user connections. The limitation comes from DB level or OS level. If I shall use Windows Server 2012 R2 with SQL Server 2012 Express SP1 then what will be the max concurrent connections? How many databases can we keep under the hood of single SQL Server instance.   Any help or suggestion would be appreciated
111017
SQL Server instance on my server is using almost full memory available in my Physical Server. Say if i am having 8GB of RAM than SQL Server is using 7.8 GB of RAM from system. I also have read articles and also read many similar questions regarding same on this forum and i understand that memory is reserved and it is using memory.  But i have 2 same servers and 2 SQL Servers, why this is happening on a single SQL Instance not on other. Also when i run DBCC MemoryStatus than it is showing up...  VM Reserved 8282008 VM Committed 537936   so from this we know that SQL reserved whole 8GB memory, but why this VM Committed keeps increasing. What i understand is VM Committed is:      VM Committed: This value shows the overall amount of VAS that SQL Server has committed. VAS that is committed has been associated with physical memory.   So this is the memory SQL Server has committed (from this i understand that physical memory actually SQL Server is using at instance).  So like to know the reason behind this ever increasing VM Committed memory on my server and not on another. Thanks in Advance.
97097
I am looking for the best practices or analysis someone would have followed to identify the unused or inactive databases on my SQL Server.  I went through many articles, which say possibly to go for user connections and activating traces to find the DB usage, but implementing those created more confusion.  Kindly help me in understanding if there is a better and safer way I can take my databases offline once identified.