Hot answers tagged

342

A Shorter Answer: You probably either have a long running transaction running (Index maintenance? Big batch delete or update?) or you are in the "default" (more below on what is meant by default) recovery mode of Full and have not taken a log backup (or aren't taking them frequently enough). If it is a recovery model issue, the simple answer could be ...


121

Since I'm not really satisfied with any of the answers over on Stack Overflow, including the most heavily up-voted suggestion, and because there are a few things I'd like to address that Mike's answer does not, I thought I would provide my input here too. I placed a copy of this answer there as well. Making a log file smaller should really be reserved for ...


32

Some Initial Caveats: It is generally known as a worst practice to ever shrink a production database or data file (Log files are another issue as this question talks about). I advise people to not shrink their databases in blog posts like this where I talk about "right-sizing" and good planning. I'm not alone there (Paul Randal, Brent Ozar, just to provide ...


30

You can also see the content of your log file. To do that, you can use the undocumented fn_dblog, or a transaction log reader, such as ApexSQL Log. It doesn't show index reorganization, but it shows all DML and various DDL events: ALTER, CREATE, DROP, trigger enable/disable, grant/revoke permissions, object rename. Disclaimer: I work for ApexSQL as a ...


21

In AGs writes can only occur on the primary. Shrink operations are writes. Therefore you must do the shrink on the primary. Note that the shrink may not shrink as much as you expect, your test on the restored DB had probably leveraged simple recovery model. Read How to shrink the SQL Server log for more info. Do not shrink to 160MB. Determine why did the ...


15

DBCC SHRINKDB (and its cousin SHRINKFILE) are extremely slow, because there is a lot of single threaded execution going on in that code. A much faster way to shrink a database file is this: Allocate a new filegroup to database Make this filegroup as large as it has to be (use sp_spaceused to determine just how large) Rebuild all indexes to this new ...


15

I will be deleting approx 400 million records from this table. Hopefully, you are doing it in chunks - to avoid bloating transaction log. notice that i am not see any drop in my hard drive space You wont, as you have to explicitly shrink the database file to release space. Just deleting the records, SQL server wont release the space back to the OS. ...


14

Here is the answer to my own question. Run the below query to get information about the log file's reuse wait: SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'DBName' I got the following output: log_reuse_wait_desc ------------------- REPLICATION There were some replication-related objects remaining in the database, even after removing the ...


14

If you want to: ignore all of the warnings from very smart people with a lot of experience in SQL Server; assume that because in this case you didn't experience log growth and in this case fixing the fragmentation was quick and didn't cause any further growth, that will always be the case; and, not confirm that this grow/shrink/grow/shrink cycle has any ...


14

Jeff, Brent here. We put a lot of documentation in that URL - make sure you copy/paste the URL into your web browser to read the full documentation. If you're in simple recovery model, and you've got log files larger than the database, there are lots of possible causes: Someone started a transaction, locked their workstation, and went home for the weekend ...


13

I tunned up a little the query to shrink only the LOG as it is requested: set nocount on SELECT 'USE [' + d.name + N']' + CHAR(13) + CHAR(10) + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) FROM sys.master_files mf JOIN sys.databases d ON mf.database_id = d....


13

if any table has page count more [than] 50 [pages] That is still extremely small. I typically don't take fragmentation into account for any index with less than 1000 pages. and avg_fragmentation_in_percent > 30 then the index is REBUILD That's a good threshold for rebuilds, but I'd also consider reorganizing indexes with fragmentation in the range of 5%...


13

Use Script to Shrink Log files of all databases other than the system DBs. USE MASTER GO SET QUOTED_IDENTIFIER ON GO SET ARITHABORT ON GO DECLARE @DBName NVARCHAR(255),@LogicalFileName NVARCHAR(255),@DBRecoveryDesc Varchar(200) DECLARE DatabaseList CURSOR FOR SELECT name,recovery_model_desc FROM sys.databases WHERE state_desc = '...


