please can anyone help?  I have a SQL 2008R2 instance with 4 cores, configured with 1 tempdb data file and 1 logfile.   I want to change this setup as per best practices by adding 3 additional euqally sized files. I want to size the files to fill the capacity of the drive. The dedicated drive has a capacity of 50GB but a recent ad hoc user query pushed the size up to 47GB (log is 2GB).   Are the following order of steps correct? Will it require a restart of SQLServer? Will the files be used proportinally by SQL Server correctly after, i.e. not pushing contention to a new file?  1) Shrink the original data file down to 12GB using DBCC SHRINKFILE.  * Size taken up by the drive should now be 14GB (2GB log + 12GB data file 1)  2) Add 3 new additional data files at 12GB each.  * The drive should now be full at 50GB capacity  This will be done during a planned outage on a production system, but wanted to know if there will be any potential issues with the approach?  Thanks in advance.
121599
Does adding Tempdb files require a restart? We would like to Add files on the same drive, Not Delete and Not modify. We tested in Dev and QA environment, restart was not required. However for production I would like to be sure everything works properly. Some are stating conditions, not always necessary, sometimes necessary, I am reading resources below:  ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'G:\tempdb2.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 ) GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'G:\tempdb3.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0    Do You Need an Outage to Add tempdb files? Brent Ozar  "Technically, not always. But practically speaking, yes. If you have to shrink files, that’s very difficult while SQL Server is in use, and we’ve seen cases where anti-virus/file control tools didn’t act up until SQL Server was restarted after the files were added."   "Once the additional files have been created, it’s not always necessary to perform a service restart.  However, in many production environments, simply modifying the filesize of the existing TempDB datafile (tempdev) is not possible without restarting services first. "  The following resources are saying no restart required:
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.
12524
I am working with a 500 GB SQL Server database. I need to reduce the size of the database data file after removing a large amount (50%) of the data.  I tried re indexing, it's good the database has de-fragmented. But the DB size is not reduced.  I am not prepared to use shrink database. I have already tried compression and DBCC UpdateUsage but no space gain. Please advise me any other ways to reduce the database size.  Our client will not allow us to take the DB Offline. It might take 5 hr to shrink the database and then re indexing will take 3 hrs, so in total we would need 8 hr down time. I am looking for a solution without downtime.
102125
I have an HP Proliant DL360 G6 server with 8 HD bays.  Currently I have the first two bays working with ESXi and I'm just running RAID 0 on a single drive.  This is not a production machine.  It has a P410i Controller.  I'm trying to add a third drive to machine and when I go into the controller menu at boot time and select "Create Logical Drive" it says I've reached the max of 2 logical drives.  I was under the impression I could many more logical drives than just 2?  I'm not a server admin by any means, so this is definitely not my strength, but any help would be greatly appreciated.  Thank you.
77130
I created an image of a Windows Server 2008 system using the free VMware Player and set the maximum hard drive size to 100GB. I then gave those image files - 30GB at the time - to a server hosting company. A few weeks later I asked them for a copy of them back; the image should not have changed much in size, but the files I got back were the full 100GB size.  I am not a VMware expert at all, so I have two questions:   How/why did they expand the whole dynamic disk? Is there any way I can somehow shrink this image down to the size that actually contains data?   I tried the VMware converter, and it can't seem to read my image (vmdk) file; it just outputs a generic error.
60646
We're planning to install a SQL Server on this physical architecture:  A hyper-v cluster based on windows server 2019 A cluster shared volume where all VMs live A full-flash SAN that contains the CSV  I've always read that is a common best practice to have multiple disks for data files, log files, temp db files ect... But that best practice is referred to a bare-bone installation. So, what I'm asking is: what are the actual benefits of doing so on the above described architecture (if any)?
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?
90784
I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 GB.  The DB files auto growth is set to 5MB and I would like to try a different approach, so I am looking for suggestions and or comments.  There is a tuning task that runs every-week on Sunday night at 1:30 AM. The task will:    Check Database Integrity Shrink the Log File – (This is ok because logging mode is Simple) Shrink Database Reorganize Index Rebuild Index Update Statistics Clean Up History   I would like to add two more steps to the weekly tuning plan:   Grow the database file by 500 MB if the used space reaches a certain threshold or total size.   Grow the log file by 250 MB (after the shrink) if the used space reaches a certain threshold of total size.     By placing the growth burden in offline hours, I hope to gain performance by reducing the number of auto-growth events during heavy loads.  I have two questions relating to auto growing files.   The best place to put the file grow steps would be prior to the current steps or after?  If I use the ALTER DATABASE|MODIFY FILE to grow the file then how can I determine if SpaceUsedInFile &gt;= (TotalFileSpace-@AllowanceThreshold)?
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.
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?
61601
This is a followup question to  about why I couldn't shrink the log file on the principal database.  To make a long story short, I setup database mirroring but forgot to make sure the job that backed up the transaction log was running again, and the transaction log grew to almost 60GB.   Since mirroring was set up, this size increase was duplicated on the mirrored server, and eventually took up all the disk space and made the mirror database unusable.  Per  about transaction log maintenance on a mirror database, you can't back up the log on the mirror, but when asked specifically in  about how to shrink an overgrown log file on a mirror database, a  was left that      One way to do this would be to failover to the mirrored database and   do the shrink there. Thoroughly test this out in a non-production   environment to ensure it has the behavior you want/expect.   This seems to suggest that there might be other ways to shrink the log file on a mirror, and this method might not necessarily be safe to do on a production server.  Is there a way to safely shrink the transaction log file on a database mirror?
109201
I'm looking to replace my hard-drives with bigger-ones and I would like to know what are the best practices in doing this. I have a HP ProLiant with its 256MB version of RAID controller:  03:00.0 RAID bus controller: Hewlett-Packard Company Smart Array G6 controllers (rev 01)   I also have a pair of 10.000 rpm 146GB SAS drives in RAID 1. The OS is Ubuntu 10.04, 64-bit. I'm not using LVM.  zduric@pbksrv002:~$ df -h Filesystem            Size  Used Avail Use% Mounted on /dev/cciss/c0d0p1     130G  119G  9.7G  93% / none                  3.0G  192K  3.0G   1% /dev none                  3.0G     0  3.0G   0% /dev/shm none                  3.0G   64K  3.0G   1% /var/run none                  3.0G     0  3.0G   0% /var/lock none                  3.0G     0  3.0G   0% /lib/init/rw zduric@pbksrv002:~$ mount /dev/cciss/c0d0p1 on / type ext4 (rw,errors=remount-ro)   We experienced a huge growth in content stored there and now I need to replace this array with another pair, 300GB disks. Ubuntu 10.04 is up there with a tomcat and a document management application (which is filling up most of the drive).  I'm looking for the best way to do it.  I was thinking that I could do one of the following:  Option I: replacing a drive at a time. Basically, I'd do something like this:   Replace drive 1 with the new drive. Rebuild the array to put the data on the new drive. Replace drive 2 with the new drive. Rebuild the array.   I don't like the two-times rebuilding part about it. I also don't know, if that would work at all and even worse, how much downtime is that. (around 115GB data, OS included.)  Option II: Create a new array. The idea here is that I would add the new disks and create a second array. I could then migrate my tomcat application to the new array - this would mean a simple cp operation.  I like this option much better, because it would also leave me the first array to use solely for the OS and I could do OS upgrades more easily in the future.  Is the option II doable at all? Where could I find some good resources on doing this whole procedure?  Are there other, better, smarter options I should try?  Thanks in advance.
102267
I moved to a new company recently wherein they have 2 databases in a SQL server of size 20-25 GB. I'm unable to shrink the log file of the databases. Transaction log backup of the databases are set to run every 30 mins from 6 AM-7PM. They are of size 10 and 2 Gb respectively. I tried to shrink it around 8-9 PM last night but I was not able to do.  The log_reuse_wait column stated it's waiting on a log_backup. I tried taking couple of log backups which didn't help. I ran DBCC OPENTRAN but there are no open transactions.  Also, this morning even after the Tlog backups started at 6:00 AM till now(10:30) it still shows LOG_BACKUP in log_reuse_wait column. And no OPEN TRAN as well.  When I researched on this I found this article from Paul Randal:   It states that if the DB is small and all the content of tlog file is in same VLF it couldn't truncate. I have some 300-400 VLFs in the Databases. So that doesn't make sense here as well.   One thing I noticed that every morning at 6 AM the tlog is a little bigger than the other ones. So I'm thinking to check with the lead DBA to run tlog round the clock. Other than that, I would like to see why is this happening and Am i missing anything.  I tried to shrink with the below command:  Use DB1 go DBCC SHRINKFILE(2,10)  GO   Note: It's SQL 2012 instance running 0n 2012 compatibility
103452
In SQL server every database has a property Initial Size (MB) which can be seen in properties of database in SSMS. By default it will be 3 MB for mdf and 1 MB for ldf file.  So now if create a new database then it will be set to default size(i.e 3 MB for mdf and 1 mb for ldf file). But after creating the database  I change the initial size to some other value say For mdf 10 MB and ldf 5 MB.(In real-time some database administrator may want to change initial after creating database)  But now if shrink the database it will shrink beyond the initial size I have set after creation(consider that no data is there in database otherwise it will shrink to its actual content size). It will shrink till the initial size it had during creation of database(i.e 3mb for mdf and 1 mb for ldf). I was expecting to shrink till the initial size which I have set(i.e 10 mb for mdf and 5mb for ldf). Can it be possible to do? If possible then how?  In some article i saw like dbcc SHRINKFILE can be used to shrink beyond initial size and dbcc SHRINKDATABASE cannot shrink beyond initial size. But I want to shrink only till the initial size to which I have set.  Note: I know that shrink is bad and should not be done.But I want to know how it can be done? If sql server considers only the initial size set during creation of database then what is the use of setting the initial size after creating database?  EDIT:  From microsoft shrink related article: Using DBCC SHRINKDATABASE you can not shrink database below its initial size.  My question is Can I change the initial size of existing database itself?  EDIT:  I have about 5 sql server instance. All the database where created with default size of 3 mb for data and 1 mb for log(about 3 years back). Now all the databases are in the range of 100-1000 mb. I wont shrink the database normally as it is not good practice. But some database grow more than 10 GB(mdf file itself as i insert so much data in it). So when database is large I will shift unimportant and old contents from this database to another server as it is not required in that server for me. So that database size reduces from 10 GB to 1 GB. Now the remaining 9 Gb left unused. Now I  want to specify database size to be reduced to 2 GB so that i will get 1 GB free space(Also 2 Gb is the ideal size for this database), Which can be used to avoid autogrowth till next 1 Gb of data is filled. Also i have set 100 MB auto-growth for this kind of large database which has more writes. So instead of specifying size as 2 GB in shrinkfile command,if it was possible to change initial size to 2 GB (which shrink considers) then it would have been better. During this database creation there was no plan about size ,autogroth etc. As it was created about 3 years back. Now i am planning to set autogrowth etc. I cant again create new database with new initial size and shift data. So I was asking whether I can change the Initial size for existing database?
100035
When configuring new SQL Servers on VMs, it is a good idea to have the Database, TempDB, logs, and backups on separate logical drives, even if the underlying storage is the same? I know that this is a good practice on physical servers; the separation actually helps us to reduce the pains caused by concurrent I/O.   I am trying to understand if having 3 drives instead of 5 would make sense. Can someone please provide their views/suggestions for this idea?
58507
how I can shrink tempdb log file when reaching a specific size?? I'm scheduling job to shrink tempdb but still I face a problem of disk space due to tempdb is full!
105649
Two days ago I had a full C: drive, after which I deleted 8 GB of data. The next day the hard drive was full again, so I continued with deleting another 5 GB, and once again the next day the disk was full.   After some searching for what caused the disk space to be filled so quickly, I used the windirstat tool to find which files took up the most space. I found out that the CBS.log file, which is located in c:\windows\logs\cbs\, is 20 GB large.  I am using Windows 8.   Should this file be this large, and if not, how can I reduce the size? What is the purpose of this file? Can I delete it?