I have a few SQL Server 2005 databases in simple recovery mode. The disk volume in which my transaction logs are stored had only 1GB free, when I started a stored procedure (massive delete in a single table) the log grows a lot and the disk is running out of space, and the stored procedure seems to work indefinetly. How can I manage this transaction log?
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?
88544
I get error from System Center Operations Manager (SCOM).  How to solve this error?      SQL DB 2012 Engine Stolen Server Memory is too high.   I run query and get this counters:  ╔═══════════════════════════════╦═════════════╗ ║ Stolen Server Memory (MB)     ║ 7354.773437 ║ ║ Lock Memory (MB)              ║ 106.195312  ║ ║ Free Memory (MB)              ║ 64.632812   ║ ║ Connection Memory (MB)        ║ 24.203125   ║ ║ Log Pool Memory (MB)          ║ 14.085937   ║ ║ Optimizer Memory (MB)         ║ 2.351562    ║ ║ Granted Workspace Memory (MB) ║ 1.296875    ║ ║ Cursor memory usage           ║ 0.000000    ║ ║ Cursor memory usage           ║ 0.000000    ║ ║ Cursor memory usage           ║ 0.000000    ║ ╚═══════════════════════════════╩═════════════╝
28160
Is it possible to have 64.5GB log files in Ubuntu? Can I delete those files? My laptop is dual booted and the Ubuntu partition is only 100GB.
63444
I have received the email below that states that an alert has been fired and     a significant part of SQL Server process memory has been paged out   should I raise the Minimum server memory to 1740 too?     Microsoft SQL Server 2005 - 9.00.5000.00 (X64)    Dec 10 2010 10:38:40      Copyright (c) 1988-2005 Microsoft Corporation   Standard Edition   (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)     There are other applications, even monitoring applications running on that server.   You can have a glimpse of those applications by looking at the picture below.    We received an alert at 12:15:37 PM on the wrsleic1 default instance that "a significant part of SQL Server process memory has been paged out."  We've investigated the alert and found that SQL Server previously reported this condition last year on 13/04/2015. SQL Server is currently allocated a maximum memory of 1.7 GB while the server has 4 GB physical memory available to it (and a dynamically sized page file that is currently 5 GB).  At the time of the alert SQL Server had it's maximum memory reserved while not necessarily actively using the cached data. Therefore when the server detected memory pressures from an external process (potentially IIS but we can't determine this retrospectively), SQL Server trimmed its working set to accommodate the requirement and is currently utilising approximately 900 MB.  The OS is responsible for memory management and SQL Server is designed to respond to memory release requests as it did at the time of the alert. Therefore if there was no perceivable impact to performance on the application that uses the hosted databases on this instance then we don't recommend any further action.   Lock Pages in Memory   The lock pages in memory has not been enabled for any sql server account.    I was going to change this, however, I decided to leave this server as it is because:  1) it is running on standard edition sql server 2005 SP2 and       Standard Edition Up until SQL Server 2005 SP3 Cumulative Update   Package 4 and SQL Server 2008 SP1 Cumulative Update Package 2 'Lock   Pages in Memory' was not supported in SQL Server Standard Edition.   2) after enabling it - needs a reboot      Question:         “Weather we require Lock Pages in Memory in SQL   Server 64-Bit”?      Answer:            YES   3) We should (hope so) migrate this server to sql server 2014 soon    and a reminder:
27084
I am quite new to SQL Server. I would like to collect database disk space metrics for the SQL Server instance as a whole.  I have found a code sample that can collect disk space information per database.  IF OBJECT_ID('DISK.dbo.disk_activity') IS NOT NULL     DROP TABLE disk_activity  CREATE TABLE disk_activity (        servername NVARCHAR(100)     ,  database_id INT PRIMARY KEY     , name NVARCHAR(MAX)     , data_used_size DECIMAL(18,2)     , log_used_size DECIMAL(18,2) )  DECLARE @SQL NVARCHAR(MAX)  SELECT @SQL = STUFF((     SELECT '     USE [' + d.name + ']     INSERT INTO DISK.dbo.disk_activity (servername, database_id, name, data_used_size, log_used_size)     SELECT           (SELECT @@SERVERNAME AS "Server Name") AS SERVER_NAME         , DB_ID()         , DB_NAME()         , SUM(CASE WHEN [type] = 0 THEN space_used END)         , SUM(CASE WHEN [type] = 1 THEN space_used END)     FROM (         SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)         FROM sys.database_files s         GROUP BY s.[type]     ) t;'     FROM sys.databases d     WHERE d.[state] = 0     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')  EXEC sys.sp_executesql @SQL   However, I would like to collect the metrics across the entire server, i.e. total disk space allocated for all databases, total disk space used, total disk space free, and percentage of disk space used.  How should I modify the above script or are there other options available?
17156
In Windows 10 Resource Monitor I found that the system process is constantly writing C:\ProgramData\Microsoft\Windows\wfp\wfpdiag.etl at like 30-100KB/s. This equals 1TB write/year which is not healthy for SSD. There are other log write like C:\Windows\System32\LogFiles*** too.   Although logs is needed for diagnostics, it's better to be turned on only when problem has already occured.  Is it possible to disable as much system logs as possible to decrease garbage write amoung to SSD?
59952
We have an instance of SQL Server 2008 installed on a customer server. The customer's IT department is in charge of the various backups of the machine.  The SQL Server log shows there is a full backup every day at 7 PM but we can't find evidence of any plan scheduled in SQL Server.  The technicians we contacted couldn't tell us if there was some sort of automatic backup, all they told is that the entire machine is being backup up.  Using a script I found in  I found out that the physical device name is a GUID and that this means that this is an external backup process:  USE [msdb] GO  SELECT      [bs].[database_name],      [bs].[backup_start_date],      [bs].[backup_finish_date],      [bs].Server_name,     [bs].user_name AS [BackupCreator] ,     [bmf].physical_device_name FROM msdb..backupset bs   INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]  ORDER BY [bs].[backup_start_date] DESC   This is a row from the above query:     db_name 2015-09-16 19:01:23.000   2015-09-16 19:01:28.000SERVER_NAME  NT AUTHORITY\SYSTEM{424F084A-F35D-4A66-8FC7-072268A89A77} 5   Moreover the backup start and finish date spans only for 5 seconds, so I guess it's clear it's not a job of sql server.  This is a line from the log:     2015-09-03 19:02:30.71 Backup   Database backed up.   Database: db_name, creation date(time): 2012/10/12(20:52:11), pages dumped: 3290021,   first LSN: 276028:152755:172, last LSN: 276028:152827:1, number of dump devices: 1,   device information:   (FILE=1, TYPE=VIRTUAL_DEVICE: {'{95380B0A-D50B-408F-B95F-1AB8975BA7F8}5'}).   This is an informational message only. No user action is required.   So, since they can't help us what can I do to track down the process responsible of the backups? We need this because we want to coordinate the backup in an ordered manner, and do the transaction log backups too in order to keep the log at a reasonable size (now we do shrink the log every week, and this is not the good way to go).
9664
I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
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.
33028
I have a SQL Server table that contains over 20 billion rows, with 13 billion rows in the last partition.  The table is currently partitioned 6 ways by a "Time" field:  2011 2012 2013 2014 2015 2016/2017/2018   The 2016/2017/2018 partition needs to be split into their own partitions so there is a 2016, 2017, 2018 partition.    How can this be done and in the process avoiding a massive log file growth? The database is in Simple Recovery and currently each partition is in a separate file group on its own set of disks.   CREATE TABLE [dbo].[Log](        [RefNo] [nchar](7) NOT NULL,        [DevID] [nvarchar](7) NOT NULL,        [Time] [datetime] NOT NULL,        [summary] [float] NULL,        [staging] [float] NULL,        [position] [float] NULL,  CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED  (        [RefNo] ASC,        [DevID] ASC,        [Time] ASC  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 96)  )
87245
Our SQL Server (version 2012) is housed on a cloud server, for which we pay whenever we need additional space. We determined that we could delete some of the data to prevent the need for continually buying more storage.    The delete queries did not appear to open space - the size of the drive and available space doesn't change.    It is my understanding that even though available space has not changed, available space is created by the delete query. So new records imported can populate places where data was deleted. Is this correct?  I understand the Shrink Database is not a good choice.  Should I run -  sp_clean_db_file_free_space based on the following advice?
7631
I'm using SQL Server 2008 R2, and I need to restore 36 .trn logs to a database. I have restored the first 4 using the restore transaction log functionality in SSMS (right-click database > tasks > restore > transaction log).   Is there a way to restore them all in order using this tool or with a script? The .trn files are in a folder on a remote server. I do not not need to restore all the files in the folder, only ones between specific dates.  I'm looking for a way to automate this so I don't have to manually restore each individual log.  I access the files using an address like the one below.  \\remote server IP\SQL_Backup\LMSQL02\TLog\file.trn
104386
A potential customer wants to evaluate our storage system. They run Windows 2008R2 x64 with 4KB NTFS on a virtual test machine they've sent us. They did not seem to know this at hand, so I think it's reasonable to assume that environment was not tweaked. The tests are inserting, indexing, searching, deleting. Tool is not known to me.  Given that the windows NTFS block size is 4KB, and SQL write in 64KB chunks - is it safe to assume that a block size of 64k on the SAN is a good choice?  They run SQL Server 2008, perhaps standard.
13850
When issuing a backup command within a stored procedure that uses a try catch and dynamic sql, the error messages are very general when compared to running the backup command directly.  Try/Catch within SP:         begin try         execute sp_executesql @sql;  -- a backup command     end try     begin catch           print ERROR_MESSAGE();  -- save to log, etc.     end catch   Results in      50000:usp_Backup:117:  BACKUP DATABASE is terminating abnormally.   wheareas issuing the raw command:      backup DATABASE someDb to disk...   Results in better details:         Lookup Error - SQL Server Database Error: A nonrecoverable I/O error     occurred on file "H:\FolderName\Filename.bak:" 112(There is not enough     space on the disk.).   Is there a way to catch these details into variables within the stored procedure (to log, pass back to caller, for retry logic)?  It seems the details are coming through on the message channel but I would like them available within the SP.
83010
We have a fairly big MS SQL 2008R2 database that resides on a SSD drive. The drive itself only has ~110Gb of space, and the database files are the only files on the drive.  The database is in "Simple" recovery mode, and only has two files, .MDF and .LDF.   The disk is now nearly full: the MDF is currently 109Gb in size. However, SSMS tells me there's nearly 18Gb of "Space Available" (in the 'General' properties page), and if I go through the motions of Shrinking the file it also tells me there's 18Gb of space free. SSMS also tells me the database size is around 132Gb, which surprises me - that wouldn't fit on the drive!  From what I've read, shrink is a really bad idea. However, I'm starting to see replication errors (could not allocate space for object). We've previously tried shrinking the database, but within a few hours the file was back to it's original size.  How should we proceed - given there's apparently 18Gb of free space, should SQL automatically use that free space? Or is it as simple as: we really need more disk space?
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?
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?