Here is my query in SQL Server 2005 that I use for truncating log files.  My log file will grow to 7G and when I execute this query in a nightly job it will truncate it to 10M. How do I replicate this query in SQL Server 2008 R2? My current query errors with NO_LOG.  Use Master  BACKUP log DBNAME WITH NO_LOG  --Shrinks the size of the Log (.ldf) file USE DBNAME  DBCC SHRINKFILE (DBNAME_Log)
43086
I'm not a SQL expert, and I'm reminded of the fact every time I need to do something beyond the basics. I have a test database that is not large in size, but the transaction log definitely is. How do I clear out the transaction log?
57312
Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)?  I know that I can either   Use a DELETE without a where clause and then RESEED the identity (or) Remove the FK, truncate the table, and recreate the FK.   I thought that as long as I truncated the child table before the parent, I'd be okay without doing either of the options above, but I'm getting this error:     Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.
17740
I have Lubuntu 16.10 installed on my disk mounted as /.  The problem is that the CUPS log file( /var/log/cups/error.log)  grows all the time until I no have free space left on the disk...   So when I delete that file, the space on disk freed up again  I already tried:    fsck disk usage analyzer every apt-cleaning/autocleaning, and dependencies fixing.    How should I proceed?  du -sxh:  9,0G    .  Model: ATA ST3160815AS (scsi) Disk /dev/sda: 160GB Sector size (logical/physical): 512B/512B Partition Table: msdos Disk Flags:   Number  Start   End     Size    Type      File system     Flags  1      32,3kB  50,3GB  50,3GB  primary   ext4            boot  2      50,3GB  160GB   110GB   extended  5      50,3GB  158GB   108GB   logical   ext4  6      158GB   160GB   2136MB  logical   linux-swap(v1)   parted --list &amp;&amp; sudo df -h:  Model: ATA ST3160815AS (scsi) Disk /dev/sda: 160GB Sector size (logical/physical): 512B/512B Partition Table: msdos Disk Flags:   Number  Start   End     Size    Type     File system  Flags  1      32,3kB  50,3GB  50,3GB  primary  ext4         boot  2      50,3GB  160GB   110GB   primary  ext4   Filesystem      Size  Used Avail Use% Mounted on udev            985M     0  985M   0% /dev tmpfs           201M  6,3M  195M   4% /run /dev/sda1        46G   43G  709M  99% / tmpfs          1003M  188K 1003M   1% /dev/shm tmpfs           5,0M  4,0K  5,0M   1% /run/lock tmpfs          1003M     0 1003M   0% /sys/fs/cgroup tmpfs           201M   36K  201M   1% /run/user/1000 /dev/sda2       101G   60M   96G   1% /media/aram/
56305
So I've gotten into an argument with several people regarding the number of transaction log files a give databases should have.  I've seen several post saying you should only have 1 transaction log file for each database, nothing in any Microsoft whitepapers though. However in many cases I've seen that increasing the number of transaction log files for a given database will actually improve the write performance to the database.  I should note that all these databases are in Full recovery model and are using a large SAN frame for the I/O sub system.  If as many of the posts say that the transaction writes all happen serially to the one file until the end of the file is reached then the writes move on to the subsequent log file, why would increasing the number of log files end up have a very noticeable improvement on the Write speed to the Disk?  In the most recent case we saw IO jump from 700 Kb/s to more than 60 Mb/s by increasing the number of log files from 1 to 8.  Any input would be appreciated.
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?
100087
I understand that there are many variables in this and am not looking for an exact number of min:sec :) I've read many resources that state that a TRUNCATE uses far less resources than a DELETE and hence will perform faster. But there are no order of magnitudes mentioned. As a TRUNCATE is a DDL (Data Definition Language) operation and not a DML (Data Manipulation Language) operation and only the system tables are part of the transaction, I'm presuming that it should not take very long.   Anyone with experience on doing this with a table anywhere near this size that can lend some insight? We talking up to a minute, up to an hour, days, weeks?
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.
12462
I'm a bit of an Exchange admin noob so I don't know if this is normal, but on our Exchange 2007 server under C:\Program Files\Microsoft\Exchange Server\Mailbox\First Storage Group\ there are 30,000 1MB .log files.  I don't know if those should be there or not, but they keep multiplying like little jackrabbits.  I've so far resisted deleting them as I'm sure something would break, but it's come to the point now where our 80GB disk is completely full up and Exchange has stopped receiving messages.  My best guess is that these are transaction logs and would be useful in rebuilding the database should something go awry, but 30GB of transaction logging seems a bit excessive.  Is there a way to limit it or is that even a good idea?  And of course did I completely miss the point?
47595
I created a temp table which resides in the temp table folder. The problem is the name is unacceptably long which make it almost impossible to efficiently work on querying some field from it. Is it any way to have a shorter name like db.#SPR1 instead of the long one as below?   dbo.#SPR1_______________________________________________________________________________________________________________000000003A3E
32429
DBCC ShrinkDatabase() DBCC ShrinkFile()    Do I need to run both DBCC commands in order to shrink the database? What is the difference between these two above?
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?
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?
73313
I have a massive log file which breaks the 2Meg limit and hence truncates.  There is some information I need that I am logging by doing  System.debug   However, I don't get to see because of the logfile truncation.  I change the logging settings so that everything is logged at ERROR except apex code but this still does not bring logging under 2 Meg.  Any ideas?  Thanks.
76694
A few days ago the amount of used space in both the tempdb data and log files started growing rapidly.  After 30 minutes, the tempdb log remained around 99% space used for about 7 hours.  Towards the end of the 7 hour period, I:\Databases\templog.ldf: Operating system error 112(There is not enough space on the disk.) encountered. was logged followed by about 30 instances of The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION' over a 3 second period, and then suddenly the tempdb log file returned to 1% used space.  This usage data is recorded in logs every 3 minutes via a SQL agent job. I also have a SQL agent job that runs sp_WhoIsActive every 1 minute and captures the output.  I don't see anything that stands out in this data that explains what was consuming or holding space in the tempdb.  There are some maintenance jobs running for several minutes at a time, typically running no longer than 5 or 10 minutes each.  Even if one of those jobs or another session used up tempdb log space, I would expect the space to be reclaimable after any transactions complete or at least after the session ends.  It's a 200 GB drive dedicated for tempdb data and log files. Both the data and log files grew during this time until there was no more space on disk. There's a tempdb_2.ndf file I don't recall seeing before (maybe it's always been there...).  tempdb.mdf and tempdb_2.ndf are the same exact size, around 29 GBs each.  templog.ldf is using 137 GBs. Running Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64)  I'm trying to understand what happened and how to prevent it again.  Is it possible for tempdb log space to not be reclaimable for hours even if there are no sessions running for anywhere near that long? What caused tempdb log space used to suddenly free up 3 seconds after the above error messages (not enough space on disk and tempdb transaction log is full)? Is sp_WhoIsActive logging not enough to capture what process was holding onto the tempdb log space, what other additional logging can I do? If the tempdb log space used was actually reclaimable but a glitch in SQL Server prevented it from clearing out, is there a T-SQL command I can run to try and free up available space in the log?  Any thoughts or suggestions are very appreciated.
118514
I have a table without a clustered index in SQL Server 2008R2 Standard with a lot of unused space, as shown in the following image:    How do I reclaim unused space from MyTable?  Said table is declared as follows:  CREATE TABLE [dbo].[MyTable](     [RecordID] [varchar](50) NULL,     [DocumentID] [nvarchar](100) NULL,     [DocumentName] [varchar](100) NULL,     [DocumentOwner] [varchar](50) NULL,     [DocumentTemplate] [varchar](50) NULL,     [DocumentData] [ntext] NULL,     [DocumentDate] [char](10) NULL,     [DocumentTime] [char](10) NULL,     [DocumentSize] [int] NULL,     [DocumentUpdateVersion] [int] NULL,     [SecondaryStorageURI] [varchar](150) NULL,     [PreviousExportUri] [varchar](150) NULL )    What steps should I follow to free unused space?
27167
I just took a backup of a SQL Server database.  The MDF and LDF files together total around 29 GB, but the .bak file was only 23 GB, about 20% smaller.  My first guess when one version of a set of data is smaller than another version containing the same data would be data compression, but compression usually yields a much better compression ratio than 20%, especially for highly-ordered data (such as database tables.) Also, compressed data can't easily be compressed further, but I know that .bak files can be compressed.  So if the data isn't being compressed, and nothing's being discarded, (because the whole point of making a backup is to be able to restore it to an identical state afterwards,) then what's that 20% that's unaccounted for?
79475
I have a fairly busy database server running SQL Server 2008 R2 that has the following setup:   SATA RAID 1 (2 Drives)  - OS / Programs SAS  RAID 10 (4 Drives) - Sql Database Files (data and logs) SAS  RAID 1 (2 Drives)  - TempDB (data and logs)   Assuming I can't add additional drives into this server, have I made the best use of the configuration I have available? Or should I consider another scheme here where logs are isolated from the data files, for example?  Update:  For those that requested further hardware details:   The SATA drives (used for the OS / Program partition) are: WD 7200 RPM 3 Gb/s 3.5 Inch SATA The SAS drives used in the other arrays are: Seagate 15K RPM 6 Gb/s 3.5 inch SAS The RAID controller used is an: LSI 9260-8i SAS/SATA 6 Gb 8 port   Update 2:  Based upon the feedback I've received, it looks like I have the following viable options to choose from - I will award the bounty to someone that can tell me which is likely to be the best in the environment that I've outlined:   Leave everything as is - I probably won't do much better  Move my 2 SAS RAID 1 drives into my existing RAID 10 array to have it composed of 6 disks in total  Move my log files onto the SAS RAID 1 and/or relocate TempDB (data or logs) back to the RAID 10