13

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 would recommend to use (from BOL : ) DBCC CLEANTABLE ( { database_name | database_id | 0 } , { table_name | table_id | view_name | view_id } ...


11

Steps for shrinking the log are going to be Backup transaction log through either SSMS or T-SQL and then perform a shrink commands for SSMS are under the tasks if you right click the database name BACKUP LOG <Databasename> TO DISK N'<path\database_log.ldf'; GO DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS You will ...


11

I don't recommend to shrink your database files unless you are completely sure that the space will not ever be needed again. Why shrink every night only for it to grow every day? You're going to run into the pain points of shrinking your data files, and you're forcing yourself to run into performance impact when the database files need to grow during the ...


11

In your size estimates, have you taken into account the amount of space taken by indexes? Also if you have text fields that are set as multi-byte (N[VAR]CHAR rather than [VAR]CHAR) and the input files are UTF-8 or plain one-byte-per-character then that will push your storage requirements up by up to a factor of two. Furthermore remember that if you have a ...


10

Read How to Shrink SQL Server log for an explanation how the circular nature of the log may prevent shrink after truncation. Is possible that you log's last LSN point into a VLF that is at the tail of the LDF. Counter intuitively you must advance the log, by generating log writes, to allow it to shrink.


10

In SQL Server, shrinking a database/datafile and compression are not the same. Shrinking a file (which, by the way, isn't really recommended) is the process of removing unused space from data files in your database. When files are created, SQL Server "reserves" space by sizing files out (depending on how the file is created), even if it doesn't actually ...


10

If you are going to continue logging data in this database, the last thing on earth you want to do is shrink the database file (and then perform index maintenance that will require it to grow again). Never mind that these shrink and grow operations will interfere with performance, and that the end result is not going to leave you much better off than when ...


10

Is there a way to look at the complete history of growth and shrink events for the last weeks/months on SQL Server 2014 ? There is easy way to get information about Data/log file autogrowth and Auto shrink events from database reports. The report fetches data from default trace. I am not sure how recent would be your report that depends on when was default ...


10

All things being equal, it should be enough to compact the large object (LOB) column OriginalHTML. You don't specify the clustered index name in the question, so: ALTER INDEX ALL ON dbo.Articles REORGANIZE WITH (LOB_COMPACTION = ON); See ALTER INDEX (Transact-SQL) If you have the clustered index name (not just the clustered column(s)), replace the ALL ...


9

The largest table stores complete XML documents from transactions between the app and APIs of sites like eBay. I'm working with a system at the moment that does exactly the same. While Mat's comment about 230GB not being massive by today's standards is fair, it's still 200GB more than you need for the system to operate. It may be occupying buffer pool, it'...


9

Initial size is not just 3MB, it is taken from the model database (if not specified during the creation of your user database.) So assuming you haven't specified a initial size during the creation of your user db and you haven't altered the model database file sizes after you have created your userdb you can do the following: --Create testDB CREATE DATABASE ...


9

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 Most of the advice given on the Internet is copied and is disseminated, while not reading the whole topic carefully. There is no doubt that shrinking of data files is bad, but if you ask any SQL guru he would always say, "yes I have ...


9

Well, ask yourself: What is the point of shrinking, if you know the reindex (and any other data changes, really) will expand the file again after the shrink? Even if the reindex doesn't bring it back up to 9GB, if the database is going to grow in the future, I see absolutely zero value in reclaiming some of that disk space temporarily. What will you do ...


8

This is the most frequently faced issue for almost all the DBAs where the logs grows and fills out the disk. •What are some reasons the transaction log grows so large? Long Active Transaction High logging transactions like Index rebuild, re-organise, Bulk Insert, Deletes etc. Any HA like Replication, Mirroring configured which holds the log and does not ...


8

You do not have to shrink the log (ie. make the file smaller) but instead you have to truncate the log (ie. allow the file to be reused internally, so it doesn't grow to start with). How to achieve this depends on your database recovery model. Under SIMPLE recovery model the truncation occurs automatically. Under other models (FULL or BULK-LOGGED) the ...


8

Note: this post might be useful too: Issues with TempDB mdf file ever increasing Unless you can figure out what process is using that work table (and can safely kill it), I'd have to agree with what your searches have already yielded: cycle the server and you should be able to shrink tempdb. A different question has dealt with figuring this out for #temp ...


Only top voted, non community-wiki answers of a minimum length are eligible